Home » SQL & PL/SQL » SQL & PL/SQL » cONCAT ISSUE
cONCAT ISSUE [message #317953] Mon, 05 May 2008 01:48 Go to next message
avrillavinge
Messages: 98
Registered: July 2007
Member



Hi,

I have a table as shown for example..

A B C D E F G
111 null null 111 111 111 null





According to the fields that are null we have a target table column that shows messages for null values with a pipe {|} operator between them.

In the above example the error msg field in error table shows. | B is null | C is null ||| G is null

Now I want to replaces multiple pipes which come in between by one single pipe like

| B is null | C is null | G is null.

The scenario can be anything like ||,|||,|||||..etc.



I need to replace all such multiple pipes with a single pipe since its is creating a problem for testing resources to test such data.

How will I do it by using a combination of REPLACE and LIKE function. please suggest some thing……

Re: cONCAT ISSUE [message #317958 is a reply to message #317953] Mon, 05 May 2008 01:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
select decode(a, null, '| A is null ') ||
       decode(b, null, '| B is null ') ||
       decode(c, null, '| C is null ') ||
       decode(d, null, '| D is null ') ||
       decode(e, null, '| E is null ') ||
       decode(f, null, '| F is null ') ||
       decode(g, null, '| G is null ')
from my table
/

Regards
Michel
Re: cONCAT ISSUE [message #317961 is a reply to message #317958] Mon, 05 May 2008 02:05 Go to previous messageGo to next message
avrillavinge
Messages: 98
Registered: July 2007
Member
Thanks a lot for the quick reply
.Regards,

Avril
Re: cONCAT ISSUE [message #317965 is a reply to message #317953] Mon, 05 May 2008 02:26 Go to previous messageGo to next message
avrillavinge
Messages: 98
Registered: July 2007
Member
Thanks ...am using informatica and this is the issue


if the error message is B is null | C is null ||| G is null

then there i want B is null | C is null | G is null

any where if there are mltiple pipes the just replace by a single pipe over there.

Please find the Excel Sheet


Regards,
Avril
Re: cONCAT ISSUE [message #317967 is a reply to message #317965] Mon, 05 May 2008 02:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Is this an Informatica or a SQL question?
Who generates the message?
How does it do it?
What is your final goal?
How do you use the generated file?
...

Regards
Michel

[Updated on: Mon, 05 May 2008 02:35]

Report message to a moderator

Re: cONCAT ISSUE [message #317974 is a reply to message #317967] Mon, 05 May 2008 02:58 Go to previous messageGo to next message
avrillavinge
Messages: 98
Registered: July 2007
Member
its an sql queston, since i want to achieve the concat thing.

i hope my question is clear .
pls pardon me if its still unclear.

Ca u use replace which will replace single lines with double lines


Regards,
Avril
Re: cONCAT ISSUE [message #317998 is a reply to message #317974] Mon, 05 May 2008 04:49 Go to previous message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Sorry but what is the input? Table or CSV file?
What is the output? CSV file?
What is the reason your posted this CSV file?

Regards
Michel
Previous Topic: ORACLE PDF,s
Next Topic: faq's on Oracle SQL n PL/SQL
Goto Forum:
  


Current Time: Fri Dec 02 14:14:20 CST 2016

Total time taken to generate the page: 0.26220 seconds