Home » SQL & PL/SQL » SQL & PL/SQL » Additional Column Indicator for Failed or Passed (Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production)
| Additional Column Indicator for Failed or Passed [message #677180] |
Thu, 29 August 2019 07:47  |
wtolentino
Messages: 370
Registered: March 2005
|
Senior Member |
|
|
i have this situation where i need an additional column as indicator of failed or passed. this is not the actual code (too long to discuss) but similar to this so i mimic the situation to illustrate an example.
here is the query:
with t as
(select 'Coach' position, 'Coach A' team_person, null coach_by, null training_passed from dual union all
select 'Captain' position, 'Player 01' team_person, 'Coach A' coach_by, 'Yes' training_passed from dual union all
select 'Defense' position, 'Player 02' team_person, 'Coach A' coach_by, 'Yes' training_passed from dual union all
select 'Defense' position, 'Player 03' team_person, 'Coach A' coach_by, 'Yes' training_passed from dual union all
select 'Forward' position, 'Player 04' team_person, 'Coach A' coach_by, 'No' training_passed from dual union all
select 'Forward' position, 'Player 05' team_person, 'Coach A' coach_by, 'Yes' training_passed from dual union all
select 'Point' position, 'Player 06' team_person, 'Coach A' coach_by, 'Yes' training_passed from dual union all
select 'Coach' position, 'Coach B' team_person, null coach_by, null training_passed from dual union all
select 'Captain' position, 'Player 07' team_person, 'Coach B' coach_by, 'Yes' training_passed from dual union all
select 'Defense' position, 'Player 08' team_person, 'Coach B' coach_by, 'Yes' training_passed from dual union all
select 'Defense' position, 'Player 09' team_person, 'Coach B' coach_by, 'Yes' training_passed from dual union all
select 'Forward' position, 'Player 10' team_person, 'Coach B' coach_by, 'Yes' training_passed from dual union all
select 'Forward' position, 'Player 11' team_person, 'Coach B' coach_by, 'Yes' training_passed from dual union all
select 'Point' position, 'Player 12' team_person, 'Coach B' coach_by, 'Yes' training_passed from dual)
select t.position,
decode(level,1,t.team_person,lpad('- '||t.team_person,length('- '||t.team_person)+level)) team_person,
t.training_passed,
CONNECT_BY_ISLEAF isleaf,
level
from t
start with coach_by is null
connect by nocycle t.coach_by = prior t.team_person
order siblings by t.team_person, t.coach_by;
output of the above query
POSITION TEAM_PERSON TRAINING_PASSED ISLEAF LEVEL
---------- ------------------------- --------------- ---------- ----------
Coach Coach A 0 1
Captain - Player 01 Yes 1 2
Defense - Player 02 Yes 1 2
Defense - Player 03 Yes 1 2
Forward - Player 04 No 1 2
Forward - Player 05 Yes 1 2
Point - Player 06 Yes 1 2
Coach Coach B 0 1
Captain - Player 07 Yes 1 2
Defense - Player 08 Yes 1 2
Defense - Player 09 Yes 1 2
Forward - Player 10 Yes 1 2
Forward - Player 11 Yes 1 2
Point - Player 12 Yes 1 2
the expected output is:
POSITION TEAM_PERSON TRAINING_PASSED ISLEAF LEVEL TEAM_EFFORT
---------- ------------------------- --------------- ---------- ---------- -----------
Coach Coach A 0 1 Failed
Captain - Player 01 Yes 1 2 Failed
Defense - Player 02 Yes 1 2 Failed
Defense - Player 03 Yes 1 2 Failed
Forward - Player 04 No 1 2 Failed
Forward - Player 05 Yes 1 2 Failed
Point - Player 06 Yes 1 2 Failed
Coach Coach B 0 1 Passed
Captain - Player 07 Yes 1 2 Passed
Defense - Player 08 Yes 1 2 Passed
Defense - Player 09 Yes 1 2 Passed
Forward - Player 10 Yes 1 2 Passed
Forward - Player 11 Yes 1 2 Passed
Point - Player 12 Yes 1 2 Passed
basically if one of the players did not passed their training the team also gets failed. so it is a team effort. in the case of the above example Coach A's player 04 failed the training this results in the team also failed their training.
please help thank you.
|
|
|
|
| Re: Additional Column Indicator for Failed or Passed [message #677189 is a reply to message #677180] |
Thu, 29 August 2019 10:35  |
 |
Michel Cadot
Messages: 68417
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Slightly modifying your query:
SQL> col team_person format a20
SQL> col team_effort format a11
SQL> with t as
2 (select 'Coach' position, 'Coach A' team_person, null coach_by, null training_passed from dual union all
3 select 'Captain' position, 'Player 01' team_person, 'Coach A' coach_by, 'Yes' training_passed from dual union all
4 select 'Defense' position, 'Player 02' team_person, 'Coach A' coach_by, 'Yes' training_passed from dual union all
5 select 'Defense' position, 'Player 03' team_person, 'Coach A' coach_by, 'Yes' training_passed from dual union all
6 select 'Forward' position, 'Player 04' team_person, 'Coach A' coach_by, 'No' training_passed from dual union all
7 select 'Forward' position, 'Player 05' team_person, 'Coach A' coach_by, 'Yes' training_passed from dual union all
8 select 'Point' position, 'Player 06' team_person, 'Coach A' coach_by, 'Yes' training_passed from dual union all
9 select 'Coach' position, 'Coach B' team_person, null coach_by, null training_passed from dual union all
10 select 'Captain' position, 'Player 07' team_person, 'Coach B' coach_by, 'Yes' training_passed from dual union all
11 select 'Defense' position, 'Player 08' team_person, 'Coach B' coach_by, 'Yes' training_passed from dual union all
12 select 'Defense' position, 'Player 09' team_person, 'Coach B' coach_by, 'Yes' training_passed from dual union all
13 select 'Forward' position, 'Player 10' team_person, 'Coach B' coach_by, 'Yes' training_passed from dual union all
14 select 'Forward' position, 'Player 11' team_person, 'Coach B' coach_by, 'Yes' training_passed from dual union all
15 select 'Point' position, 'Player 12' team_person, 'Coach B' coach_by, 'Yes' training_passed from dual),
16 compute as (
17 select t.position,
18 decode(level,1,t.team_person,lpad('- '||t.team_person,length('- '||t.team_person)+level)) team_person,
19 t.training_passed,
20 CONNECT_BY_ISLEAF isleaf,
21 level lvl,
22 connect_by_root team_person coach
23 from t
24 start with coach_by is null
25 connect by nocycle t.coach_by = prior t.team_person
26 order siblings by t.team_person, t.coach_by
27 )
28 select position, team_person, training_passed, isleaf, lvl "LEVEL",
29 decode(min(training_passed) over (partition by coach), 'Yes', 'Passed', 'Failed') team_effort
30 from compute
31 /
POSITIO TEAM_PERSON TRA ISLEAF LEVEL TEAM_EFFORT
------- -------------------- --- ---------- ---------- -----------
Coach Coach A 0 1 Failed
Captain - Player 01 Yes 1 2 Failed
Defense - Player 02 Yes 1 2 Failed
Defense - Player 03 Yes 1 2 Failed
Forward - Player 04 No 1 2 Failed
Forward - Player 05 Yes 1 2 Failed
Point - Player 06 Yes 1 2 Failed
Coach Coach B 0 1 Passed
Captain - Player 07 Yes 1 2 Passed
Defense - Player 08 Yes 1 2 Passed
Defense - Player 09 Yes 1 2 Passed
Forward - Player 10 Yes 1 2 Passed
Forward - Player 11 Yes 1 2 Passed
Point - Player 12 Yes 1 2 Passed
14 rows selected. |
|
|
|
|
|
Goto Forum:
Current Time: Wed Mar 22 08:00:00 CDT 2023
|
] 
Search
Help
Members
Register
Login
Home
