1 2 Previous Next 21 Replies Latest reply: Feb 26, 2014 6:39 PM by 896976 Go to original post RSS
      • 15. Re: Display the desaiere  output aggregate query
        896976

        just one issue and believe I try to get to work myself but I am not sucessfull how I can make to work like this?

        Thanks again   

         

         

        AFRS3301 Two Of:[(AFRS 2000-2969) or (AFRS 200-289)] AND [AFRS 1101 or 101]

        When subject is the same in a set, can we combine?

        AFRS3301 Two of:[(AFRS 2000-2969) or (AFRS 200-289)] AND [AFRS 1101 or 101]

        AFRS3362 One Of:[(AFRS 1000-3999) or (LAS 1000-3999) or (AFRS 010-399) or (LAS 010-399)]

        about grouping, if in same set, can we order first by subject?

        One of: (AFRS 1000-3999 or 010-399) OR (LAS 1000-3999 or 010-399)

        • 16. Re: Display the desaiere  output aggregate query
          896976

          I try to get this thing to work but I have not been successful

          Thanks a lot again this is cool code

           

           

           

          AFRS3301 Two Of:[(AFRS 2000-2969) or (AFRS 200-289)] AND [AFRS 1101 or 101]

          When subject is the same in a set, can we combine?

          AFRS3301 Two of:[(AFRS 2000-2969) or (AFRS 200-289)] AND [AFRS 1101 or 101]

          AFRS3362 One Of:[(AFRS 1000-3999) or (LAS 1000-3999) or (AFRS 010-399) or (LAS 010-399)]

          about grouping, if in same set, can we order first by subject?

          One of: (AFRS 1000-3999 or 010-399) OR (LAS 1000-3999 or 010-399)

          ANTH-SOC One Of:[ANTH 1101 or 101 or SOC 1101 or 101]

          When subject changes can you put a parenthesis around it? Also Capital OR when changing subject.

          ANTH-SOC One Of:[(ANTH 1101 or 101) OR (SOC 1101 or 101)]

          • 17. Re: Display the desaiere  output aggregate query
            Partha Sarathy S

            Your output data seems to be different. Can you please post output for the given data or post sample data for the given output

            • 18. Re: Display the desaiere  output aggregate query
              Partha Sarathy S

              Check is this what you are looking for.

              WITH T1 AS(
              SELECT SMRACAA_AREA,
                     SMRACAA_TERM_CODE_EFF,
                     SMRACAA_SEQNO,
                     SMRACAA_SET,
                     SMRACAA_SUBSET,
                     SMRACAA_SUBJ_CODE,
                     SMRACAA_CRSE_NUMB_LOW,
                SMRACAA_CRSE_NUMB_HIGH,
                     SMRACAA_ATTS_CODE,
                     STVATTS_DESC
              FROM SMRACAA_TEST
                   LEFT OUTER JOIN
                   STVATTS_TEST
              ON SMRACAA_TEST.SMRACAA_ATTS_CODE=STVATTS_TEST.STVATTS_CODE
              ORDER BY SMRACAA_SEQNO),
              T2 AS (
              select SMRACAA_AREA,
                     SMRACAA_TERM_CODE_EFF,
                     SMRACAA_SEQNO,
                     SMRACAA_SET,
                     SMRACAA_SUBSET,
                     SMRACAA_SUBJ_CODE,
                     SMRACAA_CRSE_NUMB_LOW,
                     SMRACAA_CRSE_NUMB_HIGH,
                     SMRACAA_ATTS_CODE,
                     STVATTS_DESC,
                     row_number() over(partition by smracaa_area,smracaa_set,smracaa_subj_code order by smracaa_set,SMRACAA_SEQNO,smracaa_crse_numb_low) rn,
              case
              when stvatts_desc is not null  then '(Placement in' || substr(stvatts_desc,instr(stvatts_desc,':')+1) ||')'
              when stvatts_desc is not null then '('||stvatts_desc||' with placement)'
              when TRIM(smracaa_crse_numb_high) is not null then '('||smracaa_subj_code || ' '|| smracaa_crse_numb_low|| '-' ||TRIM(smracaa_crse_numb_high)||')'
              when TRIM(smracaa_crse_numb_LOW) is not null  AND TRIM(smracaa_crse_numb_high)  IS NULL AND  row_number() over(partition by smracaa_area,smracaa_set,smracaa_subj_code order by smracaa_set,SMRACAA_SEQNO,smracaa_crse_numb_low) = 1 then
              smracaa_subj_code || ' '|| smracaa_crse_numb_low
              when TRIM(smracaa_crse_numb_LOW) is not null AND TRIM(smracaa_crse_numb_high) IS NULL AND row_number() over(partition by smracaa_area,smracaa_set,smracaa_subj_code order by smracaa_set,SMRACAA_SEQNO,smracaa_crse_numb_low) != 1 then
              smracaa_crse_numb_low
              when TRIM(smracaa_crse_numb_high) is null and row_number() over(partition by smracaa_area,smracaa_set,smracaa_subj_code order by smracaa_set,SMRACAA_SEQNO,smracaa_crse_numb_low) = 1
                then '('|| smracaa_subj_code || ' '|| smracaa_crse_numb_low||')'|| ' or ' ||lead(smracaa_crse_numb_low) over(partition by smracaa_area,smracaa_set,smracaa_subj_code order by smracaa_set,SMRACAA_SEQNO,smracaa_crse_numb_low)||')'
              else null
              end tvstr
              from T1
              )
              SELECT
              --COUNT(SMRACAA_AREA)||' '||'One of:'||' '||
              SMRACAA_AREA||' '||TO_CHAR(TO_DATE(COUNT(smracaa_area),'J'),'Jsp')||' of:'||' '||
                     LISTAGG(TEMP_RES,' AND ')WITHIN GROUP (ORDER BY NULL)
              FROM (
              SELECT SMRACAA_AREA,
                 '[('||LISTAGG(tvstr,' or ') WITHIN GROUP (ORDER BY SMRACAA_SET,SMRACAA_SEQNO,smracaa_crse_numb_low)||')]' TEMP_RES
              --- '['||LISTAGG(tvstr,' or ') WITHIN GROUP (ORDER BY smracaa_subj_code)||']' TEMP_RES
              FROM T2
              WHERE TVSTR IS NOT NULL
              ----and smracaa_area = 'ANTH2020'
              ---and  smracaa_area =  'AFRS3362'
              ---and  smracaa_area  = 'ECON2555'
              GROUP BY SMRACAA_AREA,SMRACAA_SET,SMRACAA_SUBJ_CODE ORDER BY SMRACAA_SUBJ_CODE)
              GROUP BY SMRACAA_AREA;


              OUTPUT:

               

              ANTH2020 Two of: [(ANTH 1150 or 102)] AND [(ARCH 1101 or 102 or 1102 or 102 or 2090 or 209 or 200 or 2100 or (ARCH 3200-3600))]

              If this is not your required output, then post the sample data for your required output you mentioned. I have tried to get the output using the earlier data which you provided

              • 19. Re: Display the desaiere  output aggregate query
                896976

                WOW you are amazing, I really appreciate this

                 

                here is the data

                INSERT INTO SMRACAA_TEST( SMRACAA_AREA,SMRACAA_TERM_CODE_EFF, SMRACAA_SEQNO, SMRACAA_SET, SMRACAA_SUBSET,SMRACAA_SUBJ_CODE,SMRACAA_CRSE_NUMB_LOW,SMRACAA_CRSE_NUMB_HIGH, SMRACAA_ATTS_CODE)

                SELECT  'AFRS3301','201420','1','A10','110','AFRS','1101',' ',' ' FROM DUAL;

                 

                 

                INSERT INTO SMRACAA_TEST( SMRACAA_AREA,SMRACAA_TERM_CODE_EFF, SMRACAA_SEQNO, SMRACAA_SET, SMRACAA_SUBSET,SMRACAA_SUBJ_CODE,SMRACAA_CRSE_NUMB_LOW,SMRACAA_CRSE_NUMB_HIGH, SMRACAA_ATTS_CODE)

                SELECT  'AFRS3301','201420','2','A10','120','AFRS','101',' ',' ' FROM DUAL;

                 

                 

                INSERT INTO SMRACAA_TEST( SMRACAA_AREA,SMRACAA_TERM_CODE_EFF, SMRACAA_SEQNO, SMRACAA_SET, SMRACAA_SUBSET,SMRACAA_SUBJ_CODE,SMRACAA_CRSE_NUMB_LOW,SMRACAA_CRSE_NUMB_HIGH, SMRACAA_ATTS_CODE)

                SELECT  'AFRS3301','201420','3','B10','120','AFRS','101',' ',' ' FROM DUAL;

                 

                 

                INSERT INTO SMRACAA_TEST( SMRACAA_AREA,SMRACAA_TERM_CODE_EFF, SMRACAA_SEQNO, SMRACAA_SET, SMRACAA_SUBSET,SMRACAA_SUBJ_CODE,SMRACAA_CRSE_NUMB_LOW,SMRACAA_CRSE_NUMB_HIGH, SMRACAA_ATTS_CODE)

                SELECT  'AFRS3301','201420','4','B10','120','AFRS','2000','2969 ',' ' FROM DUAL;

                 

                 

                INSERT INTO SMRACAA_TEST( SMRACAA_AREA,SMRACAA_TERM_CODE_EFF, SMRACAA_SEQNO, SMRACAA_SET, SMRACAA_SUBSET,SMRACAA_SUBJ_CODE,SMRACAA_CRSE_NUMB_LOW,SMRACAA_CRSE_NUMB_HIGH, SMRACAA_ATTS_CODE)

                SELECT  'AFRS3301','201420','5','B10','120','AFRS','200','289 ',' ' FROM DUAL;

                 

                This is what the user (picky) want

                 

                AFRS3301 Two Of:[(AFRS 2000-2969) or (AFRS 200-289)] AND [AFRS 1101 or 101]

                When subject is the same in a set, can we combine?

                AFRS3301 Two of:[(AFRS 2000-2969) or (AFRS 200-289)] AND [AFRS 1101 or 101]

                • 20. Re: Display the desaiere  output aggregate query
                  Partha Sarathy S

                  Try this.

                   

                  WITH T1 AS(

                  SELECT SMRACAA_AREA,

                         SMRACAA_TERM_CODE_EFF,

                         SMRACAA_SEQNO,

                         SMRACAA_SET,

                         SMRACAA_SUBSET,

                         SMRACAA_SUBJ_CODE,

                         SMRACAA_CRSE_NUMB_LOW,

                    SMRACAA_CRSE_NUMB_HIGH,

                         SMRACAA_ATTS_CODE,

                         STVATTS_DESC

                  FROM SMRACAA_TEST

                       LEFT OUTER JOIN

                       STVATTS_TEST

                  ON SMRACAA_TEST.SMRACAA_ATTS_CODE=STVATTS_TEST.STVATTS_CODE

                  ORDER BY SMRACAA_SEQNO),

                  T2 AS (

                  select SMRACAA_AREA,

                         SMRACAA_TERM_CODE_EFF,

                         SMRACAA_SEQNO,

                         SMRACAA_SET,

                         SMRACAA_SUBSET,

                         SMRACAA_SUBJ_CODE,

                         SMRACAA_CRSE_NUMB_LOW,

                         SMRACAA_CRSE_NUMB_HIGH,

                         SMRACAA_ATTS_CODE,

                         STVATTS_DESC,

                  case

                  when stvatts_desc is not null  then '(Placement in' || substr(stvatts_desc,instr(stvatts_desc,':')+1) ||')'

                  when stvatts_desc is not null then '('||stvatts_desc||' with placement)'

                  when TRIM(smracaa_crse_numb_high) is not null AND row_number() over(partition by smracaa_area,smracaa_set,smracaa_subj_code order by smracaa_set,SMRACAA_SEQNO,smracaa_crse_numb_low) = 1

                  then '('||smracaa_subj_code || ' '|| smracaa_crse_numb_low|| '-' ||TRIM(smracaa_crse_numb_high)

                  when TRIM(smracaa_crse_numb_high) is not null AND row_number() over(partition by smracaa_area,smracaa_set,smracaa_subj_code order by smracaa_set,SMRACAA_SEQNO,smracaa_crse_numb_low) != 1

                  AND row_number() over(partition by smracaa_area,smracaa_set,smracaa_subj_code order by smracaa_set,SMRACAA_SEQNO DESC,smracaa_crse_numb_low)! = 1

                  then  smracaa_crse_numb_low|| '-' ||TRIM(smracaa_crse_numb_high)

                  when TRIM(smracaa_crse_numb_high) is not null AND row_number() over(partition by smracaa_area,smracaa_set,smracaa_subj_code order by smracaa_set,SMRACAA_SEQNO DESC,smracaa_crse_numb_low) = 1

                  then  smracaa_crse_numb_low|| '-' ||TRIM(smracaa_crse_numb_high)||')'

                  when TRIM(smracaa_crse_numb_LOW) is not null  AND TRIM(smracaa_crse_numb_high)  IS NULL AND  row_number() over(partition by smracaa_area,smracaa_set,smracaa_subj_code order by smracaa_set,SMRACAA_SEQNO,smracaa_crse_numb_low) = 1 then

                  '('||smracaa_subj_code || ' '|| smracaa_crse_numb_low

                  when TRIM(smracaa_crse_numb_LOW) is not null AND TRIM(smracaa_crse_numb_high) IS NULL AND row_number() over(partition by smracaa_area,smracaa_set,smracaa_subj_code order by smracaa_set,SMRACAA_SEQNO,smracaa_crse_numb_low) != 1

                  AND row_number() over(partition by smracaa_area,smracaa_set,smracaa_subj_code order by smracaa_set,SMRACAA_SEQNO DESC,smracaa_crse_numb_low) != 1then

                  smracaa_crse_numb_low

                  when TRIM(smracaa_crse_numb_LOW) is not null  AND TRIM(smracaa_crse_numb_high)  IS NULL AND  row_number() over(partition by smracaa_area,smracaa_set,smracaa_subj_code order by smracaa_set,SMRACAA_SEQNO DESC,smracaa_crse_numb_low) = 1  then

                  smracaa_crse_numb_low||')'

                  when TRIM(smracaa_crse_numb_high) is null and row_number() over(partition by smracaa_area,smracaa_set,smracaa_subj_code order by smracaa_set,SMRACAA_SEQNO,smracaa_crse_numb_low) = 1

                    then '('|| smracaa_subj_code || ' '|| smracaa_crse_numb_low||')'|| ' or ' ||lead(smracaa_crse_numb_low) over(partition by smracaa_area,smracaa_set,smracaa_subj_code order by smracaa_set,SMRACAA_SEQNO,smracaa_crse_numb_low)||')'

                  else null

                  end tvstr

                  from T1

                  )

                  SELECT

                  --COUNT(SMRACAA_AREA)||' '||'One of:'||' '||

                  SMRACAA_AREA||' '||TO_CHAR(TO_DATE(COUNT(smracaa_area),'J'),'Jsp')||' of:'||' '||

                         LISTAGG(TEMP_RES,' AND ')WITHIN GROUP (ORDER BY NULL)

                  FROM (

                  SELECT SMRACAA_AREA,

                     '['||LISTAGG(tvstr,' or ') WITHIN GROUP (ORDER BY SMRACAA_SET,SMRACAA_SEQNO,smracaa_crse_numb_low)||']' TEMP_RES

                  --- '['||LISTAGG(tvstr,' or ') WITHIN GROUP (ORDER BY smracaa_subj_code)||']' TEMP_RES

                  FROM T2

                  WHERE TVSTR IS NOT NULL

                  ----and smracaa_area = 'ANTH2020'

                  ---and  smracaa_area =  'AFRS3362'

                  ---and  smracaa_area  = 'ECON2555'

                  GROUP BY SMRACAA_AREA,SMRACAA_SET,SMRACAA_SUBJ_CODE ORDER BY SMRACAA_SUBJ_CODE)

                  GROUP BY SMRACAA_AREA;



                  OUTPUT:

                  AFRS3301 Two of: [(AFRS 101 or 2000-2969 or 200-289)] AND [(AFRS 1101 or 101)]

                   

                  • 21. Re: Display the desaiere  output aggregate query
                    896976

                    It broke another one

                    AFRS3362 Two of: [(AFRS 1000-3999 OR 010-399)] AND [(LAS 1000-3999 OR 010-399)]…it should be ‘One of’ with a an OR not an AND.

                     

                    AFRS3301 Two Of:[(AFRS 2000-2969) or (AFRS 200-289)] AND [AFRS 1101 or 101]

                    When subject is the same in a set, can we combine?

                    AFRS3301 Two of:[(AFRS 2000-2969) or (AFRS 200-289)] AND [AFRS 1101 or 101]

                    AFRS3362 One Of:[(AFRS 1000-3999) or (LAS 1000-3999) or (AFRS 010-399) or (LAS 010-399)]

                    Note to Jim about grouping, if in same set, can we order first by subject?

                    One of: (AFRS 1000-3999 or 010-399) OR (LAS 1000-3999 or 010-399)

                     

                    Here is the data for AFRS3362

                    Again I try myself but I don't get it

                    Thank you again for your time


                     

                    INSERT INTO SMRACAA_TEST( SMRACAA_AREA,SMRACAA_TERM_CODE_EFF, SMRACAA_SEQNO, SMRACAA_SET, SMRACAA_SUBSET,SMRACAA_SUBJ_CODE,SMRACAA_CRSE_NUMB_LOW,SMRACAA_CRSE_NUMB_HIGH, SMRACAA_ATTS_CODE)

                    SELECT   'AFRS3362','201420','1','A10','110','AFRS','1000','3999 ',' ' FROM DUAL;

                     

                     

                    INSERT INTO SMRACAA_TEST( SMRACAA_AREA,SMRACAA_TERM_CODE_EFF, SMRACAA_SEQNO, SMRACAA_SET, SMRACAA_SUBSET,SMRACAA_SUBJ_CODE,SMRACAA_CRSE_NUMB_LOW,SMRACAA_CRSE_NUMB_HIGH, SMRACAA_ATTS_CODE)

                    SELECT  'AFRS3362','201420','2','A10','120','LAS','1000','3999 ',' ' FROM DUAL;

                     

                     

                    INSERT INTO SMRACAA_TEST( SMRACAA_AREA,SMRACAA_TERM_CODE_EFF, SMRACAA_SEQNO, SMRACAA_SET, SMRACAA_SUBSET,SMRACAA_SUBJ_CODE,SMRACAA_CRSE_NUMB_LOW,SMRACAA_CRSE_NUMB_HIGH, SMRACAA_ATTS_CODE)

                    SELECT   'AFRS3362','201420','1','A10','110','AFRS','010','399 ',' ' FROM DUAL;

                     

                     

                    INSERT INTO SMRACAA_TEST( SMRACAA_AREA,SMRACAA_TERM_CODE_EFF, SMRACAA_SEQNO, SMRACAA_SET, SMRACAA_SUBSET,SMRACAA_SUBJ_CODE,SMRACAA_CRSE_NUMB_LOW,SMRACAA_CRSE_NUMB_HIGH, SMRACAA_ATTS_CODE)

                    SELECT  'AFRS3362','201420','2','A10','120','LAS','010','399 ',' ' FROM DUAL;

                    1 2 Previous Next