OraFAQ Forum: Reports & Discoverer

Home » Developer & Programmer » Reports & Discoverer » hi

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

hi [message #207729]

Wed, 06 December 2006 16:12

selva_lee
Messages: 1
Registered: December 2006

Junior Member

i have made an report with existing report in report2.5.In that i have added a above query to the main query.But when iam try to put the query inside the SQL DATA MODEL its says "REP-0496 :Internal error Parsing sql".Below is my query
SELECT r.Responsibility_Id,
m.User_Menu_Name,
r.Application_Id,
r.Responsibility_Name,
m.User_Menu_Name,
m.Description User_desc,
m.Menu_Id
FROM fnd_Responsibility_vl r,
fnd_Menus_vl m,
fnd_Application a
WHERE r.Application_Id = a.Application_Id
AND a.Application_Short_Name = Nvl(:P_APPLICATION_SHORTNAME,a.Application_Short_Name)
AND r.Responsibility_Name = Nvl(:P_RESPONSIBILITY_NAME,r.Responsibility_Name)
AND r.Menu_Id = m.Menu_Id
AND r.Start_Date IS NOT NULL
AND DECODE(:P_CHECK,'Y',Nvl(r.End_Date,SYSDATE),
'N',Trunc(SYSDATE),
Trunc(SYSDATE)) >= DECODE(:P_CHECK,'Y',Trunc(SYSDATE),
'N',Trunc(SYSDATE),
Trunc(SYSDATE))
AND DECODE(:P_USER,'Y',1,
'N',0,
0) <= DECODE(:P_USER,'Y',(SELECT COUNT(* )
FROM fnd_User_Responsibility Fur,
fnd_User fu
WHERE Fur.User_Id = fu.User_Id
AND fur.responsibility_id=r.responsibility_id
AND fu.User_Name <> ('AUTOINSTALL')
AND Nvl(fu.End_Date,SYSDATE) >= SYSDATE
AND fu.Start_Date IS NOT NULL ),
'N',1,
0)

Can any one help on this that how to rectify this error.And also i have attached the report FNDMNMNU_4.rdf.This is very urgent.

Regards,
selva

Attachment: FNDMNMNU_04.rdf
(Size: 80.00KB, Downloaded 1179 times)

Report message to a moderator

Re: hi [message #207733 is a reply to message #207729]

Wed, 06 December 2006 16:40

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

I'm afraid the bottom part of the WHERE clause won't compile (the one with DECODE(... (SELECT ...)) - it should be vice versa: SELECT DECODE(...), for example

AND DECODE(:p_user, 1, 1, 0) <= (SELECT DECODE(:p_user, 1, COUNT(*), 0, 1, 0)
                                 FROM fnd_user_responsibility fur, fnd_user fu
                                 WHERE ...
                                )

Note that I've changed parameter values (Y -> 1, N -> 0) because, if you leave it the way you did, you're about to end upi with the "wrong number or types of arguments". Of course, it doesn't have to be (1, 0) combination, but must be some numeric combination.

Unless, of course, I'm wrong about it.

Report message to a moderator

Previous Topic:	Problem in Viewing the Graphs in Application Server10g
Next Topic:	Euro Sign

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Nov 26 15:25:14 CST 2024