Home » SQL & PL/SQL » SQL & PL/SQL » How to return in one row only (Oracle 10g)
How to return in one row only [message #564179] Tue, 21 August 2012 22:53 Go to next message
bhibe_17
Messages: 24
Registered: July 2012
Junior Member
hi,
i need to have an SQL statement that will return only in one row.
in this SELECT statement,
select NVL(a.jj_crdr,'CR'), 
       nvl(a.CR_cnt,0) CR_cnt, 
       NVL(a.CR_amt,0) CR_amt, 
       NVL(b.jj_crdr,'DR'), 
       NVL(b.DR_cnt,0) DR_cnt, 
       NVL(b.DR_amt,0) DR_amt
from (select jj_creator jj_creator, jj_crdr, count(1) CR_cnt, sum(jj_amount) CR_amt
     from  journal_tbl
  WHERE jj_creator = 'user1' 
     AND jj_crdr = 'CR'
     AND jj_status = 1 
group by jj_creator, jj_crdr) a,
(select jj_creator jj_creator, jj_crdr, count(1) DR_cnt, NVL(sum(jj_amount),0) DR_amt
from  journal_tbl  
  WHERE jj_creator = 'user1' 
     AND jj_crdr = 'DR'
     AND jj_status = 1 
group by jj_creator, jj_crdr) b
where a.jj_creator = b.jj_creator(+)

it returned
Tot_cr tot_cr_amt tot_dr tot_dr_amt
1 100 0 0
because there is record for CR and there is no DR.

but when there's record in DR and no in CR, there's no record returned.
because the outer join is in the 2nd Select. i need to change the condition to: 'where a.jj_creator(+) = b.jj_creator'
in order to return a record.

what i need is to be flexible so that it will return records even if one of the source doesn't have record.

*BlackSwan added {code} tags. Do so yourself in the future!

[Updated on: Tue, 21 August 2012 23:06] by Moderator

Report message to a moderator

Re: How to return in one row only [message #564187 is a reply to message #564179] Tue, 21 August 2012 23:48 Go to previous messageGo to next message
Michel Cadot
Messages: 58619
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
With any SQL question, Post a working Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Before, Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.

Regards
Michel
Re: How to return in one row only [message #564195 is a reply to message #564179] Wed, 22 August 2012 00:37 Go to previous messageGo to next message
flyboy
Messages: 1769
Registered: November 2006
Senior Member
It looks like a simple full outer join. I recommend you to use ANSI syntax for it. Anyway, if you like the old Oracle syntax more, you will just have to do a bit of extra typing, like shown e.g. in this article on AskTom: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:6585774577187
Re: How to return in one row only [message #564204 is a reply to message #564187] Wed, 22 August 2012 01:34 Go to previous messageGo to next message
bhibe_17
Messages: 24
Registered: July 2012
Junior Member
Hi Michel,
here's the create table, insert scripts, and test cases. Thanks!

create table journal_tbl
(JJ_CREATOR VARCHAR2(30),
JJ_CRDR VARCHAR2(2),
JJ_AMOUNT NUMBER(13,4),
JJ_STATUS NUMBER(2));

insert into journal_tbl values ('CREATOR1','DR',100,1);
insert into journal_tbl values ('CREATOR1','DR',10,1);
insert into journal_tbl values ('CREATOR1','CR',80,1);
insert into journal_tbl values ('CREATOR1','CR',100,1);
insert into journal_tbl values ('CREATOR2','CR',50,1);
insert into journal_tbl values ('CREATOR2','CR',40,1);
insert into journal_tbl values ('CREATOR3','DR',30,1);
insert into journal_tbl values ('CREATOR3','DR',20,1);

commit;

get total count and sum of DR and CR for CREATOR1, CREATOR2, and CREATOR3

Result 1:
CREATOR1, 2, 110, 2, 180

Result 2:
CREATOR2, 0, 0, 2, 90

Result 3:
CREATOR3, 2, 50, 0, 0
Re: How to return in one row only [message #564206 is a reply to message #564204] Wed, 22 August 2012 01:40 Go to previous messageGo to next message
bhibe_17
Messages: 24
Registered: July 2012
Junior Member
Note: Should only return one row in the SELECT statement.
Re: How to return in one row only [message #564209 is a reply to message #564206] Wed, 22 August 2012 01:57 Go to previous messageGo to next message
Michel Cadot
Messages: 58619
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You mean 1 row per creator?
What are the columns of the result? (what is their definition?)
CREATOR1, 2, 110, 2, 180
What is 2? what is 110? what is 2? what is 180?

Regards
Michel
Re: How to return in one row only [message #564210 is a reply to message #564209] Wed, 22 August 2012 02:01 Go to previous messageGo to next message
Michel Cadot
Messages: 58619
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Guess:
SQL> select jj_creator,
  2         count(decode(jj_crdr, 'DR', 1)) dr_cnt,
  3         nvl(sum(decode(jj_crdr, 'DR', jj_amount)),0) dr_amount,
  4         count(decode(jj_crdr, 'CR', 1)) cr_cnt,
  5         nvl(sum(decode(jj_crdr, 'CR', jj_amount)),0) cr_amount
  6  from journal_tbl
  7  group by jj_creator
  8  order by jj_creator
  9  /
JJ_CREATOR     DR_CNT  DR_AMOUNT     CR_CNT  CR_AMOUNT
---------- ---------- ---------- ---------- ----------
CREATOR1            2        110          2        180
CREATOR2            0          0          2         90
CREATOR3            2         50          0          0

3 rows selected.

Regards
Michel
Re: How to return in one row only [message #564212 is a reply to message #564210] Wed, 22 August 2012 02:10 Go to previous messageGo to next message
bhibe_17
Messages: 24
Registered: July 2012
Junior Member
Hi Michel,
The SELECT should have WHERE clause parameterized: WHERE jj_creator = &creator

so for the Test Case 1: JJ_CREATOR = 'CREATOR1'
and result should be:
JJ_CREATOR     DR_CNT  DR_AMOUNT     CR_CNT  CR_AMOUNT
---------- ---------- ---------- ---------- ----------
CREATOR1            2        110          2        180


Test Case 2: JJ_CREATOR = 'CREATOR2'
and result should be:
JJ_CREATOR     DR_CNT  DR_AMOUNT     CR_CNT  CR_AMOUNT
---------- ---------- ---------- ---------- ----------
CREATOR2            0          0          2         90


Test Case 3: JJ_CREATOR = 'CREATOR3'
and result should be:
JJ_CREATOR     DR_CNT  DR_AMOUNT     CR_CNT  CR_AMOUNT
---------- ---------- ---------- ---------- ----------
CREATOR2            2         50          0          0
Re: How to return in one row only [message #564214 is a reply to message #564212] Wed, 22 August 2012 02:16 Go to previous messageGo to next message
bhibe_17
Messages: 24
Registered: July 2012
Junior Member
Hi Michel,
It works! Thanks a lot for your help!
Re: How to return in one row only [message #564231 is a reply to message #564179] Wed, 22 August 2012 05:01 Go to previous messageGo to next message
bhibe_17
Messages: 24
Registered: July 2012
Junior Member
Hi Michel,
I added another criteria in the query wherein it should get total count and sum of DR and CR for CREATOR1, CREATOR2, and CREATOR3 per JJ_STATUS.
i have added insert scripts for your testing:
insert into journal_tbl values ('CREATOR1','DR',10,2);
insert into journal_tbl values ('CREATOR1','DR',10,2);
insert into journal_tbl values ('CREATOR1','CR',20,2);
insert into journal_tbl values ('CREATOR1','CR',20,2);
insert into journal_tbl values ('CREATOR2','CR',30,2);
insert into journal_tbl values ('CREATOR2','CR',30,2);
insert into journal_tbl values ('CREATOR3','DR',40,2);
insert into journal_tbl values ('CREATOR3','DR',40,2);

insert into journal_tbl values ('CREATOR1','DR',5,3);
insert into journal_tbl values ('CREATOR1','DR',5,3);
insert into journal_tbl values ('CREATOR1','CR',10,3);
insert into journal_tbl values ('CREATOR1','CR',10,3);
insert into journal_tbl values ('CREATOR2','CR',15,3);
insert into journal_tbl values ('CREATOR2','CR',15,3);
insert into journal_tbl values ('CREATOR3','DR',20,3);
insert into journal_tbl values ('CREATOR3','DR',20,3);

result should be:
JJ_CREATOR STAT1 DRCNTSTAT1	DRAMTSTAT1 CRCNTSTAT1 CRAMTSTAT1 JJSTAT2 DRCNTSTAT2 DRAMTSTAT2 CRCNTSTAT2 CRAMOUNTSTAT2 JJSTAT3 DRCNTSTAT3 DRAMTSTAT3 CRCNTSTAT3 CRAMTSTAT3
CREATOR1	   1   		  2		   110 		    2        180	   2  		  2         20          2            40       3          2         10          2         20
CREATOR2       1          0          0          2         90       2          0          0          2            60       3          0          0          2         30
CREATOR3 	   1          2         50          0          0       2          2         80          0             0       3          2         40          0          0


apologize for the sample result. i have attached the sample result with better alignment. thanks!
  • Attachment: Result.txt
    (Size: 0.64KB, Downloaded 45 times)
Re: How to return in one row only [message #564234 is a reply to message #564231] Wed, 22 August 2012 05:31 Go to previous message
Michel Cadot
Messages: 58619
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Just use the same method.
As DECODE is hard to use in this case, use CASE isntead:
CASE WHEN jj_crdr = 'DR' and JJ_STATUS = 1 THEN ... END
For every column and case.

Regards
Michel
Previous Topic: Writable MVIEW
Next Topic: ORA-29280: invalid directory path - Using UTL_FILE
Goto Forum:
  


Current Time: Wed Jul 30 10:44:29 CDT 2014

Total time taken to generate the page: 0.18415 seconds