SQL Query

Hi i ahve the following Query

SELECT     TOP (100) PERCENT (CASE WHEN dbo.itran.it_fcrate = 0 THEN (dbo.itran.it_qtyinv * dbo.itran.it_price / 100)
                      ELSE (dbo.itran.it_qtyinv * dbo.itran.it_price / dbo.itran.it_fcrate / 100) END) AS it_total_gbp, dbo.itran.it_doc, dbo.itran.it_quan, dbo.itran.it_qtyinv,
                      (CASE WHEN dbo.itran.it_fcrate = 0 THEN (dbo.itran.it_qtyinv * dbo.itran.it_price / 100) ELSE (dbo.itran.it_qtyinv * dbo.itran.it_price / dbo.itran.it_fcrate / 100) END)
                      AS it_total_Invoice_Value, (CASE WHEN dbo.itran.it_fcrate = 0 THEN (dbo.itran.it_qtyinv * dbo.itran.it_price / 100)
                      ELSE (dbo.itran.it_qtyinv * dbo.itran.it_price / dbo.itran.it_fcrate / 100) END) - (CASE WHEN dbo.itran.it_fcrate = 0 THEN (dbo.itran.it_qtyinv * dbo.itran.it_price / 100)
                      ELSE (dbo.itran.it_qtyinv * dbo.itran.it_price / dbo.itran.it_fcrate / 100) END) AS OutstandingValue, dbo.itran.it_due, dbo.ihead.ih_orddate, dbo.ihead.ih_invoice,
                      dbo.itran.it_fcrate, dbo.itran.it_price, dbo.itran.it_anal, dbo.itran.it_stock, dbo.itran.it_status, dbo.ihead.ih_name, dbo.ihead.ih_account, dbo.itran.it_recno,
                      dbo.ihead.ih_sorder, dbo.itran.it_qtydelv, dbo.ihead.ih_credit
FROM         dbo.itran LEFT OUTER JOIN
                      dbo.ihead ON dbo.itran.it_doc = dbo.ihead.ih_doc
WHERE     (dbo.ihead.ih_sorder > ' ') AND (dbo.itran.it_status = 'A') AND (dbo.ihead.ih_account = 'acr001')
ORDER BY dbo.ihead.ih_account

But i need to say in the where clause WHERE OUTSTANDING VALUE IS GREATER THAN 0.

Can anybody assist as i cannot seem to get it working

thankyou
pepps11976Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

Roman GhermanSenior Software EngineerCommented:
SELECT *
FROM
(SELECT     TOP (100) PERCENT (CASE WHEN dbo.itran.it_fcrate = 0 THEN (dbo.itran.it_qtyinv * dbo.itran.it_price / 100)
                      ELSE (dbo.itran.it_qtyinv * dbo.itran.it_price / dbo.itran.it_fcrate / 100) END) AS it_total_gbp, dbo.itran.it_doc, dbo.itran.it_quan, dbo.itran.it_qtyinv,
                      (CASE WHEN dbo.itran.it_fcrate = 0 THEN (dbo.itran.it_qtyinv * dbo.itran.it_price / 100) ELSE (dbo.itran.it_qtyinv * dbo.itran.it_price / dbo.itran.it_fcrate / 100) END)
                      AS it_total_Invoice_Value, (CASE WHEN dbo.itran.it_fcrate = 0 THEN (dbo.itran.it_qtyinv * dbo.itran.it_price / 100)
                      ELSE (dbo.itran.it_qtyinv * dbo.itran.it_price / dbo.itran.it_fcrate / 100) END) - (CASE WHEN dbo.itran.it_fcrate = 0 THEN (dbo.itran.it_qtyinv * dbo.itran.it_price / 100)
                      ELSE (dbo.itran.it_qtyinv * dbo.itran.it_price / dbo.itran.it_fcrate / 100) END) AS OutstandingValue, dbo.itran.it_due, dbo.ihead.ih_orddate, dbo.ihead.ih_invoice,
                      dbo.itran.it_fcrate, dbo.itran.it_price, dbo.itran.it_anal, dbo.itran.it_stock, dbo.itran.it_status, dbo.ihead.ih_name, dbo.ihead.ih_account, dbo.itran.it_recno,
                      dbo.ihead.ih_sorder, dbo.itran.it_qtydelv, dbo.ihead.ih_credit
FROM         dbo.itran LEFT OUTER JOIN
                      dbo.ihead ON dbo.itran.it_doc = dbo.ihead.ih_doc
WHERE     (dbo.ihead.ih_sorder > ' ') AND (dbo.itran.it_status = 'A') AND (dbo.ihead.ih_account = 'acr001')
ORDER BY dbo.ihead.ih_account) as t1
WHERE OutstandingValue > 0
pepps11976Author Commented:
Hi Thanks for replying

