6 Replies Latest reply: May 7, 2012 9:05 AM by Paulie RSS

    SQL help needed

    sarvan
      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
          Arun Kumar Gupta
          Can you tell "AND" or "OR" conditions for your three statements ? Statements are bit confusing..


          Regards
          Arun
          • 2. Re: SQL help needed
            Kunwar
            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
              /* 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
                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
                  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
                    >
                    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