?
Solved

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

Posted on 2014-12-12
8
Medium Priority
?
126 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 57

Expert Comment

by:HainKurt
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 57

Expert Comment

by:HainKurt
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
What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

 
LVL 66

Assisted Solution

by:Jim Horn
Jim Horn earned 1000 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 57

Expert Comment

by:HainKurt
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 1000 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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
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
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

770 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