Joining Two Subqueries That Contain A Subquery

Still new to SQL. I have looked around for an answer on this, and the examples that I have found, I have mirrored my query to look like the examples, and this query still isn't working. All Im trying to do is join two subqueries, but both of these subqueries contain a subquery inside. I'm receiving the following error:

Msg 156, Level 15, State 1, Line 26
Incorrect syntax near the keyword 'On'.

This is the query, please assist, thank you.

Select *
From
(
Select * From(
Select x.ID, x.Date, x.USOHist, x.OVXHist,
Abs(Cast((((x.USOHist / NullIf((y.USOHist),0))-1)*100) as Decimal(10,2))) AS '%USOH',
Abs(Cast((((x.OVXHist / NullIf((y.OVXHist),0))-1)*100) as Decimal(10,2))) AS '%OVXH'
From (Select a.Date as aDate, Max(b.Date) As aPrevDate From USO_OVX_Hist a Inner Join USO_OVX_Hist b on a.Date > b.Date
Group By a.Date) Sub1
Inner Join USO_OVX_Hist x on Sub1.aDate = x.Date
Inner Join USO_OVX_Hist y on Sub1.aPrevDate = y.Date
) Sub2

Inner Join
(
Select * From(
Select z.ID, z.ID2, z.Date, z.USO as USOP, z.OVX as OVXP,
Cast(((z.USO / NullIf((q.USO),0)- 1) * 100) as Decimal(10,2)) AS '%USOP',
Cast(((z.OVX / NullIf((q.OVX),0)- 1) * 100) as Decimal(10,2)) AS '%OVXP'
From (Select c.Date as cDate, Max(d.Date) As cPrevDate From USO_OVX_Price c Inner Join USO_OVX_Price d on c.Date > d.Date
Group By c.Date) Sub3
Inner Join USO_OVX_Price z on Sub3.cDate = z.Date
Inner Join USO_OVX_Price q on Sub3.cPrevDate = q.Date
) Sub4

On Sub2.Date = Sub4.Date


2 Answers
2

Added closing parenthesis before sub2 and sub4 and new alias t2 and t4:

sub2

sub4

t2

t4

select * from
(
Select * From(
Select x.ID, x.Date, x.USOHist, x.OVXHist,
Abs(Cast((((x.USOHist / NullIf((y.USOHist),0))-1)*100) as Decimal(10,2))) AS '%USOH',
Abs(Cast((((x.OVXHist / NullIf((y.OVXHist),0))-1)*100) as Decimal(10,2))) AS '%OVXH'
From (Select a.Date as aDate, Max(b.Date) As aPrevDate From USO_OVX_Hist a Inner Join USO_OVX_Hist b on a.Date > b.Date
Group By a.Date) Sub1
Inner Join USO_OVX_Hist x on Sub1.aDate = x.Date
Inner Join USO_OVX_Hist y on Sub1.aPrevDate = y.Date
) t2 ) sub2

Inner Join
(
Select * From (
Select z.ID, z.ID2, z.Date, z.USO as USOP, z.OVX as OVXP,
Cast(((z.USO / NullIf((q.USO),0)- 1) * 100) as Decimal(10,2)) AS '%USOP',
Cast(((z.OVX / NullIf((q.OVX),0)- 1) * 100) as Decimal(10,2)) AS '%OVXP'
From (Select c.Date as cDate, Max(d.Date) As cPrevDate From USO_OVX_Price c Inner Join USO_OVX_Price d on c.Date > d.Date
Group By c.Date) Sub3
Inner Join USO_OVX_Price z on Sub3.cDate = z.Date
Inner Join USO_OVX_Price q on Sub3.cPrevDate = q.Date
) t4 ) sub4

On sub2.Date = sub4.Date


Use the following:

SELECT *
FROM
(
SELECT *
FROM
(
SELECT
x.id,
x.date,
x.usohist,
x.ovxhist,
ABS(CAST((((x.usohist / NULLIF((y.usohist), 0)) - 1) * 100) AS decimal(10, 2))) AS '%USOH',
ABS(CAST((((x.ovxhist / NULLIF((y.ovxhist), 0)) - 1) * 100) AS decimal(10, 2))) AS '%OVXH'
FROM
(
SELECT
a.date AS adate,
MAX(b.date) AS aprevdate
FROM uso_ovx_hist a
INNER JOIN uso_ovx_hist b
ON a.date > b.date
GROUP BY a.date
) Sub1
INNER JOIN uso_ovx_hist x
ON sub1.adate = x.date
INNER JOIN uso_ovx_hist y
ON sub1.aprevdate = y.date
) Sub2
INNER JOIN
(SELECT * FROM
(
SELECT
z.id,
z.id2,
z.date,
z.uso AS usop,
z.ovx AS ovxp,
CAST(((z.uso / NULLIF((q.uso), 0) - 1) * 100) AS decimal(10, 2)) AS '%USOP',
CAST(((z.ovx / NULLIF((q.ovx), 0) - 1) * 100) AS decimal(10, 2)) AS '%OVXP'
FROM
(SELECT
c.date AS cdate,
MAX(d.date) AS cprevdate
FROM uso_ovx_price c
INNER JOIN uso_ovx_price d
ON c.date > d.date
GROUP BY c.date) Sub3
INNER JOIN uso_ovx_price z
ON sub3.cdate = z.date
INNER JOIN uso_ovx_price q
ON sub3.cprevdate = q.date
) Sub4
) t
ON sub2.date = t.date
) t2


You can use online Instant SQL Formatter to format your SQL.

Thanks this worked great. I'm having a hard time understanding the point about adding the alias in order for the join to work. Do you mind explaining it to me? I am still kind of new to SQL. Also, how exactly do I make more subqueries or join another table on that query?

Just another: InnerJoin NYSEData on Sub4.Date = NYSEData.Date

I get an error stating that Sub4 object cannot be bound?

I understand that I will need to add another select and from standard in the query in order to add another table or subquery? Please help, thank you.

that formatting is truly horrible!
– Mitch Wheat
Jun 23 at 4:23

how about the new one..?
– satishcse
Jun 23 at 4:36

thanks that worked great. sorry bout the formatting :)
– UndergroundMan
Jun 27 at 23:41

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.