| help with sql grouping [message #664887] |
Thu, 10 August 2017 02:07  |
 |
ramkumar10
Messages: 19
Registered: March 2017
|
Junior Member |
|
|
hi I have a scenerio where I have to mark a row as o or 1 based on the values of a group
example
FNO CODE
10 100
10 100
20 100
20 50
30 60
30 70
EXPECTED OUTPUT
FNO CODE FLAG
10 100 1
10 100 1
20 100 0
20 50 0
30 60 0
30 70 0
so basically if all codes under a group(FNO) is 100 then I have to mark flag as 1 else if any one of the code is not 100 or if code value 100 is not present in the group then I have to mark flag as 0. I have managed to write a sql something like below to get the output
select min(flag), fno from (
select distinct fno,1 as flag from table where code=100
union
select distinct fno,0 as flag from table where code<>100
union
select distinct fno,0 as flag from table where code=100 and fno in(select fno from table where code<>100)
) group by fno;
I would like to know if there is a better way to write this in sql.
Thank you for your help.
|
|
|
|
|
|
| Re: help with sql grouping [message #664900 is a reply to message #664887] |
Thu, 10 August 2017 06:52  |
flyboy
Messages: 1901
Registered: November 2006
|
Senior Member |
|
|
Hi,
I would not say that the query you posted produces the expected output - it does not contain CODE column and the GROUP BY clause returns only one row per FNO.
If the expected output and its text description are correct, you will need to use Oracle analytic function(s). Something like this may help you to start:
count(case when code = 100 then 1 end) over (partition by fno)
count(case when code != 100 then 1 end) over (partition by fno) |
|
|
|
| Re: help with sql grouping [message #664908 is a reply to message #664900] |
Thu, 10 August 2017 22:50 |
|
ramkumar10
Messages: 19
Registered: March 2017
|
Junior Member |
|
|
Hi all thanks for the reply.
I would try to present the question better
CREATE table t1
(
fno varchar2(5) NOT NULL,
code varchar2(5) NOT NULL,
);
INSERT INTO t1 (fno, code)
VALUES
(10, 100 );
INSERT INTO t1 (fno, code)
VALUES
(10, 100 );
INSERT INTO t1 (fno, code)
VALUES
(20, 100 );
INSERT INTO t1 (fno, code)
VALUES
(20, 30 );
INSERT INTO t1 (fno, code)
VALUES
(20, 40 );
INSERT INTO t1 (fno, code)
VALUES
(30, 30 );
INSERT INTO t1 (fno, code)
VALUES
(30, 20 );
INSERT INTO t1 (fno, code)
VALUES
(40, 50 );
INSERT INTO t1 (fno, code)
VALUES
(50, 100 );
tgt table
CREATE table t2
(
fno varchar2(5) NOT NULL,
code varchar2(5) NOT NULL,
flag varchar2(1) notnull
);
expected output
fno code flag
10 100 1
10 100 1
20 100 0
20 30 0
20 40 0
30 30 0
30 20 0
40 50 0
50 100 1
Quote:
flyboy: Thanks for the reply but I have to also consider the scenerio where a group in fno say 30 contains code 100 also another code which is <> 100 in this case group 30 should be flagged as 0. My sql does this but i understand that it was a bad way of writing code.
In the above example fno:20 is flagged as 0 because it has a group with code values other than 100. where code values in fno 10 is 100 for whole group hence marked as 1.
Hope I have explained it better this time any inputs is appreciated.
Also there is no cosntarints here as down the lane i am loding data into datasets etc downstream.
Thanks.
|
|
|
|
|
|
| Re: help with sql grouping [message #664912 is a reply to message #664909] |
Fri, 11 August 2017 01:19 |
 |
Michel Cadot
Messages: 68417
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
When you post a test case, please test it before:
SQL> CREATE table t1
2 (
3 fno varchar2(5) NOT NULL,
4 code varchar2(5) NOT NULL,
5 );
)
*
ERROR at line 5:
ORA-00904: : invalid identifier SQL> select fno, code,
2 case
3 when count(decode(code,100,1)) over (partition by fno)
4 = count(*) over (partition by fno)
5 then 1
6 else 0
7 end flag
8 from t1
9 order by 1, 2
10 /
FNO CODE FLAG
----- ----- ----------
10 100 1
10 100 1
20 100 0
20 30 0
20 40 0
30 20 0
30 30 0
40 50 0
50 100 1
9 rows selected. |
|
|
|
|
|
] 
Search
Help
Members
Register
Login
Home
