Skip to main content
Solved

SQL Transform Join Error

Chris Selph
Seasoned
Forum|alt.badge.img

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

Best answer by Noah Karsky

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`

 

View original
    Anita Hæhre
    1 person likes this
    • Anita Hæhre
      Anita Hæhre
    Did this topic help you find an answer to your question?

    2 replies

    Noah Karsky
    Practitioner
    Forum|alt.badge.img
    • Noah Karsky
    • Data Engineer
    • 6 replies
    • Answer
    • May 24, 2023

    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`

     

    Chris Selph
    Seasoned
    Forum|alt.badge.img
    • Chris Selph
    • Author
    • Seasoned
    • 26 replies
    • May 25, 2023

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

    Noah Karsky
    1 person likes this
    • Noah Karsky
      Noah Karsky

    Reply


    Terms & ConditionsCookie Settings

    Cookie Policy

    We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

     
    Cookie Settings