OraFAQ Forum: Forms » Order by

Home » Developer & Programmer » Forms » Order by

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

Order by [message #284260]

Thu, 29 November 2007 04:33

kuwait
Messages: 55
Registered: October 2007

Member

Hi all,

I have a problem in ordering a block by using order by statement
I want to order a data block according to the field trnamea
I used order by and run the form it is still miss ordered as if no order by there
this is my code I've written it in the post-query of the data block crsinvoiced:

begin
    select decode(a.trnamea, '',b.titlee||a.trnamee, b.titlea||a.trnamea)
    into :crsinvoiced.namea
    from ads.trainees a, ads.title b
    where a.trno=:crsinvoiced.custid
    and a.tid=b.titleid
    order by trnamea asc; --This the order by that is not working


exception
    when others then
    null;
end;

Please can you help?
Thanx

Report message to a moderator

Re: Order by [message #284275 is a reply to message #284260]

Thu, 29 November 2007 05:09

scorpio_biker
Messages: 154
Registered: November 2005
Location: Kent, England

Senior Member

Hi,

You say this code is in a post query trigger? If you are trying to order the block you probably need to put the order by on the query specified for the block.

Report message to a moderator

Re: Order by [message #284283 is a reply to message #284275]

Thu, 29 November 2007 05:29

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

Senior Member
Account Moderator

Exactly; put "trnamea" into the ORDER BY clause of the data block Property Palette.

Is there a special reason you are populating a block like this? It will change only the first record's items. As far as I can tell (with the information you provided), this POST-QUERY trigger should be removed (as well as that silly WHEN OTHERS exception handler which doesn't help at all, but - eventually - might make things more difficult).

Report message to a moderator

Re: Order by [message #285007 is a reply to message #284283]

Mon, 03 December 2007 01:59

kuwait
Messages: 55
Registered: October 2007

Member

Hello thanks for ur replies,

I've tried to set the property order by trnamea but it cannot be done it gives me ORA-00904: invalid column name, because trnamea is not defined for that data block it's in another table which is trainees that is related to the data block I want to order by (trno=custid), so that I used a post query but as I said before it's not working it execute the records miss-ordered for trnamea but ordered for custid.

Regards

Report message to a moderator

Re: Order by [message #285016 is a reply to message #285007]

Mon, 03 December 2007 02:07

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

Senior Member
Account Moderator

So you have a basket full of apples and want to order them by size of pears.

It seems that you'll have to find out another way to do that; would creation of a view (which would join those two tables by "trno = custid") be a solution?

Report message to a moderator

Re: Order by [message #285027 is a reply to message #284260]

Mon, 03 December 2007 02:39

kuwait
Messages: 55
Registered: October 2007

Member

Thanks Littlefoot
I've already made the join a.trno=:crsinvoiced.custid in the post query and it's working fine just I want to order it, I think there's a way rather than making a view

begin
    select decode(a.trnamea, '',b.titlee||a.trnamee, b.titlea||a.trnamea)
    into :crsinvoiced.namea
    from ads.trainees a, ads.title b
    where a.trno=:crsinvoiced.custid --This is the join condition it's working fine
    and a.tid=b.titleid
    order by trnamea asc; --This the order by that is not working


exception
    when others then
    null;
end;

Thanx

Report message to a moderator

Re: Order by [message #285041 is a reply to message #285027]

Mon, 03 December 2007 03:11

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

Senior Member
Account Moderator

You only *think* that POST-QUERY works fine - it does not. As it fires ONCE PER RECORD (i.e. if there were 7 records fetched in a block, this trigger would fire 7 times), ORDER BY clause is a surplus - it does nothing (as if you use ORDER BY when selecting from the DUAL table) - ordering a single record doesn't make sense and is useless.

Report message to a moderator

Re: Order by [message #285068 is a reply to message #285041]

Mon, 03 December 2007 05:08

kuwait
Messages: 55
Registered: October 2007

Member

Thankyou all Smile

Smile

I've solved the problem.

Report message to a moderator

Re: Order by [message #285074 is a reply to message #285068]

Mon, 03 December 2007 05:26

Maaher
Messages: 7065
Registered: December 2001

Senior Member

You might want to get rid of the WHEN OTHERS THEN NULL. You are hiding error information. Consider an error logging procedure.

MHE

Report message to a moderator

Re: Order by [message #285077 is a reply to message #285074]

Mon, 03 December 2007 05:53

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

Senior Member
Account Moderator

@Kuwait, would you mind to post the solution, please?

@Maarten: he might (as suggested in message #284283), but (apparently) doesn't want to.

Report message to a moderator

Re: Order by [message #285078 is a reply to message #285077]

Mon, 03 December 2007 05:55

Maaher
Messages: 7065
Registered: December 2001

Senior Member

Sorry, Littlefoot. It seems that I'm not yet fully awake.

MHE

Report message to a moderator

Re: Order by [message #285082 is a reply to message #285078]

Mon, 03 December 2007 06:01

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

Senior Member
Account Moderator

I think I could go to bed again. This rainy weather is killing me.

Report message to a moderator

Re: Order by [message #285103 is a reply to message #284260]

Mon, 03 December 2007 08:31

MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA

Senior Member

Better than the icy weather here :}

Report message to a moderator

Re: Order by [message #285218 is a reply to message #285103]

Tue, 04 December 2007 00:00

kuwait
Messages: 55
Registered: October 2007

Member

Hi all,

I Altered the table crsinvoiceid, added the column trnamea, populated it, and set the order by property to be by trnamea.

Thank you all for your efforts

[Updated on: Tue, 04 December 2007 00:00]

Report message to a moderator

Re: Order by [message #285274 is a reply to message #285218]

Tue, 04 December 2007 03:25

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

Senior Member
Account Moderator

Fine. You have fixed one problem, and opened the gate for new ones to come. I hope you realize that you'll have to maintain this information in two tables, that you've abandoned normalization, and who knows what else.

Probably the worst solution, in my opinion.

Report message to a moderator

Re: Order by [message #285278 is a reply to message #285274]

Tue, 04 December 2007 03:31

kuwait
Messages: 55
Registered: October 2007

Member

You are right Littlefoot worst but it's working, we'll live with it if somthing goes wrong in the future I'll restore the old one.

Thanks

Report message to a moderator

Previous Topic:	make HOST cmd
Next Topic:	Make Privileges to admin and different users in forms

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Sep 27 05:06:53 CDT 2025