Link to home
Start Free TrialLog in
Avatar of pepps11976
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
Avatar of Roman Gherman
Roman Gherman
Flag of Moldova, Republic of image

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
Avatar of pepps11976
pepps11976

ASKER

Hi Thanks for replying

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

Open in new window

Avatar of Rikin Shah
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

replacing where with  and can do the trick.
if my script doesn't return any values means you don't have any
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
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
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
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

does your initial statement return anything where outstanding value > 0 ?????
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
Avatar of mayank_joshi
mayank_joshi
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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?
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
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."