Solved

Round 3: SQL can update as desired but does not check to see if values has previously been updated. Can anyone help with this?

Posted on 2015-02-12
2
191 Views
Last Modified: 2015-02-17
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
0
Comment
Question by:Enyimba
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 40607285
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
 

Author Closing Comment

by:Enyimba
ID: 40615649
Thanks PortletPaul
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
Introduction Regular patching is part of a system administrator's tasks. However, many patches require that the system be in single-user mode before they can be installed. A cluster patch in particular can take quite a while to apply if the machine…
Learn how to get help with Linux/Unix bash shell commands. Use help to read help documents for built in bash shell commands.: Use man to interface with the online reference manuals for shell commands.: Use man to search man pages for unknown command…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question