incorrect syntax near 'b' - 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

incorrect syntax near 'b'

Author

Topic

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-10-12 : 12:08:41

Can someone tell me why I'm receiving this?

I'm using two different tables they both can be joined on the cossn field.

Select Sort='1', Reg='NAT', Region='0', Area='00', Dist='000', Doc='000', cossn, FLG_CDT, PendFoDDSCnt, MFT_POSN1_CD, AGED_ALIEN_RSW
From
(Select FO, count(cossn) as PendFoDDSCnt, cossn, FLG_CDT, MFT_POSN1_CD, AGED_ALIEN_RSW
From T16pendall
Where MFT_POSN1_CD = 'B' or MFT_POSN1_CD='D' or (MFT_POSN1_CD='A' and AGED_ALIEN_RSW='Y')
Group by FO, cossn, FLG_CDT, MFT_POSN1_CD, AGED_ALIEN_RSW
) a

Right join

(Select mvt_typ, mvt_cdt, count(cossn) as PendDDSCnt, MVT_LOC

from t16pendmvt

Where (MVT_TYP='R' and MVT_Loc = 'R**') or MVT_LOC='S**' or MVT_LOC='V**'

Group by mvt_typ, mvt_cdt, MVT_LOC

)b - getting it here

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-10-12 : 12:25:40

Okay I got rid of some fields in the first query:

Select sort='1', Reg='NAT', Region='0', Area='00', Dist='000', Doc='000', totpendfodds

From (Select count(cossn) as TotPendFODDS
From t16pendall t inner join natdocfile n on t.fo = n.doc
WHERE (MFT_POSN1_CD = 'b') OR
(MFT_POSN1_CD = 'd') OR
(MFT_POSN1_CD = 'a') AND (AGED_ALIEN_RSW = 'y')

Group by reg) a

Right join

(Select mvt_cdt, count(cossn) as PendDDSCnt, MVT_LOC

From T16pendMVT

Where (MVT_TYP='R' and MVT_Loc = 'R**') or (MVT_LOC='S**' or MVT_LOC='V**')

Group by reg )b

Still getting incorrect syntax near b What am I missing?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-12 : 12:27:13

Looks like that you have to use an ON clause for your join!

No, you're never too old to Yak'n'Roll if you're too young to die.

Sachin.Nand

2937 Posts

Posted - 2010-10-12 : 12:31:22

It's something like


select * from a right join b ON a.somevalue=b.somevalue

PBUH

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-10-12 : 12:43:04

Okay I added this and getting
The multi-part identifier "tependmvt.cossn" could not be bound
The multi-part identifier "tependall.cossn" could not be bound

Select sort='1', Reg='NAT', Region='0', Area='00', Dist='000', Doc='000', totpendfodds

From (Select count(cossn) as TotPendFODDS
From t16pendall t inner join natdocfile n on t.fo = n.doc
WHERE (MFT_POSN1_CD = 'b') OR
(MFT_POSN1_CD = 'd') OR
(MFT_POSN1_CD = 'a') AND (AGED_ALIEN_RSW = 'y')

Group by fo) a
Right join

(Select count(cossn) as PendDDSCnt

From T16pendMVT c

Where (MVT_TYP='R' and MVT_Loc = 'R**') or (MVT_LOC='S**' or MVT_LOC='V**')

Group by cossn)b

on tpendmvt.cossn = t16pendall.cossn

Sachin.Nand

2937 Posts

Posted - 2010-10-12 : 12:46:01

It should be

on a.cossn = b.cossn

PBUH

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-12 : 12:47:23

quote:
Originally posted by JJ297

Okay I added this and getting
The multi-part identifier "tependmvt.cossn" could not be bound
The multi-part identifier "tependall.cossn" could not be bound

Select sort='1', Reg='NAT', Region='0', Area='00', Dist='000', Doc='000', totpendfodds

From (Select count(cossn) as TotPendFODDS
From t16pendall t inner join natdocfile n on t.fo = n.doc
WHERE (MFT_POSN1_CD = 'b') OR
(MFT_POSN1_CD = 'd') OR
(MFT_POSN1_CD = 'a') AND (AGED_ALIEN_RSW = 'y')

Group by fo) a
Right join

