Forum Discussion

darshthakkar's avatar
darshthakkar
Valued Contributor
4 years ago
Solved

Fetching 1k records from table using "Snowflake select snap"

I’m planning to fetch the data from a table using “Snowflake select snap” while putting the condition of getting 1k records ONLY in WHERE clause however it is not getting any results.

I can put the entire query in “Snowflake execute snap” as well but wanting to do with Snowflake Select as of now. Any help is highly appreciable.

Thanks!

  • darshthakkar's avatar
    darshthakkar
    4 years ago

    Wanted to share the solution here. The query was right, no need to change the “Fetch-size” as well.
    The only thing that was missing was ’ ’ for the IDs that we were passing in the query. Thus, the query would look like -
    WHERE NAME IN (‘102345’, ‘1701’, ‘5604878’)

27 Replies

    • darshthakkar's avatar
      darshthakkar
      Valued Contributor
      4 years ago

      Does that mean, I would never be able to get 1k records from the Snowflake DB?
      Today, the requirement is to fetch 1k records, tomorrow it may be 5k, 10k, so on and so forth.

      I’m actually putting those IDs in the WHERE clause so it might exceed the character limit of 255 characters.

    • darshthakkar's avatar
      darshthakkar
      Valued Contributor
      4 years ago

      I tried limiting it but I want all those 1k records, am I doing the right thing by putting 1k IDs in the WHERE clause of Snowflake Select snap?

      Still brand new to snapLogic so I’m sure there would be something where I’m making a mistake.

      • bojanvelevski's avatar
        bojanvelevski
        Valued Contributor
        4 years ago

        You have a particular set of IDs you want to process, and that set counts 1000 records? Or you just want the first 1000 from the table?

  • darshthakkar's avatar
    darshthakkar
    Valued Contributor
    4 years ago

    Wanted to share the solution here. The query was right, no need to change the “Fetch-size” as well.
    The only thing that was missing was ’ ’ for the IDs that we were passing in the query. Thus, the query would look like -
    WHERE NAME IN (‘102345’, ‘1701’, ‘5604878’)

    • akidave's avatar
      akidave
      Employee
      4 years ago

      The Snowflake Lookup snap covers this functionality. That will allow lookup on any number of ids, it will internally batch as required. Also, Lookup snap will use bind parameters, avoiding possible issues with SQL Injection which can happen with a SQL statement string constructed in the Select snap.

  • darshthakkar's avatar
    darshthakkar
    Valued Contributor
    4 years ago

    Getting the same error even though reducing the IDs in where clause to 6:

    Also the query has been limited to 10 records too.

    • alchemiz's avatar
      alchemiz
      Contributor III
      4 years ago

      Hi Sir Darsh,

      Good day, hope is all well.

      Seems like a setting from the account that you are using in the Snowflake execute snap where a fetch size is a required setting. Hope this help

      Thanks,
      EmEm

      • darshthakkar's avatar
        darshthakkar
        Valued Contributor
        4 years ago

        Thank you @alchemiz, I did check the account settings and fetch size is mandatory as you mentioned.
        Can you walk me through on how to resolve this?

        Do we need to ensure that Fetch size is non-mandatory or increase from 100 to some x number, if that’s the case, what would be that x number?
        Thank you in advance…