Incorrect syntax near the keyword 'WHERE'

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

SQL Server 2005 Forums

Transact-SQL (2005)

Incorrect syntax near the keyword 'WHERE'

Author

Topic

duanecwilson
Constraint Violating Yak Guru

273 Posts

Posted - 2009-09-04 : 10:08:18

Can you help me with this? I have tried every combination of syntax except the right one. I have eliminated the brackets, bracketed everything, changed the aliases, etc., but it just won't work. The inner query works fine, however. Thank you.

SELECT COUNT(*)
FROM
    (
         SELECT
             pb.[patient_Id]
             , ptd.[patient_Id]
         FROM
             PT_BASIC pb
             LEFT JOIN PT_DEMOGRAPHIC ptd ON ptd.[patient_Id] = pb.[patient_Id]
    )
WHERE
    ptd.patient_Id IS NULL

Duane

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-09-04 : 10:10:55

SELECT COUNT(*)
FROM
(
SELECT
pb.[patient_Id]
, ptd.[patient_Id]
FROM
PT_BASIC pb
LEFT JOIN PT_DEMOGRAPHIC ptd ON ptd.[patient_Id] = pb.[patient_Id]
)s
WHERE
s.patient_Id IS NULL

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-04 : 10:11:16

[code]SELECT COUNT(*)
FROM
(
SELECT
pb.[patient_Id]
, ptd.[patient_Id]
FROM
PT_BASIC pb
LEFT JOIN PT_DEMOGRAPHIC ptd ON ptd.[patient_Id] = pb.[patient_Id]
) as t
WHERE
ptd.patient_Id IS NULL[/code]

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-04 : 10:11:39

Madhivanan

Failing to plan is Planning to fail

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-09-04 : 10:12:14

quote:

Originally posted by madhivanan

SELECT COUNT(*)
FROM
    (
         SELECT
             pb.[patient_Id]
             , ptd.[patient_Id]
         FROM
             PT_BASIC pb
             LEFT JOIN PT_DEMOGRAPHIC ptd ON ptd.[patient_Id] = pb.[patient_Id]
    ) as t
WHERE 
    ptdt.patient_Id IS NULL

Madhivanan

Failing to plan is Planning to fail

duanecwilson
Constraint Violating Yak Guru

273 Posts

Posted - 2009-09-04 : 10:19:55

