'Is there a way that I can return multiple columns from a subquery rather than repeating the subquery for each column? It works but takes too long
As you can see I'm trying to add 4 columns by repeating the subquery, this works but takes too long to be useful. I tried to put all the joins at the end but then I only get the records that have values in [dbo.permit_terms] whereas I want all the records from [dbo.prd]. I hope this makes sense and will be very grateful for an answer.
SELECT WLC.name AS WELLCOMPL
, WLC.id_subsidiary AS SUB
, PD.Date
, PD.days
, PD.oil
, PD.water
, PD.gas
, PD.interest
, PD.operator
, PD.comments
, (
SELECT PT.net_working_int_oil
FROM dbo.prd AS P (NOLOCK)
left JOIN dbo.wellbore_compl AS W WITH (NOLOCK) ON (P.id_subsidiary = W.id_subsidiary) AND (P.id_wellborecompl = W.id)
left JOIN dbo.wellstring WITH (NOLOCK) ON W.id_wellstring = dbo.wellstring.id
left JOIN dbo.wellbore wb WITH (NOLOCK) ON dbo.wellstring.id_wellbore = wb.id
left JOIN dbo.wellbore_permit WP WITH (NOLOCK) ON wb.id = WP.id_wellbore
left JOIN dbo.permit WITH (NOLOCK) ON dbo.permit.id = WP.id_permit
left JOIN dbo.permit_terms PT WITH (NOLOCK) ON dbo.permit.id = PT.id_permit
where (p.[date] >= pt.start_date and p.[date] <=pt.end_date)
-- AND W.[name] = WLC.[name]
AND p.id = pd.id
AND pd.id_subsidiary = w.id_subsidiary
AND P.deletedAt Is Null
AND W.deletedAt Is Null
AND dbo.wellstring.deletedAt Is Null
AND dbo.permit.deletedAt Is Null
AND wb.deletedAt Is Null
AND PT.deletedAt Is Null
)
as permit_interest
, (
SELECT PT.operator
FROM dbo.prd AS P (NOLOCK)
left JOIN dbo.wellbore_compl AS W WITH (NOLOCK) ON (P.id_subsidiary = W.id_subsidiary) AND (P.id_wellborecompl = W.id)
left JOIN dbo.wellstring WITH (NOLOCK) ON W.id_wellstring = dbo.wellstring.id
left JOIN dbo.wellbore wb WITH (NOLOCK) ON dbo.wellstring.id_wellbore = wb.id
left JOIN dbo.wellbore_permit WP WITH (NOLOCK) ON wb.id = WP.id_wellbore
left JOIN dbo.permit WITH (NOLOCK) ON dbo.permit.id = WP.id_permit
left JOIN dbo.permit_terms PT WITH (NOLOCK) ON dbo.permit.id = PT.id_permit
where (p.[date] >= pt.start_date and p.[date] <=pt.end_date)
-- AND W.[name] = WLC.[name]
AND p.id = pd.id
AND pd.id_subsidiary = w.id_subsidiary
AND P.deletedAt Is Null
AND W.deletedAt Is Null
AND dbo.wellstring.deletedAt Is Null
AND dbo.permit.deletedAt Is Null
AND wb.deletedAt Is Null
AND PT.deletedAt Is Null
)
as permit_operator
, (
SELECT pd.oil*PT.net_working_int_oil/100
FROM dbo.prd AS P (NOLOCK)
left JOIN dbo.wellbore_compl AS W WITH (NOLOCK) ON (P.id_subsidiary = W.id_subsidiary) AND (P.id_wellborecompl = W.id)
left JOIN dbo.wellstring WITH (NOLOCK) ON W.id_wellstring = dbo.wellstring.id
left JOIN dbo.wellbore wb WITH (NOLOCK) ON dbo.wellstring.id_wellbore = wb.id
left JOIN dbo.wellbore_permit WP WITH (NOLOCK) ON wb.id = WP.id_wellbore
left JOIN dbo.permit WITH (NOLOCK) ON dbo.permit.id = WP.id_permit
left JOIN dbo.permit_terms PT WITH (NOLOCK) ON dbo.permit.id = PT.id_permit
where (p.[date] >= pt.start_date and p.[date] <=pt.end_date)
-- AND W.[name] = WLC.[name]
AND p.id = pd.id
AND pd.id_subsidiary = w.id_subsidiary
AND P.deletedAt Is Null
AND W.deletedAt Is Null
AND dbo.wellstring.deletedAt Is Null
AND dbo.permit.deletedAt Is Null
AND wb.deletedAt Is Null
AND PT.deletedAt Is Null
)
as Oil_net
, (
SELECT pd.gas*PT.net_working_int_gas/100
FROM dbo.prd AS P (NOLOCK)
left JOIN dbo.wellbore_compl AS W WITH (NOLOCK) ON (P.id_subsidiary = W.id_subsidiary) AND (P.id_wellborecompl = W.id)
left JOIN dbo.wellstring WITH (NOLOCK) ON W.id_wellstring = dbo.wellstring.id
left JOIN dbo.wellbore wb WITH (NOLOCK) ON dbo.wellstring.id_wellbore = wb.id
left JOIN dbo.wellbore_permit WP WITH (NOLOCK) ON wb.id = WP.id_wellbore
left JOIN dbo.permit WITH (NOLOCK) ON dbo.permit.id = WP.id_permit
left JOIN dbo.permit_terms PT WITH (NOLOCK) ON dbo.permit.id = PT.id_permit
where (p.[date] >= pt.start_date and p.[date] <=pt.end_date)
-- AND W.[name] = WLC.[name]
AND p.id = pd.id
AND pd.id_subsidiary = w.id_subsidiary
AND P.deletedAt Is Null
AND W.deletedAt Is Null
AND dbo.wellstring.deletedAt Is Null
AND dbo.permit.deletedAt Is Null
AND wb.deletedAt Is Null
AND PT.deletedAt Is Null
)
as Gas_net
FROM (dbo.prd AS PD
INNER JOIN dbo.wellbore_compl AS WLC ON (PD.id_subsidiary = WLC.id_subsidiary) AND (PD.id_wellborecompl = WLC.id))
WHERE
PD.deletedAt Is Null
AND WLC.deletedAt Is Null
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
