pepps11976
asked on
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
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
ASKER
Hi Thanks for replying
Pasted query in ok but it returns no values
Pasted query in ok but it returns no values
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
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
replacing where with and can do the trick.
if my script doesn't return any values means you don't have any
ASKER
Getting Invalid Column Name ' OutstandingValue'
plz can you tell what do you exactly mean by ' OutstandingValue'
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
(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
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
either you need to put that case in the were statement which is very slow and better do a select in select
ASKER
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
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
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
does your initial statement return anything where outstanding value > 0 ?????
ASKER
Ok with that query i get the multi part identifier dbo.ihead.ih_account could not be bound
use cte.ih_account instead
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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?
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?
the difference is here:-
i ve used and in place of where
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')
i ve used and in place of where
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."
"But i need to say in the where clause WHERE OUTSTANDING VALUE IS GREATER THAN 0."
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