Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
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
Medium Priority
?
198 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 49

Accepted Solution

by:
PortletPaul earned 2000 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Learn how to navigate the file tree with the shell. Use pwd to print the current working directory: Use ls to list a directory's contents: Use cd to change to a new directory: Use wildcards instead of typing out long directory names: Use ../ to move…
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.
Suggested Courses

722 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