•
•
•
•
What is DaniWeb IT Discussion Community?
You're currently browsing the MS SQL section within the Web Development category of DaniWeb, a massive community of 397,790 software developers, web developers, Internet marketers, and tech gurus who are all enthusiastic about making contacts, networking, and learning from each other. In fact, there are 2,437 IT professionals currently interacting right now! Registration is free, only takes a minute and lets you enjoy all of the interactive features of the site.
Please support our MS SQL advertiser:
Views: 8017 | Replies: 5
 |
•
•
Join Date: Dec 2006
Posts: 4
Reputation: 
Rep Power: 0
Solved Threads: 0
Newbie Poster
Incorrect syntax near '='. I'm getting the error
Help me understand what I'm doing wrong (beside everything) :cheesy:
Incorrect syntax near '='. when I use the following case statement in a view. Obviously I'm not an expert on MS SQL, but I'm curious as to why I am allowed to use the '=' sign in a preceeding statement but not in the THEN section.Help me understand what I'm doing wrong (beside everything) :cheesy:
CASE
WHEN
MBAL.ALCUCD = '1' AND -- ADDRESS CODE
MABL.ALE2ST = '1' -- ADDRESS TYPE
THEN
MBDE.DEB9CD = 'NULL' AND
'B' AS ADDRESS_TYPE
END
•
•
Join Date: Sep 2006
Posts: 44
Reputation:
Rep Power: 2
Solved Threads: 3
Light Poster
•
•
•
•
Originally Posted by Qmoto 
I'm getting the errorIncorrect syntax near '='.when I use the following case statement in a view. Obviously I'm not an expert on MS SQL, but I'm curious as to why I am allowed to use the '=' sign in a preceeding statement but not in the THEN section.
Help me understand what I'm doing wrong (beside everything) :cheesy:
CASE WHEN MBAL.ALCUCD = '1' AND -- ADDRESS CODE MABL.ALE2ST = '1' -- ADDRESS TYPE THEN MBDE.DEB9CD = 'NULL' AND 'B' AS ADDRESS_TYPE END
CASE is used when you want to return different values based on different conditions, such as:
select case when priority=1 then 'Low' when priority=2 then 'Med' when priority=3 then 'High' end from priorities
or the alternative
select case priority when 1 then 'Low' when 2 then 'Med' when 3 then 'High' end from priorities
I'm not sure what you're trying to do. SQL also has an IF syntax, for example:
if exists(select * from whatever)
begin
do something
end
else
begin
do something else
end
Can you give the entire SQL statement so that we can understand exactly what you're trying to do?
•
•
Join Date: Dec 2006
Posts: 4
Reputation:
Rep Power: 0
Solved Threads: 0
Newbie Poster
This code is used in the creation of a view, so I do not believe the if/then structure will work.
What I'm trying to do is set column
Does that help?
Also, I've just been informed that I need to interpret 00000001 as '1', could I do that with IsNumeric or something?
Here's a shortened version of what I am doing
What I'm trying to do is set column
MBDE.DEB9CD = 'NULL' and ADDRESS_TYPE = 'B' where MBAL.ALCUCD AND MABL.ALE2ST are equal to one.Does that help?
Also, I've just been informed that I need to interpret 00000001 as '1', could I do that with IsNumeric or something?
Here's a shortened version of what I am doing
CREATE VIEW CUSTOMER
AS
SELECT
CAST(MBBF.CUSNO as varchar(48)) AS BU_ACCOUNT_KEY
,CAST(MBBF.CUSNM as varchar(64)) AS BU_ACCOUNT
...
,CAST(MBDE.DEB9CD as varchar(48)) AS ADDRESS_KEY
,CASE
WHEN
MBAL.ALCUCD = '1' AND
MABL.ALE2ST = '1'
THEN
MBDE.DEB9CD = 'NULL' AND
'B' AS ADDRESS_TYPE
END
,'NULL' AS ADDRESS_TELEPHONE
....
FROM
TABLE1
LEFT JOIN
TABLE2
ECT... Last edited by Qmoto : Dec 7th, 2006 at 11:34 am. Reason: Forgot to close the [code] tag
•
•
Join Date: Dec 2006
Posts: 4
Reputation:
Rep Power: 0
Solved Threads: 0
Newbie Poster
Never mind, I fixed it... I was making the problem too difficult.
I simply had to use a case statement on the field in question, so instead of one CASE statement trying to do two (2) things, I ended up with two CASE statements each doing only one thing.
Thanks,
Steve
I simply had to use a case statement on the field in question, so instead of one CASE statement trying to do two (2) things, I ended up with two CASE statements each doing only one thing.
Thanks,
Steve
•
•
Join Date: Sep 2006
Posts: 44
Reputation:
Rep Power: 2
Solved Threads: 3
Light Poster
Are all the values in the column like 000000001? Because you could just do field+0 that would implicitly convert it... or cast(field as int) to explicitly do it. if you have mixed fields, then you could have a case statement like
case when field like replicate('[0-9]',len(field)) then cast(field as int) else field end
(untested.. but something like that)
case when field like replicate('[0-9]',len(field)) then cast(field as int) else field end
(untested.. but something like that)
•
•
Join Date: Dec 2006
Posts: 4
Reputation:
Rep Power: 0
Solved Threads: 0
Newbie Poster
Sorry for the late reply.
This column is made up of various data, not just the 0000001, but thanks for the idea of doing a field+0 to do an implicit conversion. I'm guessing that will come in handy at some point in the future.
Thanks again,
Steve
This column is made up of various data, not just the 0000001, but thanks for the idea of doing a field+0 to do an implicit conversion. I'm guessing that will come in handy at some point in the future.
Thanks again,
Steve
|
•
•
•
•
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
•
•
•
•
•
•
•
•
DaniWeb MS SQL Marketplace
Linear Mode
- Updated : Simple ASP.Net Login Page (ASP.NET)
- Syntax error in "like" in sql statement (ASP.NET)
- c++ SQLServer ODBC errors (C++)
- Multi-threading with C++.NET (C++)
Other Threads in the MS SQL Forum
- Previous Thread: Table query returning error -- help!
- Next Thread: Active Users in MSSQL
