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 Go to next message
wtolentino
Messages: 280
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 Go to previous messageGo to next message
Michel Cadot
Messages: 66636
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.
Re: Additional Column Indicator for Failed or Passed [message #677193 is a reply to message #677189] Thu, 29 August 2019 11:45 Go to previous message
wtolentino
Messages: 280
Registered: March 2005
Senior Member
that works thanks so much.
Previous Topic: Create table as select
Next Topic: Purging Data From table!
Goto Forum:
  


Current Time: Mon Oct 14 17:51:12 CDT 2019