Solved

SQL Transform Join Error


Userlevel 3
Badge

Executing a SQL Transform joining 2 tables from Raw and writing results to a 3rd Raw table.

Perhaps there is a syntax issue? 

Not sure if it matters but I noticed the error msg omits some backticks like here:

'ChemPlant.`Alarms-2-INActive`.EventAssociationID' there is no backtick before Event…

 

Thank you.

SELECT        
`ChemPlant`.`Alarms-2-INActive`.`EventID`, `ChemPlant`.`Alarms-2-INActive`.`SourceName`,
`ChemPlant`.`Alarms-1-Active`.`EventTimeStamp`, `ChemPlant`.`Alarms-2-INActive`.`EventTimeStamp` AS AlarmOffTime,
`ChemPlant`.`Alarms-2-INActive`.`Message`, `ChemPlant`.`Alarms-2-INActive`.`Active`,
`ChemPlant`.`Alarms-2-INActive`.`EventAssociationID` as key, `ChemPlant`.`Alarms-2-INActive`.`EventAssociationID`


FROM
`ChemPlant`.`Alarms-2-INActive` INNER JOIN
`ChemPlant`.`Alarms-1-Active` ON
`ChemPlant`.`Alarms-2-INActive`.`EventAssociationID` = `ChemPlant`.`Alarms-1-Active`.`EventAssociationID`

Error Message:

Cannot resolve 'ChemPlant.`Alarms-2-INActive`.EventAssociationID' given input columns: [z5c7cbb8b077d4fd19dc8cbc02578e8cc.Active, z5c7cbb8b077d4fd19dc8cbc02578e8cc.EventAssociationID, z90abb9dac9834d29b7135fa9996cfab4.EventAssociationID, z5c7cbb8b077d4fd19dc8cbc02578e8cc.EventID, z5c7cbb8b077d4fd19dc8cbc02578e8cc.EventTimeStamp, z90abb9dac9834d29b7135fa9996cfab4.EventTimeStamp, z5c7cbb8b077d4fd19dc8cbc02578e8cc.Message, z90abb9dac9834d29b7135fa9996cfab4.Message, z5c7cbb8b077d4fd19dc8cbc02578e8cc.SourceName, z5c7cbb8b077d4fd19dc8cbc02578e8cc.key, z90abb9dac9834d29b7135fa9996cfab4.key, z5c7cbb8b077d4fd19dc8cbc02578e8cc.lastUpdatedTime, z90abb9dac9834d29b7135fa9996cfab4.lastUpdatedTime]; line 14 pos 0; 'Project ['ChemPlant.Alarms-2-INActive.EventID, 'ChemPlant.Alarms-2-INActive.SourceName, 'ChemPlant.Alarms-1-Active.EventTimeStamp, 'ChemPlant.Alarms-2-INActive.EventTimeStamp AS AlarmOffTime#944038, 'ChemPlant.Alarms-2-INActive.Message, 'ChemPlant.Alarms-2-INActive.Active, 'ChemPlant.Alarms-2-INActive.EventAssociationID AS key#944039, 'ChemPlant.Alarms-2-INActive.EventAssociationID] +- 'Join Inner, ('ChemPlant.Alarms-2-INActive.EventAssociationID = 'ChemPlant.Alarms-1-Active.EventAssociationID) :- SubqueryAlias z5c7cbb8b077d4fd19dc8cbc02578e8cc : +- View (`z5c7cbb8b077d4fd19dc8cbc02578e8cc`, [key#944118,lastUpdatedTime#944119,Active#944120,EventAssociationID#944121,EventID#944122,EventTimeStamp#944123,Message#944124,SourceName#944125]) : +- Relation [key#944118,lastUpdatedTime#944119,Active#944120,EventAssociationID#944121,EventID#944122,EventTimeStamp#944123,Message#944124,SourceName#944125] cognite.spark.v1.RawTableRelation@46813d77 +- SubqueryAlias z90abb9dac9834d29b7135fa9996cfab4 +- View (`z90abb9dac9834d29b7135fa9996cfab4`, [key#944154,lastUpdatedTime#944155,EventAssociationID#944156,EventTimeStamp#944157,Message#944158]) +- Relation [key#944154,lastUpdatedTime#944155,EventAssociationID#944156,EventTimeStamp#944157,Message#944158] cognite.spark.v1.RawTableRelation@31a30f12 | code: 400 | X-Request-ID: 339a5174-27cd-9df1-8b56-fa156ab329fb

icon

Best answer by Noah Karsky 24 May 2023, 18:30

View original

2 replies

Userlevel 3
Badge

Noah, thanks a ton for taking the time to find my error. The code above is working!

Badge

Might be because you have it selected twice at the end, although I feel this will not solve it. Putting in aliases for the tables might also help with the debugging, and make sure that the EventAssociationID is indeed in both the INActive and Active tables

SELECT  
  INActive.`EventID`, 
  INActive.`SourceName`, 
  Active.`EventTimeStamp` AS AlarmOnTime,
  INActive.`EventTimeStamp` AS AlarmOffTime,
  INActive.`Message`, 
  INActive.`Active`, 
  INActive.`EventAssociationID` AS Key

FROM            
  `ChemPlant`.`Alarms-2-INActive` AS INActive
INNER JOIN
  `ChemPlant`.`Alarms-1-Active` AS Active
ON 
  INActive.`EventAssociationID` = Active.`EventAssociationID`

 

Reply