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!

Retrieve an empty value

Discussion in 'SQL PL/SQL' started by bimalsd, Nov 10, 2014.

  1. bimalsd

    bimalsd Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    hi,
    I have the below query:

    SELECT EXTRACT (YEAR FROM app.application_date) AS recd_year,
    EXTRACT (MONTH FROM app.application_date) AS recd_month,
    APP.PERMISSION_TYPE, app.application_type, COUNT (DISTINCT app.transaction_number) AS total
    FROM lnr_application app
    WHERE app.permission_type IN ('HCDG')
    AND app.application_date >= TO_DATE ('&sdate', 'dd/mm/yyyy')
    AND app.application_date <= TO_DATE ('&edate', 'dd/mm/yyyy')
    GROUP BY EXTRACT (YEAR FROM app.application_date),
    EXTRACT (MONTH FROM app.application_date),
    APP.PERMISSION_TYPE, app.application_type
    ORDER BY recd_year, recd_month, APP.PERMISSION_TYPE

    and retrieves the below results:
    RECD_YEAR RECD_MONTH PERMISSION_TYPE APPLICATION_TYPE TOTAL
    2014 7 HCDG N 1
    2014 7 HCDG W 1
    2014 9 HCDG N 2
    2014 9 HCDG W 1
    2014 10 HCDG N 3
    2014 10 HCDG W 1

    I am looking to automate the above where all data gets auto-populated into an excel sheet. I have figured out how to do that. However, with the automation, if the query retrieves the above results where there is no data for the month of August (8), all data gets shifted up a month. So is there a way to include some empty values for the month of August like below for my automation to work accurately.

    RECD_YEAR RECD_MONTH PERMISSION_TYPE APPLICATION_TYPE TOTAL
    2014 7 HCDG N 1
    2014 7 HCDG W 1
    2014 8 HCDG N 0
    2014 8 HCDG W 0
    2014 9 HCDG N 2
    2014 9 HCDG W 1
    2014 10 HCDG N 3
    2014 10 HCDG W 1

    Any help will be appreciate.

    Thanks
     
  2. heepth

    heepth Active Member

    Messages:
    19
    Likes Received:
    0
    Trophy Points:
    100
    Location:
    chennai
    Hi Bimalsd,
    I have used "nvl" function in your query.so wherever its getting value it will get changed to '0'.

    Code ( (Unknown Language)):

     
     
  3. bimalsd

    bimalsd Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    Hi Heepth,
    Thanks for the response and I tried to run the modified query but to no avail. I still dont see the occurrences of the month of August.

    Note: the entity, lnr_application does not have any records for the month of August

    Any suggestions?
     
  4. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Hi Bimal,

    NVL is not enough; try the following (I've used in-line views as I don't have your table structures or script to create them):

    Code (SQL):
    WITH lnr_application AS (
    SELECT DATE '2014-07-01' application_date, 'HCDG' permission_type, 'N' application_type, 101 transaction_number FROM dual UNION ALL
    SELECT DATE '2014-07-02' , 'HCDG' , 'W' , 102  FROM dual UNION ALL
    SELECT DATE '2014-09-01' , 'HCDG' , 'N' , 200  FROM dual UNION ALL
    SELECT DATE '2014-09-02' , 'HCDG' , 'N' , 201  FROM dual UNION ALL
    SELECT DATE '2014-09-01' , 'HCDG' , 'W' , 210  FROM dual UNION ALL
    SELECT DATE '2014-10-01' , 'HCDG' , 'N' , 301  FROM dual UNION ALL
    SELECT DATE '2014-10-02' , 'HCDG' , 'N' , 302  FROM dual UNION ALL
    SELECT DATE '2014-10-03' , 'HCDG' , 'N' , 303  FROM dual UNION ALL
    SELECT DATE '2014-10-01' , 'HCDG' , 'W' , 310  FROM dual
    ),
    period AS (
    SELECT EXTRACT (YEAR FROM ADD_MONTHS(TO_DATE ('&&sdate', 'dd/mm/yyyy'),level-1)) yr,  
           EXTRACT (MONTH FROM ADD_MONTHS(TO_DATE ('&&sdate', 'dd/mm/yyyy'),level-1)) mn FROM dual
    CONNECT BY level <= months_between(TO_DATE ('&&edate', 'dd/mm/yyyy'), TO_DATE ('&&sdate', 'dd/mm/yyyy') )+1
    ),
    typ AS (
    SELECT 'HCDG' ptype, 'N' atype FROM dual UNION ALL
    SELECT 'HCDG' ptype, 'W' atype FROM dual
    )
    --
    SELECT p.yr AS recd_year,
      p.mn  AS recd_month,
      NVL(APP.PERMISSION_TYPE, 'HCDG') ,
      NVL(app.application_type, p.atype),
      COUNT (DISTINCT app.transaction_number) AS total
    FROM lnr_application app, (SELECT yr, mn, t.ptype, t.atype FROM period, typ t) p
    WHERE app.permission_type(+) IN ('HCDG')
    AND app.permission_type(+) = p.ptype
    AND app.application_type(+) = p.atype
    AND app.application_date(+)  >= TO_DATE ('&&sdate', 'dd/mm/yyyy')
    AND app.application_date(+)  <= TO_DATE ('&&edate', 'dd/mm/yyyy')
    AND to_char(app.application_date(+),'YYYY') = p.yr
    AND to_char(app.application_date(+), 'MM')  = p.mn
    GROUP BY p.yr,
      p.mn,
      APP.PERMISSION_TYPE,
       NVL(app.application_type, p.atype)
    ORDER BY recd_year,
      recd_month,
      APP.PERMISSION_TYPE
    ;
     
     
  5. bimalsd

    bimalsd Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    Hi Rajen,
    Thanks for your response. I blindly executed the query that you provided and seems to have worked for the month of August 2014. So we are on the right path. However, I was and am still trying to make sense of the query and to be honest, I have not come across the WITH .. AS clause before. I have been doing some googl'ing on it and trying to understand the concept. I thought I'd bring the below issue to your attention as by the time I get myself acquainted with your query, it may take some time.

    Issue is that as I executed your query, the results for month of July, Sept, and Oct match my original query. But when I executed my original query, I have got some results for the month of November now, and your query returns value "0" for both instances (N, W) for the month of Nov.

    Result from my original query:
    RECD_YEAR RECD_MONTH PERMISSION_TYPE APPLICATION_TYPE TOTAL
    2014 7 HCDG N 1
    2014 7 HCDG W 1
    2014 9 HCDG N 2
    2014 9 HCDG W 1
    2014 10 HCDG N 3
    2014 10 HCDG W 1
    2014 11 HCDG N 1
    2014 11 HCDG W 1

    Result from your query:
    RECD_YEAR RECD_MONTH NVL(APP.PERMISSION_TYPE,'HCDG') NVL(APP.APPLICATION_TYPE,P.ATYPE) TOTAL
    2014 7 HCDG N 1
    2014 7 HCDG W 1
    2014 8 HCDG N 0
    2014 8 HCDG W 0
    2014 9 HCDG N 2
    2014 9 HCDG W 1
    2014 10 HCDG N 3
    2014 10 HCDG W 1
    2014 11 HCDG N 0
    2014 11 HCDG W 0
    2014 12 HCDG N 0
    2014 12 HCDG W 0
    2015 1 HCDG N 0
    2015 1 HCDG W 0
    2015 2 HCDG N 0
    2015 2 HCDG W 0
    2015 3 HCDG N 0
    2015 3 HCDG W 0
    2015 4 HCDG N 0
    2015 4 HCDG W 0
    2015 5 HCDG N 0
    2015 5 HCDG W 0
    2015 6 HCDG N 0
    2015 6 HCDG W 0

    As you can see for the month of November, the results are quite different, so does this mean that we need to update the WITH .. AS clause?

    Look forward to your reply.
    Thanks
     
  6. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Hi Bimal,

    The WITH clause, or subquery factoring clause, was added into the Oracle SQL syntax in Oracle 9.2. The WITH clause may be processed as an inline view or resolved as a temporary table. The advantage of the latter is that repeated references to the subquery may be more efficient as the data is easily retrieved from the temporary table, rather than being requeried by each reference.

    It can be used to reduce repetition and simplify complex SQL statements and in my case, as I don't have your table in my database, I cannot build a query and test on it. With the WITH clause, I building a "temporary table" with the data I defined in the statement.

    I mentioned that in my post:
    My data starts from July till October and was based on the sample data you provided in your initial post and that's why it returns zero for November. You have to replace this part with your data or select from your table. Therefore, please do the following:

    Replace:

    with:

    Code (SQL):
    WITH lnr_app AS (
    SELECT * FROM lnr_application
    )
    And change all reference of lnr_application in rest of statement with lnr_app - in line:

    change to:

    Code (SQL):
    FROM lnr_app app, (SELECT yr, mn, t.ptype, t.atype FROM period, typ t) p

    Alternative:

    Code (SQL):
    WITH period AS (
    SELECT EXTRACT (YEAR FROM ADD_MONTHS(TO_DATE ('&&sdate', 'dd/mm/yyyy'),level-1)) yr,  
           EXTRACT (MONTH FROM ADD_MONTHS(TO_DATE ('&&sdate', 'dd/mm/yyyy'),level-1)) mn FROM dual
    CONNECT BY level <= months_between(TO_DATE ('&&edate', 'dd/mm/yyyy'), TO_DATE ('&&sdate', 'dd/mm/yyyy') )+1
    ),
    typ AS (
    SELECT 'HCDG' ptype, 'N' atype FROM dual UNION ALL
    SELECT 'HCDG' ptype, 'W' atype FROM dual
    )
    --
    SELECT p.yr AS recd_year,
      p.mn  AS recd_month,
      NVL(APP.PERMISSION_TYPE, 'HCDG') ,
      NVL(app.application_type, p.atype),
      COUNT (DISTINCT app.transaction_number) AS total
    FROM lnr_application app, (SELECT yr, mn, t.ptype, t.atype FROM period, typ t) p
    WHERE app.permission_type(+) IN ('HCDG')
    AND app.permission_type(+) = p.ptype
    AND app.application_type(+) = p.atype
    AND app.application_date(+) >= TO_DATE ('&&sdate', 'dd/mm/yyyy')
    AND app.application_date(+) <= TO_DATE ('&&edate', 'dd/mm/yyyy')
    AND to_char(app.application_date(+),'YYYY') = p.yr
    AND to_char(app.application_date(+), 'MM') = p.mn
    GROUP BY p.yr,
      p.mn,
      APP.PERMISSION_TYPE,
       NVL(app.application_type, p.atype)
    ORDER BY recd_year,
      recd_month,
      APP.PERMISSION_TYPE
    ;
     
     
  7. bimalsd

    bimalsd Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    Hi Rajen,
    Appreciate the brief explanation of the WITH clause. And also appreciate the modified query. I executed it and works as expected. Thank you once again for your expert assistance.

    Regards