T-SQL, SQL Server Help

Hi all,

Bit of SQL help here, i'm really stuck been working on this for a couple hours. No idea why i'm getting an error whenever I run this SQL query, using a text driver through an ODBC connection to query a csv file...

Can anyone see anything wrong with my sql?

select  payee.*,
payee.AHIRDA+'/'+payee.AHIRMO+'/'+payee.AHIRYR AS Date_Of_Hire,
CASE ATRMDA
      WHEN 0 THEN '31/12/2999'
      ELSE payee.ATRMDA+'/'+payee.ATRMMO+'/'+ payee.ATRMYR
END AS Termination_Date
from
ptaagr0999.csv payee

Any help much appreciated.

I keep getting hte following error:

Syntax error(Missing operator) in query expression
'CASE ATRMDA
      WHEN 0 THEN '31/12/2999'
      ELSE payee.ATRMDA+'/'+payee.ATRMMO+'/'+ payee.ATRMYR
END'

Thanks a lot..
lp84Asked:
Who is Participating?
 
Dale FyeConnect With a Mentor Commented:
If you are doing this from Access, then the CASE statement will not work.  As Angel mentioned, load the CSV file into an Access table.  Then, instead of using the Case statement, use the IIF( ) function

IIF(ATRMDA = 0, '31/12/2999', payee.ATRMDA+'/'+payee.ATRMMO+'/'+ payee.ATRMYR)
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>using a text driver through an ODBC connection to query a csv file...you cannot do that, the text driver does not support all the MS SQL syntax.you have to load the text file with a plain SELECT * into a local table, and then query from there.this technique is called "staging" ...
0
 
HugoHiaslCommented:
Which data type does ATRMDA have?

If it is date and you want to check for NULL you need to use

CASE ATRMDA
      WHEN NULL THEN '31/12/2999'
      ELSE payee.ATRMDA+'/'+payee.ATRMMO+'/'+ payee.ATRMYR
END AS Termination_Date

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.