Pasted query in ok but it returns no values
mayank_joshiCommented:
try this ( ive made an alteration after last join condition)

SELECT     TOP (100) PERCENT (CASE WHEN dbo.itran.it_fcrate = 0 THEN (dbo.itran.it_qtyinv * dbo.itran.it_price / 100)
                      ELSE (dbo.itran.it_qtyinv * dbo.itran.it_price / dbo.itran.it_fcrate / 100) END) AS it_total_gbp, dbo.itran.it_doc, dbo.itran.it_quan, dbo.itran.it_qtyinv,
                      (CASE WHEN dbo.itran.it_fcrate = 0 THEN (dbo.itran.it_qtyinv * dbo.itran.it_price / 100) ELSE (dbo.itran.it_qtyinv * dbo.itran.it_price / dbo.itran.it_fcrate / 100) END)
                      AS it_total_Invoice_Value, (CASE WHEN dbo.itran.it_fcrate = 0 THEN (dbo.itran.it_qtyinv * dbo.itran.it_price / 100)
                      ELSE (dbo.itran.it_qtyinv * dbo.itran.it_price / dbo.itran.it_fcrate / 100) END) - (CASE WHEN dbo.itran.it_fcrate = 0 THEN (dbo.itran.it_qtyinv * dbo.itran.it_price / 100)
                      ELSE (dbo.itran.it_qtyinv * dbo.itran.it_price / dbo.itran.it_fcrate / 100) END) AS OutstandingValue, dbo.itran.it_due, dbo.ihead.ih_orddate, dbo.ihead.ih_invoice,
                      dbo.itran.it_fcrate, dbo.itran.it_price, dbo.itran.it_anal, dbo.itran.it_stock, dbo.itran.it_status, dbo.ihead.ih_name, dbo.ihead.ih_account, dbo.itran.it_recno,
                      dbo.ihead.ih_sorder, dbo.itran.it_qtydelv, dbo.ihead.ih_credit
FROM         dbo.itran LEFT OUTER JOIN
                      dbo.ihead ON dbo.itran.it_doc = dbo.ihead.ih_doc
and     (dbo.ihead.ih_sorder > ' ') AND (dbo.itran.it_status = 'A') AND (dbo.ihead.ih_account = 'acr001')
where OutstandingValue > 0
ORDER BY dbo.ihead.ih_account

Open in new window

The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

Rikin ShahMicrosoft Dynamics CRM ConsultantCommented:
SELECT     TOP (100) PERCENT (CASE WHEN dbo.itran.it_fcrate = 0 THEN (dbo.itran.it_qtyinv * dbo.itran.it_price / 100)
                      ELSE (dbo.itran.it_qtyinv * dbo.itran.it_price / dbo.itran.it_fcrate / 100) END) AS it_total_gbp, dbo.itran.it_doc, dbo.itran.it_quan, dbo.itran.it_qtyinv,
                      (CASE WHEN dbo.itran.it_fcrate = 0 THEN (dbo.itran.it_qtyinv * dbo.itran.it_price / 100) ELSE (dbo.itran.it_qtyinv * dbo.itran.it_price / dbo.itran.it_fcrate / 100) END)
                      AS it_total_Invoice_Value, (CASE WHEN dbo.itran.it_fcrate = 0 THEN (dbo.itran.it_qtyinv * dbo.itran.it_price / 100)
                      ELSE (dbo.itran.it_qtyinv * dbo.itran.it_price / dbo.itran.it_fcrate / 100) END) - (CASE WHEN dbo.itran.it_fcrate = 0 THEN (dbo.itran.it_qtyinv * dbo.itran.it_price / 100)
                      ELSE (dbo.itran.it_qtyinv * dbo.itran.it_price / dbo.itran.it_fcrate / 100) END) AS OutstandingValue, dbo.itran.it_due, dbo.ihead.ih_orddate, dbo.ihead.ih_invoice,
                      dbo.itran.it_fcrate, dbo.itran.it_price, dbo.itran.it_anal, dbo.itran.it_stock, dbo.itran.it_status, dbo.ihead.ih_name, dbo.ihead.ih_account, dbo.itran.it_recno,
                      dbo.ihead.ih_sorder, dbo.itran.it_qtydelv, dbo.ihead.ih_credit
