Incorrect syntax near 'NULL'. Error in SQL server 2012

Question

1.00/5 (1 vote)

See more:

In following code I am getting Error in SQL Server 2012
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near 'NULL'.

SQL

SELECT TBLTRANSUSERS.FirstName + ' ' + TBLTRANSUSERS.LastName + '(' + TBLTRANSUSERS.UserCode + ')' as 'User Name',TBLTRANSJOBS.JobName as 'Job Name', TBLTRANSDOCUMENTS.DocName as 'Loan Name',Convert(Varchar(25),AssignedDate,131) as 'Assigned Date',
(SELECT RIGHT('0' + CAST(Datediff(second,AssignedDate,GetDate()) / 3600 AS VARCHAR),2) + ':' + RIGHT('0' + CAST((Datediff(second,AssignedDate,GetDate()) / 60) % 60 AS VARCHAR),2) + ':' + RIGHT('0' + CAST(Datediff(second,AssignedDate,GetDate()) % 60 AS VARCHAR),2) as 'Elapsed Time'
FROM ((TBLTRANSDOCUMENTS inner join TBLTRANSJOBS ON TBLTRANSJOBS.JobID = TBLTRANSDOCUMENTS.JobID) inner join TBLTRANSUSERS on TBLTRANSDOCUMENTS.ExtractionAssigned=TBLTRANSUSERS.UserID) WHERE KeyedDate is NULL and 
ExtractionAssigned is not NULL

What I have tried:

No Solutions are coming in the above problem.

Posted 25-Oct-18 0:41am

Sanjoy Das2

Updated 25-Oct-18 1:47am

v2

Add a Solution

4 solutions

Solution 1

We can't solve that for you - we don't have any access to your data and that's probably going to be very relevant to the problem.

So start by cutting it down - rip it to the minimum, and see if it works and provides the right data.
Perhaps just this:

SQL

SELECT FirstName, LastName, UserCode, TBLTRANSJOBS.JobName, TBLTRANSDOCUMENTS.DocName, AssignedDate
FROM TBLTRANSDOCUMENTS 
inner join TBLTRANSJOBS ON TBLTRANSJOBS.JobID = TBLTRANSDOCUMENTS.JobID

Then slowly add bits back until the problem reoccurs.
When it does, look at what you added and see how that would affect the data.

But we can't do any of that for you!

Posted 25-Oct-18 0:50am

OriginalGriff

Solution 2

Formatting your code to relevant block is a good idea - it helps to find unclosed/wrongly nested code blocks...
In your case there is a missing ) (closing bracket) at the end, after IS NOT NULL...

Posted 25-Oct-18 0:54am

Kornfeld Eliyahu Peter

Solution 3

Further to the valid comments in solutions 1 and 2 here is some more advice...

1. Leave the formatting of output to the UI layer and not the data layer ... so instead of

SQL

TBLTRANSUSERS.FirstName + ' ' + TBLTRANSUSERS.LastName + '(' + TBLTRANSUSERS.UserCode + ')' as 'User Name'

just return all the parts required and let the business or UI layer format it accordingly. I.e.

SQL

TBLTRANSUSERS.FirstName, TBLTRANSUSERS.LastName, TBLTRANSUSERS.UserCode

The immediate benefit of this is that your code can be reused for cases where you don't want to concatenate the fields. It will also run more efficiently.

2. Especially do not convert dates to strings in your SQL. Very inefficient and it also means the calling code has to do a data conversion back to date.

SQL

Convert(Varchar(25),AssignedDate,131) as 'Assigned Date',

should just be

SQL

AssignedDate as 'Assigned Date',

3. Try to get out of the habit of using spaces in field names ('Assigned Date', 'User Name'). It's a real PITA having to use square brackets to refer the column name and can lead to hard-to-find errors being introduced into your code.

4. Be consistent! You have used Table Names to unambiguously identify many of your columns except for AssignedDate ... which table is that coming from? The same is true of the columns in the WHERE clause. You might also want to consider using short ALIAS names for tables - it will save some typing and can also make things a little clearer

5. This

SQL

(
	SELECT RIGHT('0' + CAST(Datediff(second,AssignedDate,GetDate()) / 3600 AS VARCHAR),2) + ':' + 
		RIGHT('0' + CAST((Datediff(second,AssignedDate,GetDate()) / 60) % 60 AS VARCHAR),2) + ':' + 
		RIGHT('0' + CAST(Datediff(second,AssignedDate,GetDate()) % 60 AS VARCHAR),2
) as 'Elapsed Time'

is truly awful but in any event belongs in the UI layer. At most return the time difference in seconds if you really must.

Try applying these changes and compare the return from the SQL Profiler (Run SQL Server Profiler | Microsoft Docs[^] plus there are articles here on CodeProject)

Posted 25-Oct-18 1:25am

CHill60

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Sanjoy Das2 · Accepted Answer · 2018-10-25T01:48:00

SQL

SELECT TBLTRANSUSERS.FirstName + ' ' + TBLTRANSUSERS.LastName + '(' + TBLTRANSUSERS.UserCode + ')' as 'User Name',TBLTRANSJOBS.JobName as 'Job Name', TBLTRANSDOCUMENTS.DocName as 'Loan Name',AssignedDate as 'Assigned Date',
                                            (SELECT RIGHT('00' + CAST(Datediff(second,D.AssignedDate,GetDate()) / 3600 AS VARCHAR),3) + ':' + RIGHT('0' + CAST((Datediff(second,D.AssignedDate,GetDate()) / 60) % 60 AS VARCHAR),2) + ':' + RIGHT('0' + CAST(Datediff(second,D.AssignedDate,GetDate()) % 60 AS VARCHAR),2) FROM TBLTRANSDOCUMENTS D Where D.DocID=TBLTRANSDOCUMENTS.DocID) as 'Elapsed Time'
                                            FROM ((TBLTRANSDOCUMENTS  inner join TBLTRANSJOBS ON TBLTRANSJOBS.JobID = TBLTRANSDOCUMENTS.JobID) inner join TBLTRANSUSERS on TBLTRANSDOCUMENTS.ExtractionAssigned=TBLTRANSUSERS.UserID) WHERE KeyedDate is NULL and 
                                            ExtractionAssigned is not NULL order by TBLTRANSUSERS.Firstname