sqlsybaseisql

iSQL Query Returning Value Multiple Times

The output of my column "SettlementsPayable" is returning the value for each line item even though that is not my desired result. It should only be returning the value when the ratecode = SHUNTING Maybe it is how I am getting the data into my statement? I've included a screen shot of my data results in iSQL

     SELECT      ds_id AS TMP, ds_ship_date AS ShipDate, ds_ref1_text AS ContainerNumber,  

        (CASE ds_status WHEN 'A' THEN 'TEMPLATE'  

WHEN 'C' THEN 'CANCELLED'  

WHEN 'D' THEN 'DECLINED'    

WHEN 'E' THEN 'QUOTED'  

WHEN 'F' THEN 'OFFERED'   

WHEN 'H' THEN 'PENDING'  

WHEN 'K' THEN 'OPEN'  

WHEN 'N' THEN 'AUTHORIZED'  

WHEN 'Q' THEN 'AUDIT REQUIRED'  

WHEN 'T' THEN 'AUDITED'  

WHEN 'W' THEN 'BILLED'   

END) AS 'TMPStatus',  

        b.co_name as "BillTo", o.co_name AS Origin, o.co_city AS OriginCity, o.co_state AS 
OriginState,  

        de_arrdate AS DeliveryDate, de_arrtime AS ArrivalTime, de_deptime AS DepartureTime,    

        dba.disp_items.di_qty AS QTY, dba.disp_items.ratecodename AS RateCode, 
dba.disp_items.di_our_rate AS OURRATE, dba.disp_items.di_our_itemamt AS ITEMAMT, 
dba.disp_items.amounttype AS AMTTYPE, dba.disp_items.di_pay_itemamt AS CarrierPayables,  

( select sum ( amount ) from dba.amountowed where DBA.AmountOwed.Description = 'SHUNTING' and 
string ( ds_id ) = amountowed.shipment ) AS SettlementsPayable,  

        CASE ds_ship_type   

                WHEN '2201' THEN 'MONTREAL'   

                WHEN '2202' THEN 'DRYVAN'   

                WHEN '2203' THEN 'BROKERAGE'   

                WHEN '2204' THEN 'OLD BROKERAGE'   

                WHEN '2205' THEN 'LIFTING'   

                WHEN '2206' THEN 'WAREHOUSE'  
        END   

        AS Division  

FROM        dba.disp_ship   

JOIN        dba.disp_events ON de_shipment_id = ds_id   

JOIN        dba.disp_items ON dba.disp_items.di_shipment_id = dba.disp_ship.ds_id 

JOIN        dba.companies o ON o.co_id = ds_origin_id    

JOIN        dba.companies b on b.co_id = ds_billto_id   

WHERE       de_site = ds_findest_id   

AND de_event_type IN ('D','R','N')

and ds_id = '82261'

GROUP BY TMP, SHIPDATE, CONTAINERNUMBER, TMPSTATUS, BILLTO, ORIGIN, ORIGINCITY, ORIGINSTATE, 
DELIVERYDATE, ARRIVALTIME, DEPARTURETIME, QTY, RATECODE, OURRATE, ITEMAMT, AMTTYPE, 
CARRIERPAYABLES, SETTLEMENTSPAYABLE, DIVISION 

ORDER BY SETTLEMENTSPAYABLE DESC

Results:

enter image description here

Solution

  • The subquery has a where clause that looks for rows with DBA.AmountOwed.Description = 'SHUNTING' ...

    ( select sum ( amount ) from dba.amountowed where DBA.AmountOwed.Description = 'SHUNTING' and
string ( ds_id ) = amountowed.shipment ) AS SettlementsPayable,

    ... but I don't see any place in the parent query where you limit the results to just rows with SHUNTING (this is supported by the image showing RateCode values of PORT, RATES or SHUNTING) so I'm guessing what you really want to see is a value under the SettlementsPayable column that is conditioned on the value in the RateCode column.

    Assumptions:

    One idea using a case statement:

    select ... snip ...
       dba.disp_items.ratecodename AS RateCode,             # no change here, just displaying so we know where 'RateCode' comes from
       ... snip ...

       case when dba.disp_items.ratecodename != 'SHUNTING' then 0.00

            else (select sum(amount) 
                  from   dba.amountowed
                  where  DBA.AmountOwed.Description = 'SHUNTING'
                  and    string(ds_id) = amountowed.shipment)

       end case AS SettlementsPayable, 

       ... snip ...
from   ... snip ...