MS SQL Incorrect syntax near '=', statements could not be prepared

[aajcl]

Hi, I am new to SQL and just trying to self teach myself... I have written the below script and I am recieving an error. If anyone could help this would be much appreciated...

SELECT 
(CASE WHEN SL_PL_NL_DETAIL.DET_NOMINALCR = NULL THEN SL_PL_NL_DETAIL.DET_NOMINALDR
ELSE SL_PL_NL_DETAIL.DET_NOMINALDR = NULL THEN 
SL_PL_NL_DETAIL.DET_NOMINALCR END)]

[debasisdas]

try using IS NULL

read more here.

[aajcl]

Hi, thanks but this just changes the error to saying "IS" instead of "="....

[jbisono]

select case when sl_pl_nl_detail.det_nominalcr is null then sl_pl_nl_detail.det_nominaldr else sl_pl_nl_detail.det_nominaldr end as some_name

/*i think this can work for you too*/

select COALESCE(sl_pl_nl_detail.det_nominalcr, sl_pl_nl_detail.det_nominaldr) as some_name

/*or this*/

SELECT ISNULL(sl_pl_nl_detail.det_nominalcr, sl_pl_nl_detail.det_nominaldr) as some_name

[aajcl]

Sorry I'm having problems with the latter part of my code when I use the top one of these, it is giving me a syntax on "AND" now, here is my full code:

SELECT (CASE WHEN sl_pl_nl_detail.det_nominalcr IS NULL THEN sl_pl_nl_detail.det_nominaldr ELSE sl_pl_nl_detail.det_nominaldr END) as some_name

AND CST_DETAIL.CT_NOMINAL = NL_ACCOUNTS.NCODE AND CST_COSTHEADER.CH_CODE = CST_DETAIL.CT_COSTHEADER AND NL_MAJORHEADING.NL_MAJORCODE = NL_ACCOUNTS.NMAJORHEADCODE

[jbisono]

Is it that your full code? Because you are missing the "from" and "where" clause.

Eg. Select some_fields from some_tables where some_fields = some_values and some_other_fields = some_other_values.

[aajcl]

Ahh no here is all my code.....

SELECT SL_PL_NL_DETAIL.DET_TYPE, SL_PL_NL_DETAIL.DET_YEAR, SL_PL_NL_DETAIL.DET_PERIODNUMBR, SL_PL_NL_DETAIL.DET_DESCRIPTION, SL_PL_NL_DETAIL.DET_NOMINALDR, SL_PL_NL_DETAIL.DET_NOMINALCR, SL_PL_NL_DETAIL.DET_NOMINALVAT, SL_PL_NL_DETAIL.DET_COSTHEADER, CST_COSTHEADER.CH_NAME, NL_ACCOUNTS.NMAJORHEADCODE, NL_MAJORHEADING.NL_MAJORNAME, NL_ACCOUNTS.NCATEGORYCODE1, NL_ACCOUNTS.NCATEGORYCODE2, NL_MAJORHEADING.NL_MAJORNAME, NL_ACCOUNTS.NCATEGORYCODE3, NL_ACCOUNTS.NCATEGORYCODE5, NL_ACCOUNTS.NCATEGORYCODE6, NL_ACCOUNTS.NCATEGORYCODE7, NL_ACCOUNTS.NCATEGORYCODE8, SL_PL_NL_DETAIL.DET_NETT, SL_PL_NL_DETAIL.DET_VAT, SL_PL_NL_DETAIL.DET_GROSS
FROM NUSUKLIVE.dbo.CST_COSTHEADER CST_COSTHEADER, NUSUKLIVE.dbo.CST_DETAIL CST_DETAIL, NUSUKLIVE.dbo.NL_ACCOUNTS NL_ACCOUNTS, NUSUKLIVE.dbo.NL_MAJORHEADING NL_MAJORHEADING, NUSUKLIVE.dbo.SL_PL_NL_DETAIL SL_PL_NL_DETAIL
SELECT (CASE WHEN sl_pl_nl_detail.det_nominalcr IS NULL THEN sl_pl_nl_detail.det_nominaldr ELSE sl_pl_nl_detail.det_nominaldr END) as some_name

AND CST_DETAIL.CT_NOMINAL = NL_ACCOUNTS.NCODE AND CST_COSTHEADER.CH_CODE = CST_DETAIL.CT_COSTHEADER AND NL_MAJORHEADING.NL_MAJORCODE = NL_ACCOUNTS.NMAJORHEADCODE

[debasisdas]

where is the WHERE clause ?

[jbisono]

it seems like you have two select statement, lets see how can we help you organize that statement. try to run this part of the statement and let us know if it work.

