August 30, 2006 at 9:21 am
All,
I written the following code and just don't see what the problem is. Running it on SQL 2005.
select o.Portfolio , sum(TXFAMT) as TotGL, a.CTACCT
from
broker b, outmgd o, glpct a, GLPTX t, GLPTH h
inner
join (select round(sum([Column 17]), 2), [Column 0] from
ptopost_tab
where [Column 3] = 'caus'
group
by [Column 0]) p on o.Portfolio = p.[Column 0]
where
b.BrokerCode = upper(substring(o.Portfolio, 6, 3))
and
(substring(o.Portfolio, 3, 3) + b.BrokerAccount) = a.CTACCT
and
a.CTPAGE = t.TXPAGE
and
TXJRNL = THJRNL
and
TXMORS = 'M'
and
TH8DAT <= 08242006
group
by TXPAGE, a.CTACCT, o.Portfolio
order
by CTACCT
Table ptopost_tab has multiple rows, as does GLPTX. I need the sum of amounts from both. When I try and run the above, I get the following error:
Msg 8155, Level 16, State 2, Line 1
No column was specified for column 1 of 'p'.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "o.Portfolio" could not be bound.
Am I over-complicating things? What am I missing? I've fooled around with trying to get this working for the last 1 and 1/2 days and think it's total frustration setting in.
ptopost_tab is defined as follows:
Column 0 nvarchar no 16 yes (n/a) (n/a) SQL_Latin1_General_CP1_CI_AS
Column 1 nvarchar no 4 yes (n/a) (n/a) SQL_Latin1_General_CP1_CI_AS
Column 2 nvarchar no 140 yes (n/a) (n/a) SQL_Latin1_General_CP1_CI_AS
Column 3 nvarchar no 8 yes (n/a) (n/a) SQL_Latin1_General_CP1_CI_AS
Column 4 nvarchar no 18 yes (n/a) (n/a) SQL_Latin1_General_CP1_CI_AS
Column 5 int no 4 10 0 yes (n/a) (n/a) NULL
Column 6 nvarchar no 2 yes (n/a) (n/a) SQL_Latin1_General_CP1_CI_AS
Column 7 nvarchar no 2 yes (n/a) (n/a) SQL_Latin1_General_CP1_CI_AS
Column 8 real no 4 24 NULL yes (n/a) (n/a) NULL
Column 9 nvarchar no 2 yes (n/a) (n/a) SQL_Latin1_General_CP1_CI_AS
Column 10 nvarchar no 2 yes (n/a) (n/a) SQL_Latin1_General_CP1_CI_AS
Column 11 nvarchar no 2 yes (n/a) (n/a) SQL_Latin1_General_CP1_CI_AS
Column 12 nvarchar no 2 yes (n/a) (n/a) SQL_Latin1_General_CP1_CI_AS
Column 13 nvarchar no 2 yes (n/a) (n/a) SQL_Latin1_General_CP1_CI_AS
Column 14 nvarchar no 2 yes (n/a) (n/a) SQL_Latin1_General_CP1_CI_AS
Column 15 nvarchar no 2 yes (n/a) (n/a) SQL_Latin1_General_CP1_CI_AS
Column 16 nvarchar no 2 yes (n/a) (n/a) SQL_Latin1_General_CP1_CI_AS
Column 17 real no 4 24 NULL yes (n/a) (n/a) NULL
TIA.
Terry
August 30, 2006 at 9:31 am
You are creating a derived table and calling it "p".
Right here:
inner join ( select round(sum([Column 17]), 2),
The expression I've highlighted in red is "column 1" as the error is telling you. You haven't given it a name that it can be referenced by:
inner join (
select round(sum([Column 17]), 2) AS [WhateverYouWantToCallIt],
August 30, 2006 at 9:57 am
That seems to have fixed the column 1 issue. Thank you. Now, what about not being able to bind o.portfolio?
Terry
August 30, 2006 at 10:40 am
Stupid question... Are you sure that this column exists in the table "outmgd"?
August 30, 2006 at 10:49 am
It does. I've googled the error and I'm still sorting through all the hits. Hoping for a quicker answer though!
Terry
August 30, 2006 at 10:56 am
Well it's not a syntax error for sure.
Can you post the rest of the tables definition (you can use enterprise manager to genererate a script for you).?
Can you run this query on your database and tell me what it returns?
Select
Count(*) as Total from dbo.SysObjects where name = 'outmgd'
August 30, 2006 at 11:02 am
I think you should focus on 1 SQL style or another. You have some tables that you are joining in the FROM and some in the WHERE. Either go ANSI standard or not.
select o.Portfolio , sum(TXFAMT) as TotGL, a.CTACCT
from broker b
Inner Join outmgd o
On ( b.BrokerCode = upper(substring(o.Portfolio, 6, 3)) )
Inner Join glpct a
On ( (substring(o.Portfolio, 3, 3) + b.BrokerAccount) = a.CTACCT )
Inner Join GLPTX t
On ( a.CTPAGE = t.TXPAGE )
Inner Join GLPTH h
{Can't help you here - where's "h" in youe WHERE clause ??}
Inner join (
select round(sum([Column 17]), 2) As Whatever, [Column 0]
from ptopost_tab
where [Column 3] = 'caus'
group by [Column 0]
) p
on o.Portfolio = p.[Column 0]
-- Are these JOINs or FILTERs ?
and TXJRNL = THJRNL
and TXMORS = 'M'
and TH8DAT <= 08242006
group
by TXPAGE, a.CTACCT, o.Portfolio
order by CTACCT
August 30, 2006 at 11:54 am
OK. That kind of worked (see modifications below). It's still not returning the sum of [Column 17] as PTO_Sum, it's simply returning the first three fieldws in the main select. That snippet of code will, however, run on it's own.
select o.Portfolio , sum(TXFAMT) as TotGL, a.CTACCT
from
broker b
Inner
Join outmgd o
On ( b.BrokerCode = upper(substring(o.Portfolio, 6, 3)) )
Inner
Join glpct a
On ( (substring(o.Portfolio, 3, 3) + b.BrokerAccount) = a.CTACCT )
Inner
Join GLPTX t
On ( a.CTPAGE = t.TXPAGE )
Inner
Join GLPTH h
On (t.TXJRNL = h.THJRNL )
Inner
join (
select round(sum([Column 17]), 2) As PTO_Sum, [Column 0]
from ptopost_tab
where [Column 3] = 'caus'
group by [Column 0]
)
p
on (o.Portfolio = p.[Column 0])
--Filters
where
t.TXMORS = 'M'
and
h.TH8DAT <= 08242006
group
by TXPAGE, a.CTACCT, o.Portfolio
order
by CTACCT
Terry
August 30, 2006 at 12:15 pm
>>It's still not returning the sum of [Column 17] as PTO_Sum,
Right. Because you haven't included that column in the outer SELECT. 1st line of your query should be:
select o.Portfolio , sum(TXFAMT) as TotGL, a.CTACCT, Sum(p.PTO_Sum) As PTO_Sum
August 30, 2006 at 2:04 pm
Perhaps I'm not explaining it correctly. I need two subtotals, from different tables. If I put this in the select, it yields incorrect results. Let me know what I can provide in order to fully explain my need. I swear I found something on this site last night showing that this can be accomplished using the inner join very similar to what I've posted. The claim was it would work so I adapted it to my needs (or so I thought). I'll search for it to see if I can point you to that.
Terry
August 30, 2006 at 2:23 pm
Needed :
- Table definitions with indexes and keys
- Sample data (really helpfull if you provide the insert statements too)
- Required output from that sample data
- The query you are currently using.
Then we'll most likely be able to figure out what's going on.
August 31, 2006 at 8:19 am
If I understand you correctly, Pw was on the right track, except that you've already summed [column 17] in the subquery for each distinct [Column 0], so just include o.PTO_Sum in the outer select:
select o.Portfolio
, sum(TXFAMT) as TotGL
, a.CTACCT
, o.PTO_Sum
from broker b
Inner Join outmgd o
On ( b.BrokerCode = upper(substring(o.Portfolio, 6, 3)) )
Inner Join glpct a
On ( (substring(o.Portfolio, 3, 3) + b.BrokerAccount) = a.CTACCT )
Inner Join GLPTX t
On ( a.CTPAGE = t.TXPAGE )
Inner Join GLPTH h
On (t.TXJRNL = h.THJRNL )
Inner join (select round(sum([Column 17]), 2) As PTO_Sum
, [Column 0]
from ptopost_tab
where [Column 3] = 'caus'
group by [Column 0]
) p
On (o.Portfolio = p.[Column 0])
where t.TXMORS = 'M'
and h.TH8DAT <= 08242006
group by TXPAGE, a.CTACCT, o.Portfolio
order by CTACCT
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply