2 replies to this topic

[polisasimo]

Hello Everyone,

I am new to the forum and a bit wet behind the ears when it comes to SQL programming :) Below is a sample script I have generated and I am looking for some constructive criticism on the code.
[HIGHLIGHT="SQL"]
update ODB.PN_INVENTORY_DETAIL
set PHYSICAL_COUNT_DATE = to_date('15-JAN-2007','dd-MON-yyyy')
where ((
(ODB.PN_MASTER.CATEGORY = 'EXP') or
(ODB.PN_MASTER.CATEGORY = 'EXP B737') or
(ODB.PN_MASTER.CATEGORY = 'EXPB727')
) and (ODB.PN_INVENTORY_DETAIL.BIN >= '1' and ODB.PN_INVENTORY_DETAIL.BIN <= '10'))
[/HIGHLIGHT]

In addition to any criticism I also have a quick question. As seen above my intentions are to set the value of the PHYSICAL_COUNT_DATE field = to a given date based on a few conditional clauses. Lets say I had 100 total "ODB"."PN_INVENTORY_DETAIL"."BIN" values and wanted to set the PHYSICAL_COUNT_DATE by incriments of 30 days on 10 percent of the bins at a time. Is it possible using SQL to loop the above code to incriment the date and bin values by 30 days and 10 bins at a time. I was thinking something like reading the date and bin value from a txt file and looping until the end of file.

Thanks for your time in reading and posting in advance.

[WingedPanther]

One observation is that it appears that ODB.PN_INVENTORY_DETAIL.BIN contains numeric data, but you are doing a string comparison. That seems likely to give interesting results if your DB platform doesn't support automatic conversions or does the wrong type of conversion. For example, '2' > '10', even though 2 < 10.

[polisasimo]

Panther,

Thanks for the input, it is much appreciated :)