Solved

Pervasive SQL query help

Posted on 2016-07-26
18
30 Views
Last Modified: 2016-08-12
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
0
Comment
Question by:classnet
  • 7
  • 6
  • 4
  • +1
18 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 41729900
order by case when fieldb ='B' then 1 else 0 end, fieldA
0
 

Author Comment

by:classnet
ID: 41729939
Guy... you sure Pervasive likes that?  Not sure if Pervasive can do case when:
Error
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 41729954
sorry, forgot that persative has a different syntax ( not using it myself ... )
http://help.pervasive.com/plugins/servlet/mobile#content/view/6390355
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 41729957
and it's as from version 9 ...
0
 

Author Comment

by:classnet
ID: 41729968
Thank you for the effort... still get an error.
Error
0
 
LVL 18

Expert Comment

by:mirtheil
ID: 41730060
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.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 41730153
I see, you put order by select case.
Remove select keyword

Order by case ...
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 41730155
And also it is end and not end select
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 41731049
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

0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:classnet
ID: 41731063
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

0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 41731087
thats also a possibility
or you create a view that dies the order by, and your app only does a select * from your_view...
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 41731121
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.)
0
 

Accepted Solution

by:
classnet earned 0 total points
ID: 41738733
I was able to use the IF statement in the SELECT portion of the code... couldn't get it to work in the ORDER BY clause.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 41738819
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

0
 

Author Comment

by:classnet
ID: 41738821
The sort desired was pretty simple (list all then B) so I simply used the IF statement directly in the SELECT.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 41738827
ok. thought you were still stuck.
curious that a standard case expression should not work but IF() does, oh well.
0
 

Author Comment

by:classnet
ID: 41738876
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.
0
 

Author Closing Comment

by:classnet
ID: 41753521
Solved myself
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now