OraFAQ Forum: Reports & Discoverer » report does'nt work on IN() function

Home » Developer & Programmer » Reports & Discoverer » report does'nt work on IN() function (dev6i, ora9i)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

report does'nt work on IN() function [message #286791]

Mon, 10 December 2007 02:32

annu-agi
Messages: 238
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

Littlefoot
Messages: 21811
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)

Report message to a moderator

Previous Topic:	report layout
Next Topic:	Preview and Print Report

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Jun 11 15:56:31 CDT 2024