Date manipulaton with ODBC connection

[idingsdale]

I'm doing some work on a Progress 10.1A database, connected by ODBC to a .NET application in Visual Studio 2008.

My SQL query is returning survey responses and looks like this:

SELECT DISTINCT COUNT(PUB.satresponse."job-num") AS Expr1, CASE PUB.satresponse.response
WHEN '90' THEN 'Very Satisfied'
WHEN '60' THEN 'Satisfied'
WHEN '10' THEN 'Disatisfied'
WHEN '30' THEN 'Neither'
WHEN '0' THEN 'No Answer' END AS Expr2

FROM PUB.satresponse, PUB.job WHERE PUB.satresponse."job-num" = PUB.job.num AND (PUB.satresponse."satis-cde" = 14)

GROUP BY PUB.satresponse.response HAVING (PUB.satresponse.response IS NOT NULL)

I want to be able to return surveys for jobs completed in the past 4 weeks, but no functions such as "select today()" or "select cur_date()" seem, to work and give the error:

Syntax error at or about %s(statement excerpt).

I'm sure I've just got the wrong syntax, but cant find anything in the Progress SQL guide. So how do I go about using the current date?

Thanks in advance!

 

[SamAra]

Hi
I have been looking for a thread on how to connect to a progress DB using ODBC from VS2008
Can you help?
thanks

 

[idingsdale]

Its the same as any other ODBC data source - create a DSN, test it and then you should be able to use it in VS.

I figured out my query problem in the end, the secret is to use the current date as a select parameter - the final query turned into this:

SELECT DISTINCT COUNT(PUB.satresponse."job-num") AS Quantity, 

CASE PUB.satresponse.response 
WHEN '90' THEN 'Very Satisfied' 
WHEN '60' THEN 'Satisfied' 
WHEN '10' THEN 'Disatisfied' 
WHEN '30' THEN 'Neither' 
WHEN '0' THEN 'No Answer' 
END AS Response, 

CURDATE() AS currentdate FROM PUB.satresponse, PUB.job 

WHERE PUB.satresponse."job-num" = PUB.job.num 
AND (PUB.satresponse."satis-cde" = 14) 
AND (PUB.job."practical-completion-dat" BETWEEN TIMESTAMPADD(SQL_TSI_DAY, - 28, currentdate) 
AND currentdate) 

GROUP BY PUB.satresponse.response 
HAVING (PUB.satresponse.response IS NOT NULL)