SELECT CASE statement works on one database and not the other

TheBugSlayer · Sep 3, 2013

All,

I have two connection strings for two different customer Pervasive DBs. The query containing the CASE statement below works on the first DB and fails on the second DB, working on it only if I remove the statement. My version of Pervasive is 8. At this time I suspect the latter DB may be in a version that did not support the CASE statement? What PSQL statement returns the database version like SQL Server's SELECT @@VERSION?

Code:

SELECT CASE DeptCode
			WHEN 'A' THEN '*'
			WHEN 'AB' THEN '*'
			WHEN 'AS' THEN '*'
			WHEN 'BS' THEN '*'
			WHEN 'C' THEN '*'
			WHEN 'D' THEN '*'
			WHEN 'E' THEN '*'
			WHEN 'F' THEN '*'
			WHEN 'FD' THEN '*'
			WHEN 'G' THEN '*'
			WHEN 'GM' THEN '*'
			WHEN 'I' THEN '*'
			WHEN 'JS' THEN '*'
			WHEN 'K' THEN '*'
			WHEN 'L' THEN '*'
			WHEN 'M' THEN '*'
			WHEN 'MK' THEN '*'	
			WHEN 'N' THEN '*'
			WHEN 'P' THEN '*'
			WHEN 'PS' THEN '*'
			WHEN 'R' THEN '*'
			WHEN 'RK' THEN '*'
			WHEN 'S' THEN '*'
			WHEN 'SD' THEN '*'
			WHEN 'SL' THEN '*'
			WHEN 'T' THEN '*'
			WHEN 'V' THEN '*'
			WHEN 'W' THEN '*'
			WHEN 'Z' THEN '*'	
			ELSE DeptCode
	   END AS Code...

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

mirtheil · Sep 3, 2013

Pervasive SQL doesn't support @@Version in any version. The best way to check the version is to look at the version resource of the DLLs / EXEs. Specifically, check W3ODBCEI.DLL, W3ODBCCI.DLL, NTDBSMGR.EXE.

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com

TheBugSlayer · Sep 4, 2013

Thank you mirtheil. I know the version on my machine and I am certain about that of the machine where the CASE statement works. I am not able to tell the for the other machine. What about the CASE statement? Is there any chance it did not exist in older versions?

Thank you.

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

TheBugSlayer · Sep 4, 2013

Now it looks like it does not like subqueries neither...This is terrible for someone who is used to T-SQL...

This does not work here, it does on the P V8:

Code:

SELECT FacilityID
, CASE DeptCode
			WHEN 'F' THEN 'J'
			WHEN 'H' THEN 'D'
			WHEN 'HB' THEN 'T'
			WHEN 'HC' THEN 'D'
			WHEN 'HF' THEN 'T'
			WHEN 'HG' THEN 'T'
			WHEN 'HS' THEN 'D'
			WHEN 'HT' THEN 'T'
			WHEN 'HV' THEN 'J'
			WHEN 'HX' THEN 'D'
			WHEN 'M' THEN 'J'
			WHEN 'N' THEN 'D'
			WHEN 'P' THEN 'T'
			WHEN 'R' THEN 'D'
			WHEN 'S' THEN 'D'
			WHEN 'T' THEN 'D'
			WHEN 'X' THEN 'D'			
			ELSE DeptCode
  END AS ProductCode
, (SUM(DayDiff)/1.0)/Count(1) AS AverageTAT
, DateReported AS ReportDate
FROM
(
	SELECT 'XY' AS LocationCode, DateRecieved, DateReported, DATEDIFF(day, DateRecieved, DateReported) AS DayDiff
	FROM MPI 
	WHERE DateReported BETWEEN '{0}' AND '{1}' AND DeptCode <> 'A' AND DeptCode <> 'HM' 
) A
GROUP BY ReportDate, ProductCode

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

mirtheil · Sep 4, 2013

I downloaded the PSQL v8 documentation from

http://www.pervasive.com/database/Home/Resources/Documentation.aspx

and couldn't find a CASE statement like you are using in there. There is a CASE statement but it is for case sensitivity.
As far as Subqueries, I thing they are only supported in the WHERE clause with V8.

If this is going to be an ongoing project, I would really suggest upgrading to PSQL v11. It does support the CASE statement and the table subquery. At the least, I would suggest downloading the PSQL v11 workgroup engine, installing it on a test machine, and trying it with your data / queries / application.

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

SELECT CASE statement works on one database and not the other

TheBugSlayer

Programmer

mirtheil

Programmer

TheBugSlayer

Programmer

TheBugSlayer

Programmer

mirtheil

Programmer

Similar threads

Part and Inventory Search

Sponsor