SQL Expressions - Revision history

Ollvihe: /* Date functions */

2025-12-12T14:08:39Z

Date functions

← Older revision		Revision as of 14:08, 12 December 2025
Line 243:		Line 243:
	Examples:		Examples:
	<pre>		<pre>
	~~ToTimeStamp~~("2023-09-15 16:31:01", "YYYY-MM-DD HH24:MI:SS")		ToTimestamp("2023-09-15 16:31:01", "YYYY-MM-DD HH24:MI:SS")
	~~ToTimeStamp~~("01.12.2023 09:14:58.849 PM", "DD.MM.YYYY HH12:MI:SS.FF3 AM")		ToTimestamp("01.12.2023 09:14:58.849 PM", "DD.MM.YYYY HH12:MI:SS.FF3 AM")
	</pre>		</pre>
	\|-		\|-
Line 255:		Line 255:
	Example: Successful date conversion:		Example: Successful date conversion:
	<pre>		<pre>
	~~TryToTimeStamp~~("2023-09-15 16:31:01", "YYYY-MM-DD HH24:MI:SS")		TryToTimestamp("2023-09-15 16:31:01", "YYYY-MM-DD HH24:MI:SS")
	</pre>		</pre>

	Example: Returns null because formatting doesn't match with the data:		Example: Returns null because formatting doesn't match with the data:
	<pre>		<pre>
	~~TryToTimeStamp~~("2023-09-15", "DD.MM.YYYY")		TryToTimestamp("2023-09-15", "DD.MM.YYYY")
	</pre>		</pre>
	\|-		\|-

Ollvihe: /* Window functions */

2025-11-14T12:56:00Z

Window functions

← Older revision		Revision as of 12:56, 14 November 2025
Line 683:		Line 683:
	\|-		\|-
	\|\|Sum		\|\|Sum
	\|\|Calculate sum of values within given window~~. Returns the minimum value within an ordered group of values configured using window parameters~~.		\|\|Calculate sum of values within given window.

	Parameters:		Parameters:

Ollvihe: /* Window functions */

2025-08-30T15:44:34Z

Window functions

← Older revision		Revision as of 15:44, 30 August 2025
Line 694:		Line 694:
	Example: Calculates sum of the values of Revenue column within a cumulative window from the current row to the end where window is partitioned by Id column and rows ordered by Year column:		Example: Calculates sum of the values of Revenue column within a cumulative window from the current row to the end where window is partitioned by Id column and rows ordered by Year column:
	<pre>		<pre>
	Sum(Column("Revenue"), [Column("Year")], null, [Column("Id")], #{"~~Sliding~~": #{ "UseRange": false, "Following": true }})		Sum(Column("Revenue"), [Column("Year")], null, [Column("Id")], #{"Cumulative": #{ "UseRange": false, "Following": true }})
	</pre>		</pre>
	\|}		\|}

Ollvihe: /* String functions */

2025-03-21T13:46:37Z

String functions

← Older revision		Revision as of 13:46, 21 March 2025
Line 401:		Line 401:
	# '''Subject''': The string in which to do the replacements.		# '''Subject''': The string in which to do the replacements.
	# '''Pattern''': Substring to find in subject.		# '''Pattern''': Substring to find in subject.
	# '''Replacement''': ~~The value~~ used as a replacement for the pattern. If null or empty all occurrences of pattern will be removed from subject.		# '''Replacement''': Substring used as a replacement for the pattern. If ''null'' or empty, all occurrences of pattern will be removed from subject.

	Example:		Example:

Ollvihe: /* String functions */

2025-03-21T13:43:09Z

String functions

← Older revision		Revision as of 13:43, 21 March 2025
Line 399:		Line 399:
	\|\|Replace		\|\|Replace
	\|\|Removes all occurrences of a specified substring and replaces them with another substring. Parameters:		\|\|Removes all occurrences of a specified substring and replaces them with another substring. Parameters:
	# Subject: The string in which to do the replacements.		# '''Subject''': The string in which to do the replacements.
	# Pattern: Substring to find in subject.		# '''Pattern''': Substring to find in subject.
	# Replacement: The value used as a replacement for the pattern. If null or empty all occurrences of pattern will be removed from subject.		# '''Replacement''': The value used as a replacement for the pattern. If null or empty all occurrences of pattern will be removed from subject.

	Example:		Example:

Ollvihe: /* String functions */

2025-03-21T13:42:30Z

String functions

@@ Line 396: / Line 396: @@
 Returns: false
 </pre>
 |-
 ||Substring

Ollvihe at 16:21, 18 January 2025

2025-01-18T16:21:53Z

