Home » Developer & Programmer » Forms » Order by
icon5.gif  Order by [message #284260] Thu, 29 November 2007 04:33 Go to next message
kuwait
Messages: 52
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
Re: Order by [message #284275 is a reply to message #284260] Thu, 29 November 2007 05:09 Go to previous messageGo to next message
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.
Re: Order by [message #284283 is a reply to message #284275] Thu, 29 November 2007 05:29 Go to previous messageGo to next message
Littlefoot
Messages: 20895
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).
Re: Order by [message #285007 is a reply to message #284283] Mon, 03 December 2007 01:59 Go to previous messageGo to next message
kuwait
Messages: 52
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
Re: Order by [message #285016 is a reply to message #285007] Mon, 03 December 2007 02:07 Go to previous messageGo to next message
Littlefoot
Messages: 20895
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?
Re: Order by [message #285027 is a reply to message #284260] Mon, 03 December 2007 02:39 Go to previous messageGo to next message
kuwait
Messages: 52
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
Re: Order by [message #285041 is a reply to message #285027] Mon, 03 December 2007 03:11 Go to previous messageGo to next message
Littlefoot
Messages: 20895
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.
Re: Order by [message #285068 is a reply to message #285041] Mon, 03 December 2007 05:08 Go to previous messageGo to next message
kuwait
Messages: 52
Registered: October 2007
Member
Thankyou all Smile I've solved the problem.
Re: Order by [message #285074 is a reply to message #285068] Mon, 03 December 2007 05:26 Go to previous messageGo to next message
Maaher
Messages: 7062
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
Re: Order by [message #285077 is a reply to message #285074] Mon, 03 December 2007 05:53 Go to previous messageGo to next message
Littlefoot
Messages: 20895
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.
Re: Order by [message #285078 is a reply to message #285077] Mon, 03 December 2007 05:55 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Sorry, Littlefoot. It seems that I'm not yet fully awake.

MHE
Re: Order by [message #285082 is a reply to message #285078] Mon, 03 December 2007 06:01 Go to previous messageGo to next message
Littlefoot
Messages: 20895
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I think I could go to bed again. This rainy weather is killing me.
Re: Order by [message #285103 is a reply to message #284260] Mon, 03 December 2007 08:31 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
Better than the icy weather here :}
Re: Order by [message #285218 is a reply to message #285103] Tue, 04 December 2007 00:00 Go to previous messageGo to next message
kuwait
Messages: 52
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 Go to previous messageGo to next message
Littlefoot
Messages: 20895
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.
Re: Order by [message #285278 is a reply to message #285274] Tue, 04 December 2007 03:31 Go to previous message
kuwait
Messages: 52
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
Previous Topic: make HOST cmd
Next Topic: Make Privileges to admin and different users in forms
Goto Forum:
  


Current Time: Mon Dec 05 21:35:41 CST 2016

Total time taken to generate the page: 1.14102 seconds