| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
03-01-04, 08:22
|
|
Registered User
|
|
Join Date: Jan 2003
Posts: 15
|
|
|
SUM problem
|
|
Code:
SQL = "SELECT * FROM PurchaseCardCostCodes "
SQL = SQL & " INNER JOIN PurchaseCardTransactionLog ON PurchaseCardCostCodes.costcode_id
= PurchaseCardTransactionLog.costcode_id "
SQL = SQL & " WHERE PurchaseCardTransactionLog.tl_date BETWEEN '"&DateOne&"' AND '"&DateTwo&"'"
SQL = SQL & " AND PurchaseCardCostCodes.costcode_owner = '"&User&"'"
SQL = SQL & " AND PurchaseCardCostCodes.costcode_id = '"&RadioButtonVal&"'"
SQL = SQL & " SELECT SUM(tl_net) AS nettot FROM PurchaseCardTransactionLog"
The ASP code doesn't recognise:
Code:
<% nettot = objRec("nettot").Value%>
<td><%=nettot%></td>
The Error is :
Item cannot be found in the collection corresponding to the requested name or ordinal.
I've tried the SUM function on its own, and it works fine, but part of a bigger SQL statement and I get problems 
|
|
03-01-04, 08:53
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,525
|
|
the sql you have does not make sense
it looks like two separate sql statements just smooshed together
|
|
03-01-04, 08:55
|
|
Registered User
|
|
Join Date: Jan 2003
Posts: 15
|
|
|
|
Yeah, I guess changing the last line to
SQL = SQL & " SUM(PurchaseCardTransactionLog.tl_net) AS nettot FROM PurchaseCardTransactionLog"
won't work either
|
|
03-01-04, 10:09
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,525
|
|
okay, i think i see where you want to go with that
try this:
PHP Code:
select CC.col1
, CC.col2
, CC.col3
, sum(TL.tl_net) as nettot
from PurchaseCardCostCodes CC
inner
join PurchaseCardTransactionLog TL
on CC.costcode_id
= TL.costcode_id
where TL.tl_date between [i]DateOne[/i]
and [i]DateTwo[/i]
and CC.costcode_owner = [i]User[/i]
and CC.costcode_id = [i]RadioButtonVal[/i]
group
by CC.col1
, CC.col2
, CC.col3
since you are grouping TL rows to each CC row, you cannot use SELECT *
you need to list the columns you want in the SELECT list and also the GROUP BY clause
|
|
03-01-04, 10:48
|
|
Registered User
|
|
Join Date: Jan 2003
Posts: 15
|
|
Hmm, had a go, but I get more errors:
Ambiguous column name 'costcode_id'.
The bold bit is not from the costcode table, that's where the error is, I think.
Code:
SQL = "SELECT costcode_id, costcode_bc, costcode_unit, costcode_activity, costcode_location, costcode_dept, costcode_owner, SUM(PurchaseCardTransactionLog.tl_net) AS nettot
FROM PurchaseCardCostCodes"
SQL = SQL & " INNER JOIN PurchaseCardTransactionLog ON PurchaseCardCostCodes.costcode_id = PurchaseCardTransactionLog.costcode_id"
SQL = SQL & " WHERE PurchaseCardTransactionLog.tl_date BETWEEN '"&DateOne&"' AND '"&DateTwo&"'"
SQL = SQL & " AND PurchaseCardCostCodes.costcode_owner = '"&User&"'"
SQL = SQL & " AND PurchaseCardCostCodes.costcode_id = '"&RadioButtonVal&"'"
SQL = SQL & " GROUP BY costcode_id, costcode_bc, costcode_unit, costcode_activity, costcode_location, costcode_dept, costcode_owner"
|
|
03-01-04, 10:55
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,525
|
|
it's the very first column after the SELECT keyword
there are two columns in your query with that name
you need to qualify which one you want:
SELECT PurchaseCardCostCodes.costcode_id, ...
|
|
03-02-04, 05:05
|
|
Registered User
|
|
Join Date: Jan 2003
Posts: 15
|
|
Getting there...but still probs:
Code:
SQL = "SELECT PurchaseCardTransactionLog.costcode_id,
PurchaseCardCostCodes.costcode_bc,
PurchaseCardCostCodes.costcode_unit,
PurchaseCardCostCodes.costcode_activity,
PurchaseCardCostCodes.costcode_location,
PurchaseCardCostCodes.costcode_dept,
PurchaseCardCostCodes.costcode_owner, SUM
(PurchaseCardTransactionLog.tl_net) AS nettot FROM PurchaseCardCostCodes "
SQL = SQL & " INNER JOIN PurchaseCardTransactionLog ON
PurchaseCardCostCodes.costcode_id =
PurchaseCardTransactionLog.costcode_id"
SQL = SQL & " WHERE PurchaseCardTransactionLog.tl_date
BETWEEN '"&DateOne&"' AND '"&DateTwo&"'"
SQL = SQL & " AND PurchaseCardCostCodes.costcode_owner
= '"&User&"'"
SQL = SQL & " AND PurchaseCardCostCodes.costcode_id
= '"&RadioButtonVal&"'"
SQL = SQL & " GROUP BY
PurchaseCardTransactionLog.costcode_id,
PurchaseCardCostCodes.costcode_bc, PurchaseCardCostCodes.costcode_unit,
PurchaseCardCostCodes.costcode_activity,
PurchaseCardCostCodes.costcode_location,
PurchaseCardCostCodes.costcode_dept,
PurchaseCardCostCodes.costcode_owner"
I can output all fields from costcode but not TransactionLog.
If I add fields from TL to the SELECT and GroupBy statements, makes no difference.
|
|
03-02-04, 06:21
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,525
|
|
if you add fields from TransactionLog, you can no longer do a SUM() over the TransactionLog grouped rows
i think you need to study grouping a bit more
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
SUM problem
