Home » SQL & PL/SQL » SQL & PL/SQL » append description
append description [message #330105] Fri, 27 June 2008 10:38 Go to next message
shoaib123
Messages: 118
Registered: December 2007
Location: Chicago
Senior Member

select
expense_account,
bnk_number,
brnch_number,
chk_number,
reason_code
from 
bnk1_acct_number_xref b
where
account_number is null
tran_date is null
tran_type is null
test_1 is null
test_2 is null
test_3 is null
test_4 is null

i need to update the reason_code column with the decritption, so if any of this column is null, it will update reason_code
column with the respective descrption.

account_number is null >>>> then update the reason_code = 'account_number is null'
tran_date is null >>>>> then update the reason_code = 'tran_date is null'
tran_type is null >>>>> then update the reason_code = 'tran_type is null'
and so on ....
I am trying to get some idea to create logic .. if any records has found more than one column value null, it should append both description in
reason_code column. Meaning if any records in the results set has account_number and tran_type is null then it should update the reason_ocde
column with both descriptions ...."account_number is null\tran_date is null"

I am not looking for detail answer, any hint would be appreciated..
Re: append description [message #330112 is a reply to message #330105] Fri, 27 June 2008 10:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Here's a clue:

decode(account_number,null,'account_number is null')
||decode(trans_date,null,'trans_date is null')
||decode(trans_type,null,'trans_type is null')

Regards
Michel

[Updated on: Fri, 27 June 2008 10:46]

Report message to a moderator

Re: append description [message #330115 is a reply to message #330105] Fri, 27 June 2008 10:51 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
>o if any of this column is null, it will update reason_code
column with the respective descrption.

What should happen when two or more columns are NULL?
Re: append description [message #330118 is a reply to message #330115] Fri, 27 June 2008 10:58 Go to previous messageGo to next message
shoaib123
Messages: 118
Registered: December 2007
Location: Chicago
Senior Member

That's where the challenge is...

If 5 columns of any record contaning null values, it should update the reason_code with it's 5 respective description. so if 2 then update with 2 description and if three then update with three descriptions .......
Re: append description [message #330134 is a reply to message #330118] Fri, 27 June 2008 11:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is what I posted: concatenate.

Regards
Michel
Re: append description [message #330148 is a reply to message #330134] Fri, 27 June 2008 12:07 Go to previous messageGo to next message
shoaib123
Messages: 118
Registered: December 2007
Location: Chicago
Senior Member

Thanks Micheal it really workout well. once last thing...
i need create space between each description.. i tried something like but did not work it out.. Any hint would be apprecaite on this ....

select 
(decode(loan_id, null,'Invalid or Missing loan_id') || '  '
decode(finc_tran_id,null,'Invalid or Missing finc_tran_id')) as reason_code
from table1 

Re: append description [message #330149 is a reply to message #330105] Fri, 27 June 2008 12:10 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
> i tried something like but did not work it out
Try using valid SQL syntax.
Re: append description [message #330153 is a reply to message #330149] Fri, 27 June 2008 12:20 Go to previous message
shoaib123
Messages: 118
Registered: December 2007
Location: Chicago
Senior Member


Smile

My problem gets solved and i am posting the answer...


select 
(decode(loan_id, null,'Invalid or Missing loan_id. ') || 
decode(finc_tran_id,null,'Invalid or Missing finc_tran_id. ')) as reason_code
from table1 

Previous Topic: Problem while joining
Next Topic: ORA-00936: missing expression
Goto Forum:
  


Current Time: Wed Dec 07 05:17:20 CST 2016

Total time taken to generate the page: 0.08196 seconds