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.
Brad BansnerWeb DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

HainKurtSr. System AnalystCommented:
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
0
Brad BansnerWeb DeveloperAuthor Commented:
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.
0
HainKurtSr. System AnalystCommented:
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?
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>But I get "invalid column name h".
Correct.  The way SQL Server Order of Execution for processing queries is essentially FROM > WHERE > GROUP BY > HAVING > SELECT > ORDER BY, so when it processes the WHERE h doesn't exist yet, as it's in the SELECT later down the processing order.

So, you'll need a subquery to pull this off.  Something like..
SELECT t1.a, t1.b, t1.c, t1.h
FROM table t1
JOIN (
   SELECT select a, b, c, (select d from e where f=g) as h 
   from table) t2 ON t2.h = t1.i 

Open in new window

0
Brad BansnerWeb DeveloperAuthor Commented:
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

0
HainKurtSr. System AnalystCommented:
I see what the problem is:

with a as (... you cannot use b here ...)
select ... from a, b
0
Scott PletcherSenior DBACommented:
You need OUTER APPLY (or CROSS APPLY if the value will always be present).  You can then use that new column in any other part of the SELECT (except a join that precedes the APPLY).


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,
      apply1.orderdate

from tbl_masterparts_temp
outer apply (
    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 apply1


where partno<>'' and not(partno is NULL)
    and apply1.orderdate ...some condition goes here...

order by tbl_masterparts_temp.partno
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Brad BansnerWeb DeveloperAuthor Commented:
Thanks for your help, guys.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.