>I have this query:
>
>
> SELECT tp.sTP_Name1 AS AcctName,
> t.iTrading_Partner_ID AS CustNum,
> CASE t.sTran_Group_CD
> WHEN 'MAR' THEN 'MARGIN'
> WHEN 'PHY' THEN 'PHYSICAL'
> WHEN 'FUT' THEN 'FUTURES'
> WHEN 'FOR' THEN 'FORWARDS'
> WHEN 'REP' THEN 'REPO'
> WHEN 'CON' THEN 'CONSIGNMENT'
> WHEN 'SCR' THEN 'SCRAP'
> WHEN 'PRO' THEN 'PROVISIONAL PRICING'
> WHEN 'PL' THEN 'POOL METAL'
> ELSE ''
> END AS TranGroup,
> dtTrade_DT AS TradeDate,
> iOrder_Hdr_ID AS OrdNum,
> iTrade_ID AS TR_Number,
> sTrade_Type_CD AS TradeType,
> sProduct_CD AS Product,
> decTrade_Qty AS Quantity,
> decTrade_Unit_Price AS Price,
> decTrade_Qty * decTrade_Unit_Price AS NetAmount,
> decTrade_Cash_Balance AS CashBalance,
> decTrade_Product_Balance AS ProductBal,
>
>
> CASE WHEN i.iOrder_Hdr_id IS NULL THEN 'N' ELSE 'Y' END as Invoiced
> iTrade_Conf_No AS Conf_Num
> FROM Trade t
> JOIN Trading_Partner tp ON tp.iTrading_Partner_ID = t.iTrading_Partner_ID
> LEFT JOIN Invoice_Hdr i ON i.iOrder_Hdr_id = t.iOrder_Hdr_id
> WHERE t.decTrade_Product_Balance <> 0 OR
> t.decTrade_Cash_Balance <> 0
>
See changes inside, not tested.
If it's not broken, fix it until it is.
My Blog