Some SQL Gobblety Gook.

patrickHaroldKelly
edited November 2021 in Roy

Looking for some recommendations to use UNION all select queries in stored procedure

I have a sql query involving UNION and each of the select query have outer joins. This query gives me a report that I am looking for. How can the sql query result obtained in form of stored procedure.


Below is the sql query that works. How to covert this to stored procedure to get the same output.




select A.table_name,A.row_count amrowcount, W.row_count wprowcount,C.row_count clrowcount


from CARE_TBLROWCOUNT@am_prd_link A, CARE_TBLROWCOUNT@wp_prd_link W, CARE_TBLROWCOUNT@cl_prd_link C


where A.table_name=W.table_name(+)


and W.table_name=C.table_name(+)


and A.table_name=C.table_name(+)


UNION


select W.table_name,A.row_count amrowcount, W.row_count wprowcount,C.row_count clrowcount


from CARE_TBLROWCOUNT@am_prd_link A, CARE_TBLROWCOUNT@wp_prd_link W, CARE_TBLROWCOUNT@cl_prd_link C


where W.table_name=A.table_name(+)


and A.table_name=C.table_name(+)


and W.table_name=C.table_name(+)


UNION


select C.table_name,A.row_count amrowcount, W.row_count wprowcount,C.row_count clrowcount


from CARE_TBLROWCOUNT@am_prd_link A, CARE_TBLROWCOUNT@wp_prd_link W, CARE_TBLROWCOUNT@cl_prd_link C


where C.table_name=A.table_name(+)


and A.table_name=W.table_name(+)


and C.table_name=W.table_name(+)

Comments

  • Looking for some recommendations to use UNION all select queries in stored procedure

    I have a sql query involving UNION and each of the select query have outer joins. This query gives me a report that I am looking for. How can the sql query result obtained in form of stored procedure.


    Below is the sql query that works. How to covert this to stored procedure to get the same output.




    select A.table_name,A.row_count amrowcount, W.row_count wprowcount,C.row_count clrowcount


    from CARE_TBLROWCOUNT@am_prd_link A, CARE_TBLROWCOUNT@wp_prd_link W, CARE_TBLROWCOUNT@cl_prd_link C


    where A.table_name=W.table_name(+)


    and W.table_name=C.table_name(+)


    and A.table_name=C.table_name(+)


    UNION


    select W.table_name,A.row_count amrowcount, W.row_count wprowcount,C.row_count clrowcount


    from CARE_TBLROWCOUNT@am_prd_link A, CARE_TBLROWCOUNT@wp_prd_link W, CARE_TBLROWCOUNT@cl_prd_link C


    where W.table_name=A.table_name(+)


    and A.table_name=C.table_name(+)


    and W.table_name=C.table_name(+)


    UNION


    select C.table_name,A.row_count amrowcount, W.row_count wprowcount,C.row_count clrowcount


    from CARE_TBLROWCOUNT@am_prd_link A, CARE_TBLROWCOUNT@wp_prd_link W, CARE_TBLROWCOUNT@cl_prd_link C


    where C.table_name=A.table_name(+)


    and A.table_name=W.table_name(+)


    and C.table_name=W.table_name(+)