| order by with substr [message #264760] |
Tue, 04 September 2007 08:43  |
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  |
joy_division
Messages: 4963
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 |
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 |
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 |
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 #264988 is a reply to message #264760] |
Wed, 05 September 2007 03:35 |
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 |
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 |
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 ] ] )
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.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
