Help with subqueries - 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

Help with subqueries

Author

Topic

jaimealvarez
Starting Member

31 Posts

Posted - 2012-04-20 : 10:51:52

Hi, I'm new to SQL. I wrote the code below based on examples I have seen, basically I'm trying to combine two subqueries for a report (need to summarize amounts by invoice number). When i try to run the code I get an error of "no column name specified". Any ideas?? Any help will be appreciated. thanks

WITH T AS(
SELECT
timecard.tinvoice AS 'Invoice'
, timecard.tmatter AS 'Matter'
, ISNULL(matter.mdesc1,' ') +' '+ISNULL(matter.mdesc2,' ')+' '+ISNULL(matter.mdesc3,' ') AS 'Description'
, timecard.tbilldt AS 'Billdt'
, Sum(timecard.tbilldol) AS 'TimecardAmount'

FROM son_db.dbo.matter matter
, son_db.dbo.timecard timecard
, son_db.dbo.timekeep timekeep

WHERE matter.mmatter = timecard.tmatter
AND matter.mbillaty = timekeep.tkinit

GROUP BY timecard.tinvoice
, timecard.tmatter
, matter.mdesc1
,matter.mdesc2
,matter.mdesc3
,timecard.tbilldt
, timekeep.tkloc
, timecard.tstatus

HAVING (timecard.tinvoice Is Not Null)
AND (timekeep.tkloc='LDN')
AND (timecard.tstatus='B')
)

,L AS(
SELECT
ledger.linvoice AS 'Linvoice'
, ledger.lmatter AS 'Lmatter'
, matter.mbillaty AS 'BRLID'
, timekeep.tklast + ', ' + timekeep.tkfirst AS 'BRL'
, Min(ledger.ltradat) AS 'ltradat'
, ledger.llcode
, Sum(ledger.lamount) AS 'lfamount'

FROM son_db.dbo.ledger ledger
, son_db.dbo.matter matter
, son_db.dbo.timekeep timekeep

WHERE matter.mmatter = ledger.lmatter
AND matter.mbillaty = timekeep.tkinit

GROUP BY
ledger.linvoice
, ledger.lmatter
, matter.mbillaty
, timekeep.tklast
, timekeep.tkfirst
, ledger.llcode
, timekeep.tkloc

HAVING (ledger.llcode='FEES') AND (timekeep.tkloc='LDN')
)

SELECT t.Invoice, t.Matter, t.Description, l.BRLID, l.BRL, l.ltradr, l.lfamount, t.TimecardAmount
FROM T, L
WHERE t.invoice = l.linvoice AND t.matter = l.lmatter
GROUP BY t.Invoice, t.Matter, t.Description, l.BRLID, l.BRL, l.ltradr, l.lfamount, t.TimecardAmount

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-04-20 : 11:51:10

Visual inspection of the query does not show anything that would indicate that type of error. Usually that error happens when you have a column in the cte that does not have a name. For example, for the third column in T in your query if you did not have the "AS 'Description'", that error would happen. However, in that case, the message would be something like "No column name was specified for column 3 of T". It would/should tell you which column is causing the error.

What is the exact error message you are seeing?
Are you running this in SQL Server Management Studio, or from a client program?

vinu.vijayan
Posting Yak Master

227 Posts

Posted - 2012-04-23 : 09:01:11

Please post the error that you get. Otherwise the code seems fine.

N 28° 33' 11.93148"
E 77° 14' 33.66384"

vinu.vijayan
Posting Yak Master

227 Posts

Posted - 2012-04-23 : 09:03:32

Is this the only query you run in the editor?...Maybe you should try adding ';' before 'When' if its not the only query you have written in the query editor.

If that doesn't work, please post DDL, sample data and the error

N 28° 33' 11.93148"
E 77° 14' 33.66384"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources