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: 33
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: 1851
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: 4821
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 message
bobghw
Messages: 33
Registered: July 2012
Member
Resolved already, but thanks for replies .
Previous Topic: How to get the functions name used by a column in the whole database
Next Topic: BULK COLLECT ISSUE
Goto Forum:
  


Current Time: Tue Feb 20 00:36:26 CST 2018

Total time taken to generate the page: 0.01548 seconds