All Forums UDA
terausr 7 posts Joined 01/06
20 Jan 2006
SQL to update a column using another column

I need the logic/ansi sql to update a column, LP_Qty in my table using another col in the same table this is RF_Qty. RF_Qty gets updated everyday as the table refreshes everyday. But LP_Qty should be equal to RF_Qty only when it is FisPeriodEnd_Dt, else it should retain it's previous value till it is FisPeriodEnd_Dt. My target table has the following Cols Item_IdFiscalPeriod_Id (MonthLevel)RF_Qty,LP_Qtythere is another table called Fiscal_Period that hasFiscalPeriod_Id (MonthLevel)FiscalPeriodStart_DtFiscalPeriodEnd_DtWe also have current_date to use as this is not for historyhow do I populate the target table?thanks in advance

jasonf 5 posts Joined 08/04
20 Jan 2006

I am not entirely sure this will work since I don't fully understand your ETL process or the data involved, but could you add an update to LP_Qty as part of your daily refresh process? Only when it is FisPeriodEnd_Dt though would the value actually change. Something like:Update Target_TableFrom Fiscal_Period FPSet LP_Qty = Case When FP.FiscalPeriodEnd_Dt = Current_Date Then RF_Qty Else LP_Qty EndWhere Target_Table.FiscalPeriod_Id = FP.FiscalPeriod_Id;Good luck,Jason

terausr 7 posts Joined 01/06
21 Jan 2006

Thanks Jason for your reply. My ETL process deletes all data from the target table everyday and reloads it with fresh data. It's a DSS. I need to do the update with previous day's value if the condition for end of fiscalperiod is not met (FiscalPeriodEnd_Dt = current_date). I need to get a snapshot of the previous day's LastProjected_Qty value and use this for update else use current value of ResultantForecast_Qty.What modifications to your code would achieve that??Thanks again

jasonf 5 posts Joined 08/04
23 Jan 2006

Seems like you should try to add the logic to your insert statement so that you do not need to do a sperate update. Could you post your current insert statement for review?

terausr 7 posts Joined 01/06
23 Jan 2006

this is my original insert statement it updates LP_Qty with the RF_Qty everyday now.DELETE FROM RTL_DEV_DATA.RPLNSHMNT_FORECAST ALL;INSERT INTO RTL_DEV_DATA.RPLNSHMNT_FORECASTSEL it.Item_ID, fp.Fiscalperiod_ID, rc.Facility_ID, sum(rc.Dtl_Qty), sum(dm.ResultantForecast_Qty), sum(dm.ResultantForecast_Qty), sum(dm.ForcedSysForecast_Qty)FROM RTL_DEV_DATA.FISCAL_PERIOD fp ,RTL_DEV_DATA.ITEM it, RTL_DEV_WORK.TSTAGING_RECEIPTS rc,RTL_DEV_WORK.TSTAGING_DMND dmWHERE it.Class_id=rc.Class_IDAND it.VENDORORG_ID=rc.VENDORORG_IDAND it.STYLE_ID=rc.STYLE_IDAND it.COLOR_ID=rc.COLOR_IDAND it.SIZE_ID=rc.SIZE_IDAND it.CHAIN_ID=rc.CHAIN_IDAND it.Class_id=dm.Class_IDAND it.VENDORORG_ID=dm.VENDORORG_IDAND it.STYLE_ID=dm.STYLE_IDAND it.COLOR_ID=dm.COLOR_IDAND it.SIZE_ID=dm.SIZE_IDAND it.CHAIN_ID=dm.CHAIN_IDAND rc.Class_id=dm.Class_IDAND rc.VENDORORG_ID=dm.VENDORORG_IDAND rc.STYLE_ID=dm.STYLE_IDAND rc.COLOR_ID=dm.COLOR_IDAND rc.SIZE_ID=dm.SIZE_IDAND rc.CHAIN_ID=dm.CHAIN_IDAND rc.Facility_ID=dm.Facility_IDAND dm.Fiscalperiod_ID=fp.Fiscalperiod_IDAND fp.FiscalPeriodEnd_Dt = rc.Dtl_DateAND rc.REQMT_CODE='S'group by 1,2,3;

jasonf 5 posts Joined 08/04
23 Jan 2006

You said initially that LP_Qty "should retain it's previous value till it is FisPeriodEnd_Dt". I don't know your data well enough to see how to calculate this on a daily basis. Can you provide a SQL statement that would calculate LP_Qty for a NON-FisPeriodEnd_Dt day? Perhaps you will need to change your insert to join 2 derived tables to work. Such as:INSERT INTO RTL_DEV_DATA.RPLNSHMNT_FORECASTSEL A.Item_ID,A.Fiscalperiod_ID,A.Facility_ID,A.Dtl_Qty,Case When A.FiscalPeriodEnd_Dt = Current_Date Then A.RF_qty else B.LP_qty end,A.RF_Qty,A.ForcedSysForecast_QtyFROM(SEL it.Item_ID,fp.Fiscalperiod_ID,rc.Facility_ID,sum(rc.Dtl_Qty),sum(dm.ResultantForecast_Qty),sum(dm.ResultantForecast_Qty),sum(dm.ForcedSysForecast_Qty)FROM RTL_DEV_DATA.FISCAL_PERIOD fp ,RTL_DEV_DATA.ITEM it, RTL_DEV_WORK.TSTAGING_RECEIPTS rc,RTL_DEV_WORK.TSTAGING_DMND dmWHERE it.Class_id=rc.Class_IDAND it.VENDORORG_ID=rc.VENDORORG_IDAND it.STYLE_ID=rc.STYLE_IDAND it.COLOR_ID=rc.COLOR_IDAND it.SIZE_ID=rc.SIZE_IDAND it.CHAIN_ID=rc.CHAIN_IDAND it.Class_id=dm.Class_IDAND it.VENDORORG_ID=dm.VENDORORG_IDAND it.STYLE_ID=dm.STYLE_IDAND it.COLOR_ID=dm.COLOR_IDAND it.SIZE_ID=dm.SIZE_IDAND it.CHAIN_ID=dm.CHAIN_IDAND rc.Class_id=dm.Class_IDAND rc.VENDORORG_ID=dm.VENDORORG_IDAND rc.STYLE_ID=dm.STYLE_IDAND rc.COLOR_ID=dm.COLOR_IDAND rc.SIZE_ID=dm.SIZE_IDAND rc.CHAIN_ID=dm.CHAIN_IDAND rc.Facility_ID=dm.Facility_IDAND dm.Fiscalperiod_ID=fp.Fiscalperiod_IDAND fp.FiscalPeriodEnd_Dt = rc.Dtl_DateAND rc.REQMT_CODE='S'group by 1,2,3) as A,(SEL Item_ID,Sum(Some_Column) as LP_QtyFrom Some_Table) as BWHERE A.Item_ID = B.ITEM_ID;I am probably missing a whole lot that will prevent this from working logically. I probably can not give you the exact answer to your question without fully understanding all the tables and relationships involved. So my advice is to play around with this example. Assuming that the data is available (that LP_Qty can be calculated somehow on a daily basis), then the use of a CASE statement and maybe derived tables should work. I would try to achieve it in one statement verses an Insert + Update if the performance demands can be met.Good luckJason

You must sign in to leave a comment.