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!

Help Needed On SQL Query

Discussion in 'SQL PL/SQL' started by angsuman, Sep 21, 2013.

  1. angsuman

    angsuman Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    Hi All,

    I am using following sql query to fetch some records.

    HTML:

    select 'RP5_dsc_dlk_MED_DEL_SBH_20130919_5799.out.bc'||'|'||tm_cdr_file_name||'|'||min(obj_id0)||'|'||max(obj_id0)||'|'||count(obj_id0)||'|'||trim(to_char(sum(VOLUME_RECEIVED)/100,'9999999999999990.99')) from EVENT_DLAY_SESS_TLCS_T where obj_id0 between 1433833603724199101 and 1433833603724199109 group by tm_cdr_file_name
     

    The above query is perfect and returns data. In this case the returned data is as below:

    HTML:

       RP5_dsc_dlk_MED_DEL_SBH_20130919_5799.out.bc|ICP_dsc_dlk_MED_DEL_SBH_20130919_5799.edr|1433833603724199101|1433833603724199109|9|2.00
     
    However, I also want to return NA and 0 when there is no matching obj_id0. My expected result will be as below:

    HTML:

       RP5_dsc_dlk_MED_DEL_SBH_20130919_5799.out.bc|NA|0|0|0|0
     
    I tried NVL and decode but it did not work. Any help on this is highly appreciated.

    Thanks
    Angsuman
     
  2. angsuman

    angsuman Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    Hi All,

    Any help on this? Really appreciate your response.

    Thanks
    Angsuman
     
  3. dariyoosh

    dariyoosh Forum Advisor

    Messages:
    118
    Likes Received:
    19
    Trophy Points:
    260
    Hello,

    Please provide a complete working test case so we may be able to work on it (the CREATE TABLE statement with description of columns and constraints of your table with INSERT statements giving enough values for different cases related to the problem).

    I don't understand the last (rightmost) zero which is normally the value for trim(to_char(. . .)), the value of this expression depends on a column named VOLUME_RECEIVED, what it is the link between this column and obj_id0?

    That's why it is necessary to give a complete and detailed description of the problem.
     
  4. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Hi Angsuman,
    Try below query for your requirement.

    SELECT nvl((SELECT 'RP5_dsc_dlk_MED_DEL_SBH_20130919_5799.out.bc'||'|'||tm_cdr_file_name||'|'||MIN(obj_id0)||'|'
    ||MAX(obj_id0)||'|'||COUNT(obj_id0)||'|'||trim(TO_CHAR(SUM(VOLUME_RECEIVED)/100,'9999999999999990.99'))
    FROM EVENT_DLAY_SESS_TLCS_T WHERE obj_id0 BETWEEN 1433833603724199101 AND 1433833603724199109
    GROUP BY tm_cdr_file_name),(SELECT 'RP5_dsc_dlk_MED_DEL_SBH_20130919_5799.out.bc'||'|'||'NA'||'|'||0||'|'||0||'|'||0||'|'||0 from dual)) from dual;
     
  5. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You have tested this query to verify it returns the correct results?
     
  6. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Hi David Fitzjarrell,
    Yes I tested it giving correct results.
     
  7. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    It would have been nice of you to post the output along with the query to prove that it works.