Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 325
  • Last Modified:

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..
0
lp84
Asked:
lp84
1 Solution
 
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
 
Dale FyeCommented:
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

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now