Where's my syntax?? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Where's my syntax??

Author

Topic

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2011-12-09 : 16:02:03

The following is a portion of a select from a view. I'm being blinded by all the syntax errors in it. Please guide me in seeing the error of my ways:
ISNULL(dbo.BoatingWarningHuntCodes.WarningCode, '') AS
BoatingWarningHuntCode =
CASE
WHEN '1' THEN 'Yes'
WHEN '0' THEN 'No' ELSE '' End,
ISNULL(dbo.BoatingWarningQualityCodes.WarningCode, '') AS
BoatingWarningQualityCode=
CASE
WHEN '1' THEN 'Yes'
WHEN '0' THEN 'No' ELSE '' End,,
ISNULL(dbo.BoatingWarningBoatCodes.WarningCode, '') AS
BoatingWarningBoatCode=
CASE
WHEN '1' THEN 'Yes'
WHEN '0' THEN 'No' ELSE '' End,,
ISNULL(dbo.BoatingWarningFishCodes.WarningCode, '') AS
BoatingWarningFishCode=
CASE
WHEN '1' THEN 'Yes'
WHEN '0' THEN 'No' ELSE '' End
FROM dbo.BoatingWarning

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-12-09 : 16:30:26

Each of them has syntax problems. Let's take one as an example:
ISNULL(dbo.BoatingWarningHuntCodes.WarningCode, '') AS
BoatingWarningHuntCode =
CASE
WHEN '1' THEN 'Yes'
WHEN '0' THEN 'No' ELSE '' End

Try this instead:

BoatingWarningHuntCode =
CASE ISNULL(dbo.BoatingWarningHuntCodes.WarningCode, '')
WHEN '1' THEN 'Yes'
WHEN '0' THEN 'No' ELSE '' End

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2011-12-09 : 16:35:04

But 'BoatingWarningHuntCode' is the alias that i need to use for the WarningCode column in the BoatingWarningHuntCodes table. When i lead my line in the query with BoatingWarningHuntCode it sees it as my attempt to identify a column which does not exist.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-12-09 : 16:37:55

I don't understand then. You'll need to show us a data example.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2011-12-09 : 16:45:01

no data in the tables yet, but i can show you the structure. What i originally had (and was working fine) was this:

CREATE VIEW [dbo].[vwsc_BoatingWarningasXML]
AS

SELECT
(Select dbo.Boatingwarning.uniquekey,
dbo.BoatingWarning.DescriptionOfViolations,
dbo.BoatingWarning.OfficerAgencyGroup,
dbo.BoatingWarning.WarningBoatCodesExist AS WBCE,
dbo.BoatingWarning.WarningFishCodesExist AS WFCE,
dbo.BoatingWarning.WarningHuntCodesExist AS WHCE,
dbo.BoatingWarning.WarningQualityCodesExist WQCE,
ISNULL(dbo.BoatingWarningHuntCodes.WarningCode,'') AS HWC,
ISNULL(dbo.BoatingWarningQualityCodes.WarningCode,'') AS QWC,
ISNULL(dbo.BoatingWarningBoatCodes.WarningCode,'') AS BWC,
ISNULL(dbo.BoatingWarningFishCodes.WarningCode,'') AS FWC
FROM dbo.BoatingWarning
LEFT OUTER JOIN dbo.BoatingWarningBoatCodes
ON dbo.BoatingWarning.UniqueKey = dbo.BoatingWarningBoatCodes.BoatingWarningUniqueFKey
LEFT OUTER JOIN dbo.BoatingWarningHuntCodes
ON dbo.BoatingWarning.UniqueKey = dbo.BoatingWarningHuntCodes.BoatingWarningUniqueFKey
LEFT OUTER JOIN dbo.BoatingWarningQualityCodes
ON dbo.BoatingWarning.UniqueKey = dbo.BoatingWarningQualityCodes.BoatingWarningUniqueFKey
LEFT OUTER JOIN dbo.BoatingWarningFishCodes
ON dbo.BoatingWarning.UniqueKey = dbo.BoatingWarningFishCodes.BoatingWarningUniqueFKey
FOR XML AUTO, ELEMENTS, TYPE) AS t

GO

Now, the "man with the plan" wants a case expression applied to the values found in the WarningCode column for those four tables so that if the value is true (1) then "yes" is displayed, if the value is false (0) then "no" is displayed. This was the direction i was originally going with this yesterday until he decided to tell me to stick the "ISNULL" portion in front of each instead. Needless to say, i never made to the point of success with the case expression. At any rate, there you are.

thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-12-09 : 16:52:02

Do the case expression outside of the derived table.

SELECT CASE ..., CASE ..., <-- Use HWC, QWC, BWC, and FWC here.
(Select ...) AS t

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2011-12-09 : 16:55:30

Thank you. I'll put that on my agenda for monday morning. My time has come. I'm outta here. A weekend of kayak fishing awaits.

thanks again for your help!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-12-09 : 16:57:05

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2011-12-12 : 09:45:38