FROM         dbo.itran LEFT OUTER JOIN
                      dbo.ihead ON dbo.itran.it_doc = dbo.ihead.ih_doc
WHERE     (dbo.ihead.ih_sorder > ' ') AND (dbo.itran.it_status = 'A') AND (dbo.ihead.ih_account = 'acr001')
AND (dbo.itran.it_qtyinv * dbo.itran.it_price / dbo.itran.it_fcrate / 100) > 0
ORDER BY dbo.ihead.ih_account

Open in new window

mayank_joshiCommented:
replacing where with  and can do the trick.
Roman GhermanSenior Software EngineerCommented:
if my script doesn't return any values means you don't have any
pepps11976Author Commented:
Getting Invalid Column Name ' OutstandingValue'
mayank_joshiCommented:
plz can you tell what do you exactly mean by ' OutstandingValue'
Roman GhermanSenior Software EngineerCommented:
OutstandingValue is this

 (CASE WHEN dbo.itran.it_fcrate = 0 THEN (dbo.itran.it_qtyinv * dbo.itran.it_price / 100)
                      ELSE (dbo.itran.it_qtyinv * dbo.itran.it_price / dbo.itran.it_fcrate / 100) END) - (CASE WHEN dbo.itran.it_fcrate = 0 THEN (dbo.itran.it_qtyinv * dbo.itran.it_price / 100)
                      ELSE (dbo.itran.it_qtyinv * dbo.itran.it_price / dbo.itran.it_fcrate / 100) END) AS OutstandingValue
Roman GhermanSenior Software EngineerCommented:
OutstandingValue can not be included in the where statement right in the statement where it is calculated

either you need to put that case in the were statement which is very slow and better do a select in select
pepps11976Author Commented:
Basically what i am trying to do is work out Outstanding Value Owed from a total Invoice value

so i have a it_total_gbp Column which is the total amount for parts ordered.

Then if we say only ship 5 out of the 10 parts ordered and we invoice for 5, i then have a column called it_total_invoice_value.

The outstanding value would be the it_total_invoice_value -  it_total_gbp to give me the outstanding value
mayank_joshiCommented:
try this:-

with cte as
(
SELECT     TOP (100) PERCENT (CASE WHEN dbo.itran.it_fcrate = 0 THEN (dbo.itran.it_qtyinv * dbo.itran.it_price / 100)
                      ELSE (dbo.itran.it_qtyinv * dbo.itran.it_price / dbo.itran.it_fcrate / 100) END) AS it_total_gbp, dbo.itran.it_doc, dbo.itran.it_quan, dbo.itran.it_qtyinv,
                      (CASE WHEN dbo.itran.it_fcrate = 0 THEN (dbo.itran.it_qtyinv * dbo.itran.it_price / 100) ELSE (dbo.itran.it_qtyinv * dbo.itran.it_price / dbo.itran.it_fcrate / 100) END)
                      AS it_total_Invoice_Value, (CASE WHEN dbo.itran.it_fcrate = 0 THEN (dbo.itran.it_qtyinv * dbo.itran.it_price / 100)
                      ELSE (dbo.itran.it_qtyinv * dbo.itran.it_price / dbo.itran.it_fcrate / 100) END) - (CASE WHEN dbo.itran.it_fcrate = 0 THEN (dbo.itran.it_qtyinv * dbo.itran.it_price / 100)
                      ELSE (dbo.itran.it_qtyinv * dbo.itran.it_price / dbo.itran.it_fcrate / 100) END) AS OutstandingValue, dbo.itran.it_due, dbo.ihead.ih_orddate, dbo.ihead.ih_invoice,
                      dbo.itran.it_fcrate, dbo.itran.it_price, dbo.itran.it_anal, dbo.itran.it_stock, dbo.itran.it_status, dbo.ihead.ih_name, dbo.ihead.ih_account, dbo.itran.it_recno,
                      dbo.ihead.ih_sorder, dbo.itran.it_qtydelv, dbo.ihead.ih_credit
FROM         dbo.itran LEFT OUTER JOIN
                      dbo.ihead ON dbo.itran.it_doc = dbo.ihead.ih_doc
and     (dbo.ihead.ih_sorder > ' ') AND (dbo.itran.it_status = 'A') AND (dbo.ihead.ih_account = 'acr001')
)
select * from cte where OutstandingValue > 0
ORDER BY dbo.ihead.ih_account

