Home » Developer & Programmer » Forms » order by with substr
order by with substr [message #264760] Tue, 04 September 2007 08:43 Go to next message
marsguy85
Messages: 14
Registered: August 2007
Junior Member
i want to get the result or a query by date .

i have a button which when clicked gives out the query result in order of desc date(when done) .

the button works for normal but if i do a query then it does not work.

example if i query id > 40
then it gives me all ids greater than 40 but not in date order.


i know there gotta be a substr and instr but i am new and dont know how .


please help me !


cheers
ash
Re: order by with substr [message #264763 is a reply to message #264760] Tue, 04 September 2007 08:55 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
Precious little helpful information given, so I am going to say that you do not have an order by clause, so use an order by clause in the block properties.

N.B.: do NOT sort by any substr or instr of a DATE column.

[Updated on: Tue, 04 September 2007 08:56]

Report message to a moderator

Re: order by with substr [message #264771 is a reply to message #264760] Tue, 04 September 2007 09:35 Go to previous messageGo to next message
marsguy85
Messages: 14
Registered: August 2007
Junior Member
hi mate , yes i have a order by clause .

here is my code :


DECLARE
temp varchar2(500);


BEGIN
temp := get_block_property('results',last_query);








set_block_property('results',last_query,temp);

message(temp);
set_block_property('results',ORDER_BY,'id') ;

--set_block_property('results',default_where,'result is null or result = ''Fail''');

execute_query;
res_lov;

END;
Re: order by with substr [message #264772 is a reply to message #264760] Tue, 04 September 2007 09:38 Go to previous messageGo to next message
marsguy85
Messages: 14
Registered: August 2007
Junior Member
that code is on ' when mouse clicked' for button 'order'.


thats if a normal checks is done but if a query is done i want the button order to dynamically put the query out results in order of date.


cheers
Re: order by with substr [message #264801 is a reply to message #264760] Tue, 04 September 2007 10:56 Go to previous messageGo to next message
scorpio_biker
Messages: 154
Registered: November 2005
Location: Kent, England
Senior Member
Hi,

You do have an order by clause but you are ordering by id

 set_block_property('results',ORDER_BY,'id') ; 


You need to change this to the date field you want to order by

Re: order by with substr [message #264979 is a reply to message #264760] Wed, 05 September 2007 03:18 Go to previous messageGo to next message
marsguy85
Messages: 14
Registered: August 2007
Junior Member
Hi,

I have attached the file , kindly see the triggers on button 'order' .
mouse click and mouse double click.


in normal condition the order by clause works fine but when i do query then the order by clause does not work.


i have user a substr and instr , but its giving me error , maybe syntax.


please help


cheers
ash.
  • Attachment: shc.fmb
    (Size: 200.00KB, Downloaded 142 times)
Re: order by with substr [message #264988 is a reply to message #264760] Wed, 05 September 2007 03:35 Go to previous messageGo to next message
scorpio_biker
Messages: 154
Registered: November 2005
Location: Kent, England
Senior Member
The information you are giving is a little confusing.

You have an order by clause declared in both triggers, for id in the when-mouse-click trigger and for date in the when-mouse-doubleclick.

However you keep talking about the substr and instr possibly giving errors, but those are used on the construction of the where clause.

Also - can you clarify 'normal checks' and 'query' in terms of how you expect the form to function?
Re: order by with substr [message #265063 is a reply to message #264760] Wed, 05 September 2007 06:06 Go to previous messageGo to next message
marsguy85
Messages: 14
Registered: August 2007
Junior Member
Okay,

normal checks is as per say , when today is clicked a list of ids and checks come up . and then i have to put in the result .



when query is clicked i get all the results for the query i put in. example i query '>44' then all ids with 44 comes up irrespective of result , date, etc .

so here now i need to use the order button . but if clicked now , gives the result of query from 1 to 49 and not 45 - 49.


order button works well when no query done but when query done then it does not work on the query out results but it gets all the results in order by (id or date which ever clicked).



hope that make things clear.

cheers
Re: order by with substr [message #265120 is a reply to message #264760] Wed, 05 September 2007 08:15 Go to previous messageGo to next message
scorpio_biker
Messages: 154
Registered: November 2005
Location: Kent, England
Senior Member
I think I might have a vague grasp of it now.

I think your problem is with your construction of your where clause.

You use the code
temp := SUBSTR(temp,INSTR(temp),instr(temp));


From what I can make out you are trying to use the previous where clause in the new query having got it from the block properties. However your use of instr is not quite right.

For example the get block properties returns the full sql string. So temp would contain something like
temp := 'select field1 from table where field1 = 1'


You want to extract the where clause from this string and apply it as the default where. Firstly use instr to find the where string in that sql string - syntax is instr( string1, string2 [, start_position [, nth_appearance ] ] )
instr(temp,'where')

This gives the position of the word where in the string.

Then you can link that with the substr
substr(temp,instr(temp,'where'))


But remember you don't need the word where in the default where clause so you need to add 6 to the instr
substr(temp,instr(temp,'where')+6)


Hopefully I've understood your problem OK?

If I have also bear in mind that you may need to strip off any subsequent query clauses such as your order by as the last_query returns the whole sql string. Just use the same logic to locate the order by and use the instr figure to help calculate the length value in the substr.
Re: order by with substr [message #265400 is a reply to message #264760] Thu, 06 September 2007 04:40 Go to previous message
marsguy85
Messages: 14
Registered: August 2007
Junior Member
Yeah thanks for your help , i tried some combinations and it works now .

cheers m8
Previous Topic: Problem with resolution on windows 98
Next Topic: Report from form
Goto Forum:
  


Current Time: Sat Dec 03 06:16:14 CST 2016

Total time taken to generate the page: 0.07997 seconds