1 2 Previous Next 15 Replies Latest reply: Jan 25, 2013 2:46 AM by user13653962 RSS

    Dynamic Case Expression

    user13653962
      select  count(*)
        from  vwsr_all_merged_data s
       where  sr_tio_priority in ('Level 0','CEO','TER','Priority Assistance','Enquiry')
         and  sr_status = 'Open'
         and  SR_BUS_UNIT IN ('Business Support and Improvement','Finance and Administration','Cust Sat Simplification and Productivity','Project New and Customer Experience','Corp Strategy and Customer Experience','Other')
      
        COUNT(*)
      ----------
             126
      the same should be possible dynamically with Case expression ,before that i create the string as
      SELECT 
           CASE 
              WHEN :name = 'BS&I' THEN ('('||''''||'Business Support and Improvement'||''''||','||''''||'Finance and Administration'||','||''''||'Cust Sat Simplification and Productivity'
                                ||''''||','||''''||'Project New and Customer Experience'||''''||','||''''||'Corp Strategy and Customer Experience'||''''||','||''''||'Other'||''''||')')
               ELSE null 
           END  
      FROM dual
      /
      
      CASEWHEN:NAME='BS&I'THEN('('||''''||'BUSINESSSUPPORTANDIMPROVEMENT'||''''||','||
      -----------------------------------------------------------------------------------------------------------
      ('Business Support and Improvement','Finance and Administration,'Cust Sat Simplification and Productivity','Project New and Customer Experience','Corp Strategy and Customer Experience','Other')
      
      SQL> var name varchar2(10)
      SQL> exec :name:='BS&I'
      
      PL/SQL procedure successfully completed.
      the same string query i put here within subquery to make above string , it does not work.
      select  count(*)--sr_bus_unit,count(trunc(sr_open_date))
        from  vwsr_all_merged_data s
       where  sr_tio_priority in ('Level 0','CEO','TER','Priority Assistance','Enquiry')
         and  sr_status = 'Open'
         and  SR_BUS_UNIT IN 
                    (   SELECT 
                          CASE 
                             WHEN :name = 'BS&I' THEN ('('||''''||'Business Support and Improvement'||''''||','||''''||'Finance and Administration'||','||''''||'Cust Sat Simplification and Productivity'
                                ||''''||','||''''||'Project New and Customer Experience'||''''||','||''''||'Corp Strategy and Customer Experience'||''''||','||''''||'Other'||''''||')')
                         ELSE null 
                     END  
                              FROM dual
                     ) 
      /
      COUNT(*)--SR_BUS_UNIT,COUNT(TRUNC(SR_OPEN_DATE))
      ------------------------------------------------
                                                     0
      Edited by: user13653962 on 24/01/2013 17:09

      Edited by: user13653962 on 24/01/2013 17:10
        • 1. Re: Dynamic Case Expression
          NSK2KSN
          did u passed 'BS&I' only as input to the last query
          • 2. Re: Dynamic Case Expression
            user13653962
            yeah i did , initially i pasted wrong workaround now i have edited it , please see again original post.
            • 3. Re: Dynamic Case Expression
              NSK2KSN
              sorry if it makes no sense to you, but can you please put below query output for me
              select  count(*)
                from  vwsr_all_merged_data s
               where  sr_tio_priority in ('Level 0','CEO','TER','Priority Assistance','Enquiry')
                 and  sr_status = 'Open'
                 and  SR_BUS_UNIT IN ('Business Support and Improvement','Finance and Administration','Cust Sat Simplification and Productivity','Project New and Customer Experience','Corp Strategy and Customer Experience','Other')
               union all
              select  count(*)--sr_bus_unit,count(trunc(sr_open_date))
                from  vwsr_all_merged_data s
               where  sr_tio_priority in ('Level 0','CEO','TER','Priority Assistance','Enquiry')
                 and  sr_status = 'Open'
                 and  SR_BUS_UNIT IN 
                            (   SELECT 
                                  CASE 
                                     WHEN :name = 'BS&I' THEN ('('||''''||'Business Support and Improvement'||''''||','||''''||'Finance and Administration'||','||''''||'Cust Sat Simplification and Productivity'
                                        ||''''||','||''''||'Project New and Customer Experience'||''''||','||''''||'Corp Strategy and Customer Experience'||''''||','||''''||'Other'||''''||')')
                                 ELSE null 
                             END  
                                      FROM dual
                             ) 
              • 4. Re: Dynamic Case Expression
                user13653962
                Oh its two in one , union :)
                SQL> select  count(*)
                  2    from  vwsr_all_merged_data s
                  3   where  sr_tio_priority in ('Level 0','CEO','TER','Priority Assistance','Enquiry')
                  4     and  sr_status = 'Open'
                  5     and  SR_BUS_UNIT IN ('Business Support and Improvement','Finance and Administration','Cust S
                at Simplification and Productivity','Project New and Customer Experience','Corp Strategy and Custome
                r Experience','Other')
                  6   union all
                  7  select  count(*)--sr_bus_unit,count(trunc(sr_open_date))
                  8    from  vwsr_all_merged_data s
                  9   where  sr_tio_priority in ('Level 0','CEO','TER','Priority Assistance','Enquiry')
                 10     and  sr_status = 'Open'
                 11     and  SR_BUS_UNIT IN 
                 12        (   SELECT 
                 13          CASE 
                 14             WHEN :name = 'BS&I' THEN ('('||''''||'Business Support and Improvement'||''''||','||
                ''''||'Finance and Administration'||','||''''||'Cust Sat Simplification and Productivity'
                 15                ||''''||','||''''||'Project New and Customer Experience'||''''||','||''''||'Corp 
                Strategy and Customer Experience'||''''||','||''''||'Other'||''''||')')
                 16             ELSE null 
                 17     END  
                 18                          FROM dual
                 19         )
                 20  
                SQL>  
                SQL>  
                SQL>  
                SQL> 
                SQL> /
                
                  COUNT(*)
                ----------
                       126
                         0
                Edited by: user13653962 on 24/01/2013 17:34
                • 5. Re: Dynamic Case Expression
                  NSK2KSN
                  hey if you are running it sql prompt, do the following and post me the output
                  set define off;
                  select  count(*)--sr_bus_unit,count(trunc(sr_open_date))
                        from  vwsr_all_merged_data s
                       where  sr_tio_priority in ('Level 0','CEO','TER','Priority Assistance','Enquiry')
                        and  sr_status = 'Open'
                        and  SR_BUS_UNIT IN 
                           (   SELECT 
                             CASE 
                                WHEN :name = 'BS&I' THEN ('('||''''||'Business Support and Improvement'||''''||','||
                  ''''||'Finance and Administration'||','||''''||'Cust Sat Simplification and Productivity'
                                   ||''''||','||''''||'Project New and Customer Experience'||''''||','||''''||'Corp 
                  Strategy and Customer Experience'||''''||','||''''||'Other'||''''||')')
                                ELSE null 
                        END  
                                             FROM dual
                            );
                  • 6. Re: Dynamic Case Expression
                    user13653962
                    i did it in the first place by set scan off, however again but no luck.
                    SQL> set define off;
                    SQL> select  count(*)--sr_bus_unit,count(trunc(sr_open_date))
                      2        from  vwsr_all_merged_data s
                      3       where  sr_tio_priority in ('Level 0','CEO','TER','Priority Assistance','Enquiry')
                      4        and  sr_status = 'Open'
                      5        and  SR_BUS_UNIT IN 
                      6           (   SELECT 
                      7             CASE 
                      8                WHEN :name = 'BS&I' THEN ('('||''''||'Business Support and Improvement'||''''||',
                    '||
                      9  ''''||'Finance and Administration'||','||''''||'Cust Sat Simplification and Productivity'
                     10                   ||''''||','||''''||'Project New and Customer Experience'||''''||','||''''||'Co
                    rp 
                     11  Strategy and Customer Experience'||''''||','||''''||'Other'||''''||')')
                     12                ELSE null 
                     13        END  
                     14                             FROM dual
                     15            );
                    
                    COUNT(*)--SR_BUS_UNIT,COUNT(TRUNC(SR_OPEN_DATE))
                    ------------------------------------------------
                                                                   0
                    • 7. Re: Dynamic Case Expression
                      NSK2KSN
                      now you will get the output
                      set define off;
                      select  count(*)--sr_bus_unit,count(trunc(sr_open_date))
                            from  vwsr_all_merged_data s
                           where  sr_tio_priority in ('Level 0','CEO','TER','Priority Assistance','Enquiry')
                            and  sr_status = 'Open'
                            and  SR_BUS_UNIT IN 
                               (   SELECT 
                                 CASE 
                                    WHEN :name = 'BS&I' THEN (''''||'Business Support and Improvement'||''''||','||
                      ''''||'Finance and Administration'||','||''''||'Cust Sat Simplification and Productivity'
                                       ||''''||','||''''||'Project New and Customer Experience'||''''||','||''''||'Corp 
                      Strategy and Customer Experience'||''''||','||''''||'Other'||'''')
                                    ELSE null 
                            END  
                                                 FROM dual
                                )
                      • 8. Re: Dynamic Case Expression
                        user13653962
                        nop not yet.
                        SQL> set define off;
                        SQL> select  count(*)--sr_bus_unit,count(trunc(sr_open_date))
                          2        from  vwsr_all_merged_data s
                          3       where  sr_tio_priority in ('Level 0','CEO','TER','Priority Assistance','Enquiry')
                          4        and  sr_status = 'Open'
                          5        and  SR_BUS_UNIT IN 
                          6           (   SELECT 
                          7             CASE 
                          8                WHEN :name = 'BS&I' THEN (''''||'Business Support and Improvement'||''''||','||''''||'Finance and Administration'||','||''''||'Cust Sat Simplification and Productivity'||''''||','||
                        ''''||'Project New and Customer Experience'||''''||','||''''||'Corp Strategy and Customer Experience
                        '||''''||','||''''||'Other'||'''')
                          9                ELSE null 
                         10        END  
                         11                             FROM dual
                         12            )
                         13  /
                        
                        COUNT(*)--SR_BUS_UNIT,COUNT(TRUNC(SR_OPEN_DATE))
                        ------------------------------------------------
                                                                       0
                        • 9. Re: Dynamic Case Expression
                          NSK2KSN
                          am really worried why my last query didn't returned the output

                          can you give a try for me
                          SET DEFINE OFF;
                          
                          SELECT COUNT (*)
                            FROM vwsr_all_merged_data s
                           WHERE     sr_tio_priority IN
                                        ('Level 0', 'CEO', 'TER', 'Priority Assistance', 'Enquiry')
                                 AND sr_status = 'Open'
                                 AND SR_BUS_UNIT IN
                                        ('Business Support and Improvement',
                                         'Finance and Administration',
                                         'Cust Sat Simplification and Productivity',
                                         'Project New and Customer Experience',
                                         'Corp Strategy and Customer Experience',
                                         'Other')
                          UNION ALL
                          SELECT COUNT (*)                      --sr_bus_unit,count(trunc(sr_open_date))
                            FROM vwsr_all_merged_data s
                           WHERE     sr_tio_priority IN
                                        ('Level 0', 'CEO', 'TER', 'Priority Assistance', 'Enquiry')
                                 AND sr_status = 'Open'
                                 AND SR_BUS_UNIT IN
                                        (SELECT CASE
                                                   WHEN 'BS&I' = 'BS&I'
                                                   THEN
                                                      (   ''''
                                                       || 'Business Support and Improvement'
                                                       || ''''
                                                       || ','
                                                       || ''''
                                                       || 'Finance and Administration'
                                                       || ','
                                                       || ''''
                                                       || 'Cust Sat Simplification and Productivity'
                                                       || ''''
                                                       || ','
                                                       || ''''
                                                       || 'Project New and Customer Experience'
                                                       || ''''
                                                       || ','
                                                       || ''''
                                                       || 'Corp Strategy and Customer Experience'
                                                       || ''''
                                                       || ','
                                                       || ''''
                                                       || 'Other'
                                                       || '''')
                                                   ELSE
                                                      NULL
                                                END
                                           FROM DUAL)
                          • 10. Re: Dynamic Case Expression
                            user13653962
                            SQL> set scan off
                            SQL> var name varchar2(10)
                            SQL> exec :name:='BS&I'
                            
                            PL/SQL procedure successfully completed.
                            
                            SQL> SELECT COUNT (*)
                              2    FROM vwsr_all_merged_data s
                              3   WHERE     sr_tio_priority IN
                              4                ('Level 0', 'CEO', 'TER', 'Priority Assistance', 'Enquiry')
                              5         AND sr_status = 'Open'
                              6         AND SR_BUS_UNIT IN
                              7                ('Business Support and Improvement',
                              8                 'Finance and Administration',
                              9                 'Cust Sat Simplification and Productivity',
                             10                 'Project New and Customer Experience',
                             11                 'Corp Strategy and Customer Experience',
                             12                 'Other')
                             13  UNION ALL
                             14  SELECT COUNT (*)                      --sr_bus_unit,count(trunc(sr_open_date))
                             15    FROM vwsr_all_merged_data s
                             16   WHERE     sr_tio_priority IN
                             17                ('Level 0', 'CEO', 'TER', 'Priority Assistance', 'Enquiry')
                             18         AND sr_status = 'Open'
                             19         AND SR_BUS_UNIT IN
                             20                (SELECT CASE
                             21                           WHEN 'BS&I' = 'BS&I'
                             22                           THEN
                             23                              (   ''''
                             24                               || 'Business Support and Improvement'
                             25                               || ''''
                             26                               || ','
                             27                               || ''''
                             28                               || 'Finance and Administration'
                             29                               || ','
                             30                               || ''''
                             31                               || 'Cust Sat Simplification and Productivity'
                             32                               || ''''
                             33                               || ','
                             34                               || ''''
                             35                               || 'Project New and Customer Experience'
                             36                               || ''''
                             37                               || ','
                             38                               || ''''
                             39                               || 'Corp Strategy and Customer Experience'
                             40                               || ''''
                             41                               || ','
                             42                               || ''''
                             43                               || 'Other'
                             44                               || '''')
                             45                           ELSE
                             46                              NULL
                             47                        END
                             48                   FROM DUAL)
                             49  
                            SQL>  
                            SQL>  
                            SQL>  
                            SQL> 
                            SQL> /
                            
                              COUNT(*)
                            ----------
                                   126
                                     0
                            • 11. Re: Dynamic Case Expression
                              NSK2KSN
                              this time you will get output for sure
                              SET DEFINE OFF;
                              
                              SELECT COUNT (*)
                                FROM vwsr_all_merged_data s
                               WHERE     sr_tio_priority IN
                                            ('Level 0', 'CEO', 'TER', 'Priority Assistance', 'Enquiry')
                                     AND sr_status = 'Open'
                                     AND SR_BUS_UNIT IN
                                            ('Business Support and Improvement',
                                             'Finance and Administration',
                                             'Cust Sat Simplification and Productivity',
                                             'Project New and Customer Experience',
                                             'Corp Strategy and Customer Experience',
                                             'Other')
                              UNION ALL
                              SELECT COUNT (*)                      --sr_bus_unit,count(trunc(sr_open_date))
                                FROM vwsr_all_merged_data s
                               WHERE     sr_tio_priority IN
                                            ('Level 0', 'CEO', 'TER', 'Priority Assistance', 'Enquiry')
                                     AND sr_status = 'Open'
                                     AND SR_BUS_UNIT IN
                                            (SELECT CASE
                                                       WHEN 'BS&I' = 'BS&I'
                                                       THEN
                                                          (   ''''
                                                           || 'Business Support and Improvement'
                                                           || ''''
                                                           || ','
                                                           || ''''
                                                           || 'Finance and Administration'
                                                           || ''''
                                                           || ','
                                                           || ''''
                                                           || 'Cust Sat Simplification and Productivity'
                                                           || ''''
                                                           || ','
                                                           || ''''
                                                           || 'Project New and Customer Experience'
                                                           || ''''
                                                           || ','
                                                           || ''''
                                                           || 'Corp Strategy and Customer Experience'
                                                           || ''''
                                                           || ','
                                                           || ''''
                                                           || 'Other'
                                                           || '''')
                                                       ELSE
                                                          NULL
                                                    END
                                               FROM DUAL)
                              • 12. Re: Dynamic Case Expression
                                NSK2KSN
                                did you got the expected output
                                • 13. Re: Dynamic Case Expression
                                  user13653962
                                  No man , it is still no working , anyhow i really appreciate yours help , yours help drive me to find other way and you contributed 70% to achieve the result. Next query worked.
                                  SQL> SET DEFINE OFF;
                                  SQL>  
                                  SQL> SELECT COUNT (*)
                                    2    FROM vwsr_all_merged_data s
                                    3   WHERE     sr_tio_priority IN
                                    4                ('Level 0', 'CEO', 'TER', 'Priority Assistance', 'Enquiry')
                                    5         AND sr_status = 'Open'
                                    6         AND SR_BUS_UNIT IN
                                    7                ('Business Support and Improvement',
                                    8                 'Finance and Administration',
                                    9                 'Cust Sat Simplification and Productivity',
                                   10                 'Project New and Customer Experience',
                                   11                 'Corp Strategy and Customer Experience',
                                   12                 'Other')
                                   13  UNION ALL
                                   14  SELECT COUNT (*)                      --sr_bus_unit,count(trunc(sr_open_date))
                                   15    FROM vwsr_all_merged_data s
                                   16   WHERE     sr_tio_priority IN
                                   17                ('Level 0', 'CEO', 'TER', 'Priority Assistance', 'Enquiry')
                                   18         AND sr_status = 'Open'
                                   19         AND SR_BUS_UNIT IN
                                   20                (SELECT CASE
                                   21                           WHEN 'BS&I' = 'BS&I'
                                   22                           THEN
                                   23                              (   ''''
                                   24                               || 'Business Support and Improvement'
                                   25                               || ''''
                                   26                               || ','
                                   27                               || ''''
                                   28                               || 'Finance and Administration'
                                   29                               || ''''
                                   30                               || ','
                                   31                               || ''''
                                   32                               || 'Cust Sat Simplification and Productivity'
                                   33                               || ''''
                                   34                               || ','
                                   35                               || ''''
                                   36                               || 'Project New and Customer Experience'
                                   37                               || ''''
                                   38                               || ','
                                   39                               || ''''
                                   40                               || 'Corp Strategy and Customer Experience'
                                   41                               || ''''
                                   42                               || ','
                                   43                               || ''''
                                   44                               || 'Other'
                                   45                               || '''')
                                   46                           ELSE
                                   47                              NULL
                                   48                        END
                                   49                   FROM DUAL)
                                   50  
                                  SQL>  
                                  SQL> 
                                  SQL> /
                                  
                                    COUNT(*)
                                  ----------
                                         126
                                           0
                                  Second Query
                                  SQL> var name varchar2(10)
                                  SQL> exec :name:='BS&I'
                                  
                                  PL/SQL procedure successfully completed.
                                  
                                  SQL> set define off
                                  SQL> SELECT COUNT (*)                      --sr_bus_unit,count(trunc(sr_open_date))
                                    2    FROM vwsr_all_merged_data s
                                    3   WHERE     sr_tio_priority IN
                                    4                ('Level 0', 'CEO', 'TER', 'Priority Assistance', 'Enquiry')
                                    5         AND sr_status = 'Open'
                                    6         AND SR_BUS_UNIT IN
                                    7                (SELECT CASE
                                    8                           WHEN :NAME = 'BS&I'
                                    9                           THEN
                                   10                              ('Business Support and Improvement')
                                   11                           ELSE
                                   12                              NULL
                                   13                        END
                                   14                 FROM DUAL
                                   15                UNION
                                   16                SELECT  CASE
                                   17                 WHEN :NAME = 'BS&I'
                                   18                           THEN
                                   19                              ('Finance and Administration')
                                   20                           ELSE
                                   21                              NULL
                                   22         END
                                   23                   FROM DUAL
                                   24                UNION
                                   25                SELECT  CASE
                                   26                 WHEN :NAME = 'BS&I'
                                   27                           THEN
                                   28                              ('Cust Sat Simplification and Productivity')
                                   29                           ELSE
                                   30                              NULL
                                   31         END
                                   32                 FROM DUAL
                                   33                UNION
                                   34                SELECT  CASE
                                   35                 WHEN :NAME = 'BS&I'
                                   36                           THEN
                                   37                              ('Project New and Customer Experience')
                                   38                           ELSE
                                   39                              NULL
                                   40         END
                                   41                 FROM DUAL
                                   42                UNION
                                   43                SELECT  CASE
                                   44                 WHEN :NAME = 'BS&I'
                                   45                           THEN
                                   46                              ('Corp Strategy and Customer Experience')
                                   47                           ELSE
                                   48                              NULL
                                   49         END
                                   50                 FROM DUAL
                                   51                UNION
                                   52                SELECT  CASE
                                   53                 WHEN :NAME = 'BS&I'
                                   54                           THEN
                                   55                              ('Other')
                                   56                           ELSE
                                   57                              NULL
                                   58         END
                                   59                 FROM DUAL
                                   60  )
                                   61  /
                                  
                                  COUNT(*)--SR_BUS_UNIT,COUNT(TRUNC(SR_OPEN_DATE))
                                  ------------------------------------------------
                                                                               126
                                  
                                  SQL> 
                                  • 14. Re: Dynamic Case Expression
                                    ascheffer
                                    Isn't that the same as
                                    SELECT COUNT (*)
                                    FROM vwsr_all_merged_data s
                                    WHERE     sr_tio_priority IN ( 'Level 0'
                                                                           , 'CEO'
                                                                           , 'TER'
                                                                           , 'Priority Assistance'
                                                                           , 'Enquiry'
                                                                           )
                                    AND sr_status = 'Open'
                                    AND SR_BUS_UNIT IN ( 'Business Support and Improvement'
                                                       , 'Finance and Administration'
                                                       , 'Cust Sat Simplification and Productivity'
                                                       , 'Project New and Customer Experience'
                                                       , 'Corp Strategy and Customer Experience'
                                                       , 'Other'
                                                           )
                                    AND :NAME = 'BS&I'
                                    1 2 Previous Next