asked on

How to calculate a value in the middle of a SQL query one time, use repeatedly

For better or worse, I have a query which looks similar to this:

select a, b, c, (select d from e where f=g) as h from table where h=i

But I get "invalid column name h". So I have to repeat the sub-query like this:

select a, b, c, (select d from e where f=g) as h from table where (select d from e where f=g)=i

In my actual code, the sub-query is a lot more complex, and having to run it over and over again is causing timeout errors. How can I create that sub-query, run it just one time, but use it in the where clause?

I am doing this with ASP, so I have to run it in a single query statement.

Thank you.

HainKurt

select a, b, c, (select d from e where f=g) as h from table where (select d from e where f=g)=i

>>> maybe this

with h as (select d from e where f=g)
select a, b, c, h.d as h from table, h where h.d=i

Brad Bansner

ASKER

I get:

The multi-part identifier "..." could not be bound.

Because my sub-query is tied to the other items in the recordset, I guess? I'm not selecting a static value in the sub-query, it changes depending on the record.

HainKurt

oops, my mistake, try this structure...

with h as (select d from e where f=g)
select a, b, c, h.d from table, h where h.d=i

Open in new window

what is f,g ? are those columns in table? can you try to create a real example?

SOLUTION

Jim Horn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Brad Bansner

ASKER

Yeah, I don't know (because this is beyond my SQL query writing abilities), but I'm getting the same error. This is my entire query right now, I get:

The multi-part identifier "tbl_masterparts_temp.partno" could not be bound.

with orderdate as (select max(convert(datetime, tbl_order_parts_cust.timestamp)) as orderdate from tbl_order_parts, tbl_order_parts_cust where tbl_order_parts.ordnum=tbl_order_parts_cust.ordnum and tbl_order_parts.partno=tbl_masterparts_temp.partno and tbl_order_parts_cust.status='2')

select distinct tbl_masterparts_temp.lm_index, 
	tbl_masterparts_temp.partno, 
	tbl_masterparts_temp.description, 
	convert(int, tbl_masterparts_temp.quantity) as qtycurrent, 
	(select sum(partpo_orders_items.qty) as qtyonorder from partpo_orders, partpo_orders_items where partpo_orders.lm_index=partpo_orders_items.ordernum and partpo_orders.status in (0, 1, 2) and partpo_orders_items.itemnum=tbl_masterparts_temp.lm_index) as qtyonorder, 
	tbl_masterparts_temp.cost, 
	(select sum(convert(int, qty)) as qtyshipped from tbl_order_parts, tbl_order_parts_cust where tbl_order_parts.ordnum=tbl_order_parts_cust.ordnum and tbl_order_parts.partno=tbl_masterparts_temp.partno and tbl_order_parts_cust.status='2' and convert(datetime, tbl_order_parts_cust.timestamp)>='1/1/2000' and convert(datetime, tbl_order_parts_cust.timestamp)-1<='12/31/2014') as qtyshipped, 
	tbl_masterparts_temp.po_model, 
	(select max(convert(datetime, tbl_order_parts_cust.timestamp)) as orderdate from tbl_order_parts, tbl_order_parts_cust where tbl_order_parts.ordnum=tbl_order_parts_cust.ordnum and tbl_order_parts.partno=tbl_masterparts_temp.partno and tbl_order_parts_cust.status='2') as orderdate

from tbl_masterparts_temp

where partno<>'' and not(partno is NULL)

order by tbl_masterparts_temp.partno

Open in new window

HainKurt

I see what the problem is:

with a as (... you cannot use b here ...)
select ... from a, b

ASKER CERTIFIED SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Brad Bansner

ASKER

Thanks for your help, guys.