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:

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
;

Open in new window

Thanks
EnyimbaAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
PortletPaulConnect With a Mentor Commented:
I just lost my carefully prepared answer, so this is briefer than I wanted...

this is a 32 million row table, performance is an issue
previous advice is to use "chunks" and to use indexed fields, which advice REMAINS TRUE
I suggest ADDING to the filter along these lines

      FROM tt
--  First critical filter
      where ID between 196001 and 197000 -- example of using indexed fields for "chunks"
      and (
              text like ('%WGT_%')
            OR
              text like ('%WGT__%')
            OR
              text like ('%WGT___%')
            OR
              text like ('%WGT____%')
            )
      and NOT (
              text like ('%WGT_.%')
            OR
              text like ('%WGT__.%')
            OR
              text like ('%WGT___.%')
            OR
              text like ('%WGT____.%')
        )

Open in new window


see it working, using SQL Server, and sample data here: http://sqlfiddle.com/#!3/f6c78/4
0
 
EnyimbaAuthor Commented:
Thanks PortletPaul
0
All Courses

From novice to tech pro — start learning today.