This discussion is archived
1 2 Previous Next 15 Replies Latest reply: Jan 25, 2013 12:46 AM by user13653962 RSS

Dynamic Case Expression

user13653962 Newbie
Currently Being Moderated
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 Journeyer
    Currently Being Moderated
    did u passed 'BS&I' only as input to the last query
  • 2. Re: Dynamic Case Expression
    user13653962 Newbie
    Currently Being Moderated
    yeah i did , initially i pasted wrong workaround now i have edited it , please see again original post.
  • 3. Re: Dynamic Case Expression
    NSK2KSN Journeyer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    did you got the expected output
  • 13. Re: Dynamic Case Expression
    user13653962 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points