OraFAQ Forum: Reports & Discoverer » Want to order by at run time?but problem is this that i have 2 different datatypes

Home » Developer & Programmer » Reports & Discoverer » Want to order by at run time?but problem is this that i have 2 different datatypes

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

Want to order by at run time?but problem is this that i have 2 different datatypes [message #205271]

Fri, 24 November 2006 02:13

gladiator
Messages: 21
Registered: April 2006
Location: Lahore, Pakistan

Junior Member

Hi Dears,

I have a report with group above latyout. and in parent group i have 2 fields vendor_name and vendor_id, i want to sort the report at run time, user will select the option on which he wana sort it.

Now the problem is this that order by is not working in the query, the column that is placed at the top in parent group layout report is automatically sorted at that column.

I used the decode option to select the column at run time but decode set the datatype of all columns to the 1st column in decode e.g

decode(:user_option,'ven_name',vendor_name,vendor_id) as order

In above case the datatype of vendor_id is also converted to character.

and when i take vendor_id 1st in decode like folowing it sets the datatype of decode to number and tries to converts the vendor_name to number and if vendor_name is returned from decode it gives error.

I also try to use the CASE WHEN statement but it also dont allow to use different datatypes.

If any one have the solution/workaround, Please reply me.

MANY MANY THANKS FOR YOUR PRECIOUS TIME.

BEST REGARDS,
USMAN.

Report message to a moderator

Re: Want to order by at run time?but problem is this that i have 2 different datatypes [message #205355 is a reply to message #205271]

Fri, 24 November 2006 05:48

sandeepk7
Messages: 137
Registered: September 2006

Senior Member

HI,
Try below option...

Write your query in following way.

Select * from ur_table_name 
where  Ur_conditions................
&orderby

and pass user selected option to this lexical parameter. Like

:orderby:=' Order By vendor_name';
or 
:orderby:=' Order By vendor_id';

Hope it will work for u

Sandy

Report message to a moderator

Re: Want to order by at run time?but problem is this that i have 2 different datatypes [message #206268 is a reply to message #205355]

Wed, 29 November 2006 06:26

gladiator
Messages: 21
Registered: April 2006
Location: Lahore, Pakistan

Junior Member

Hi There,

Tnx for reply.

But i am using group above so the order by clause is not working there, report is only sorted on the 1st column in the parent group.

plz give it a try, and then reply me.

BEST REGARDS,
USMAN.

Report message to a moderator

Re: Want to order by at run time?but problem is this that i have 2 different datatypes [message #206278 is a reply to message #206268]

Wed, 29 November 2006 07:22

ab_trivedi
Messages: 460
Registered: August 2006
Location: Pune, India

Senior Member

hi usman,

i am sending you example as an attachment. Steps are :

1) write the query with lexical parameter like &ordby

2) Add list of values in parameter field

3) Select the option

4) Run report

Bye
Ashu

Attachment: lexical_pa.RDF
(Size: 48.00KB, Downloaded 1231 times)

Report message to a moderator

Re: Want to order by at run time?but problem is this that i have 2 different datatypes [message #206281 is a reply to message #206278]

Wed, 29 November 2006 07:31

gladiator
Messages: 21
Registered: April 2006
Location: Lahore, Pakistan

Junior Member

Hi Dear,

I am sending back you this report with some changes in the data group, i did't changed any thing in query just created a parent group and moved enpno and ename in above group ename at the top of parent group now this report is only sorted at ename, order by clause in query is not working, plz check out this attachment.

Tnx for ur time.

BEST RAGARDS,
USMAN

Attachment: lexical_pa.RDF
(Size: 80.00KB, Downloaded 1197 times)

Report message to a moderator

Re: Want to order by at run time?but problem is this that i have 2 different datatypes [message #206440 is a reply to message #206281]

Thu, 30 November 2006 01:23

ab_trivedi
Messages: 460
Registered: August 2006
Location: Pune, India

Senior Member

hi,

but why r u taking the unique detials of an employee in repeating frame ...

i am trying presently busy send u in more details ...

bye
Ashu

Report message to a moderator

Re: Want to order by at run time?but problem is this that i have 2 different datatypes [message #206517 is a reply to message #206281]

Thu, 30 November 2006 07:17

ab_trivedi
Messages: 460
Registered: August 2006
Location: Pune, India

Senior Member

hi usman,

sending you another rdf file as attachment. i tried your an come to the conclsion that:

1) If you will not take more than one group then you can order by on more than one columns i.e order by ename, order by sal, order by empno etc.

2) But when you will take more than 1 group then only the first column in the group can only be ordered in the report . So it is not correct that only order by ename is happenong if you move empno above then order by empno is possible.

I am sending you another version of rdf ... see it and if you found any solution then return to me. I will also try ...

bye
Ashu

Attachment: usmlexical_pa.RDF
(Size: 36.00KB, Downloaded 1110 times)

Report message to a moderator

Re: Want to order by at run time?but problem is this that i have 2 different datatypes [message #206932 is a reply to message #205271]

Sat, 02 December 2006 08:08

gladiator
Messages: 21
Registered: April 2006
Location: Lahore, Pakistan

Junior Member

HELLO,

Thanks for Ur time.

Problem is still there, I am still working on it.When i got the solution i will share with you.

BEST REGARDS,
Usman.

Report message to a moderator

Re: Want to order by at run time?but problem is this that i have 2 different datatypes [message #207263 is a reply to message #206932]

Mon, 04 December 2006 21:30

ab_trivedi
Messages: 460
Registered: August 2006
Location: Pune, India

Senior Member

hi usman,

how are you and what about the problem ...

Bye
Ashu

Report message to a moderator

Re: Want to order by at run time?but problem is this that i have 2 different datatypes [message #207293 is a reply to message #205271]

Mon, 04 December 2006 23:37

gladiator
Messages: 21
Registered: April 2006
Location: Lahore, Pakistan

Junior Member

Hello Ashu,

I am sorry yaar did't got the time to think. lil busy in the work well TNX for the reminder well i will try my best to spare some time today for this problem,

You also keep trying.I am sure u will definitly find a good solution.

WISH U GOOD LUCK

Take good care,
GOOD BYE,
Usman.

Report message to a moderator

Previous Topic:	Ladger balance ?? urgent
Next Topic:	Total in Formula

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Nov 26 15:38:15 CST 2024