Improve company productivity with a Business Account.Sign Up

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
?
210 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
2 Comments
 
LVL 50

Accepted Solution

by:
Paul 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
In a previous video, we went over how to export a DynamoDB table into Amazon S3.  In this video, we show how to load the export from S3 into a DynamoDB table.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

608 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