Home » SQL & PL/SQL » SQL & PL/SQL » using in() function
using in() function [message #286849] Mon, 10 December 2007 04:27 Go to next message
annu-agi
Messages: 203
Registered: July 2005
Location: Karachi
Senior Member

i made one report and its perfectly work if i use static values on where conditions and format which is i set isnt change but when i change coding and add user parameters the report format change in charector mode and all the formats goes abnormal . One more thing i m using IN() function in the query . If i use user parameter and just pass one value in IN() function it works fine but when i pass mutliple values report doesnt work and nothing show ..
Experts see my query and tell me how can i use IN()function with multiple selections in reports users parameters what i m doing wrong and what goes wrong with my format and how can i get back my format setting . I tried this query in SQL PLUS with the help of & parameter and its working fine , even on multi selection .

suggust me and help me out of this problem ..

Thanks in advance

select a.d_code, a.name, B.CODE STAT_CD ,B.DESCRIPT STAT_NM, c.code ROUTE_CD, C.DESCRIPT ROUTE_NM,
A.VISIT1,A.VISIT2,A.VISIT3,A.VISIT4,A.VISIT5,A.VISIT6,A.VISIT7,A.PHONE, A.ADDRESS1||' '||
A.ADDRESS2||' '||A.ADDRESS3
   from 
(SELECT * FROM DEALER_INFO WHERE (VISIT1||VISIT2||VISIT3||VISIT4||VISIT5||VISIT6||VISIT7) IS NOT NULL ) a,
(select CODE, DESCRIPT  from sld_info WHERE KEYFIELD='T') B,
(select CODE, STATION, DESCRIPT from sld_info WHERE KEYFIELD='R') c
where 
B.CODE=SUBSTR(D_cODE,1,2) AND
B.CODE='01' AND
c.station=B.CODE AND
C.CODE=SUBSTR(D_CODE,3,6) AND
C.CODE IN ('KHA11A','KHA11B','KHA11C','KHA11D','KHA11E','KHA11F','KHA11G','DMZ63I','GSN13A')
ORDER BY 1

if i change coding and add user parametres replace

B.CODE=:p_station_Cd AND
c.station=B.CODE AND
C.CODE=SUBSTR(D_CODE,3,6) AND
C.CODE IN (:p_route_Cd)
ORDER BY 1

Re: using in() function [message #286854 is a reply to message #286849] Mon, 10 December 2007 04:39 Go to previous message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
AskTom: varying elements in IN list

Regards
Michel
Previous Topic: Dependency
Next Topic: Cacading Triggers
Goto Forum:
  


Current Time: Thu Dec 08 18:47:06 CST 2016

Total time taken to generate the page: 0.09982 seconds