×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

*
*
*
*

Thanks. We have received your request and will respond promptly.

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

SELECT CASE statement works on one database and not the other

thread318-1717704

SELECT CASE statement works on one database and not the other

SELECT CASE statement works on one database and not the other

(OP)
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 --> P-SQL

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)

RE: SELECT CASE statement works on one database and not the other

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

RE: SELECT CASE statement works on one database and not the other

(OP)
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)

RE: SELECT CASE statement works on one database and not the other

(OP)
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 --> P-SQL

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)

RE: SELECT CASE statement works on one database and not the other

I downloaded the PSQL v8 documentation from http://www.pervasive.com/database/Home/Resources/D... 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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login
Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close