Home » SQL & PL/SQL » SQL & PL/SQL » User variable to decide which in statement to use in query
User variable to decide which in statement to use in query [message #668233] Tue, 13 February 2018 15:04 Go to next message
bobghw
Messages: 34
Registered: July 2012
Member
I have a string containing codes I want the user to select internal or external for parameter A and the use case statement to assign the correct string value to the location variable which is then
used in the where clause.
this what I have but getting ORA-00972: identifier is too long error.
declare

strval varchar(10);
str1 varchar(1000) := "and CLL.CHARTLOC_CODE in ('MIDC','CAPCO','AUCK','GREEN','NATW','STAR','MMORE','CHCH','BURW','CHCHW','GISB','MER','NELS','TIM','OAM','NLAND','DUNED','SOUTH','WAIR',
                                  'HAWK','HUTT','WAKE','NSHOR','ROTO','TAUR','LAKE','TAUM','TARA','KENE','HAW','WHAK','WAIK','POR','HBEN','MASON','MIDC','LAKE','TARA','HORO','HAST',
                                  'PMAR','NMDHB','AORA','ASHB','WAIT','ABURN','GREY','PORMH','WAI')";
str2 varchar(1000) := "and CLL.CHARTLOC_CODE not in ('MIDC','CAPCO','AUCK','GREEN','NATW','STAR','MMORE','CHCH','BURW','CHCHW','GISB','MER','NELS','TIM','OAM','NLAND','DUNED','SOUTH','WAIR',
                                  'HAWK','HUTT','WAKE','NSHOR','ROTO','TAUR','LAKE','TAUM','TARA','KENE','HAW','WHAK','WAIK','POR','HBEN','MASON','MIDC','LAKE','TARA','HORO','HAST',
                                  'PMAR','NMDHB','AORA','ASHB','WAIT','ABURN','GREY','PORMH','WAI')";

begin

select mr.pt_code,mr.mvmt_date,MR.MVMT_TYPE ,MR.DATE_REQUIRED,MR.EXP_RTN_DATE, MR.CHARTTYPE_CODE,MR.CHARTVOL_NO, MR.HOSP_CODE, CLL.CHARTLOC_TITLE
from k_mvmtreg mr, k_chartloclist cll
where MR.CHARTLOC_CODE = CLL.CHARTLOC_CODE
and MR.DEL_DATE is null
and MR.CHARTLOC_CODE = CLL.CHARTLOC_CODE

and CLL.CHARTLOC_CODE =case when:strval = 'Internal' then str2 else str1 end 

and MR.EXP_RTN_DATE > sysdate -180
order by 1

[Updated on: Tue, 13 February 2018 15:53]

Report message to a moderator

Re: User variable to decide which in statement to use in query [message #668245 is a reply to message #668233] Wed, 14 February 2018 08:01 Go to previous messageGo to next message
flyboy
Messages: 1856
Registered: November 2006
Senior Member
If the query result shall be obtained via SQL statement, what is wrong with a simple OR condition (in whichever environment is the bind variable strval declared and filled)?
select ...
from ...
where ...
  and ((:strval = 'Internal' and CLL.CHARTLOC_CODE in ('aaa', 'bbb', ...))
    or ((:strval != 'Internal' or :strval is null) 
      and CLL.CHARTLOC_CODE in ('xxx', 'yyy', ...)));
If the query is called via JDBC, OCI, or similar API, constructing the query in the calling environment would be the best way.
Re: User variable to decide which in statement to use in query [message #668246 is a reply to message #668233] Wed, 14 February 2018 08:23 Go to previous messageGo to next message
joy_division
Messages: 4910
Registered: February 2005
Location: East Coast USA
Senior Member
Strings in Oracle are enclosed in single quotes.
Code is very sloppy. Spaces are missing where they need to be
Adding either of the conditional strings would give invalid syntax (even if the spaces were correct).
Re: User variable to decide which in statement to use in query [message #668251 is a reply to message #668246] Wed, 14 February 2018 12:45 Go to previous messageGo to next message
bobghw
Messages: 34
Registered: July 2012
Member
Resolved already, but thanks for replies .
Re: User variable to decide which in statement to use in query [message #668382 is a reply to message #668251] Tue, 20 February 2018 07:41 Go to previous messageGo to next message
Bill B
Messages: 1814
Registered: December 2004
Senior Member
If it is resolved, paste in your solution so that a future query trying to solve the same problem will see your solution
Re: User variable to decide which in statement to use in query [message #668401 is a reply to message #668382] Wed, 21 February 2018 12:46 Go to previous message
bobghw
Messages: 34
Registered: July 2012
Member
Resolved in MS reporting services created report that used 2 datasets one for internal one for external. Created 2 report parameters internal and external, if user selected internal then the external datasets visibility property would be set to false etc....
Previous Topic: SQL Query
Next Topic: Query help
Goto Forum:
  


Current Time: Wed Nov 21 02:36:02 CST 2018