[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 52
  • 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
classnet
Asked:
classnet
  • 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
 
classnetAuthor 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
and it's as from version 9 ...
0
 
classnetAuthor 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
 
PortletPaulCommented:
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
 
classnetAuthor 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
 
PortletPaulCommented:
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
 
classnetAuthor 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
 
PortletPaulCommented:
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
 
classnetAuthor Commented:
The sort desired was pretty simple (list all then B) so I simply used the IF statement directly in the SELECT.
0
 
PortletPaulCommented:
ok. thought you were still stuck.
curious that a standard case expression should not work but IF() does, oh well.
0
 
classnetAuthor 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
 
classnetAuthor Commented:
Solved myself
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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