Sheldon Livingston
asked on
Pervasive SQL query help
Let's say FieldA is customer name. FieldB can contain "A", "B" or "C".
I'd like the sort order to be by name BUT listing "B" (FieldB) last.
Thank you
I'd like the sort order to be by name BUT listing "B" (FieldB) last.
Thank you
order by case when fieldb ='B' then 1 else 0 end, fieldA
sorry, forgot that persative has a different syntax ( not using it myself ... )
http://help.pervasive.com/plugins/servlet/mobile#content/view/6390355
http://help.pervasive.com/plugins/servlet/mobile#content/view/6390355
and it's as from version 9 ...
What version of Pervasive are you using? I haven't seen "SImba" errors in a long time.
I'm not sure it's going to be possible but I want to test on the version you're using.
I'm not sure it's going to be possible but I want to test on the version you're using.
I see, you put order by select case.
Remove select keyword
Order by case ...
Remove select keyword
Order by case ...
And also it is end and not end select
Pervasive does support both CASE EXPRESSIONS and CASE STATEMENTS ( they are DIFFERENT )
You need a CASE EXPRESSION (and when using these do not re-use the key word "select")
========
perhaps you will share the actual SQL you are using? In the long run it is easier for all partes
e.g. try this:
You need a CASE EXPRESSION (and when using these do not re-use the key word "select")
========
perhaps you will share the actual SQL you are using? In the long run it is easier for all partes
e.g. try this:
SELECT
job
, jstatus
, jdesc
FROM master_jcm_record_1_1
WHERE jtitl4 = 'LS'
ORDER BY
CASE
WHEN jstatus = 'In Progress' THEN 1
ELSE 0
END
, job
ASKER
I think that the Timberline Pervasive ODBC drive doesn't support CASE.
I used the code below to obtain what I need:
I used the code below to obtain what I need:
SELECT
TJOB,
TEXTRA,
TPHASE,
TCAT,
IF(TCAT = 'L', 1, 2) AS "Category Sort"
FROM
CURRENT_JCT_RECORD_1
WHERE
TJOB='03-001' AND
TCAT<>''
ORDER BY
1, 2, 3, 5, 4
thats also a possibility
or you create a view that dies the order by, and your app only does a select * from your_view...
or you create a view that dies the order by, and your app only does a select * from your_view...
If the ODBC driver does not support a case expression, but supports IF() instead try the equivalent IF directly in the ORDER BY.
SELECT
job
, jstatus
, jdesc
FROM master_jcm_record_1_1
WHERE jtitl4 = 'LS'
ORDER BY
IF(jstatus = 'In Progress' , 1, 0)
, job
If that doesn't work then try the use of column number as suggested - but using column number is a problem should anyone change the query down the track.
ps ===
I'm not keen on using order by in a View .. & does Pervasive even allow that?
(Some products won't permit order by in view definitions.)
SELECT
job
, jstatus
, jdesc
FROM master_jcm_record_1_1
WHERE jtitl4 = 'LS'
ORDER BY
IF(jstatus = 'In Progress' , 1, 0)
, job
If that doesn't work then try the use of column number as suggested - but using column number is a problem should anyone change the query down the track.
ps ===
I'm not keen on using order by in a View .. & does Pervasive even allow that?
(Some products won't permit order by in view definitions.)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
try this perhaps?
SELECT
job
, jstatus
, jdesc
FROM (
SELECT
job
, jstatus
, jdesc
, IF(jstatus = 'In Progress', 1, 0) sortby
FROM master_jcm_record_1_1
WHERE jtitl4 = 'LS'
) d
ORDER BY
sortby
;
ASKER
The sort desired was pretty simple (list all then B) so I simply used the IF statement directly in the SELECT.
ok. thought you were still stuck.
curious that a standard case expression should not work but IF() does, oh well.
curious that a standard case expression should not work but IF() does, oh well.
ASKER
Neither IF or CASE work in the ORDER BY clause implementation of Timberline's ODBC driver... supposedly in a future date. Thus I did it in the SELECT portion.
ASKER
Solved myself