ROY!

Some SQL Gobblety Gook.

patrick_admin
patrick_admin lovin 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

  • 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(+)