Results 1 to 7 of 7

Thread: Simplify query

  1. #1
    Join Date
    Feb 2004
    Location
    Charlotte, NC
    Posts
    79

    Question Unanswered: Simplify query

    Hi

    I am looking for a way to simplify a query so that a stored procedure runs faster. The problem I have is that the query currently has 150 subqueries to produce 150 columns and one row!

    The format is:

    select
    (select median_am from tablename where region_in = 'AK' and fin_in = 'T' and (asof_dt >= st_date) And (asof_dt < end_date)),
    (select median_am from tablename where region_in = 'AK' and fin_in = 'P' and (asof_dt >= st_date) And (asof_dt < end_date)),
    (select median_am from tablename where region_in = 'AK' and fin_in = 'R' and (asof_dt >= st_date) And (asof_dt < end_date)),
    (select median_am from tablename where region_in = 'AL' and fin_in = 'T' and (asof_dt >= st_date) And (asof_dt < end_date)),
    (select median_am from tablename where region_in = 'AL' and fin_in = 'P' and (asof_dt >= st_date) And (asof_dt < end_date)),
    (select median_am from tablename where region_in = 'AL' and fin_in = 'R' and (asof_dt >= st_date) And (asof_dt < end_date)),
    ...
    from tablename

    To extract the data for a period of a few years where the time period used for the parameters st_date and end_date is a week, this procedure takes an eternity to run.

    The idea is to extract a single row of data for 50 states (region_in), each of 3 financing groups (fin_in) and a date range (the stored procedure parameters st_date and end_date).

    Does anyone know of a way in which I can improve the performance?

    Thank you

    Michael

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Simplify query

    What a horrible requirement! Why can't people put up with vertical output like they always used to?

    You can use CASE:
    PHP Code:
    select
      sum
    (case when region_in 'AK' and fin_in 'T' then median_am else 0 end) as ak_t,
      ... 
    149 similar lines
    from tablename
    where 
    (asof_dt >= st_date) And (asof_dt end_date); 
    But seriously, wouldn't this be better?:
    PHP Code:
    select region_infin_inmedian_am
    from tablename
    where 
    (asof_dt >= st_date) And (asof_dt end_date);
    order by region_infin_in 

  3. #3
    Join Date
    Jul 2003
    Posts
    2,296
    sounds like you MUST have 1 row.

    use max(decode) statements to turn rows into columns from the simple query Andrew provided.

    it will be a pain in the ass to cut/paste 150 decode statements,
    but since you only need to do it once, it shouldn't be such a big deal.

    plus, this way you are only selecting as one pass instead of 150 passes.
    sounds like when they coded this they never heard 'pivot query'

    if you need help you can goto:
    http://asktom.oracle.com

    search under 'pivot'
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    The trouble is, everyone wants SQL to be a report writing tool: it isn't, it is a data manipulation tool. Yes, you can construct the one row answer as we have shown above by cutting and pasting 150 similar expressions, but really it cries out for some procedural logic like:
    PHP Code:
    for each row in 150-row query:
      
    append region_id to region_string
      append fin_in to fin_string
      append median_am to median_am_string
    end loop
    output region_string
    output fin_string
    output median_am_string 
    Having said that, I do have a handy pivot query generator package that can be used like this:
    PHP Code:
    SQLl
      1  begin
      2    pivot
    .print_pivot_query
      3      
    ('1'
      
    4      ,'region_in||fin_in'
      
    5      ,'tablename'
      
    6      ,'median_am'
      
    7      ,NULL
      8      
    ,'sum'
      
    9      ,'(asof_dt >= st_date) And (asof_dt < end_date)'
     
    10      );
     
    11end;
    SQL> /
    select 1
    ,      sum(DECODE(region_in||fin_in,'AKT'median_am)) as "AKT"
    ,      sum(DECODE(region_in||fin_in,'AJP'median_am)) as "AJP"
    ,      sum(DECODE(region_in||fin_in,'AKR'median_am)) as "AKR"
    ,      sum(DECODE(region_in||fin_in,'ALT'median_am)) as "ALT"
    ,      sum(DECODE(region_in||fin_in,'ALP'median_am)) as "ALP"
    ,      sum(DECODE(region_in||fin_in,'ALR'median_am)) as "ALR"
    ...
    from   tablename
    where  
    (asof_dt >= st_date) And (asof_dt end_date)
    group by 1
    order by 1

    PL
    /SQL procedure successfully completed

  5. #5
    Join Date
    Feb 2004
    Location
    Charlotte, NC
    Posts
    79

    Red face

    Thanks for the response

    The reason I need the output as a single row is that I don't have any say in the way that the source table is designed, and I have to get a single row of data back as the stored procedure is called to dynamically populate a single row of a spreadsheet. It's all very messy, and the only option I have is to produce data in the way that the database and data retrieval application are designed.

    I tried the case statement, but the problem I have is that it does not return a single row as the horrible subqueries SQL did. This means that the application only reads the first row, which is unfortunately full of NULL's!

    I guess I'll have to keep looking for another option, but at the moment it just makes things look like they will only get more complex again...

    Thanks for the suggestion though.

    Michael

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by robojam
    I tried the case statement, but the problem I have is that it does not return a single row as the horrible subqueries SQL did. This means that the application only reads the first row, which is unfortunately full of NULL's!
    In that case you didn't do it right. The statement I gave you will select all rows where (asof_dt >= st_date) And (asof_dt < end_date), not just one of them.

    Did you forget the SUMs?

  7. #7
    Join Date
    Feb 2004
    Location
    Charlotte, NC
    Posts
    79

    Talking

    You're absolutely right - I didn't put the SUM keywords there. I am sort of doing about 5 things at once and I think I need another coffee or 4...

    Banks! Who would work for one!...err...me...

    Thanks a lot for the help - all I need to do now is get the data validated and that's one for the vaults!

    Michael

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •