3

I am having an issue where I need to make 1 parameter have multiple options, almost like a case statement or decode function. Here is a snippit of the query below, We were using 2 parameters so he can have either or, or use a wildcard but we want to narrow it down to 1 parameter.

What I am looking for is.. if he where to choose '%' for the &industry parameter, it will show spc.characteristic_code in ('ARLFT','MINE') other wise he would put either 'MINE' or 'ARLFT' in the &Industry parameter.

If I need to clarify, please advise. Thanks in advance! 

  SELECT INV.COUNTRY
    , INV.NAME
    , INV.STATE
    , INV.CUSTOMER_ID
    , INV.CUST_GRP AS DIVISION
    , INV.DISTRICT_CODE 
    , INV.SLSPRT
    , SP.CATALOG_DESC
    , SPC.CHARACTERISTIC_CODE  
    , SUM(INV.QUANTITY) QTY
    , SUM(INV.SALES) SALES
    , SUM(INV.COST) COSTS
    , SUM(INV.SALES - INV.COST) AS MARGIN
    FROM IFSINFO.HB_INVOICING_ALL INV 
        JOIN IFSAPP.SALES_PART_CHARACTERISTIC SPC 
            ON (INV.SITE = SPC.CONTRACT) 
            AND (INV.SLSPRT = SPC.CATALOG_NO)
        JOIN IFSAPP.SALES_PART SP 
            ON (INV.SITE = SP.CONTRACT) 
            AND (INV.SLSPRT = SP.CATALOG_NO)
    WHERE (TO_DATE(INV.INVDATE) 
        BETWEEN TO_DATE('&Start_Date','mm/dd/yyyy') 
        AND TO_DATE('&End_Date','mm/dd/yyyy')
        OR  TO_DATE(INV.INVDATE)
        BETWEEN TO_DATE('&Start_Date2','mm/dd/yyyy') 
        AND TO_DATE('&End_Date2','mm/dd/yyyy'))
    AND INV.SITE = '&Site'
    AND INV.CUST_GRP like '&Cust_Grp'
    AND SPC.CHARACTERISTIC_CODE like '&Industry'
    GROUP BY INV.COUNTRY
    , INV.NAME
    , INV.STATE
    , INV.CUSTOMER_ID
    , INV.CUST_GRP
    , INV.DISTRICT_CODE 
    , INV.SLSPRT
    , SP.CATALOG_DESC
    , SPC.CHARACTERISTIC_CODE
Improve this question
7
  • To add onto the content, what I am looking for is AND SPC.CHARACTERISTIC_CODE like CASE IF '&Industry' = '%' THEN SPC.CHARACTERISTIC IN ('ARLFT','MINE') IF '&Industry' = 'ARLFT' THEN 'ARLFT' IF '&Industry' = 'MINE' then 'MINE' Hope this makes sense
    – Hector
    Commented Jul 16, 2015 at 20:24
  • are you using sqlplus? You have tagged your query with plsql but I don't see any pl/sql. I Only see SQL. You don't use pl/sql, right?
    – miracle173
    Commented Jul 17, 2015 at 6:36
  • Miracle173, thank you for clarifying, I am using ORACLE SQL, For this particular script, I am not using SQL PLUS, just firing off a script (f5 in TOAD) and running, then inserting the parameter for &Industry. Good call on the SQL/PL tag, I am going to remove that tag, I was hoping that would get more views to help answer the question, I will keep that in mind next time I ask a question, to help keep things clean and simple.
    – Hector
    Commented Jul 17, 2015 at 12:16
  • I've read this and reread it and I still can't work out exactly what you are asking. Please can you edit and clarify? For a start, who is "he" you keep referring to?
    – Jack Douglas
    Commented Jul 19, 2015 at 12:59
  • Also what do you mean by "parameter"? Are you referring to Toad's substitution variables starting with '&'?
    – Jack Douglas
    Commented Jul 19, 2015 at 13:01

1 Answer 1

Reset to default
0

I don't have a database on hand to test this but can't you do something like this:

and SPC.CHARACTERISTIC in ('ARLFT','MINE')
and SPC.CHARACTERISTIC in ('&industry')

With both of those predicates present you will default to both values if you enter '%' but be restricted to just one if you enter either 'MINE' or 'ARLFT'

Improve this answer
2
  • Unfortunately this did not work JimM. I think I need to use a REGEXP_SUBSTR function to allow the parameters to recognize a '&' to only allow 'SPC.CHARACTERISTIC_CODE IN ('ARLFT','MINE')
    – Hector
    Commented Jul 17, 2015 at 20:06
  • If &industry evaluates to %, SPC.CHARACTERISTIC in () and SPC.CHARACTERISTIC in ('&industry') cannot possibly be true. (You would essentially want a column value to be one of 'ARLFT','MINE' and to be a '%' at the same time, which is impossible.)
    – Andriy M
    Commented Jul 19, 2015 at 20:47

Not the answer you're looking for? Browse other questions tagged or ask your own question.