# What is the best way to load from a source table based on a colname - anything greater than a maxvalue of that (\ in the destination table). I tried using query result to scalar- but the variable value is not being set with the max value **URL:** \ **Category:** Matillion ETL\ **Created:** [August 17, 2020, 8:36pm UTC](https://matillioncommunity.discourse.group/t/what-is-the-best-way-to-load-from-a-source-table-based-on-a-colname-anything-greater-than-a-maxvalue-of-that-colname-in-the-destination-table-i-tried-using-query-result-to-scalar-but-the-variable-value-is-not-being-set-with-the-max-value/1164 "2020-08-17T20:36:01Z")\ **Posts on this page:** 12\ **Page:** 1
**Author:** ![VVeda](https://sea2.discourse-cdn.com/flex002/user_avatar/matillioncommunity.discourse.group/vveda/32/834_2.png) [@VVeda](https://matillioncommunity.discourse.group/u/VVeda)\ **Post date:** [August 17, 2020, 8:36pm UTC](https://matillioncommunity.discourse.group/t/what-is-the-best-way-to-load-from-a-source-table-based-on-a-colname-anything-greater-than-a-maxvalue-of-that-colname-in-the-destination-table-i-tried-using-query-result-to-scalar-but-the-variable-value-is-not-being-set-with-the-max-value/1164/1 "2020-08-17T20:36:01Z")
What is the best way to load from a source table based on a colname - anything greater than a maxvalue of that ( in the destination table). I tried using query result to scalar- but the variable value is not being set with the max value. ---
**Author:** ![Joe-Community-Manager](https://sea2.discourse-cdn.com/flex002/user_avatar/matillioncommunity.discourse.group/joe-community-manager/32/897_2.png) [@Joe-Community-Manager](https://matillioncommunity.discourse.group/u/Joe-Community-Manager)\ **Post date:** [August 19, 2020, 8:10am UTC](https://matillioncommunity.discourse.group/t/what-is-the-best-way-to-load-from-a-source-table-based-on-a-colname-anything-greater-than-a-maxvalue-of-that-colname-in-the-destination-table-i-tried-using-query-result-to-scalar-but-the-variable-value-is-not-being-set-with-the-max-value/1164/2 "2020-08-19T08:10:49Z")
Hey VVeda,   It seems the community are unable to answer this query, fear not we can ask our amazing solution architects 😀 If you would like me to raise a support ticket on your behalf please let me know and I can arrange that for you. Thank you for your post.   Kind Regards, Joe ---
**Author:** ![MichaelBoucht](https://avatars.discourse-cdn.com/v4/letter/m/3da27b/32.png) [@MichaelBoucht](https://matillioncommunity.discourse.group/u/MichaelBoucht)\ **Post date:** [August 26, 2020, 6:08am UTC](https://matillioncommunity.discourse.group/t/what-is-the-best-way-to-load-from-a-source-table-based-on-a-colname-anything-greater-than-a-maxvalue-of-that-colname-in-the-destination-table-i-tried-using-query-result-to-scalar-but-the-variable-value-is-not-being-set-with-the-max-value/1164/3 "2020-08-26T06:08:28Z")
Hello, I tried this out and this worked fine for me. I'm also on Snowflake, and I did the following:   Orchestration Job with Job Variable 'Parameter1'. Use Query Result to Scalar -\>   select 'Testing from Target' as Col1, 'ValueForParameter' as Col2 from dual   Assigned Col2 for Parameter1.     Next step, Database Query, as an source I used Oracle Database. I made this dummy query,   select 'Testing' as Col1, '${Parameter1}' as ProbableWhereclause FRom dual   and pointed it to my snowflake staging area with a appropriate name.   Result: COL1  PROBABLEWHERECLAUSE  ------- -------------------  Testing ValueForParameter       So, is this what you tried?   ---
**Author:** ![VVeda](https://sea2.discourse-cdn.com/flex002/user_avatar/matillioncommunity.discourse.group/vveda/32/834_2.png) [@VVeda](https://matillioncommunity.discourse.group/u/VVeda)\ **Post date:** [August 26, 2020, 7:54pm UTC](https://matillioncommunity.discourse.group/t/what-is-the-best-way-to-load-from-a-source-table-based-on-a-colname-anything-greater-than-a-maxvalue-of-that-colname-in-the-destination-table-i-tried-using-query-result-to-scalar-but-the-variable-value-is-not-being-set-with-the-max-value/1164/4 "2020-08-26T19:54:56Z")
I tried something similar, I'm also connected to a Oracle Database. Only difference is I did not use a staging table. In the Database query component my sql query had the where condition to check anything \> '${parameter}' and loaded it directly into the destination. It worked fine. Thanks Vani ---
**Author:** ![VVeda](https://sea2.discourse-cdn.com/flex002/user_avatar/matillioncommunity.discourse.group/vveda/32/834_2.png) [@VVeda](https://matillioncommunity.discourse.group/u/VVeda)\ **Post date:** [August 19, 2020, 1:11pm UTC](https://matillioncommunity.discourse.group/t/what-is-the-best-way-to-load-from-a-source-table-based-on-a-colname-anything-greater-than-a-maxvalue-of-that-colname-in-the-destination-table-i-tried-using-query-result-to-scalar-but-the-variable-value-is-not-being-set-with-the-max-value/1164/5 "2020-08-19T13:11:07Z")
Hi Joe, I do have a support ticket open for this, however if you'd like to open another one please feel free. They replied that variable value is using the default-value to build the SQL and so to change the SQL to use a constant instead of the variable. If this gives the correct result, then its the default for the job-variable which is causing this issue. Are they saying I should not use a default value, not sure. They suggested I review the Snowflake history to see what queries are being fired from matillion - I need to do that today and check. I'll let you know. thanks Vani ---
**Author:** ![MichaelBoucht](https://avatars.discourse-cdn.com/v4/letter/m/3da27b/32.png) [@MichaelBoucht](https://matillioncommunity.discourse.group/u/MichaelBoucht)\ **Post date:** [August 27, 2020, 8:54am UTC](https://matillioncommunity.discourse.group/t/what-is-the-best-way-to-load-from-a-source-table-based-on-a-colname-anything-greater-than-a-maxvalue-of-that-colname-in-the-destination-table-i-tried-using-query-result-to-scalar-but-the-variable-value-is-not-being-set-with-the-max-value/1164/6 "2020-08-27T08:54:57Z")
Ah, I thought you were doing some incremental loading and had already loaded the data which you acquire the 'max' value from.   Good that you got it working! -Michael ---
**Author:** ![Joe-Community-Manager](https://sea2.discourse-cdn.com/flex002/user_avatar/matillioncommunity.discourse.group/joe-community-manager/32/897_2.png) [@Joe-Community-Manager](https://matillioncommunity.discourse.group/u/Joe-Community-Manager)\ **Post date:** [August 19, 2020, 1:39pm UTC](https://matillioncommunity.discourse.group/t/what-is-the-best-way-to-load-from-a-source-table-based-on-a-colname-anything-greater-than-a-maxvalue-of-that-colname-in-the-destination-table-i-tried-using-query-result-to-scalar-but-the-variable-value-is-not-being-set-with-the-max-value/1164/7 "2020-08-19T13:39:46Z")
Thats brilliant [@VVeda](https://matillioncommunity.discourse.group/u/VVeda)​ Please keep us posted on how you get on, I am looking forward to hearing about it. Thanks Joe ---
**Author:** ![VVeda](https://sea2.discourse-cdn.com/flex002/user_avatar/matillioncommunity.discourse.group/vveda/32/834_2.png) [@VVeda](https://matillioncommunity.discourse.group/u/VVeda)\ **Post date:** [August 20, 2020, 9:07pm UTC](https://matillioncommunity.discourse.group/t/what-is-the-best-way-to-load-from-a-source-table-based-on-a-colname-anything-greater-than-a-maxvalue-of-that-colname-in-the-destination-table-i-tried-using-query-result-to-scalar-but-the-variable-value-is-not-being-set-with-the-max-value/1164/8 "2020-08-20T21:07:13Z")
Hey Joe, This was the response I got from Matillion Support - _Its possible the sample button is using the default-value in the variable to build the SQL. Change the SQL to use a constant instead of the variable. If this gives the correct result, then its the default for your job-variable which is causing this issue._   I noticed that the default value for the variable can be set after validating the components ( Query result to Scalar) and the other components in the Job ( DatabaseQuery Component). Once the validation is done then you can set a default value and then run each component - you should be now able to see the correct Sample Result set when you run your Query component. It worked for me.   thanks Vani ---
**Author:** ![SM392357](https://avatars.discourse-cdn.com/v4/letter/s/b9bd4f/32.png) [@SM392357](https://matillioncommunity.discourse.group/u/SM392357)\ **Post date:** [September 29, 2020, 4:05am UTC](https://matillioncommunity.discourse.group/t/what-is-the-best-way-to-load-from-a-source-table-based-on-a-colname-anything-greater-than-a-maxvalue-of-that-colname-in-the-destination-table-i-tried-using-query-result-to-scalar-but-the-variable-value-is-not-being-set-with-the-max-value/1164/9 "2020-09-29T04:05:34Z")
I do have the same question here. I was trying to do an incremental load and I need to get the max value from the destination table then I have to increment the value by one for every new record. Can you suggest the best way of doing it. ---
**Author:** ![VVeda](https://sea2.discourse-cdn.com/flex002/user_avatar/matillioncommunity.discourse.group/vveda/32/834_2.png) [@VVeda](https://matillioncommunity.discourse.group/u/VVeda)\ **Post date:** [September 29, 2020, 3:15pm UTC](https://matillioncommunity.discourse.group/t/what-is-the-best-way-to-load-from-a-source-table-based-on-a-colname-anything-greater-than-a-maxvalue-of-that-colname-in-the-destination-table-i-tried-using-query-result-to-scalar-but-the-variable-value-is-not-being-set-with-the-max-value/1164/10 "2020-09-29T15:15:38Z")
Try the below: Also remember to validate each component before you actually run the entire Orchestration job. I noticed that by doing so, the variable name will be set correctly.   You can define a Job variable that holds the max value of the column from your destination table. Use a Query to Scalar Component . Enter the SQL Query (select max(column\_1) from your destination table in Snowflake\>) & Scalar Variable Mapping ( map the Job variable to the max(column\_1) Then, Use a Database Query: Select \* from \ where column\_1 = '${job\_var}' +1;   ---
**Author:** ![SM392357](https://avatars.discourse-cdn.com/v4/letter/s/b9bd4f/32.png) [@SM392357](https://matillioncommunity.discourse.group/u/SM392357)\ **Post date:** [September 30, 2020, 3:17am UTC](https://matillioncommunity.discourse.group/t/what-is-the-best-way-to-load-from-a-source-table-based-on-a-colname-anything-greater-than-a-maxvalue-of-that-colname-in-the-destination-table-i-tried-using-query-result-to-scalar-but-the-variable-value-is-not-being-set-with-the-max-value/1164/11 "2020-09-30T03:17:30Z")
Sorry I couldn't mention, I'm using synapse. What could be the alternate option for Scalar Component? ---
**Author:** ![MichaelBoucht](https://avatars.discourse-cdn.com/v4/letter/m/3da27b/32.png) [@MichaelBoucht](https://matillioncommunity.discourse.group/u/MichaelBoucht)\ **Post date:** [October 8, 2020, 7:04am UTC](https://matillioncommunity.discourse.group/t/what-is-the-best-way-to-load-from-a-source-table-based-on-a-colname-anything-greater-than-a-maxvalue-of-that-colname-in-the-destination-table-i-tried-using-query-result-to-scalar-but-the-variable-value-is-not-being-set-with-the-max-value/1164/12 "2020-10-08T07:04:47Z")
'Query result To Scalar' should be available also in Synapse version of Matillion, due it's an Orchestration component. https://documentation.matillion.com/docs/en/2978362