Open in new window

Roman GhermanSenior Software EngineerCommented:
does your initial statement return anything where outstanding value > 0 ?????
pepps11976Author Commented:
Ok with that query i get the multi part identifier dbo.ihead.ih_account could not be bound
mayank_joshiCommented:
use cte.ih_account instead
mayank_joshiCommented:
with cte as
(
SELECT     TOP (100) PERCENT (CASE WHEN dbo.itran.it_fcrate = 0 THEN (dbo.itran.it_qtyinv * dbo.itran.it_price / 100)
                      ELSE (dbo.itran.it_qtyinv * dbo.itran.it_price / dbo.itran.it_fcrate / 100) END) AS it_total_gbp, dbo.itran.it_doc, dbo.itran.it_quan, dbo.itran.it_qtyinv,
                      (CASE WHEN dbo.itran.it_fcrate = 0 THEN (dbo.itran.it_qtyinv * dbo.itran.it_price / 100) ELSE (dbo.itran.it_qtyinv * dbo.itran.it_price / dbo.itran.it_fcrate / 100) END)
                      AS it_total_Invoice_Value, (CASE WHEN dbo.itran.it_fcrate = 0 THEN (dbo.itran.it_qtyinv * dbo.itran.it_price / 100)
                      ELSE (dbo.itran.it_qtyinv * dbo.itran.it_price / dbo.itran.it_fcrate / 100) END) - (CASE WHEN dbo.itran.it_fcrate = 0 THEN (dbo.itran.it_qtyinv * dbo.itran.it_price / 100)
                      ELSE (dbo.itran.it_qtyinv * dbo.itran.it_price / dbo.itran.it_fcrate / 100) END) AS OutstandingValue, dbo.itran.it_due, dbo.ihead.ih_orddate, dbo.ihead.ih_invoice,
                      dbo.itran.it_fcrate, dbo.itran.it_price, dbo.itran.it_anal, dbo.itran.it_stock, dbo.itran.it_status, dbo.ihead.ih_name, dbo.ihead.ih_account, dbo.itran.it_recno,
                      dbo.ihead.ih_sorder, dbo.itran.it_qtydelv, dbo.ihead.ih_credit
FROM         dbo.itran LEFT OUTER JOIN
                      dbo.ihead ON dbo.itran.it_doc = dbo.ihead.ih_doc
and     (dbo.ihead.ih_sorder > ' ') AND (dbo.itran.it_status = 'A') AND (dbo.ihead.ih_account = 'acr001')
)
select * from cte where OutstandingValue > 0
ORDER BY cte.ih_account

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
Roman GhermanSenior Software EngineerCommented:
pepps11976
actually there is no difference in what is done in the accepted solution and my solution - first comment.

Can you explain why the last was accepted as solution?
mayank_joshiCommented:
the difference is here:-

FROM         dbo.itran LEFT OUTER JOIN
                      dbo.ihead ON dbo.itran.it_doc = dbo.ihead.ih_doc
and     (dbo.ihead.ih_sorder > ' ') AND (dbo.itran.it_status = 'A') AND (dbo.ihead.ih_account = 'acr001')

Open in new window


i ve used and in place of where
Roman GhermanSenior Software EngineerCommented:
that means that the author has wrong results because the question was how he selects from what he has those records where  OUTSTANDING VALUE IS GREATER THAN 0


"But i need to say in the where clause WHERE OUTSTANDING VALUE IS GREATER THAN 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 2008

From novice to tech pro — start learning today.