(Select count(cossn) as PendDDSCnt

From T16pendMVT c

Where (MVT_TYP='R' and MVT_Loc = 'R**') or (MVT_LOC='S**' or MVT_LOC='V**')

Group by cossn)b

on tpendmvt.cossn = t16pendall.cossn

on a.TotPendFODDS = b.PendDDSCnt

No, you're never too old to Yak'n'Roll if you're too young to die.

X002548
Not Just a Number

15586 Posts

Posted - 2010-10-12 : 12:50:31

All I can say is to make small pieces of code and see what they do, the bind them together

Don't try and do it all at once

You could hurt yourself

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-10-12 : 12:53:23

Ugh...I tried this and now getting invlaid column name cossn on this(on a.cossn = b.cossn)

Select sort='1', Reg='NAT', Region='0', Area='00', Dist='000', Doc='000', totpendfodds

From (Select count(cossn) as TotPendFODDS
From t16pendall
WHERE (MFT_POSN1_CD = 'b') OR
(MFT_POSN1_CD = 'd') OR
(MFT_POSN1_CD = 'a') AND (AGED_ALIEN_RSW = 'y')

Group by fo) a

Right join

(Select count(cossn) as PendDDSCnt

From T16pendMVT

Where (MVT_TYP='R' and MVT_Loc = 'R**') or (MVT_LOC='S**' or MVT_LOC='V**')

Group by cossn)b

on a.cossn = b.cossn

Here are the fields in the tables:

T16pandall
Cossn char(11)
FLG_CDT char(8)
Aged_alien_RSW char(1)
MFT_POSN1_CD char(1)
FO varchar(3)

T16pendmvt
Cossn char(11)
MVT_TYP char(1)
MVT_CDT char(8)
MVT_Loc char(4)
MVT_DEST char(4)

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-12 : 12:54:53

on a.TotPendFODDS = b.PendDDSCnt

No, you're never too old to Yak'n'Roll if you're too young to die.

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-10-12 : 13:02:33

I tried this but nothing came back

on a.TotPendFODDS = b.PendDDSCnt

I will try to break them down as the first query works. The second doesn't return any records.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-12 : 13:03:46

quote:
Originally posted by JJ297

I tried this but nothing came back

on a.TotPendFODDS = b.PendDDSCnt

I will try to break them down as the first query works. The second doesn't return any records.

Sounds like a plan

No, you're never too old to Yak'n'Roll if you're too young to die.

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2010-10-12 : 13:07:18

First, let's get rid of the old Sybase dialect and write this with as much ANSI/ISO syntax as we can. And let's get rid of excess parentheses and use SQL shorthands instead of writing in the style of older languages without them.

The data elements are really vague. Do you know ISO-11179 naming rules? Why do you think the A and B are meaningful names for the subqueries? Why did you fail to qualify the column names. Your code is much too hard to read.

Where is the ON clause for the outer join?

Why did you do have columns in the SELECT that are not in the GROUP BY?

There was no need to nest queries in A; all but one column are string constants. Most Westerners have languages that read left to right. This makes the LEFT OUTER JOIN quite a bit easier to read.

Here is a re-write to get it into a readable format, but it still needs lots of work.

(SELECT *
FROM
(SELECT mvt_cdt, COUNT(cossn) AS pendddscnt, mvt_loc
FROM T16pendmvt
WHERE mvt_typ = 'R'
AND mvt_loc IN ('R**', 'S**', 'V**')
GROUP BY mvt_cdt, mvt_loc) -- cannot use reg here!

LEFT OUTER JOIN

(SELECT '1' AS sort,
'NAT' AS region_code,
'0' AS region,-- code? name?
'00' AS area,-- measurement, code?
'000' AS dist, -- something district?
'000' AS doc,
COUNT(cossn) AS totpendfodds
FROM T16pendall AS T,
NatDocFile AS N
WHERE ??.fo = N.doc
AND ??.mft_posn1_cd IN ('B', 'D', 'A')
AND n.aged_alien_rsw = 'Y'
GROUP BY ?? .reg) AS A -- needs real name

ON << who knows?>>

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-10-12 : 13:11:54

Thanks for your help. I've got another question and will open it up in a different topic.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources