Link to home
Start Free TrialLog in
Avatar of Sheldon Livingston
Sheldon LivingstonFlag for United States of America

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
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

order by case when fieldb ='B' then 1 else 0 end, fieldA
Avatar of Sheldon Livingston

ASKER

Guy... you sure Pervasive likes that?  Not sure if Pervasive can do case when:
User generated image
sorry, forgot that persative has a different syntax ( not using it myself ... )
http://help.pervasive.com/plugins/servlet/mobile#content/view/6390355
and it's as from version 9 ...
Thank you for the effort... still get an error.
User generated image
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 see, you put order by select 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:

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

Open in new window

I think that the Timberline Pervasive ODBC drive doesn't support CASE.

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

Open in new window

thats also a possibility
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.)
ASKER CERTIFIED SOLUTION
Avatar of Sheldon Livingston
Sheldon Livingston
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
;

Open in new window

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.
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.
Solved myself