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

closed as unclear what you're asking by Jack Douglas Jul 19 '15 at 12:58

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 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 Jul 16 '15 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 Jul 17 '15 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 Jul 17 '15 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 Jul 19 '15 at 12:59
  • Also what do you mean by "parameter"? Are you referring to Toad's substitution variables starting with '&'? – Jack Douglas Jul 19 '15 at 13:01
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'

  • 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 Jul 17 '15 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 Jul 19 '15 at 20:47

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