The Forums on slxdeveloper.com are now retired. The forum archive will remain available for the time being. Thank you for your participation on slxdeveloper.com!
|
|
Query builder question
Posted: 16 Apr 08 1:50 PM
|
I would like to create a group in the 7.0 lan client that looks at two dates in the same table. I would like to sort by date1 first, but if no data sort by Date2.
So this is how I would like to see th data sorted:
1 date1- 1/1/08 date2- null or has other date
2 date1 -1/2/08 date2 null or has other date
3 date1 - null date2 - 1/3/08
4 date1 1/4/08 date2 - null or has other date
Thanks |
|
|
|
Re: Query builder question
Posted: 16 Apr 08 2:40 PM
|
Very interesting. How I would normally do this in SQL in general is use a CASE statement to check for NULL values and when true use the infor from the second date field. What would you want if both date fields are NULL?
SELECT
CASE datefield1
WHEN NULL THEN datefield2
ELSE datefield1
END AS datefield
FROM tablename
ORDER BY 1
or something close to that.
Problem is this is not possible within the client. At least I don't think there is a way. The client is not capable of doing conditional sorts either.
John G. |
|
|
|
Re: Query builder question
Posted: 16 Apr 08 2:47 PM
|
| Ah yes the SQL case - good thought.. I guess if both were null sort by date1 - whatever would stick it at the end in this case. Now, I don't suppose I can force feed this to the query builder.. I don't expect it will like case statements in it's sql. |
|
|
|
Re: Query builder question
Posted: 16 Apr 08 6:26 PM
|
I can't imagine that you will get this into the query builder.
I haven't tried this, but maybe you could add a calculated field that nabs the date you want and sort by that? |
|
|
|
Re: Query builder question
Posted: 17 Apr 08 1:22 AM
|
| That's exactly how it's done - put the content of the CASE into the calc field and then use the calc field. |
|
|
|
Re: Query builder question
Posted: 17 Apr 08 6:40 AM
|
| Hmmm... I'll have to try that out. I've run into this kind of thing in the past. |
|
|
|