| write a query! [message #372490] |
Thu, 15 February 2001 04:06  |
Gayathri
Messages: 19
Registered: February 2001
|
Junior Member |
|
|
Hi oracle experts!
Pl. suggest me how to write a query for the following table to give the output which follows that.
table :test4
SQL> select * from test4;
BASE_NO AMOUNT DB
----- --------- --
00003 5678 cr
00004 7865 db
00001 32435 cr
00005 2345 db
00002 4567 cr
Output should be:
SQL> select * from test4;
BASE_NO AMOUNT DB
----- --------- --
00001 32435 cr--
00002 4567 cr--
00003 5678 cr--
00004 7865 --db
00005 2345 --db
Pl. note the changes.
How to get this output?
I hope i'll definitely get the solution from u experts.
Thx in adv.
Gayathri
|
|
|
|
| Re: write a query! [message #372491 is a reply to message #372490] |
Thu, 15 February 2001 05:17  |
prn
Messages: 1
Registered: February 2001
|
Junior Member |
|
|
Hi,
Try this sql stmt...hope this will solve u r problem..
SELECT base_no
,amount
,decode(DB,cr,'cr--',db,'--db')
FROM test4;
|
|
|
|
|
|
|
|
| Re: Unable to get output, Check! [message #372501 is a reply to message #372493] |
Fri, 16 February 2001 01:09 |
Gayathri
Messages: 19
Registered: February 2001
|
Junior Member |
|
|
Hi,
Thank u Mr. Balaji for giving me a suggestion.
Let me clear my point reg. that query.
In the table test4, the last column name is "db_cb" and type "char(2)".
SQL> desc test4;
Name Null? Type
------------------------------------ ------------
BASE_NO VARCHAR2(5)
AMOUNT NUMBER(5)
DB_CB CHAR(2)
The structure is given above.
I have tried the query given by u.I'm not getting the output what i want to get.
SQL> select base_no,amount,decode(db_cb,'cr','cr--','db','--db')db_cb from test4
2 order by base_no;
no rows selected
Tell me what to do?
Is it decode is a function to do the above formatting?
tell me more about decode() also?
Thx in advance.
Gayathri
|
|
|
|
| Re: Unable to get output, Check! [message #372503 is a reply to message #372501] |
Fri, 16 February 2001 03:40 |
Balaji Krishnamurthi
Messages: 2
Registered: February 2001
|
Junior Member |
|
|
Hi Gayathri,
I had myself created the table and tested the code which worked as required by U
anyway U have mentioned that the error message
U got was no rows selected for which the only reason can be the table has no records
as i have used no filters(where condition) in the query,so check for the same.
Now about decode
do u know about the construct given below
if condition1
then
statements
elsif condition2
statements
.
.
.
.
else
statementelse
end if
which says if condition1 is true execute the statements just below if its false check for the
consecutive elsif conditions for true values
else execute the last statements given in else
Since this proves to be a bit cumbersome if is its to be used with select so
Oracle has provided a function called
decode()
the above construct can be written as
decode(condition1,statement1,condition2,statement2,......,conditionelse,statementelse)
which U can easily use in a select statement
so the statement decode(db_cd,'cr',
'cr--','db','db--')
means if db_cd ='cr' then display 'cr--' elsif db_cd='db' display '--db'
which is what U required
I hope so i have clarified U'r doubts , in case of any doubts U can contact me
|
|
|
|
| Re: Yess..! Got it ! [message #372519 is a reply to message #372501] |
Mon, 19 February 2001 00:49 |
Gayathri
Messages: 19
Registered: February 2001
|
Junior Member |
|
|
Hi Balaji,
Thank u so much for the pain u have taken to clear my doubt. I really appreciate ur patience.
I really enjoyed posting questions(doubts i have) and getting an elaborate explanations from experts like u.
Now i'm in learning stage. This is really helping me to explore more about oracle.
Now i could clearly understand this decode().
I got the answer for that query too.
Once again, thank u so much.
Gayathri.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
