1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

creating sub query in oracle

Discussion in 'SQL PL/SQL' started by blazesback, Dec 23, 2013.

  1. blazesback

    blazesback Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    Hello,
    Am using a specilized application called connected commerce for getting deals and coupons for the merchants. I have a query issue that i mentioned below. Please help for me.
    Code (Text):
    SELECT Count(CMN1) As COUNT, 'V2' as val2
    FROM TABLE1
    WHERE CMN3 > '20-AUG-13'
      AND CMN3 < '20-DEC-13'
    UNION ALL
    SELECT Count(CMN2) as COUNT, 'V2' as val2
    FROM TABLE1
    WHERE CMN3 > '20-AUG-13'
      AND CMN3 < '20-DEC-13'
     
  2. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Hi,

    try this.

    SELECT Count(CMN1) As COUNT, 'V2' as val2
    FROM TABLE1
    WHERE CMN3 between to_date('20-AUG-13') AND to_date('20-DEC-13')
    UNION ALL
    SELECT Count(CMN2) as COUNT, 'V2' as val2
    FROM TABLE1
    WHERE CMN3 between to_date('20-AUG-13') AND to_date('20-DEC-13')

    It may help you.
     
    blazesback likes this.
  3. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    BETWEEN includes data for the bracketing dates and his original query does not; using BETWEEN may not return the same result set. Why are the counts on different rows? They both are assigned a literal value of 'V2' so it appears to make no sense to separate them. Also, using date strings without utilizing the TO_DATE function properly can result in no data returned at all:


    Code (SQL):

    SQL> ALTER SESSION SET nls_date_format = 'Mon DD, RRRR';
    SESSION altered.
    SQL>
    SQL> CREATE TABLE table1(
      2  cmn1       varchar2(20),
      3  cmn2       varchar2(20),
      4  cmn3       DATE);


    TABLE created.


    SQL>
    SQL> BEGIN
      2          FOR i IN 1..10000 loop
      3                  INSERT INTO table1(cmn1,cmn2,cmn3)
      4                  VALUES('Common1_'||i, 'Common2_'||i, add_months(sysdate, -1*(MOD(i,7))));
      5          END loop;
      6          commit;
      7  END;
      8  /


    PL/SQL PROCEDURE successfully completed.


    SQL>
    SQL> SELECT COUNT(CMN1) AS COUNT, 'V2' AS val2
      2  FROM TABLE1
      3  WHERE CMN3 > '20-AUG-13'
      4    AND CMN3 < '20-DEC-13'
      5  UNION ALL
      6  SELECT COUNT(CMN2) AS COUNT, 'V2' AS val2
      7  FROM TABLE1
      8  WHERE CMN3 > '20-AUG-13'
      9    AND CMN3 < '20-DEC-13'
     10  /
    WHERE CMN3 > '20-AUG-13'
                 *
    ERROR at line 3:
    ORA-01843: NOT a valid MONTH

    SQL>
    SQL> SELECT COUNT(CMN1) AS COUNT, 'V2' AS val2
      2  FROM TABLE1
      3  WHERE CMN3 BETWEEN to_date('20-AUG-13') AND to_date('20-DEC-13')
      4  UNION ALL
      5  SELECT COUNT(CMN2) AS COUNT, 'V2' AS val2
      6  FROM TABLE1
      7  WHERE CMN3 BETWEEN to_date('20-AUG-13') AND to_date('20-DEC-13')
      8  /
    WHERE CMN3 BETWEEN to_date('20-AUG-13') AND to_date('20-DEC-13')
                               *
    ERROR at line 3:
    ORA-01843: NOT a valid MONTH

    SQL>
    SQL> SELECT COUNT(CMN1) AS COUNT, 'V2' AS val2
      2  FROM TABLE1
      3  WHERE CMN3 > to_date('20-AUG-13' , 'DD-Mon-RR')
      4    AND CMN3 < to_date('20-DEC-13', 'DD-Mon-RR')
      5  UNION ALL
      6  SELECT COUNT(CMN2) AS COUNT, 'V2' AS val2
      7  FROM TABLE1
      8  WHERE CMN3 > to_date('20-AUG-13' , 'DD-Mon-RR')
      9    AND CMN3 < to_date('20-DEC-13', 'DD-Mon-RR')
     10  /


         COUNT VA
    ---------- --
          5716 V2
          5716 V2


    SQL>
    SQL> SELECT COUNT(CMN1) AS COUNT, 'V2' AS val2
      2  FROM TABLE1
      3  WHERE CMN3 BETWEEN to_date('20-AUG-13', 'DD-Mon-RR') AND to_date('20-DEC-13', 'DD-Mon-RR')
      4  UNION ALL
      5  SELECT COUNT(CMN2) AS COUNT, 'V2' AS val2
      6  FROM TABLE1
      7  WHERE CMN3 BETWEEN to_date('20-AUG-13', 'DD-Mon-RR') AND to_date('20-DEC-13', 'DD-Mon-RR')
      8  /


         COUNT VA
    ---------- --
          5716 V2
          5716 V2


    SQL>
    SQL> SELECT COUNT(cmn1) AS COUNT,  COUNT(cmn2) AS count2, 'V2' AS val2
      2  FROM table1
      3  WHERE cmn3 > to_date('20-Aug-13', 'DD-Mon-RR')
      4  AND cmn3 < to_date('20-Dec-13', 'DD-Mon-RR')
      5  /


         COUNT     COUNT2 VA
    ---------- ---------- --
          5716       5716 V2


    SQL>

    As illustrated using date strings without the TO_DATE function, or using TO_DATE without supplying a valid format string, can result in no data being returned and the queries returning errors since the default date string format may not be the default setting from Oracle.
     
    blazesback likes this.
  4. blazesback

    blazesback Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    Thanks David and Jagadekara Reddy. Am going to try your thoughts. Thanks for your time and help.