Hi All,
Can some one helps in implementing this SQL in WEBI ????
It contains NOT EXITS function in where clause.
SELECT s.shipmenttype,
q.carriermode,
q.shipment,
q.measure,
(SELECT COUNT (*)
FROM Iowa.findetail fd
WHERE fd.confirmstatus = 0
AND fd.shipment = q.shipment
AND fd.measure = q.measure
AND fd.trade = p.trade
AND fd.validation IS NOT NULL)
AS Calidation_errors,
q.begtime,
q.endtime,
p.trade,
p.position,
p.positiontype,
q.mass,
q.massunit,
q.quantitystatus,
p.company,
p.counterparty,
t.tradetype,
p.contract,
p.paymentterms,
q.posstatus,
pm.actualdate AS actualdate
FROM Iowa.physicalquantity q
INNER JOIN Iowa.position p
ON q.position = p.position
INNER JOIN Iowa.shipment s
ON s.shipment = q.shipment
INNER JOIN Iowa.trade t
ON t.trade = p.trade
INNER JOIN Iowa.physicalmeasure pm
ON pm.measure = q.measure
WHERE NOT EXISTS
(SELECT 1
FROM Iowa.findetail fd
INNER JOIN
Iowa.fintransact ft
ON ft.fintransact = fd.fintransact
WHERE fd.balance = 0
AND fd.shipment = q.shipment
AND fd.measure = q.measure
AND fd.trade = p.trade)
AND t.tradetype <> 'PHYSICAL SO2'
AND q.posstatus = 1
AND q.mass <> 0
AND q.quantitystatus = 'ACTUAL'
AND (q.mass > 0
AND q.mass NOT IN
(
10000.0000,
10500.0000)
AND q.mass NOT IN
(13500.0000,
14000.0000)
AND q.mass NOT IN
(14500.0000,
15000.0000)
AND q.mass NOT IN
(7500.0000,
1625.0000
))
AND pm.measure IS NOT NULL