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!

Please help me in simplifying the query...

Discussion in 'SQL PL/SQL' started by vijayspecial, Nov 26, 2013.

  1. vijayspecial

    vijayspecial Active Member

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

    Below is query which looks very big.
    Can you please give some idea and help me in simplifying the below query.




    SELECT *
    FROM
    (SELECT *
    FROM
    (SELECT LOAD_ID,
    LOAD_NAME,
    DECODE(LOAD_NAME,'DCHQRP','CBSAVA', 'DCLPD','DCLP', 'DCSDC','SAVDLY', 'DGIQ','GIQUOTES',
    'DLFIGAR','FIGARO', 'DSCARD','CRDNEW', 'CGDTWAP','DTWAPPS', 'NEWSAV','NEWSAV', 'OVRDFT','OVRDFT', 'ACTCSDC','CSDCACTIVITY', 'CVMGDW','ACTIVITYD', 'APBS','APBSACTIVITY', 'LPBATD','LPBATD',
    'CGDMORT','CGDMORT', 'ADNSTR','ADNSTR', 'DLANDP','DLANDP', 'NSTARD','NSTARD', 'WELSHE','WELSHE', 'INTNET','INTNET', 'PCRTLR','PCRTLR', 'RETSAV','RETSAV', 'SAVEME','SAVEME', 'CRDACT','CRDACT', 'DCCF','DCCF', 'WILLS','WILLS', 'LPBREF','LPBREF', 'ECONVY','ECONVY', 'SWICAN','SWICAN', LOAD_NAME ) FEED_NAME,
    TO_CHAR(START_DATE,'DD-MON-YYYY HH24:MI:SS') START_DATE,
    TO_CHAR(REPORT_DATE,'DD-MON-YYYY HH24:MI:SS') REPORT_DATE,
    STATUS,
    COUNT_DATA_RECS_FULL
    FROM NSS_LOAD_CONTROL
    WHERE REPORT_DATE = TRUNC(SYSDATE)-1
    AND LOAD_NAME IN ('DCHQRP', 'DCLPD', 'DCSDC', 'DGIQ',

    'DSCARD', 'CGDTWAP', 'NEWSAV', 'OVRDFT', 'ACTCSDC', 'CVMGDW', 'APBS', 'LPBATD',

    'CGDMORT', 'ADNSTR', 'DLANDP', 'NSTARD', 'WELSHE', 'INTNET', 'PCRTLR', 'RETSAV', 'SAVEME', 'CRDACT', 'DCCF', 'WILLS', 'LPBREF', 'ECONVY', 'SWICAN')
    UNION ALL
    SELECT LOAD_ID,
    LOAD_NAME,
    'FIGARO' FEED_NAME,
    TO_CHAR(START_DATE,'DD-MON-YYYY HH24:MI:SS') START_DATE,
    TO_CHAR(REPORT_DATE,'DD-MON-YYYY HH24:MI:SS') REPORT_DATE,
    STATUS,
    COUNT_DATA_RECS_FULL
    FROM NSS_LOAD_CONTROL
    WHERE LOAD_NAME = 'DLFIGAR'
    AND REPORT_DATE = TRUNC(SYSDATE)
    )FEEDS_CAME
    UNION
    SELECT NULL,
    LOAD_NAME,
    FEED_NAME,
    ' NOT RECEIVED',
    ' NOT RECEIVED',
    NULL,
    NULL
    FROM
    (SELECT *
    FROM
    (SELECT 'DCHQRP'LOAD_NAME,'CBSAVA' FEED_NAME FROM DUAL
    UNION
    SELECT 'DCLPD','DCLP' FROM DUAL
    UNION
    SELECT 'DCSDC','SAVDLY' FROM DUAL
    UNION
    SELECT 'DGIQ','GIQUOTES' FROM DUAL
    UNION

    SELECT 'DLFIGAR',
    'FIGARO'
    FROM DUAL
    UNION
    SELECT 'DSCARD','CRDNEW' FROM DUAL
    UNION
    SELECT 'CGDTWAP','DTWAPPS' FROM DUAL
    UNION
    SELECT 'NEWSAV','NEWSAV' FROM DUAL
    UNION
    SELECT 'OVRDFT','OVRDFT' FROM DUAL
    UNION
    SELECT 'ACTCSDC','CSDCACTIVITY' FROM DUAL
    UNION
    SELECT 'CVMGDW','ACTIVITYD' FROM DUAL
    UNION
    SELECT 'APBS','APBSACTIVITY' FROM DUAL
    UNION
    SELECT 'LPBATD','LPBATD' FROM DUAL
    UNION

    SELECT 'CGDMORT',
    'CGDMORT'
    FROM DUAL
    UNION
    SELECT 'ADNSTR','ADNSTR' FROM DUAL
    UNION
    SELECT 'DLANDP','DLANDP' FROM DUAL
    UNION
    SELECT 'NSTARD','NSTARD' FROM DUAL
    UNION
    SELECT 'WELSHE','WELSHE' FROM DUAL
    UNION
    SELECT 'INTNET','INTNET' FROM DUAL
    UNION
    SELECT 'PCRTLR','PCRTLR' FROM DUAL
    UNION
    SELECT 'RETSAV','RETSAV' FROM DUAL
    UNION
    SELECT 'SAVEME','SAVEME' FROM DUAL
    UNION
    SELECT 'CRDACT','CRDACT' FROM DUAL
    UNION
    SELECT 'DCCF','DCCF' FROM DUAL
    UNION
    SELECT 'WILLS','WILLS' FROM DUAL
    UNION
    SELECT 'LPBREF','LPBREF' FROM dual
    UNION
    SELECT 'ECONVY','ECONVY' FROM dual
    UNION
    SELECT 'SWICAN','SWICAN' FROM dual
    MINUS
    SELECT *
    FROM
    (SELECT LOAD_NAME,
    DECODE(LOAD_NAME,'DCHQRP','CBSAVA', 'DCLPD','DCLP', 'DCSDC','SAVDLY', 'DGIQ','GIQUOTES',

    'DLFIGAR','FIGARO', 'DSCARD','CRDNEW', 'CGDTWAP','DTWAPPS', 'NEWSAV','NEWSAV', 'OVRDFT','OVRDFT', 'ACTCSDC','CSDCACTIVITY', 'CVMGDW','ACTIVITYD', 'APBS','APBSACTIVITY', 'LPBATD','LPBATD',

    'CGDMORT','CGDMORT', 'ADNSTR','ADNSTR', 'DLANDP','DLANDP', 'NSTARD','NSTARD', 'WELSHE','WELSHE', 'INTNET','INTNET', 'PCRTLR','PCRTLR', 'RETSAV','RETSAV', 'SAVEME','SAVEME', 'CRDACT','CRDACT', 'DCCF','DCCF', 'WILLS','WILLS', 'LPBREF','LPBREF', 'ECONVY','ECONVY', 'SWICAN','SWICAN', LOAD_NAME ) FEED_NAME
    FROM NSS_LOAD_CONTROL
    WHERE REPORT_DATE = TRUNC(SYSDATE)-1
    AND LOAD_NAME IN ('DCHQRP', 'DCLPD', 'DCSDC', 'DGIQ',

    'DSCARD', 'CGDTWAP', 'NEWSAV', 'OVRDFT', 'ACTCSDC', 'CVMGDW', 'APBS', 'LPBATD',

    'CGDMORT', 'ADNSTR', 'DLANDP', 'NSTARD', 'WELSHE', 'INTNET', 'PCRTLR', 'RETSAV', 'SAVEME', 'CRDACT', 'DCCF', 'WILLS', 'LPBREF', 'ECONVY', 'SWICAN' )
    UNION ALL
    SELECT LOAD_NAME,
    'FIGARO' FEED_NAME
    FROM NSS_LOAD_CONTROL
    WHERE LOAD_NAME = 'DLFIGAR'
    AND REPORT_DATE = TRUNC(SYSDATE)
    ) FEEDS_CAME
    ) FEEDS_NOT_CAME
    )FINAL_FEEDS_NOT_CAME_REPORT
    )FEED_STATUS
    ORDER BY DECODE(FEED_NAME, 'CBSAVA', 'A', 'DCLP', 'B', 'SAVDLY', 'C', 'GIQUOTES', 'D',

    'FIGARO', 'F', 'CRDNEW', 'G', 'DTWAPPS', 'H', 'NEWSAV', 'I', 'OVRDFT', 'J', 'CSDCACTIVITY', 'K', 'ACTIVITYD', 'L', 'APBSACTIVITY', 'M', 'LPBATD', 'N',

    'CGDMORT', 'P', 'ADNSTR', 'Q', 'DLANDP', 'R', 'NSTARD', 'S', 'WELSHE', 'T', 'INTNET', 'U', 'PCRTLR', 'V', 'RETSAV', 'W', 'SAVEME', 'X', 'CRDACT', 'Y', 'DCCF', 'Z', 'WILLS','Z1', 'LPBREF','Z2', 'ECONVY','Z3', 'SWICAN','Z4', FEED_NAME ) ;
     
  2. DTSIGuy

    DTSIGuy Forum Advisor

    Messages:
    402
    Likes Received:
    58
    Trophy Points:
    410
    Location:
    Texas
    Generally, when I see a query like this my first thought is pity for the poor sot who will end up maintaining this. If you're stuck w/ the database design as it is...then I'd seriously considering designing to views to take the complexity out of the query. Break it up into manageable chunks and take it from there.

    What you currently have is a mess.

    CJ
     
  3. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Any query that starts with

    Code (SQL):
    SELECT *
    FROM
    (SELECT *
    FROM
     
    is a problem to begin with. I see no logic in it. Also, as was pointed out in a prior response the design is less than stellar -- the possibility to 'fix' this with views is about the only option available (outside of a complete redesign of the schema).
     
  4. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
  5. DTSIGuy

    DTSIGuy Forum Advisor

    Messages:
    402
    Likes Received:
    58
    Trophy Points:
    410
    Location:
    Texas
    I disagree...I simply don't believe some automated system is capable of kicking out a query that starts with :

    select * from (select *....

    The example given looks to be trying to build a dynamic query where the table names are not necessarily known. But, given the lack of DB design information, that's just a guess.

    For myself, if I'm ever handed a query like that and told "here's your maintenance nightmare" my first impulse would be to run, screaming, into the night.

    :cool: