Enyimba
asked on
Round 3: SQL can update as desired but does not check to see if values has previously been updated. Can anyone help with this?
Hello Gurus,
The story of this SQL thread continues...The SQL shown below and provided by kdo works as desired but I will like to have a major enhancement. It currently does not check to determine if the data has previously been modified and I will like to enhance the current code to include the ability to skip any ???? that already have a decimal point applied. Can anyone help with this requirement? The code that works is shown below:
The story of this SQL thread continues...The SQL shown below and provided by kdo works as desired but I will like to have a major enhancement. It currently does not check to determine if the data has previously been modified and I will like to enhance the current code to include the ability to skip any ???? that already have a decimal point applied. Can anyone help with this requirement? The code that works is shown below:
UPDATE tt t0
SET t0.text =
(
SELECT
case when pc <> 0 then text
when lc = 0 then
case length (text) - fc
when -1 then text || '0.00'
when 0 then left (text, fc-1) || '0.0' || right (text, 1)
when 1 then left (text, fc-1) || '0.' || right (text, 2)
else left (text, length (text) - 2) || '.' || right (text, 2)
end
when lc - fc = 0 then substr (text, 1, fc-1) || '0.00' || substr (text, fc)
when lc - fc = 1 then substr (text, 1, fc-1) || '0.0' || substr (text, fc)
when lc - fc = 2 then substr (text, 1, fc-1) || '0.' || substr (text, fc)
else substr (text, 1, lc-3) || '.' || substr (text, lc-2)
end text
FROM
(
SELECT
ID,
case when locate_in_string (text, 'WGT') = 0 then 0
else locate_in_string (text, 'WGT')+3
end fc,
case when locate_in_string (text, 'WGT') = 0 then 0
else locate_in_string (text, ',', locate_in_string (text, 'WGT'))
end lc,
case when locate_in_string (text, 'WGT') = 0 then 0
else locate_in_string (text, '.', locate_in_string (text, 'WGT'))
end pc,
text
FROM tt
-- First critical filter
-- WHERE id between 1 and 8
) t1
-- WHERE t0.id = t1.id
where t0.id = t1.id and Tran_Date>='20140101' and Tran_Date<='20140105'
and TRAN_TYPE='SALE'
)
-- Second critical filter
-- WHERE id between 1 and 8
;
Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER