Skip to Content
0
Jan 12, 2012 at 10:01 PM

SQL help

41 Views

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