How to reference parameters from 2 queries? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

SQL Server 2000 Forums

Transact-SQL (2000)

How to reference parameters from 2 queries?

Author

Topic

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2005-04-18 : 18:24:56

I need help with 2 queries that I got. First one is the base query and the second is the nested query. I'm not sure if there's a way to reference it by parameters....is there such a way? What I want to do with the 2 queries is use the first query as base and the second query as nested, problem is that I need to reference it by the parameters and I can't seem to figure that out. I want to be able to set the Parameters @OrderID and @ProductCode to the base query. How would I reference that so it will link to the first query.

Example data:

Apple.........$12........$10
Bannana.......$50........$15
Pear..........$25........$40

Anyone can advise me?

Here's my example of the two.

First query:

SELECT DISTINCT CUST_ORDER_LINE.PRODUCT_CODE, SUM(CUST_ORDER_LINE.TOTAL_AMT_SHIPPED) AS [Sale Amount]
FROM CUST_ORDER_LINE INNER JOIN
CUSTOMER_ORDER ON CUST_ORDER_LINE.CUST_ORDER_ID = CUSTOMER_ORDER.ID
WHERE (CUST_ORDER_LINE.PRODUCT_CODE IS NOT NULL) AND (CUST_ORDER_LINE.LAST_SHIPPED_DATE >= @STARTDATE) AND
(CUST_ORDER_LINE.LAST_SHIPPED_DATE <= @ENDDATE)
GROUP BY CUST_ORDER_LINE.PRODUCT_CODE
ORDER BY CUST_ORDER_LINE.PRODUCT_CODE

Second Query:

select sum(act_material_cost+act_labor_cost+act_burden_cost+act_service_cost) as TotalCost,
it.cust_order_id
from inventory_trans it
where transaction_id in (select max(transaction_id)
from cust_order_line col inner join inventory_trans it
on col.cust_order_id = it.cust_order_id and
col.line_no=it.cust_order_line_no inner join
customer_order co on col.cust_order_id = co.id
where col.cust_order_id=@ORDERID
AND COL.PRODUCT_CODE = @PRODUCTCODE
and it.type='O'
and it.class='I'
and col.last_shipped_date between @STARTDATE AND @ENDDATE
group by cust_order_line_no

Attempt to combine:

SELECT DISTINCT CUST_ORDER_LINE.PRODUCT_CODE, SUM(CUST_ORDER_LINE.TOTAL_AMT_SHIPPED) AS [Sale Amount]
FROM CUST_ORDER_LINE INNER JOIN
CUSTOMER_ORDER ON CUST_ORDER_LINE.CUST_ORDER_ID = CUSTOMER_ORDER.ID

LEFT OUTER JOIN

(select sum(act_material_cost+act_labor_cost+act_burden_cost+act_service_cost) as TotalCost,
it.cust_order_id
from inventory_trans it
where transaction_id in (select max(transaction_id)
from cust_order_line col inner join inventory_trans it
on col.cust_order_id = it.cust_order_id and
col.line_no=it.cust_order_line_no inner join
customer_order co on col.cust_order_id = co.id
where col.cust_order_id=@ORDERID
AND COL.PRODUCT_CODE = @PRODUCTCODE
and it.type='O'
and it.class='I'
and col.last_shipped_date between @STARTDATE AND @ENDDATE
group by cust_order_line_no)group by cust_order_id) cost on it.cust_order_id = cost.cust_order_id

WHERE (CUST_ORDER_LINE.PRODUCT_CODE IS NOT NULL) AND (CUST_ORDER_LINE.LAST_SHIPPED_DATE >= @STARTDATE) AND
(CUST_ORDER_LINE.LAST_SHIPPED_DATE <= @ENDDATE)
GROUP BY CUST_ORDER_LINE.PRODUCT_CODE
ORDER BY CUST_ORDER_LINE.PRODUCT_CODE

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-04-18 : 19:06:35

It is almost impossible for us to help out unless you provide DDL for your tables, INSERT INTO statements for sample data, and the expected result set using that sample data. The DDL and sample data doesn't even have to be real but they need to show what you are trying to do. Please see this recent thread that I started for more information:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47870

The declares are the DDL that I provided for my tables. I stripped out all non-essential columns and used a table variable to make this easier. Then I inserted the minimum amount of rows into them that describe what I want. Typically you'd also show us the expected result set using the sample data. I didn't do that here as it didn't apply since I already had a working solution that I was questioning.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources