Skip to Main Content
  • Questions

  • In sql how can I update a value , and then reuse the updated value and re-update it

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Imran.

Asked: November 10, 2017 - 3:01 pm UTC

Last updated: November 12, 2017 - 6:29 am UTC

Version: 12c

Viewed 1000+ times

You Asked

Hi Gurus

I need to write in SQL something which previously was done in PL/SQL if possible.

I have a Invoice Line Description e.g. 'ABC Mon Tue' for which I need to translate certain words.

I also have a lookup(fnd_lookups) which stores the translations.

Meaning Description

Mon Nom
Tue Eut


So if I had 'ABC Mon Tue' I would like it to return ''ABC Nom Eut'

I guess using PL/SQL it was easy but in sql I cannot work out how to do it.


I was trying the below but this will always return me 2 rows. I guess the no of rows it find entries for in the lookup.
So
'ABC Mon Eut'
'ABC Nom Tue'

select replace('ABC Mon Tue', meaning, description)
from fnd_lookup_values_tl
where language = 'US'
and lookup_type like 'MISAR_BPA_LANG'
and instr('ABC Mon Tue', meaning) > 0


I need to somehow workout how to do a replace and then it works off the updated value.

Any ideas if this is possible in sql ?

Thanks

and Connor said...

A recursive WITH can do this. Here's an example where I want to replace names in a string with the equivalent twitter handles, where the number of replacements is unknown.

SQL> select * from messages;

TXT
----------------------------------------------------------
I caught up with Connor and Maria Colgan today. They have taken over AskTOM for Oracle Developers

SQL> select * from twitter_handles;

  ID TERM                           HANDLE
---- ------------------------------ ---------------
   1 Connor McDonald                @connor_mc_d
   2 Connor                         @connor_mc_d
   3 Maria Colgan                   @sqlmaria
   4 Oracle Developers              @otndev
   5 Oracle                         @oracle
   6 AskTOM                         @oracleasktom

SQL> with
  2    tweetised(ind,tweet_txt)  as
  3  (
  4    select 1 ind, txt tweet_txt
  5    from   messages
  6    union all
  7    select ind+1, replace(tweet_txt,term,handle)
  8    from   tweetised, twitter_handles
  9    where  ind = id
 10  )
 11  select * from tweetised;

 IND TWEET_TXT
--------- -----------------------------------------------------------------------------------------
        1 I caught up with Connor and Maria Colgan today. They have taken over AskTOM for...
        2 I caught up with Connor and Maria Colgan today. They have taken over AskTOM for...
        3 I caught up with @connor_mc_d and Maria Colgan today. They have taken over AskTOM for...
        4 I caught up with @connor_mc_d and @sqlmaria today. They have taken over AskTOM for ...
        5 I caught up with @connor_mc_d and @sqlmaria today. They have taken over AskTOM for @otndev
        6 I caught up with @connor_mc_d and @sqlmaria today. They have taken over AskTOM for @otndev
        7 I caught up with @connor_mc_d and @sqlmaria today. They have taken over @oracleasktom ...

SQL> with
  2  tweetised(ind,tweet_txt)  as
  3  (
  4    select 1 ind, txt tweet_txt
  5    from   messages
  6    union all
  7    select ind+1, replace(tweet_txt,term,handle)
  8    from   tweetised, twitter_handles
  9    where  ind = id
 10  )
 11  select * from tweetised
 12  order by ind desc
 13  fetch first 1 row only;

       IND TWEET_TXT
---------- ------------------------------------------------------
         7 I caught up with @connor_mc_d and @sqlmaria today. They...


Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, November 13, 2017 - 11:20 am UTC


more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.