ChewCheeLim

Nested Query

Discussion created by ChewCheeLim on Feb 7, 2014
Latest reply on Feb 11, 2014 by ChewCheeLim
Hi, I would like to do nested query in PI SQL Commander. I guess I am not really understanding if I could do it. By reading The PI OLEDB Enterprise User Guide  section :  "OLE DB Porvider Options" Sounded like Nested Query is possible by enabling the NestedQueries = 1 in SQL Management Tool? If my assumption is correct, then I am getting an "Unknown Error" after executing below query. Could someone explain if this is possible? Query Sample: select  dr.* from RBCDailyRounds.Asset.ElementHierarchy h_p inner join RBCDailyRounds.Asset.ElementAttribute ea on h_p.Elementid = ea.ElementID left outer join RBCDailyRounds.Data.archive dr on dr.ElementAttributeId = ea.Id      and dr.[time] between '2014-02-05 12:56:15' and '2014-02-05 12:56:45' left outer join (             select  i_a.ElementAttributeId, max(i_a.[Time]) as last_rec_time                 from RBCDailyRounds.Asset.ElementHierarchy i_h                inner join RBCDailyRounds.Asset.ElementAttribute i_ea on i_h.Elementid = i_ea.ElementID                 inner join RBCDailyRounds.Data.archive i_a on i_a.ElementAttributeId = i_ea.Id                where i_h.path = N'\'                and i_h.name = 'Master'                and i_a.[time] between '2014-02-05 12:56:15' and '2014-02-05 12:56:45'             group by i_a.ElementAttributeId             )  as tv_max on tv_max.ElementAttributeId = ea.Id        and dr.[time] = tv_max.last_rec_time       where h_p.path = N'\' and h_p.name = 'Master' OPTION (FORCE ORDER, ALLOW EXPENSIVE)

Outcomes