Anyone know a good SQL forum?

[cmhoz]

Hi All -

I'm in search of a good SQL forum (someplace I can get some help with some SQL code that just won't work).

I've found a couple that looked promising.. but responses are very slow and not terribly useful.

Anyone know a good board??

Thanks!

 

[SydneyGeek]

UtterAccess is the biggest of the Access forums, and you could try VBA Express as well.

What is the code, by the way... have you asked here?

Denis

 

[cmhoz]

Haven't asked it here... figure it wasn't really the right place... but just in case...

I'm working in SQL 2005 and have the following code:
_______________________________
select
hdr.EXTCODE,
hdr.ACCOUNTNUM,
hdr.FROMDATE,
hdr.TOTSCR,
dt_AGDnum.AGDnum


from
dbo.EXTRACTIONS hdr
join dbo.extdata dta on hdr.extcode = dta.extcode
join dbo.ACTIVED prd on dta.MNHSCODE = prd.NHSCODE
join (select ext.extcode, sum(dta.QTY) as AGDnum from extdata ext join actived prd on
dta.MNHSCODE = prd.NHSCODE where prd.agdn = '1') as dt_AGDnum where
ext.extcode = hdr.extcode


where
hdr.extcode = 'CHACEL_P40105_20090101_20090131_20090224_SC_0020028W'

group by
hdr.EXTCODE,
hdr.ACCOUNTNUM,
hdr.FROMDATE,
hdr.TOTSCR,
dt_AGDnum.AGDnum
______________________________

But I'm getting this error:
Msg 156, Level 15, State 1, Line 22
Incorrect syntax near the keyword 'where'.


I USED to have this code:
_________________________________
select
hdr.EXTCODE,
hdr.ACCOUNTNUM,
hdr.FROMDATE,
hdr.TOTSCR,
case prd.AGDN when '1' then sum(dta.QTY) else 0 end) as AGDdnum

from
dbo.EXTRACTIONS hdr
join dbo.extdata dta on hdr.extcode = dta.extcode
join dbo.ACTIVED prd on dta.MNHSCODE = prd.NHSCODE

where
hdr.extcode = 'CHACEL_P40105_20090101_20090131_20090224_SC_00200 28W'

group by
hdr.EXTCODE,
hdr.ACCOUNTNUM,
hdr.FROMDATE,
hdr.TOTSCR,
prd.AGDN
_________________________________

It does return the correct result, but it returns 2 lines of results because I have to include the 'prd.AGDN' in the group by (AGDN can be 1 or 0).

So... much time spent with google and I attempted the derived table version... but I have failed somewhere and I have no clue what's causing the Msg 156 error.


And SQL folks here know what I'm doing wrong??

 

[SydneyGeek]

You have 2 consecutive WHERE clauses...

where 
ext.extcode = hdr.extcode
where
hdr.extcode = 'CHACEL_P40105_20090101_20090131_20090224_SC_0020028W'

That's a no-no in SQL. If the first bit is part of a subquery, wrap the whole subquery in parentheses. If you want 2 conditions try using AND:

where ext.extcode = hdr.extcode 
and hdr.extcode = 'CHACEL_P40105_20090101_20090131_20090224_SC_0020028W'

Denis

 

[cmhoz]

hmm.. okay. I fixed my double where - tried parenthesis and still got the same error, so I tried to just join with the usual 'on' and now I have this

___________________________
select
hdr.EXTCODE,
hdr.ACCOUNTNUM,
hdr.FROMDATE,
hdr.TOTSCR,
dt_AGDnum.AGDnum


from
dbo.EXTRACTIONS hdr
join dbo.extdata dta on hdr.extcode = dta.extcode
join dbo.ACTIVED prd on dta.MNHSCODE = prd.NHSCODE
join (select ext.extcode, sum(ext.QTY) as AGDnum from extdata ext join actived prd on ext.MNHSCODE = prd.NHSCODE where prd.agdn = '1' group by ext.extcode) as dt_AGDnum on ext.extcode = hdr.extcode


where
hdr.extcode = 'CHACEL_P40105_20090101_20090131_20090224_SC_0020028W'

group by
hdr.EXTCODE,
hdr.ACCOUNTNUM,
hdr.FROMDATE,
hdr.TOTSCR,
dt_AGDnum.AGDnum
____________________________

And I'm getting a new error:
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "ext.extcode" could not be bound.

 

[cmhoz]

Ha! Figured it out...

had to change "as dt_AGDnum on ext.extcode = hdr.extcode" to "as dt_AGDnum on dt_AGDnum.extcode = hdr.extcode"

 

[SydneyGeek]

In this bit...

from extdata ext join actived prd

It looks like you haven't defined which database ext or prd comes from. All the other tables have dbo. in front; these refs do not.

Just saw your latest reply. Good to see it's sorted.

Denis