SELECT SL_PL_NL_DETAIL.DET_TYPE, SL_PL_NL_DETAIL.DET_YEAR, SL_PL_NL_DETAIL.DET_PERIODNUMBR, SL_PL_NL_DETAIL.DET_DESCRIPTION, SL_PL_NL_DETAIL.DET_NOMINALDR, SL_PL_NL_DETAIL.DET_NOMINALCR, SL_PL_NL_DETAIL.DET_NOMINALVAT, SL_PL_NL_DETAIL.DET_COSTHEADER, CST_COSTHEADER.CH_NAME, NL_ACCOUNTS.NMAJORHEADCODE, NL_MAJORHEADING.NL_MAJORNAME, NL_ACCOUNTS.NCATEGORYCODE1, NL_ACCOUNTS.NCATEGORYCODE2, NL_MAJORHEADING.NL_MAJORNAME, NL_ACCOUNTS.NCATEGORYCODE3, NL_ACCOUNTS.NCATEGORYCODE5, NL_ACCOUNTS.NCATEGORYCODE6, NL_ACCOUNTS.NCATEGORYCODE7, NL_ACCOUNTS.NCATEGORYCODE8, SL_PL_NL_DETAIL.DET_NETT, SL_PL_NL_DETAIL.DET_VAT, SL_PL_NL_DETAIL.DET_GROSS
FROM NUSUKLIVE.dbo.CST_COSTHEADER CST_COSTHEADER, NUSUKLIVE.dbo.CST_DETAIL CST_DETAIL, NUSUKLIVE.dbo.NL_ACCOUNTS NL_ACCOUNTS, NUSUKLIVE.dbo.NL_MAJORHEADING NL_MAJORHEADING, NUSUKLIVE.dbo.SL_PL_NL_DETAIL SL_PL_NL_DETAIL
WHERE CST_DETAIL.CT_NOMINAL = NL_ACCOUNTS.NCODE AND CST_COSTHEADER.CH_CODE = CST_DETAIL.CT_COSTHEADER AND NL_MAJORHEADING.NL_MAJORCODE = NL_ACCOUNTS.NMAJORHEADCODE

run that above

I DONT UNDERSTAND THIS PART "SELECT (CASE WHEN sl_pl_nl_detail.det_nominalcr IS NULL THEN sl_pl_nl_detail.det_nominaldr ELSE sl_pl_nl_detail.det_nominaldr END) AS some_name
"

YOU WANT TO DISPLAY THAT LAST PART OR YOU WANT TO CREATE SOME KIND OF FILTER WITH THAT?

[aajcl]

It will not allow me to run the above it is giving syntax error "."

I basically have some data tables where the SL_PL_NL_DETAIL.DET_NOMINALDR, SL_PL_NL_DETAIL.DET_NOMINALCR and SL_PL_NL_DETAIL.DET_NOMINALVAT fields all have some blank values, so I want to create a SQL script to say, if SL_PL_NL_DETAIL.DET_NOMINALDR is blank then give SL_PL_NL_DETAIL.DET_NOMINALCR value and then if SL_PL_NL_DETAIL.DET_NOMINALCR is blank then give SL_PL_NL_DETAIL.DET_NOMINALDR value and then include the SL_PL_NL_DETAIL.DET_NOMINALVAT field whether or not it is blank.

Thanks very much,

Arron

[jbisono]

So for the fiel DET_NOMINALDR and DET_NOMINALCR is one or the other try to do something simple like this.

SELECT COALESCE(DET.DET_NOMINALDR,DET.DET_NOMINALCR) AS NOMINAL, DET.DET_NOMINALVAT
FROM SL_PL_NL_DETAIL AS DET

now remember that a blank value and null is not the samething so coalesce will work if you have null values. if what you have is blank values then you need something like this

SELECT CASE WHEN DET.DET_NOMINALDR <> '' THEN DET.DET_NOMINALDR ELSE DET.DET_NOMINALCR END AS NOMINAL, DET.DET_NOMINALVAT
FROM SL_PL_NL_DETAIL AS DET

try to run that and check if you result just for that if what you want.

[aajcl]

This gives a syntax error on "AS"

SELECT SL_PL_NL_DETAIL.DET_TYPE, SL_PL_NL_DETAIL.DET_YEAR, SL_PL_NL_DETAIL.DET_PERIODNUMBR, SL_PL_NL_DETAIL.DET_DESCRIPTION, SL_PL_NL_DETAIL.DET_NOMINALDR, SL_PL_NL_DETAIL.DET_NOMINALCR, SL_PL_NL_DETAIL.DET_NOMINALVAT, SL_PL_NL_DETAIL.DET_COSTHEADER, CST_COSTHEADER.CH_NAME, NL_ACCOUNTS.NMAJORHEADCODE, NL_ACCOUNTS.NCATEGORYCODE1, NL_ACCOUNTS.NCATEGORYCODE2, NL_ACCOUNTS.NCATEGORYCODE3, NL_ACCOUNTS.NCATEGORYCODE5, NL_ACCOUNTS.NCATEGORYCODE6, NL_ACCOUNTS.NCATEGORYCODE7, NL_ACCOUNTS.NCATEGORYCODE8, SL_PL_NL_DETAIL.DET_NETT, SL_PL_NL_DETAIL.DET_VAT, SL_PL_NL_DETAIL.DET_GROSS
FROM NUSUKLIVE.dbo.CST_COSTHEADER CST_COSTHEADER, NUSUKLIVE.dbo.NL_ACCOUNTS NL_ACCOUNTS, NUSUKLIVE.dbo.SL_PL_NL_DETAIL SL_PL_NL_DETAIL
WHERE CST_COSTHEADER.CH_CODE = SL_PL_NL_DETAIL.DET_COSTHEADER

SELECT CASE WHEN DET.DET_NOMINALDR <> '' THEN DET.DET_NOMINALDR ELSE DET.DET_NOMINALCR END AS NOMINAL, DET.DET_NOMINALVATFROM SL_PL_NL_DETAIL AS DET

I think I may also need to link the tables, SL_PL_NL_DETAIL and NL_ACCOUNTS

[jbisono]

You can't have a select statement in the "WHERE" clause unless you are kind of building a filter with a select statement in it.

SELECT SL_PL_NL_DETAIL.DET_TYPE, SL_PL_NL_DETAIL.DET_YEAR, SL_PL_NL_DETAIL.DET_PERIODNUMBR, SL_PL_NL_DETAIL.DET_DESCRIPTION, SL_PL_NL_DETAIL.DET_NOMINALDR, SL_PL_NL_DETAIL.DET_NOMINALCR, SL_PL_NL_DETAIL.DET_NOMINALVAT, SL_PL_NL_DETAIL.DET_COSTHEADER, CST_COSTHEADER.CH_NAME, NL_ACCOUNTS.NMAJORHEADCODE, NL_ACCOUNTS.NCATEGORYCODE1, NL_ACCOUNTS.NCATEGORYCODE2, NL_ACCOUNTS.NCATEGORYCODE3, NL_ACCOUNTS.NCATEGORYCODE5, NL_ACCOUNTS.NCATEGORYCODE6, NL_ACCOUNTS.NCATEGORYCODE7, NL_ACCOUNTS.NCATEGORYCODE8, SL_PL_NL_DETAIL.DET_NETT, SL_PL_NL_DETAIL.DET_VAT, SL_PL_NL_DETAIL.DET_GROSS, CASE WHEN SL_PL_NL_DETAIL.DET_NOMINALDR <> '' THEN SL_PL_NL_DETAIL.DET_NOMINALDR ELSE SL_PL_NL_DETAIL.DET_NOMINALCR END AS NOMINAL, SL_PL_NL_DETAIL.DET_NOMINALVAT
FROM NUSUKLIVE.dbo.CST_COSTHEADER CST_COSTHEADER, NUSUKLIVE.dbo.NL_ACCOUNTS NL_ACCOUNTS, NUSUKLIVE.dbo.SL_PL_NL_DETAIL SL_PL_NL_DETAIL
WHERE CST_COSTHEADER.CH_CODE = SL_PL_NL_DETAIL.DET_COSTHEADER

[aajcl]

Ahhh ok... So do I need to link the tables or not? this SQL script appears when I link my tables. I thought I needed to link my tables for the script to pick up fields across different tables.

[jbisono]

Yes it all depends what you want to do, we have to know a little bit more about you design and what you want to get, there are different ways to link it, cross join, left outer join, right outer join, inner join. im going to give you an example, the fact i dont have your design table i can't give you one with yours.

let's say you have to tables "Countries" and "States", Countries fields are (CountryID, CountryName) and States fields are (StateID, StateName, CountryID) now you can get all countries with states like this.

SELECT COUNTRY.COUNTRYNAME, STATE.STATENAME
FROM COUNTRIES AS COUNTRY INNER JOIN STATES AS STATE ON COUNTRY.COUNTRYID = STATE.COUNTRYID

IF YOU WANT TO KNOW ALL STATES FROM A SPECIFIC COUNTRY THE YOU HAVE THE SAME CODE JUST ADD A "WHERE" CLAUSE

WHERE COUNTRY.COUNTRYNAME = 'USA'