| Author |
 Topic  |
|
|
jaimealvarez
Starting Member
29 Posts |
 Posted - 04/20/2012 : 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
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 04/20/2012 : 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
India
227 Posts |
Posted - 04/23/2012 : 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
India
227 Posts |
Posted - 04/23/2012 : 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" |
Edited by - vinu.vijayan on 04/23/2012 09:05:57 |
|
|
| |
Topic |
|
|
|
Help with subqueries
