Hi I have a following query in HSQL with multiple left outer joins and this runs perfectly fine in HSQL, Note that multiple left outer joins in the query
*select b.macaddress, b.addresstype, a.vmname, d.name as hostname, a.guestosfull, a.powerStatus, a.timestamp, a.createtimestamp, * *c.name as networkname, c.vlan, e.switchipaddress, e.slot, e.port, a.id, a.hostid, f.ipaddress, e.ifindex, e.vlanid, e.ifspeed, * *e.ifadminstatus, e.portduplexmode, e.unp, e.domain, e.disposition, a.uuid, a.uptime, a.overallstatus, g.name as datacenter, * *h.name as cluster, e.timestmp as locatorts, f.tmstamp as arpts, l.vlan as vportvlan, l.key as vportkey, k.name as vcentername, * *e.classsource from vmachine a, vnetwork b, vportgroup c, vhost d, vcenter k left outer join locatorforward e on e.macaddress = b.macaddress* * and e.timestmp > b.createtimestamp left outer join locatorarp f on f.macaddress = b.macaddress left outer join vdatacenter g on g.id=d.dcid* * left outer join vcluster h on h.id=d.clusterid left outer join vport l on b.portkey = l.key and b.portgroupkey=l.portgroupkey and l.status='A'* * where a.hostid=d.id and a.id = b.vmid and ((b.networkname = c.name and b.hostid=c.hostid) or (c.hostid=-1 and c.key=b.portgroupkey))* * and d.vcenterid= k.id and a.status='A' and b.status='A' and c.status='A' and g.status='A' and k.status='A' order by e.timestmp desc,* * f.tmstamp desc * However, when I migrated the tables from HSQL to H2 and executed the same query, it gave me the following error *[SELECT - 0 row(s), 0.000 secs] [Error Code: 42122, SQL State: 42S22] Column "B.MACADDRESS" not found; SQL statement:* *select b.macaddress, b.addresstype, a.vmname, d.name as hostname, a.guestosfull, a.powerStatus, a.timestamp, a.createtimestamp, * *c.name as networkname, c.vlan, e.switchipaddress, e.slot, e.port, a.id, a.hostid, f.ipaddress, e.ifindex, e.vlanid, e.ifspeed, * *e.ifadminstatus, e.portduplexmode, e.unp, e.domain, e.disposition, a.uuid, a.uptime, a.overallstatus, g.name as datacenter, * *h.name as cluster, e.timestmp as locatorts, f.tmstamp as arpts, l.vlan as vportvlan, l.key as vportkey, k.name as vcentername, * *e.classsource from vmachine a, vnetwork b, vportgroup c, vhost d, vcenter k left outer join locatorforward e on e.macaddress = b.macaddress* * and e.timestmp > b.createtimestamp left outer join locatorarp f on f.macaddress = b.macaddress left outer join vdatacenter g on g.id=d.dcid* * left outer join vcluster h on h.id=d.clusterid left outer join vport l on b.portkey = l.key and b.portgroupkey=l.portgroupkey and l.status='A'* * where a.hostid=d.id and a.id = b.vmid and ((b.networkname = c.name and b.hostid=c.hostid) or (c.hostid=-1 and c.key=b.portgroupkey))* * and d.vcenterid= k.id and a.status='A' and b.status='A' and c.status='A' and g.status='A' and k.status='A' order by e.timestmp desc,* * f.tmstamp desc [42122-167]* * * I changed the way left outer joins are used in the query below and it does not give any syntax error now * //Modified query select b.macaddress, b.addresstype, a.vmname, d.name as hostname, a.guestosfull, a.powerStatus, a.timestamp, a.createtimestamp, c.name as networkname, c.vlan, e.switchipaddress, e.slot, e.port, a.id, a.hostid, f.ipaddress, e.ifindex, e.vlanid, e.ifspeed, e.ifadminstatus, e.portduplexmode, e.unp, e.domain, e.disposition, a.uuid, a.uptime, a.overallstatus, g.name as datacenter, h.name as cluster, e.timestmp as locatorts, f.tmstamp as arpts, l.vlan as vportvlan, l.key as vportkey, k.name as vcentername, e.classsource from vmachine a, vnetwork b left outer join locatorforward e on e.macaddress = b.macaddress and e.timestmp > b.createtimestamp left outer join locatorarp f on f.macaddress = b.macaddress left outer join vport l on b.portkey = l.key and b.portgroupkey=l.portgroupkey and l.status='A', vportgroup c, vhost d left outer join vdatacenter g on g.id=d.dcid left outer join vcluster h on h.id=d.clusterid, vcenter k where a.hostid=d.id and a.id = b.vmid and ((b.networkname = c.name and b.hostid=c.hostid) or (c.hostid=-1 and c.key=b.portgroupkey)) and d.vcenterid= k.id and a.status='A' and b.status='A' and c.status='A' and g.status='A' and k.status='A' order by e.timestmp desc, f.tmstamp desc * Can anyone please tell if the Left Outer Join syntax in H2 has got changed. Will appreciate if someone can help in this. -Manik -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To view this discussion on the web visit https://groups.google.com/d/msg/h2-database/-/jqNrONnhW70J. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.
