• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 78
  • Last Modified:

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
0
Sheldon Livingston
Asked:
Sheldon Livingston
  • 7
  • 6
  • 4
  • +1
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
order by case when fieldb ='B' then 1 else 0 end, fieldA
0
 
Sheldon LivingstonConsultantAuthor Commented:
Guy... you sure Pervasive likes that?  Not sure if Pervasive can do case when:
Error
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
sorry, forgot that persative has a different syntax ( not using it myself ... )
http://help.pervasive.com/plugins/servlet/mobile#content/view/6390355
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
and it's as from version 9 ...
0
 
Sheldon LivingstonConsultantAuthor Commented:
Thank you for the effort... still get an error.
Error
0
 
mirtheilCommented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I see, you put order by select case.
Remove select keyword

Order by case ...
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
And also it is end and not end select
0
 
PortletPaulfreelancerCommented:
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
 
Sheldon LivingstonConsultantAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
PortletPaulfreelancerCommented:
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
 
Sheldon LivingstonConsultantAuthor Commented:
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
 
PortletPaulfreelancerCommented:
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
 
Sheldon LivingstonConsultantAuthor Commented:
The sort desired was pretty simple (list all then B) so I simply used the IF statement directly in the SELECT.
0
 
PortletPaulfreelancerCommented:
ok. thought you were still stuck.
curious that a standard case expression should not work but IF() does, oh well.
0
 
Sheldon LivingstonConsultantAuthor Commented:
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
 
Sheldon LivingstonConsultantAuthor Commented:
Solved myself
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

  • 7
  • 6
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now