Solved

sql else statement

Posted on 2007-04-11
5
175 Views
Last Modified: 2010-03-20
Hi
I am trying to resolve an else statement but I keep getting syntax errors....
                        (SELECT TOP 1 lpSignDateTime FROM dbo.lpSign
                              WHERE planVersion = @intPlanversion AND planID = @intplanID AND userID = dbo.candidates.candidateID isNull Then 'NA') END 'Date Trained'
                        else
                        (SELECT TOP 1 lpSignDateTime FROM dbo.lpSign
                              WHERE planVersion = @intPlanversion AND planID = @intplanID AND userID = dbo.candidates.candidateID ORDER BY lpsigndatetime) END 'Date Trained'

Grateful for any help thanks.
0
Comment
Question by:Soluga
  • 2
  • 2
5 Comments
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 18888541
1) Please describe what you are trying to do

2) Please post the whole SQL statement so we can get the context
0
 
LVL 1

Author Comment

by:Soluga
ID: 18888571
I am trying to check for a null value in a date field, so if there is a null I can send back an 'N/A'. The code below now runs, but I get
Server: Msg 241, Level 16, State 1, Line 27
Syntax error converting datetime from character string.

                  (SELECT TOP 1 esDate FROM dbo.lpAssignDetails
                        WHERE planVersion = @intPlanversion AND planID = @intplanID and planFinished = 0 or planFinished = 1 AND userID = dbo.candidates.candidateID ORDER BY esDate) AS 'Invite Date',
                  CASE WHEN lps.lpSignDateTime IS NOT NULL THEN
                        (SELECT TOP 1 lpSignDateTime FROM dbo.lpSign
                              WHERE planVersion = @intPlanversion AND planID = @intplanID AND userID = dbo.candidates.candidateID)
                        else 'N/A'
                         END 'Date Trained'
            FROM         dbo.candidates
0
 
LVL 11

Expert Comment

by:Sven
ID: 18888709
(SELECT TOP 1 esDate FROM dbo.lpAssignDetails
 WHERE planVersion = @intPlanversion AND planID = @intplanID and planFinished = 0 or planFinished = 1 AND userID = dbo.candidates.candidateID ORDER BY esDate) AS InviteDate,
 CASE WHEN NOT lps.lpSignDateTime IS NULL THEN
 (SELECT TOP 1 lpSignDateTime FROM dbo.lpSign
 WHERE planVersion = @intPlanversion AND planID = @intplanID AND userID = dbo.candidates.candidateID) AS InviteDate
 else 'N/A' AS InviteDate
 END, [Date Trained]
 FROM dbo.candidates

Table or Field Alias must not be in single quotation marks and must not contain spaces (AS InviteDate instead of AS 'Invite Date'). You forgot the "AS InviteDate" on the second SELECT and 'N/A'.

You can also try "AS [Invite Date]" if you want to keep the space in field name. Use the brackets [] instead of single quotation marks.

Also I think you have to use "NOT lps.lpSignDateTime IS NULL" instead of "lps.lpSignDateTime IS NOT NULL".

What is 'Date Trained'? Is this another field of the table? Then you have to use this:  ...END, [Date Trained] FROM...
0
 
LVL 11

Accepted Solution

by:
Sven earned 500 total points
ID: 18888717
I got some error in my syntax or there is some more in yours ;) Try this one:

(SELECT TOP 1 esDate FROM dbo.lpAssignDetails
 WHERE planVersion = @intPlanversion AND planID = @intplanID and planFinished = 0 or planFinished = 1 AND userID = dbo.candidates.candidateID ORDER BY esDate),
 CASE WHEN NOT lps.lpSignDateTime IS NULL THEN
 (SELECT TOP 1 lpSignDateTime FROM dbo.lpSign
 WHERE planVersion = @intPlanversion AND planID = @intplanID AND userID = dbo.candidates.candidateID)
 else 'N/A'
 END AS [Date Trained]
 FROM dbo.candidates

I think the "AS InviteDate" is not needed and you want to get only a field named "Date Trained".
0
 
LVL 1

Author Comment

by:Soluga
ID: 18888830
Cheers
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Nested cursor  in SQL 9 94
MS SQL - Conversion failed when converting the varchar value 3 22
Sql Join Problem 2 22
Help Extract Specific in SQL 8 16
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
I have a large data set and a SSIS package. How can I load this file in multi threading?
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

929 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now