How to use hierarchical query in SQL

Question

I want to use the following query as a hierarchical query without any duplication in job Id and job description even though the time stamp varies

For example, there should be only one job_id, job_description showing without any duplication and by using min and max function in the following fields exec_start_time and exec_end_time. The sol_id from tbaadm.bjm table should be mapped with set_id from table tbaadm.sst.

Original Query:

Select  Sol_Id,
        Job_Id,
        Job_Desc,
        To_Char(Exec_Start_Time,'HH:MM:SS'),
        To_char(Exec_End_Time,'HH:MM:SS'),
        Job_Status
From    Tbaadm.Bjm
Where   Job_Exec_Date = '21-10-2016'
And     Job_Desc Like 'SDP%'
Order By Job_Id;

Then I got stuck with this

Select  Sol_Id,
        Job_Id,
        Job_Desc,
        Min(V_Exec_Start_Time),
        Max(V_Exec_End_Time),
        Job_Status
From    (Select Sol_Id,
                Job_Id,
                Job_Desc,
                (To_Char(Exec_Start_Time,'HH:MM:SS')) As v_exec_start_time,
                (To_Char(Exec_End_Time,'HH:MM:SS')) As v_exec_end_time,

                Job_Status
         From Tbaadm.Bjm
         Where Sol_Id In (Select Sol_Id From Tbaadm.Sst Where Set_Id = '100')
         And   Job_Exec_Date = '21-OCT-2016'
         And   Job_Desc Like 'SDP%')
Group By Sol_Id,Job_Id,Job_Desc,V_Exec_Start_Time,V_Exec_End_Time,Job_Status
Order By Job_Id;

What have you tried? What is your input and what is expected output and what you are actually getting.. Please show that in your post — YLG
– YLG, Commented Nov 28, 2018 at 6:44
sorry for that this is the query. Select Sol_Id, Job_Id, Job_Desc, To_Char(Exec_Start_Time,'HH:MM:SS'), To_char(Exec_End_Time,'HH:MM:SS'), Job_Status From Tbaadm.Bjm Where Job_Exec_Date = '21-10-2016' And Job_Desc Like 'SDP%' Order By Job_Id; — Madesh
– Madesh, Commented Nov 28, 2018 at 6:48

Jayati Makwana · Accepted Answer · 2018-11-28 07:01:33Z

1

If u just want one line for each job then it is not called hierarchical because hierarchy means there is some parent child relationship and you want data of a tree kind of fashion in tabular format.

Here you can simply use group by function to get one line for each job having "Min and Max function" applied on required fields (Exec_Start_Time,Exec_End_Time).

Thanks, Jayati

answered Nov 28, 2018 at 7:01

Jayati Makwana

111 bronze badge

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to use hierarchical query in SQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related