This discussion is archived
6 Replies Latest reply: May 7, 2012 7:05 AM by Paulie RSS

SQL help needed

sarvan Newbie
Currently Being Moderated
Hi experts,

I have more than 30 tables in which PLANT_CD is a column. "IN0001" is a valid plant code which appears as "IN001 " in few tables. In some tables both "IN0001" and "IN001 " does exists. I want to query each of these tables individually such that

1) If PLANT_CD = IN0001, it has to bring that record
2) If IN0001 and IN001 both exists, It has to bring any one
3) If "IN001 " alone exists it has to bing that record

It appears to be easy, but I'm bit confused in writing the WHERE clause

Regards,
Sarvan
  • 1. Re: SQL help needed
    ArunKumarGupta Pro
    Currently Being Moderated
    Can you tell "AND" or "OR" conditions for your three statements ? Statements are bit confusing..


    Regards
    Arun
  • 2. Re: SQL help needed
    Kunwar Newbie
    Currently Being Moderated
    hi sarvan,
    this might help you:
    select *from table_name where regexp_like(PLANT_CD,'^IN00(01|1 )');
  • 3. Re: SQL help needed
    indra budiantho Expert
    Currently Being Moderated
    /* Formatted on 2012/05/07 17:24 (Formatter Plus v4.8.8) */
    WITH t AS
         (SELECT 1 ID, 'IN0001' plant_cd
            FROM DUAL
          UNION ALL
          SELECT 1 ID, 'IN001' plant_cd
            FROM DUAL
          UNION ALL
          SELECT 2 ID, 'IN001' plant_cd
            FROM DUAL
          UNION ALL
          SELECT 3 ID, 'IN0001' plant_cd
            FROM DUAL)
    SELECT id, PLANT_CD
      FROM t
      WHERE plant_cd  IN('IN001','IN0001')
    MINUS
    SELECT id, PLANT_CD
      FROM t
     WHERE 
     plant_cd  IN('IN001','IN0001') AND
     ID =(SELECT   ID
                FROM t
                WHERE plant_cd  IN('IN001','IN0001')
            GROUP BY ID
              HAVING COUNT (ID) > 1)
           AND ROWNUM = 1;
    ID PLANT_
    ---------- ------
    1 IN001
    2 IN001
    3 IN0001

    Edited by: Cerca Trova on May 7, 2012 3:30 AM
  • 4. Re: SQL help needed
    Om Newbie
    Currently Being Moderated
    Hello Sarvan,

    Try below query.

    WITH t AS
    (SELECT 1 ID, 'IN0001' plant_cd
    FROM DUAL
    UNION ALL
    SELECT 1 ID, 'IN001' plant_cd
    FROM DUAL
    UNION ALL
    SELECT 2 ID, 'IN001' plant_cd
    FROM DUAL
    UNION ALL
    SELECT 3 ID, 'IN001' plant_cd
    FROM DUAL
    UNION ALL
    SELECT 4 ID, 'IN0001' plant_cd
    FROM DUAL
    UNION ALL
    SELECT 5 ID, 'IN0001' plant_cd
    FROM DUAL
    UNION ALL
    SELECT 5 ID, 'IN001' plant_cd
    FROM DUAL
    )
    select * from T a where
    1=1
    and plant_cd IN ('IN0001')
    OR ( plant_cd = 'IN001' AND NOT EXISTS (SELECT 1 FROM T b WHERE plant_cd='IN0001' and a.id=b.id))
  • 5. Re: SQL help needed
    Paulie Pro
    Currently Being Moderated
    sarvan wrote:
    Hi experts,

    I have more than 30 tables in which PLANT_CD is a column. "IN0001" is a valid plant code
    which appears as "IN001 " in few tables. In some tables both "IN0001" and "IN001 " does exists.
    I want to query each of these tables individually such that
    Take a look at the LIKE condition?


    Paul...

    Sarvan
  • 6. Re: SQL help needed
    Paulie Pro
    Currently Being Moderated
    >
    I have more than 30 tables in which PLANT_CD is a column. "IN0001" is a valid plant
    code which appears as "IN001 " in few tables. In some tables both "IN0001" and "IN001 " does
    exists. I want to query each of these tables individually such that
    Don't forget the wild cards: * and _ (asterisk and underscore) which you can change using the ESCAPE keyword.


    Paul...
    Sarvan

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points