How to use query with nested struct function | Adobe Higher Education
Skip to main content
A
akanshamishra1
Level 2
December 21, 2023
Beantwoord

How to use query with nested struct function

  • December 21, 2023
  • 2 reacties
  • 1225 Bekeken

I am facing error with this query while trying to use nested struct functions

ErrorCode: 58000 Batch query execution gets : [failed reason ErrorCode: 58000 Batch query execution gets : [Errors(Some(INGEST-1205-400),Some(List()),Some(The field: _XYZ does not exist in the target schema. Remove the field and try again.))]]

 

INSERT INTO recently_viewed_products
SELECT STRUCT(THIRD_ID,struct(struct(struct(PROD_ID as PROD_ID)PRODUCTDETAIL)PRODUCTS)COMMERCE)_XYZ,
PAGE_VIEWS_VALUE,
LINK_CLICKS_VALUE,
timestamp,
POIEXITS_VALUE,
POIENTRIES_VALUE,
_id
FROM (
SELECT *
FROM (
SELECT _XYZ.THIRD_ID THIRD_ID,
_XYZ.COMMERCE.PRODUCTS.PRODUCTDETAIL.PROD_ID PROD_ID,
web.webPageDetails.pageViews.value PAGE_VIEWS_VALUE,
web.webInteraction.linkClicks.value LINK_CLICKS_VALUE,
timestamp,
placeContext.POIinteraction.poiExits.value POIEXITS_VALUE,
placeContext.POIinteraction.poiEntries.value POIENTRIES_VALUE,
_id,
ROW_NUMBER() OVER (PARTITION BY _XYZ.THIRD_ID ORDER BY TIMESTAMP DESC) RW_NUM
FROM web_data
) AS subquery
WHERE RW_NUM < 6
);

    Er kunnen geen reacties meer worden geplaatst op dit onderwerp.
    Beste antwoord door abhinavbalooni

    Hey @akanshamishra1 

     

    What does your schema look like ? The error mentions that fields you are trying to access aren't available. First step would be to check what the schema structure looks like and also what is the purpose of the above query ? There can be multiple possibilities for a struct to fail.

     

    Cheers,

    Abhinav

    2 reacties

    abhinavbalooni
    Community Advisor
    abhinavbalooniCommunity AdvisorAntwoord
    Community Advisor
    December 21, 2023

    Hey @akanshamishra1 

     

    What does your schema look like ? The error mentions that fields you are trying to access aren't available. First step would be to check what the schema structure looks like and also what is the purpose of the above query ? There can be multiple possibilities for a struct to fail.

     

    Cheers,

    Abhinav

      A
      akanshamishra1Auteur
      Level 2
      December 21, 2023

      Hi @abhinavbalooni 
      we are looking for _XYZ.COMMERCE.PRODUCTS.PRODUCTDETAIL.PROD_ID, _XYZ_THIRD_ID from the schema and want to insert this data field in the dataset.
      Schema is for web events data

        NimashaJain
        Adobe Employee
        NimashaJainAdobe Employee
        Adobe Employee
        January 16, 2024

        Did you find the suggested solutions helpful? It would be great if you can  mark the answer as correct for posterity. If you have found out solution yourself, share it with wider audience in the community.

        ForumvoorwaardenAccessibility statement

        Loading footer...