7 Replies Latest reply on Jan 12, 2012 10:09 PM by Frank Kulash

    Not sure how to nest this query

    910716
      I've got data I need to pull from 2 tables, Table A and table B. My first query which returns the correct results is:

      select a.wu||a.fieldno, to_char(b.t_date,'WW'), sum(b.wvlcount);

      This gives me the concatonated field, the week # and #sum. This is great except it is only 3 columns. I need a result that will give me the concatonated field, week1,week2,week3.....week52. Total of 53 columns with the week totals of the #sum in each. I can't get CASE or DECODE to work. I know it looks like a really deep SELECT nest, but I really need this result.

      Any help is greatly appreciated.


      J
        • 1. Re: Not sure how to nest this query
          Tubby
          user8599099 wrote:
          I've got data I need to pull from 2 tables, Table A and table B. My first query which returns the correct results is:

          select a.wu||a.fieldno, to_char(b.t_date,'WW'), sum(b.wvlcount);

          This gives me the concatonated field, the week # and #sum. This is great except it is only 3 columns. I need a result that will give me the concatonated field, week1,week2,week3.....week52. Total of 53 columns with the week totals of the #sum in each. I can't get CASE or DECODE to work. I know it looks like a really deep SELECT nest, but I really need this result.

          Any help is greatly appreciated.


          J
          Sounds like you should be able to use the PIVOT clause. You didn't mention in your post if you're on version 11 or not, but you have that listed in your "tags" so i'll assume you are.

          Here's some examples on how to go about using it.
          http://www.oracle-developer.net/display.php?id=506

          There's probably many many more available on google.
          • 2. Re: Not sure how to nest this query
            GVR
            one way..
            example:
            with t as
            (select 'A' id1,'B' id2, sysdate col1, 3 col2 from dual union
            select 'A','B',sysdate,4 from dual union 
            select 'C','D',sysdate-7,1 from dual)
            select id,decode( wk, 01, sum1, null ) week1,decode( wk, 02, sum1, null ) week2
            from(
            select id1||id2 id,to_char(col1,'WW') wk,sum(col2) sum1 from t group by  id1||id2,to_char(col1,'WW')
            )
            • 3. Re: Not sure how to nest this query
              910716
              I forgot. I'm on 11g
              • 4. Pivot
                Frank Kulash
                Hi,

                Since you have Oracle 11, you can use the SELECT ... PIVOT feature, as Tubby suggested.
                http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_10002.htm#sthref6828

                If you'd like help, post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) from all tables. Simplify the problem as much as possible. For example, instead of getting all 53 weeks, post a problem that only involves, say, weeks 1, 2, 52 and 53. You'll get a solution that can easily be adapted to include all the weeks.
                Also post the results you want from that data, and an explanation of how you get those results from that data, with specific examples, and as much of the query as you can do.
                • 5. Re: Pivot
                  910716
                  The pivot command seems to work great. SQL is:

                  WITH pivot_data AS (
                  select td.wu||td.fieldno||'-'||lpad(td.trapno,2,0) as name, to_char(t.t_date,'WW') as week, sum(t.wvlcount) as wvls
                  from trapping t, trapdeploy td where td.barcode=t.barcode group by td.wu||td.fieldno||'-'||lpad(td.trapno,2,0), to_char(t.t_date,'WW'))
                  select * from pivot_data
                  PIVOT (
                  sum(wvls) FOR week IN (10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52));


                  I started just retrieving from week 1,2,10,45 then expanded it. I changed it to weeks 10-52 because there seems to be a limit on the number of statements in the IN section. I can only get a max of 41 returned.
                  • 6. Re: Pivot
                    GVR
                    IN statement has a limit but it should work for 52, it might be something else.
                    If you post a sample data that would help.
                    • 7. Re: Pivot
                      Frank Kulash
                      Hi,
                      user8599099 wrote:
                      The pivot command seems to work great. SQL is:

                      WITH pivot_data AS (
                      select td.wu||td.fieldno||'-'||lpad(td.trapno,2,0) as name, to_char(t.t_date,'WW') as week, sum(t.wvlcount) as wvls
                      from trapping t, trapdeploy td where td.barcode=t.barcode group by td.wu||td.fieldno||'-'||lpad(td.trapno,2,0), to_char(t.t_date,'WW'))
                      select * from pivot_data
                      PIVOT (
                      sum(wvls) FOR week IN (10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52));


                      I started just retrieving from week 1,2,10,45 then expanded it. I changed it to weeks 10-52 because there seems to be a limit on the number of statements in the IN section. I can only get a max of 41 returned.
                      The example you posted has 43 pivoted columns.
                      If there is a limit, 41 (or 43) is a funny place to have it. 50, or 100, or 128 would be more expected.

                      What version of Oracle are you using? I know you said "11g", but there's no 11f or 11h, so that doesn't help much. I just tried PIVOT in Oracle 11.1.0.6.0 and also in 11.2.0.1.0 and pivoted 52 columns without any problem. What exactly was the problem you had? Did you get an error message? Post the complete error message.

                      TO_CHAR, as the name suggests, returns a string, but you're comparing that string to various NUMBERs. That might account for unexpected results, but you should still get results. Anyway, I would make week a NUMBER:
                      ,  TO_NUMBER ( TO_CHAR ( t.t_date
                                                 , 'WW'
                                       )
                                )               AS week
                      but, if you prefer, you can leave it as a string and compare it to other strings:
                      FOR week IN ('10', '11', '12', ...
                      By the way, since you're doing a SUM in the main query, you don't need to do a SUM in the sub-query pivot_data, and if you don't do a SUM in pivot_data, then you don't need a GROUP BY clause.