Converting Access queries to SQL

[madamvegas]

We are moving our Access dbs into SQL and trying to copy the SQL code in the queries into Access and finding there are some differences. There is one that I am stumped on. Here is part of the code.

SELECT Data_Collection_Template.[Item Number (Catalog)], Data_Collection_Template.[NAED / UPC Number], EZView.Price_Column_Code1,
IIf(Len([Price1])> 0,Round([Price1],2),0) AS EZViewPrice1
FROM Data_Collection_Template LEFT JOIN EZView
ON Data_Collection_Template.[NAED / UPC Number] = EZView.UPC

There are mulitples iif(len......) statements but just showing one. I get an error message that says.

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '>'.

I don't see what the error is near '>'. Been trying different things but nothing working. Any suggestions would be greatly appreciated.

 

[pbaldy]

IIf is not valid in T-SQL. Check out the CASE statement in BOL as a replacement.

 

[madamvegas]

What is BOL?

I had tried the CASE WHEN ..... then ...... else......end
but I need to have the value go into a new field. So this statement IIf(Len([Price1])> 0,Round([Price1],2),0)AS EZViewPrice1 puts the value in the field EZVIEWPrice1. I tried adding the AS EZViewPrice1 in the CASE statement but it didn't like it. This is all new to me and just learning from books and stuff on line.

The following statement works
select data_collection_template.[item number (catalog)],
case when (len([price1]) > 0) then round([price1],2) else 2 end
FROM Data_Collection_Template LEFT JOIN EZView
ON Data_Collection_Template.[NAED / UPC Number] = EZView.UPC


but i need the result to go into the EZViewPrice1 field so I tried this
select data_collection_template.[item number (catalog)],
case when (len([price1]) > 0) then round([price1],2) as ezviewprice1 else 2 end
FROM Data_Collection_Template LEFT JOIN EZView
ON Data_Collection_Template.[NAED / UPC Number] = EZView.UPC


and it didn't like it.


Am I making any sense?

 

[pbaldy]

The "As" part goes after "End".

 

[pbaldy]

Sorry, BOL is Books Online, which should have been installed with SQL Server. It is its help system, as is pretty good. Here's an example:

CASE WHEN TotalTimeDecimal >= 4 THEN TotalTimeDecimal - 1 ELSE TotalTimeDecimal END As NetTimeDecimal

 

[madamvegas]

Thanks for the help. What I am seem to be having issues with now is when I create a field for the results of my CASE statement and then want to compare them. This is what I wrote
select data_collection_template.[item number (catalog)], Data_Collection_Template.[NAED / UPC Number], EZView.Price_Column_Code1,
case when (len([price1]) > 0)
then round([price1],2)
else 2
end as ezviewprice1,
case when (Len([Price 1])>0)
then Round([Price 1],2)
else 0
end AS [DCTPrice 1],
case when [EZViewPrice1]=[DCTPrice 1]
then 0
else 1
end AS Price1Diff,
EZView.Price_Column_Code2,
case when (Len([Price2])>0)
then Round([Price2],2)
else 0
end AS EZViewPrice2
FROM Data_Collection_Template LEFT JOIN EZView
ON Data_Collection_Template.[NAED / UPC Number] = EZView.UPC

so when i
case when [EZViewPrice1]=[DCTPrice 1]
i get the error that they are invalid columns
Msg 207, Level 16, State 1, Line 10
Invalid column name 'EZViewPrice1'.
Msg 207, Level 16, State 1, Line 10
Invalid column name 'DCTPrice 1'.

is there away to get around this?

 

[pbaldy]

As far as I know you can't use the alias in other fields. You can either repeat the CASE statement in the second field or base a second query on one that creates the base fields.