Skip to main content

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: nz5c7cbb8b077d4fd19dc8cbc02578e8cc.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 e'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`, ckey#944118,lastUpdatedTime#944119,Active#944120,EventAssociationID#944121,EventID#944122,EventTimeStamp#944123,Message#944124,SourceName#944125]) : +- Relation ikey#944118,lastUpdatedTime#944119,Active#944120,EventAssociationID#944121,EventID#944122,EventTimeStamp#944123,Message#944124,SourceName#944125] cognite.spark.v1.RawTableRelation@46813d77 +- SubqueryAlias z90abb9dac9834d29b7135fa9996cfab4 +- View (`z90abb9dac9834d29b7135fa9996cfab4`, 4key#944154,lastUpdatedTime#944155,EventAssociationID#944156,EventTimeStamp#944157,Message#944158]) +- Relation ikey#944154,lastUpdatedTime#944155,EventAssociationID#944156,EventTimeStamp#944157,Message#944158] cognite.spark.v1.RawTableRelation@31a30f12 | code: 400 | X-Request-ID: 339a5174-27cd-9df1-8b56-fa156ab329fb

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`

 

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

Reply


Terms & ConditionsCookie Settings