Solved

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

Posted on 2014-12-12
8
123 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 52

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 52

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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 
LVL 65

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 52

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

How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Problem Backingh Up Transaction Log on Old SQL000 Database 3 26
Sql server Error message 3 16
SQL syntax question 6 44
SQL 2012 Instance Problem 3 61
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

726 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