Home » Developer & Programmer » Reports & Discoverer » ordering problem in query with hyperlink
ordering problem in query with hyperlink [message #476573] Fri, 24 September 2010 01:02 Go to next message
narang79
Messages: 131
Registered: June 2010
Senior Member
i write following query in report but when i used ordering then it will showing err

select mr.mrn_no,
	trunc(mr.mrn_date) mrn_date,
	trunc(mr.approval_date)grs_approval_date,
	mr.bill_no as challan_no,
	mr.bill_date as challan_date,mr.supp_code,
	pm.party_name,
	mr.store_code,
	mr.manual_gate_sr_no ,
	mr.manual_gate_sr_date,
	case when nvl(trunc(mr.approval_date),trunc(mr.mrn_date)) - mr.manual_gate_sr_date >=2  then
	(nvl(trunc(mr.approval_date),trunc(mr.mrn_date)) - mr.manual_gate_sr_date)  -2
	else 
	0
	end  days_from_grs_approval,
	insp_no,
	trunc(insp_date)insp_date
from material_receipt mr,party_master pm,inspection
where trunc(mr.mrn_date) between :fromdate and :todate
and mr.supp_code = pm.party_code
and mr.ccode	 = pm.ccode
and mr.mrn_no 	 = inspection.mrn_no(+)
and trunc(mr.mrn_date) = trunc(inspection.mrn_date(+))
&approval_status
and mr.ccode	 = :ccode
and mr.fycode 	 = :fycode
and mr.store_code like :store_code
and nvl(mr.grs_type,'p') = :type
&req
order by 2

[Updated on: Fri, 24 September 2010 02:07] by Moderator

Report message to a moderator

Re: ordering problem in query with hyperlink [message #476578 is a reply to message #476573] Fri, 24 September 2010 01:26 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
And the error is ...?
Re: ordering problem in query with hyperlink [message #476583 is a reply to message #476578] Fri, 24 September 2010 01:36 Go to previous messageGo to next message
narang79
Messages: 131
Registered: June 2010
Senior Member
terminated with error: <br>rep-300: sql command not properly ended select mr . mrn_no , trunc ( mr . mrn_date ) mrn_date , trunc ( mr . approval_date ) grs_approval_date , mr . bill_no as challan_no , mr . bill_date as challan_date , mr . supp_code , pm . party_name , mr . store_code , mr . manual_gate_sr_no , mr .


but it showing through param form not from report bulider

but if i remove ordering or removed lexical parameter
then it is showing result

Re: ordering problem in query with hyperlink [message #476590 is a reply to message #476583] Fri, 24 September 2010 02:05 Go to previous messageGo to next message
ramoradba
Messages: 2456
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Quote:
req


1)Please define req,approval_status as a user defines parameter with default value as 1=1
2) replace &req with and &req
&approval_status with and &approval_status


sriram
Re: ordering problem in query with hyperlink [message #476592 is a reply to message #476590] Fri, 24 September 2010 02:11 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
2) might not be true, if lexical parameter definition already contains "and"

Anyway, there's still too few information. Narang79, do you enjoy in solving problems in a matter of days (instead of minutes)? Why don't you provide all relevant information?
Re: ordering problem in query with hyperlink [message #476593 is a reply to message #476592] Fri, 24 September 2010 02:18 Go to previous messageGo to next message
ramoradba
Messages: 2456
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
come on little foot !
I guess/hope OP resolved it already . Smile

sriram
Re: ordering problem in query with hyperlink [message #476596 is a reply to message #476593] Fri, 24 September 2010 02:57 Go to previous messageGo to next message
narang79
Messages: 131
Registered: June 2010
Senior Member
sir i have given u all the information i clarify again

this query is using in a report with lexical parameter
i add ordering in this report

after that when i m trying to open this report through parameter form it shows error

if i removed this ordering then it is running smoothly

or
if i removed lexical parameter then it is running smoothly

i can not understand what is the problem because i have allready used lexical parameter with ordering in so many reports

i can wait sir

but new thing is in this report

this query is also join with second query also

select inspection.insp_no,
trunc(inspection.insp_date)insp_date,
trunc(inspection.approval_date)insp_approval_date,
mrn_no,
trunc(mrn_date)mrn_date
from inspection
where ccode = :ccode
and fycode = :fycode
and nvl(approval_status,'a') = 'a'
order by trunc(inspection.insp_date) desc
Re: ordering problem in query with hyperlink [message #476604 is a reply to message #476596] Fri, 24 September 2010 03:14 Go to previous messageGo to next message
ramoradba
Messages: 2456
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
@ OP
Quote:
if lexical parameter definition already contains "and"

Did you understood what littlefood said ?
What are the values your parameters will pass? does it contain "and" what`s your default value will be ?
IND> select a.name,a.id
  2  from
  3  (select 'sriram' name,1 id from dual
  4  union
  5  select 'Littlefoot',2 from dual) a
  6  where &rec
  7  order by 2;
Enter value for rec: 1=1
old   6: where &rec
new   6: where 1=1

NAME               ID
---------- ----------
sriram              1
Littlefoot          2

2 rows selected.

IND> select a.name,a.id
  2  from
  3  (select 'sriram' name,1 id from dual
  4  union
  5  select 'Littlefoot',2 from dual) a
  6  where id=1 &rec
  7  /
Enter value for rec: 1=1
old   6: where id=1 &rec
new   6: where id=1 1=1
where id=1 1=1
           *
ERROR at line 6:
ORA-00933: SQL command not properly ended


IND> select a.name,a.id
  2  from
  3  (select 'sriram' name,1 id from dual
  4  union
  5  select 'Littlefoot',2 from dual) a
  6  where id=1 &rec
  7  /
Enter value for rec: and 1=1
old   6: where id=1 &rec
new   6: where id=1 and 1=1

NAME               ID
---------- ----------
sriram              1

1 row selected.


Hope you understood this now

sriram
Re: ordering problem in query with hyperlink [message #476607 is a reply to message #476604] Fri, 24 September 2010 03:22 Go to previous messageGo to next message
narang79
Messages: 131
Registered: June 2010
Senior Member
yes sir i understand

& resolved my problem

means &req is also using for ordering
Re: ordering problem in query with hyperlink [message #476639 is a reply to message #476607] Fri, 24 September 2010 05:59 Go to previous messageGo to next message
ramoradba
Messages: 2456
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Quote:
means &req is also using for ordering


I did n`t get you what you are saying ...

sriram
Re: ordering problem in query with hyperlink [message #476641 is a reply to message #476639] Fri, 24 September 2010 06:03 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
I would assume req contains an order by clause - so the resulting query has two.
Re: ordering problem in query with hyperlink [message #476740 is a reply to message #476641] Fri, 24 September 2010 23:14 Go to previous message
narang79
Messages: 131
Registered: June 2010
Senior Member
yes sir same
Previous Topic: Create Master Detail Report using Discover10g
Next Topic: Viewing the last query which reports ran
Goto Forum:
  


Current Time: Thu Mar 28 11:26:12 CDT 2024