1 Reply Latest reply: Sep 29, 2013 10:57 PM by Madhu.149 RSS

    Reg: Query with UNION

    Madhu.149

      Dear All,

       

      I have two tables

      TABLE A (A_DATE, COLA1, COLA2)

      TABLE B (B_DATE)

       

      A_DATE = B_DATE

       

      Sample tables:

       

      TABLE A (11 rows selected)

      A_DATECOLA1COLA2
      01-03-201354VAL76
      01-04-201311VAL78
      01-04-201311VAL22
      01-04-201374VAL22
      02-04-201315VAL45
      02-04-201311VAL22
      03-04-201344VAL22
      03-04-201371VAL73
      04-04-201399VAL13
      05-04-201311VAL22
      05-04-201311VAL23

       

      TABLE B (01 April to 30 April)

      B_DATE
      01-04-2013
      02-04-2013
      03-04-2013
      04-04-2013

       

      (condition 1: Month: April

      AND

      condition 2: CALA1 --> 11

      AND

      condition 3: COLA2 --> VAL22)

       

      I have to display all the records for month April from TABLE A,

      If records are not present in TABLE A for all 30 days, I need to display null, by fetching DATE information from TABLE B

       

      Expected output (Sample)

      DateCOLA1COLA2
      01-04-201311VAL22
      02-04-201311VAL22
      03-04-2013
      04-04-2013
      05-04-201311VAL22

       

       

      Currently I have the below query

       

      select A_DATE, COLA1, COLA2 from TABLEA

      where COLA1 = 11 and COLA2 in ('VAL22') and A_DATE between '2013-04-01 00:00:00.0' AND '2013-04-30 00:00:00.0'

      union all

      select B_DATE, null, null from TABLEB where date between '2013-04-01 00:00:00.0' AND '2013-04-30 00:00:00.0'

       

      But I am getting duplicate records for the values present in TABLE A

       

      Current output (sample)

      DateCOLA1COLA2
      01-04-201311VAL22
      01-04-2013
      02-04-201311VAL22
      02-04-2013
      03-04-2013
      04-04-2013
      05-04-201311VAL22
      05-04-2013

       

       

      I tried with both UNION and UNION ALL, but getting same result.

        • 1. Re: Reg: Query with UNION
          Ramin Hashimzadeh

          Hi,

          try this :

           

          select b.B_DATE, a,COLA1, a,COLA2

            from TABLEB b

           

            left join TABLEA a

            on a.A_DATE = b.B_DATE 

            and COLA1 = 11

            and COLA2 in ('VAL22')

           

          where B_DATE between to_date('2013-04-01','yyyy-mm-dd') AND to_date('2013-04-30','yyyy-mm-dd')

           

           

          ----

          Ramin Hashimzade