Solved

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

Posted on 2014-12-12
8
124 Views
Last Modified: 2014-12-12
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.
0
Comment
Question by:bbdesign
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 55

Expert Comment

by:Huseyin KAHRAMAN
ID: 40496586
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
 

Author Comment

by:bbdesign
ID: 40496602
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
 
LVL 55

Expert Comment

by:Huseyin KAHRAMAN
ID: 40496607
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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
LVL 66

Assisted Solution

by:Jim Horn
Jim Horn earned 250 total points
ID: 40496644
>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
 

Author Comment

by:bbdesign
ID: 40496652
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
 
LVL 55

Expert Comment

by:Huseyin KAHRAMAN
ID: 40496668
I see what the problem is:

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

Accepted Solution

by:
Scott Pletcher earned 250 total points
ID: 40496705
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
 

Author Comment

by:bbdesign
ID: 40496762
Thanks for your help, guys.
0

Featured Post

Enroll in June's Course of the Month

June's Course of the Month is now available! Every 10 seconds, a consumer gets hit with ransomware. Refresh your knowledge of ransomware best practices by enrolling in this month's complimentary course for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question