Some SQL Gobblety Gook.

patrickHaroldKelly

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

patrickHaroldKelly

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