No less confused this monday morning. Too cold to fish over the weekend, so it didn't give me a chance to collect my thoughts to reprepare for this on Monday morning. So, i "think" i've put the case expression i want OUTSIDE of the derived table, but in doing so, it's so riddled with red underlines and syntax errors that i'm starting to see red everywhere i look. Below is the bottom half of that derived table along with the case expression:
dbo.BoatingWarning.ViolationDescription,
USE [MobileFormsServer]
GO

/****** Object: View [dbo].[vwsc_BoatingWarningasXML] Script Date: 12/12/2011 08:18:06 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[vwsc_BoatingWarningasXML]
AS

SELECT
(Select dbo.Boatingwarning.uniquekey,
dbo.BoatingWarning.CitationNo,
dbo.BoatingWarning.CitationDateTime,
dbo.BoatingWarning.LoginName,
dbo.BoatingWarning.ViolationInstructions,
dbo.BoatingWarning.ViolationCode,
dbo.BoatingWarning.CodeViolationUniqueKey,
dbo.BoatingWarning.CodeViolation,
dbo.BoatingWarning.CodeViolationDescription,
dbo.BoatingWarning.CodeViolationInstructions,
dbo.BoatingWarning.CodeViolationCounty,
dbo.BoatingWarning.DescriptionOfViolations,
dbo.BoatingWarning.OfficerAgencyGroup,
dbo.BoatingWarning.WarningBoatCodesExist AS WBCE,
dbo.BoatingWarning.WarningFishCodesExist AS WFCE,
dbo.BoatingWarning.WarningHuntCodesExist AS WHCE,
dbo.BoatingWarning.WarningQualityCodesExist AS WQCE,
ISNULL(dbo.BoatingWarningHuntCodes.WarningCode,'') AS [Hunting Warning Code],
ISNULL(dbo.BoatingWarningQualityCodes.WarningCode,'') AS [Quality Warning Code],
ISNULL(dbo.BoatingWarningBoatCodes.WarningCode,'') AS [Boat Warning Code],
ISNULL(dbo.BoatingWarningFishCodes.WarningCode,'') AS [Fish Warning Code]
FROM dbo.BoatingWarning

LEFT OUTER JOIN dbo.BoatingWarningBoatCodes
ON dbo.BoatingWarning.UniqueKey = dbo.BoatingWarningBoatCodes.BoatingWarningUniqueFKey
LEFT OUTER JOIN dbo.BoatingWarningHuntCodes
ON dbo.BoatingWarning.UniqueKey = dbo.BoatingWarningHuntCodes.BoatingWarningUniqueFKey
LEFT OUTER JOIN dbo.BoatingWarningQualityCodes
ON dbo.BoatingWarning.UniqueKey = dbo.BoatingWarningQualityCodes.BoatingWarningUniqueFKey
LEFT OUTER JOIN dbo.BoatingWarningFishCodes
ON dbo.BoatingWarning.UniqueKey = dbo.BoatingWarningFishCodes.BoatingWarningUniqueFKey
FOR XML AUTO, ELEMENTS, TYPE) AS t

SELECT [Boat Warning Code],
CASE [Boat Warning Code],
WHEN 1 THEN 'YES'
WHEN 0 THEN 'NO'
END AS [Boat Warning Code]
FROM dbo.BoatingWarningBoatCodes,

SELECT [Hunting Warning Code],
CASE [Hunting Warning Code],
WHEN 1 THEN 'Yes'
WHEN 0 THEN 'No'
END AS [Hunt Warning Code]
FROM dbo.BoatingWarningHuntCodes,

SELECT Quality Warning Code],
CASE Quality Warning Code],
WHEN 1 THEN 'Yes'
WHEN 0 THEN 'No'
END AS [Quality Warning Code]
FROM dbo.BoatingWarningQualityCodes,

SELECT [Fish Warning Code],
CASE [Fish Warning Code],
WHEN 1 THEN 'Yes'
WHEN 0 THEN 'No'
END AS [Fish Warning Code]
FROM dbo.BoatingWarningFishCodes

GO

Where am i still going wrong?

Kristen
Test

22859 Posts

Posted - 2011-12-12 : 10:02:29

You want either:


[ColumnAliasName] = SomeExpression,
or
SomeExpression AS [ColumnAliasName],

but definitely not


SomeExpression AS [ColumnAliasName] = SomeOtherExpression,

You also cannot have


SELECT ...
FROM dbo.BoatingWarningBoatCodes,

SELECT

nor can you have


SELECT xxxQuality Warning Code],

presumably you meant to have "[" at xxx ?

and you also cannot have


CASE [Hunting Warning Code],
WHEN ...

If these errors are not apparent to you on checking your code then you need to start with something much more simple and make sure that works before adding more to it. That way as you add each new statement, and if you get an error, you will know that it is in the "new" bit you just added. Starting with a block of code with so many typographical syntax errors (assuming you do not, yet, have the skill to spot them) is not going to work for you.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources