Search code examples
javajdbcswitch-statementcasepervasive

Case statement in Pervasive 8

I want to use a case statement in Pervasive as it lacks support for Coalesce as well. But it seems like Pervasive 8 also lacks support for Case statements.

So i wanted to check if there is an alternate solution to my problem.

SELECT top 100 
STOCKTR.PHEADPR,
'' as tom
,case Pheadpr.BLNO when <> '' then Pheadpr.BLNO else STOCKTR.PHEADPR end as BLNO
,Pheadpr.custno
,Pheadpr.cust_name
,Pheadpr.company_name
,Pheadpr.company_city
,Pheadpr.invno
,Pheadpr.curr_code
,STOCKTR.RECID
,STOCKTR.ARTNO
,STOCKTR.DATE
,STOCKTR.QTY_PCS
,STOCKTR.PRICE_SEK_PCS
,STOCKTR.ULAND  
FROM STOCKTR INNER JOIN PHEADPR
ON PHEADPR.NO = STOCKTR.PHEADPR
WHERE STOCKTR.TRCODE='02' AND STOCKTR.PHEADPR <> '0'
order by STOCKTR.DATE desc

So my question is focused on the fourth row in my select statement that says

case Pheadpr.BLNO when <> '' then Pheadpr.BLNO else STOCKTR.PHEADPR end as BLNO

And i want this to result in 1 column output. Is there a way around this in pervasive 8 so that i can get the case-like behavior from my query?

As a sidenote i am using the JDBC driver and doing the query from a Java program.

Solution

  • You can use the IF statement. The syntax would be:

    IF ( search-condition , expression , expression )

    So, in your case you would probably need something like:

    IF ( Pheadpr.BLNO <> '' , Pheadpr.BLNO, STOCKTR.PHEADPR) as BLNO

    If at all possible, you should consider upgrading to the current version of PSQL V11 supports both the CASE and COALESCE statements as well as being a supported version of PSQL.