Solved

SQL Editor error

  • 4 January 2023
  • 3 replies
  • 106 views

Badge

I run this query per the instructions and I get the following preview error

SELECT
concat('Cory1965:',loc) as externalId,
IF(parent_loc='' OR parent_loc IS NULL, '', concat('Cory1965:',parent_loc)) AS parentExternalId,

CAST(lastUpdatedTime AS STRING) AS name,

to_metadata(*) AS metadata,

description AS description,

Cory1965-AvevaNet AS dataSetId
 
FROM `Cory1965`.assets

 

Error

 

An error occurred when running this query

"Cannot resolve 'Cory1965' given input columns: [zcb2dd7b7c3704ca2a0d8659e146db2f9.WMT_SYSTEM_ID, zcb2dd7b7c3704ca2a0d8659e146db2f9.WMT_TAG_ID, zcb2dd7b7c3704ca2a0d8659e146db2f9.description, zcb2dd7b7c3704ca2a0d8659e146db2f9.key, zcb2dd7b7c3704ca2a0d8659e146db2f9.lastUpdatedTime, zcb2dd7b7c3704ca2a0d8659e146db2f9.loc, zcb2dd7b7c3704ca2a0d8659e146db2f9.name, zcb2dd7b7c3704ca2a0d8659e146db2f9.parent_loc]; line 11 pos 0;\n'Project [concat(Cory1965:, loc#23978949) AS externalId#23978912, if (((parent_loc#23978951 = ) OR isnull(parent_loc#23978951))) else concat(Cory1965:, parent_loc#23978951) AS parentExternalId#23978913, cast(lastUpdatedTime#23978945 as string) AS name#23978914, map_from_arrays(array(key, lastUpdatedTime, WMT_SYSTEM_ID, WMT_TAG_ID, description, loc, name, parent_loc), array(cast(key#23978944 as string), cast(lastUpdatedTime#23978945 as string), cast(WMT_SYSTEM_ID#23978946 as string), cast(WMT_TAG_ID#23978947 as string), cast(description#23978948 as string), cast(loc#23978949 as string), cast(name#23978950 as string), cast(parent_loc#23978951 as string))) AS metadata#23978915, description#23978948 AS description#23978916, ('Cory1965 - 'AvevaNet) AS dataSetId#23978917]\n+- SubqueryAlias zcb2dd7b7c3704ca2a0d8659e146db2f9\n +- View (`zcb2dd7b7c3704ca2a0d8659e146db2f9`, [key#23978944,lastUpdatedTime#23978945,WMT_SYSTEM_ID#23978946,WMT_TAG_ID#23978947,description#23978948,loc#23978949,name#23978950,parent_loc#23978951])\n +- Relation [key#23978944,lastUpdatedTime#23978945,WMT_SYSTEM_ID#23978946,WMT_TAG_ID#23978947,description#23978948,loc#23978949,name#23978950,parent_loc#23978951] cognite.spark.v1.RawTableRelation@b7758cc | code: 400 | X-Request-ID: 5c83c507-0c4d-900e-8358-e014bc30ecbf"

icon

Best answer by Shashan Udawatte 5 January 2023, 10:30

View original

3 replies

Userlevel 1
Badge +1

Hi @cbodnar,

You are facing this issue because you need to use the Data set ID instead of Name. So please try to use the Data set ID of the Cory1965-AvevaNet Data set. 

Regards,
Shashan

Badge

Thank you for the response. I guess I’m confused as this was the directions and I followed them (I believe)

 

  • Create a query (or copy the query below) to fit your data into the target schema
    OBS! Remember to replace 1234567890 with your dataset id ( for the FirstnameBirthyear-AvevaNet data set) and FirstnameBirthyear with your first name and birth year.
SELECT
concat('FirstnameBirthyear:',loc) as externalId,
IF(parent_loc='' OR parent_loc IS NULL, '', concat('FirstnameBirthyear:',parent_loc)) AS parentExternalId,

CAST(lastUpdatedTime AS STRING) AS name,

to_metadata(*) AS metadata,

description AS description,

1234567890 AS dataSetId
 
FROM `FirstnameBirthyear`.assets
Badge

NVM figured it out thanks

Reply