← Older revision		Revision as of 16:21, 18 January 2025
Line 1,031:		Line 1,031:
	</pre>		</pre>
	\|-		\|-
	\|\|LLMComplete		\|\|<span id="LLMComplete">LLMComplete</span>
	\|\|		\|\|
	Generates a response to the given prompt using a large language model (LLM). This function uses the Snowflake's SNOWFLAKE.CORTEX.COMPLETE function (https://docs.snowflake.com/en/sql-reference/functions/complete-snowflake-cortex). Parameters can be given either as a short syntax (model name + prompt as string), or a long syntax (model name + prompt as array + options).		Generates a response to the given prompt using a large language model (LLM). This function uses the Snowflake's SNOWFLAKE.CORTEX.COMPLETE function (https://docs.snowflake.com/en/sql-reference/functions/complete-snowflake-cortex). Parameters can be given either as a short syntax (model name + prompt as string), or a long syntax (model name + prompt as array + options).

Ollvihe: /* Other functions */

2025-01-07T08:43:59Z

Other functions

← Older revision		Revision as of 08:43, 7 January 2025
Line 1,038:		Line 1,038:
	# '''LLM name''' (string): Name of the large language model. See Snowflake documentation for available models. Note that not all models are available in all Snowflake regions.		# '''LLM name''' (string): Name of the large language model. See Snowflake documentation for available models. Note that not all models are available in all Snowflake regions.
	# '''Prompt''' (string or array): Prompt as a string (short syntax), or the entire conversation history as an array (long syntax).		# '''Prompt''' (string or array): Prompt as a string (short syntax), or the entire conversation history as an array (long syntax).
	# '''Options''' (dictionary): Parameters for the LLM, such as ''temperature'' ~~and~~ ''top_p'', ''max_tokens'', and ''guardrails''. See the Snowflake documentation for all available parameters.		# '''Options''' (dictionary): Parameters for the LLM, such as ''temperature'', ''top_p'', ''max_tokens'', and ''guardrails''. Options are only given when using the long syntax. See the Snowflake documentation for all available parameters.

	Return value type depends on whether the short or long syntax has been used. For the short syntax, return value is a string. For the long syntax, return value is an object containing the response and related details.		Return value type depends on whether the short or long syntax has been used. For the short syntax, return value is a string. For the long syntax, return value is an object containing the response and related details.

Ollvihe: /* Other functions */

2025-01-07T08:40:35Z

Other functions

← Older revision		Revision as of 08:40, 7 January 2025
Line 1,033:		Line 1,033:
	\|\|LLMComplete		\|\|LLMComplete
	\|\|		\|\|
	Generates a response to the given prompt using a large language model (LLM). This function uses the Snowflake's SNOWFLAKE.CORTEX.COMPLETE function (https://docs.snowflake.com/en/sql-reference/functions/complete-snowflake-cortex). Parameters can be given either as a short ~~version~~ (model name + prompt as string) or a long ~~version~~ (model name + prompt as array + options).		Generates a response to the given prompt using a large language model (LLM). This function uses the Snowflake's SNOWFLAKE.CORTEX.COMPLETE function (https://docs.snowflake.com/en/sql-reference/functions/complete-snowflake-cortex). Parameters can be given either as a short syntax (model name + prompt as string), or a long syntax (model name + prompt as array + options).

	Parameters:		Parameters:
	# '''LLM name''' (string): Name of the large language model ~~to use~~. See Snowflake documentation for available models. Note that not all models are available in all Snowflake regions.		# '''LLM name''' (string): Name of the large language model. See Snowflake documentation for available models. Note that not all models are available in all Snowflake regions.
	# '''Prompt''' (string or array): Prompt as a string, or the entire conversation history as an array.		# '''Prompt''' (string or array): Prompt as a string (short syntax), or the entire conversation history as an array (long syntax).
	# '''Options''' (dictionary): ~~Dictionary of parameters~~ for the LLM. See the Snowflake documentation ~~about the~~ available parameters.		# '''Options''' (dictionary): Parameters for the LLM, such as ''temperature'' and ''top_p'', ''max_tokens'', and ''guardrails''. See the Snowflake documentation for all available parameters.

	~~Result~~ type depends on whether ~~options parameter~~ has been ~~specified or not~~. ~~If options has been specified~~, the ~~returns~~ an object containing the response and related details. ~~Otherwise, returns the generated string.~~		Return value type depends on whether the short or long syntax has been used. For the short syntax, return value is a string. For the long syntax, return value is an object containing the response and related details.
	Example:
			Example (short syntax):
	<pre>		<pre>
	LlmComplete(		LlmComplete(
Line 1,048:		Line 1,049:
	)		)
	</pre>		</pre>

			Example (long syntax):
	<pre>		<pre>
	Cast(		Cast(
Line 1,066:		Line 1,069:
	}		}
	)["choices"][0]["messages"],		)["choices"][0]["messages"],
	"string		"string"
	)		)
	</pre>		</pre>
			The above example also shows how the extract the text from the response object.
	\|-		\|-
	\|\|Variable		\|\|Variable

Ollvihe: /* Other functions */

2025-01-07T08:32:51Z

Other functions

← Older revision		Revision as of 08:32, 7 January 2025
Line 1,049:		Line 1,049:
	</pre>		</pre>
	<pre>		<pre>
	LlmComplete(		Cast(
	"snowflake-arctic",		LlmComplete(
	[		"snowflake-arctic",
			[
			#{
			"role": "system",
			"content": "Explain the given product group."
			},
			#{
			"role": "user",
			"content": Column("Product Group")
			}
			],
	#{		#{
	"~~role~~": ~~"system",~~		"temperature": 0
	~~"content": "Explain the given product group."~~
	},
	#{
	~~"role": "user",~~
	~~"content": Column("Product Group")~~
	}		}
	],		)["choices"][0]["messages"],
	#{		"string
	~~"temperature": 0~~		)
	}
	)["choices"][0]["messages"]
	</pre>		</pre>
	\|-		\|-