| append description [message #330105] |
Fri, 27 June 2008 10:38  |
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  |
 |
Michel Cadot
Messages: 68722
Registered: March 2007
Location: Saint-Maur, France, https...
|
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 #330118 is a reply to message #330115] |
Fri, 27 June 2008 10:58 |
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 #330148 is a reply to message #330134] |
Fri, 27 June 2008 12:07 |
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 #330153 is a reply to message #330149] |
Fri, 27 June 2008 12:20 |
shoaib123
Messages: 118
Registered: December 2007
Location: Chicago
|
Senior Member |
|
|
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