Thank you all for the quick responses, but none of them work. In order of 1,2, and 3 (bklr,
madhivanan, and bklr #2), the error messages are as follows.
--1
Msg 8156, Level 16, State 1, Line 1
The column 'patient_Id' was specified multiple times for 's'.

--2
Msg 8156, Level 16, State 1, Line 1
The column 'patient_Id' was specified multiple times for 't'.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "ptd.patient_Id" could not be bound.

--3
Msg 8156, Level 16, State 1, Line 1
The column 'patient_Id' was specified multiple times for 't'.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "ptdt.patient_Id" could not be bound.

Thanks again.

Duane

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-09-04 : 10:21:44

SELECT COUNT(*)
FROM
(
SELECT
pb.[patient_Id]
, ptd.[patient_Id]
FROM
PT_BASIC pb
LEFT JOIN PT_DEMOGRAPHIC ptd ON ptd.[patient_Id] = pb.[patient_Id]
)s
WHERE
s.[patient_Id] IS NULL
r u using the same query which was posted or ur adding some more queries

duanecwilson
Constraint Violating Yak Guru

273 Posts

Posted - 2009-09-04 : 10:29:04

quote:
Originally posted by bklr

SELECT COUNT(*)
FROM
(
SELECT
pb.[patient_Id]
, ptd.[patient_Id]
FROM
PT_BASIC pb
LEFT JOIN PT_DEMOGRAPHIC ptd ON ptd.[patient_Id] = pb.[patient_Id]
)s
WHERE
s.[patient_Id] IS NULL
r u using the same query which was posted or ur adding some more queries

Msg 8156, Level 16, State 1, Line 1
The column 'patient_Id' was specified multiple times for 's'.

I am not sure what u mean by adding more queries. None of these r mine except the first one. And none of them work yet. It is really mysterious.

Duane

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-04 : 10:32:30

SELECT COUNT(*)
FROM
(
SELECT
pb.[patient_Id]
FROM
PT_BASIC pb
LEFT JOIN PT_DEMOGRAPHIC ptd ON ptd.[patient_Id] = pb.[patient_Id]
)s
WHERE
s.[patient_Id] IS NULL
r u using the same query which was posted or ur adding some more queries

Madhivanan

Failing to plan is Planning to fail

duanecwilson
Constraint Violating Yak Guru

273 Posts

Posted - 2009-09-04 : 10:32:54

Even when I leave off the WHERE clause, I still get the error:

SELECT COUNT(*)
FROM
(
SELECT
pb.[patient_Id]
, ptd.[patient_Id]
FROM
PT_BASIC pb
LEFT JOIN PT_DEMOGRAPHIC ptd ON ptd.[patient_Id] = pb.[patient_Id]
)

Msg 102, Level 15, State 1, Line 10
Incorrect syntax near ')'.

Duane

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-09-04 : 10:33:41

You have 2 fields name patient_id in
pb.[patient_Id]
, ptd.[patient_Id]
FROM
PT_BASIC pb
LEFT JOIN PT_DEMOGRAPHIC ptd ON ptd.[patient_Id] = pb.[patient_Id]

Just alias one of them and that should fix it, i.e,
[Patient_ID2] = ptd.[patient_Id]

Jim

Everyday I learn something that somebody else already knew

duanecwilson
Constraint Violating Yak Guru

273 Posts

Posted - 2009-09-04 : 10:34:56

quote:
Originally posted by madhivanan

SELECT COUNT(*)
FROM
(
SELECT
pb.[patient_Id]
FROM
PT_BASIC pb
LEFT JOIN PT_DEMOGRAPHIC ptd ON ptd.[patient_Id] = pb.[patient_Id]
)s
WHERE
s.[patient_Id] IS NULL
r u using the same query which was posted or ur adding some more queries

Madhivanan

Failing to plan is Planning to fail

This one worked. Thank you for seeing it through. I appreciate it. Now I have to analyze why the others didn't work so I don't go through this again.

Duane

duanecwilson
Constraint Violating Yak Guru

273 Posts

Posted - 2009-09-04 : 10:39:23

quote:
Originally posted by jimf

You have 2 fields name patient_id in
pb.[patient_Id]
, ptd.[patient_Id]
FROM
PT_BASIC pb
LEFT JOIN PT_DEMOGRAPHIC ptd ON ptd.[patient_Id] = pb.[patient_Id]

Just alias one of them and that should fix it, i.e,
[Patient_ID2] = ptd.[patient_Id]

Jim

Everyday I learn something that somebody else already knew

Jim, thank you for your input, too. I can understand what went wrong now with my original query. I made the change you told me, and yours worked as well.

Duane

duanecwilson
Constraint Violating Yak Guru

273 Posts

Posted - 2009-09-04 : 10:58:16

I had some other help which also worked, but the ones that worked on this forum yielded 0 results - which is fine, if it is correct, but with the other help, I have this query that yields 275 results. In your opinion, which is correct? Here is the other one:

SELECT COUNT(*)
FROM
    (
         SELECT
             pb.[patient_Id] AS patient_id
             , ptd.[patient_Id] AS patient_id_ptd
         FROM
             PT_BASIC pb
             LEFT JOIN PT_DEMOGRAPHIC ptd ON ptd.[patient_Id] = pb.[patient_Id]
    ) AS T
WHERE
    patient_id_ptd IS NULL;

Duane

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-09-04 : 12:13:03

Does this work?

 SELECT
    COUNT(*)
 FROM
     PT_BASIC pb
 LEFT JOIN 
     PT_DEMOGRAPHIC ptd 
     ON ptd.[patient_Id] = pb.[patient_Id]
 WHERE 
	ptd.[patient_Id] IS NULL

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-09-04 : 12:14:40

The query below is correct, the other queries where couting where the pb.Patient id is null, which is not what you wanted.

quote:
Originally posted by duanecwilson

I had some other help which also worked, but the ones that worked on this forum yielded 0 results - which is fine, if it is correct, but with the other help, I have this query that yields 275 results. In your opinion, which is correct? Here is the other one:
SELECT COUNT(*)
FROM
(
SELECT
pb.[patient_Id] AS patient_id
, ptd.[patient_Id] AS patient_id_ptd
FROM
PT_BASIC pb
LEFT JOIN PT_DEMOGRAPHIC ptd ON ptd.[patient_Id] = pb.[patient_Id]
) AS T
WHERE
patient_id_ptd IS NULL;

Duane

duanecwilson
Constraint Violating Yak Guru

273 Posts

Posted - 2009-09-04 : 16:58:06

Thank you. I appreciate that. I will verify this on Tuesday. Now it's time to go home, though I do think I have something that yields the same as yours now.

Duane

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources