Some SQL Gobblety Gook.

Patrick
Patrick Mr. admin
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 [email protected]_prd_link A, [email protected]_prd_link W, [email protected]_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 [email protected]_prd_link A, [email protected]_prd_link W, [email protected]_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 [email protected]_prd_link A, [email protected]_prd_link W, [email protected]_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

  • Patrick
    Patrick Mr. admin

    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 [email protected]_prd_link A, [email protected]_prd_link W, [email protected]_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 [email protected]_prd_link A, [email protected]_prd_link W, [email protected]_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 [email protected]_prd_link A, [email protected]_prd_link W, [email protected]_prd_link C


    where C.table_name=A.table_name(+)


    and A.table_name=W.table_name(+)


    and C.table_name=W.table_name(+)