Home » Developer & Programmer » Reports & Discoverer » report does'nt work on IN() function (dev6i, ora9i)
report does'nt work on IN() function [message #286791] Mon, 10 December 2007 02:32 Go to next message
annu-agi
Messages: 199
Registered: July 2005
Location: Karachi
Senior Member

hi experts

i made on 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 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 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 it working fine , even on multi selection . i also attach BMP file for see the format change .

suggust me and help me out of this problem ..

Thanks in advance



see the coding
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



[EDITED by LF: removed a huge BMP file. Please, read the OraFAQ Forum Guide to learn which image formats are acceptable]

[Updated on: Mon, 10 December 2007 04:57] by Moderator

Report message to a moderator

Re: report does'nt work on IN() function [message #286864 is a reply to message #286791] Mon, 10 December 2007 04:56 Go to previous message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
One way might be storing these values into another table (let's call it 'code_table') and use it in the WHERE clause as
WHERE c.code IN (SELECT t.code FROM code_table t)
Previous Topic: report layout
Next Topic: Preview and Print Report
Goto Forum:
  


Current Time: Sat Dec 03 03:49:26 CST 2016

Total time taken to generate the page: 0.04825 seconds