Hi Team I need to convert below sql query to hive query. Is nested sub-queries possible in hive?
SELECT * FROM( SELECT NVL((SELECT orgname FROM organisations WHERE orgid IN(SELECT parentid FROM organisations WHERE orgid IN(SELECT parentid FROM organisations WHERE orgid IN(SELECT parentid FROM organisations WHERE orgid=7228 AND STATUS=1 AND entityid IN (1,2))AND STATUS=1 AND entityid IN (1,2))AND STATUS=1 AND entityid IN (1,2)) AND STATUS=1 AND entityid IN (1,2),'NA')) AS ReportingOrg3, NVL((SELECT orgname FROM organisations WHERE orgid IN(SELECT parentid FROM organisations WHERE orgid IN(SELECT parentid FROM organisations WHERE orgid= 7229 AND STATUS=1 AND entityid IN (1,2))AND STATUS=1 AND entityid IN (1,2)) AND STATUS=1 AND entityid IN (1,2)),'NA') AS ReportingOrg2, NVL((SELECT orgname FROM organisations WHERE orgid IN(SELECT parentid FROM organisations WHERE orgid = 7228 AND STATUS=1 AND entityid = 2) AND STATUS=1 AND entityid = 2),'NA') AS ReportingOrg1, (SELECT NVL(orgcode,'NA') FROM organisations WHERE orgid =r.rootbranch) AS OrgCode, (SELECT orgname FROM organisations WHERE orgid =r.rootbranch) AS OrgName, NVL((SELECT orgname FROM organisations WHERE orgid=(SELECT parentid FROM organisations WHERE orgid=(SELECT parentid FROM organisations WHERE orgid=(SELECT parentid FROM organisations WHERE orgid=r.parentid AND STATUS=1 AND entityid = 3)AND STATUS=1 AND entityid = 3) AND STATUS=1 AND entityid = 3) AND STATUS=1 AND entityid = 3),'NA') AS ReportingDept4, NVL((SELECT orgname FROM organisations WHERE orgid=(SELECT parentid FROM organisations WHERE orgid=(SELECT parentid FROM organisations WHERE orgid=r.parentid AND STATUS=1 AND entityid = 3)AND STATUS=1 AND entityid = 3) AND STATUS=1 AND entityid = 3),'NA') AS ReportingDept3, NVL((SELECT orgname FROM organisations WHERE orgid=(SELECT parentid FROM organisations WHERE orgid =r.parentid AND STATUS=1 AND entityid = 3) AND STATUS=1 AND entityid = 3),'NA') AS ReportingDept2, NVL((SELECT orgname FROM organisations WHERE orgid=r.parentid AND STATUS=1 AND entityid = 3),'NA') AS ReportingDept1, Thanks in Advance Cheers
