Home » Developer & Programmer » Reports & Discoverer » hi
hi [message #207729] Wed, 06 December 2006 16:12 Go to next message
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
Re: hi [message #207733 is a reply to message #207729] Wed, 06 December 2006 16:40 Go to previous message
Littlefoot
Messages: 20900
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.
Previous Topic: Problem in Viewing the Graphs in Application Server10g
Next Topic: Euro Sign
Goto Forum:
  


Current Time: Thu Dec 08 10:16:48 CST 2016

Total time taken to generate the page: 0.09225 seconds