[teiid-issues] [JBoss JIRA] (TEIID-5403) Support the use of local variables in client SQL query
SHI HONG CHIN (JIRA)
issues at jboss.org
Thu Jun 28 22:32:00 EDT 2018
[ https://issues.jboss.org/browse/TEIID-5403?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
SHI HONG CHIN updated TEIID-5403:
---------------------------------
Description:
In Microsoft SQL Server, when writing SQL query, I can directly declare and set local variables, and then use it as much as I like in the rest of SQL query.
Example:
{code:java}
DECLARE @PARAM1 VARCHAR(100);
SET @PARAM1 = ?;
SELECT
<SOME COLUMNS>
FROM TABLEA LEFT OUTER JOIN (
SELECT
<SOME COLUMNS>
FROM TABLEB
WHERE COLUMN1 = @PARAM1
) T1 ON T1.COLUMN2 = TABLEA.COLUMN3
WHERE TABLEA.COLUMN4 = @PARAM1
UNION ALL
SELECT
<SOME COLUMNS>
FROM TABLEC
WHERE TABLEC.COLUMN5 = @PARAM1;
{code}
In above example, "@PARAM1" is the local variable that been used multiple times in the query.
The benefits:
1. In programming, I only need to set the parameter for one time. For example, when using Java PreparedStatement's setString(int parameterIndex, String x) function, I only need to write "statement.setString(1, param1);" instead of writing "statement.setString(1, param1); statement.setString(2, param1); statement.setString(3, param1); ....." to set the same value.
2. I no need to create a procedure.
However, in Teiid, I cannot find anyway to achieve this.
In Teiid, please implement the support of local variables in client SQL query.
was:
In Microsoft SQL Server, when writing SQL query, I can directly declare and set local variables, and then use it as much as I like in the rest of SQL query.
Example:
{code:java}
DECLARE @PARAM1 VARCHAR(100);
SET @PARAM1 = ?;
SELECT
<SOME COLUMNS>
FROM TABLEA LEFT OUTER JOIN (
SELECT
<SOME COLUMNS>
FROM TABLEB
WHERE COLUMN1 = @PARAM1
) T1
WHERE TABLEA.COLUMN2 = @PARAM1
UNION ALL
SELECT
<SOME COLUMNS>
FROM TABLEC
WHERE TABLEC.COLUMN3 = @PARAM1;
{code}
In above example, "@PARAM1" is the local variable that been used multiple times in the query.
The benefits:
1. In programming, I only need to set the parameter for one time. For example, when using Java PreparedStatement's setString(int parameterIndex, String x) function, I only need to write "statement.setString(1, param1);" instead of writing "statement.setString(1, param1); statement.setString(2, param1); statement.setString(3, param1); ....." to set the same value.
2. I no need to create a procedure.
However, in Teiid, I cannot find anyway to achieve this.
In Teiid, please implement the support of local variables in client SQL query.
> Support the use of local variables in client SQL query
> ------------------------------------------------------
>
> Key: TEIID-5403
> URL: https://issues.jboss.org/browse/TEIID-5403
> Project: Teiid
> Issue Type: Enhancement
> Reporter: SHI HONG CHIN
> Assignee: Steven Hawkins
>
> In Microsoft SQL Server, when writing SQL query, I can directly declare and set local variables, and then use it as much as I like in the rest of SQL query.
> Example:
> {code:java}
> DECLARE @PARAM1 VARCHAR(100);
> SET @PARAM1 = ?;
> SELECT
> <SOME COLUMNS>
> FROM TABLEA LEFT OUTER JOIN (
> SELECT
> <SOME COLUMNS>
> FROM TABLEB
> WHERE COLUMN1 = @PARAM1
> ) T1 ON T1.COLUMN2 = TABLEA.COLUMN3
> WHERE TABLEA.COLUMN4 = @PARAM1
> UNION ALL
> SELECT
> <SOME COLUMNS>
> FROM TABLEC
> WHERE TABLEC.COLUMN5 = @PARAM1;
> {code}
> In above example, "@PARAM1" is the local variable that been used multiple times in the query.
> The benefits:
> 1. In programming, I only need to set the parameter for one time. For example, when using Java PreparedStatement's setString(int parameterIndex, String x) function, I only need to write "statement.setString(1, param1);" instead of writing "statement.setString(1, param1); statement.setString(2, param1); statement.setString(3, param1); ....." to set the same value.
> 2. I no need to create a procedure.
> However, in Teiid, I cannot find anyway to achieve this.
> In Teiid, please implement the support of local variables in client SQL query.
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
More information about the teiid-issues
mailing list