[
https://issues.apache.org/jira/browse/HIVE-27491?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
ASF GitHub Bot updated HIVE-27491:
----------------------------------
Labels: pull-request-available (was: )
> HPL/SQL does not allow variables in update statements
> -----------------------------------------------------
>
> Key: HIVE-27491
> URL: https://issues.apache.org/jira/browse/HIVE-27491
> Project: Hive
> Issue Type: Bug
> Components: hpl/sql
> Reporter: Dayakar M
> Assignee: Dayakar M
> Priority: Major
> Labels: pull-request-available
>
> HPL/SQL does not allow variables in update statements
> Works in Oracle:
> {noformat}
> DECLARE
> val_to_update varchar(10);
> BEGIN
> val_to_update := 'one';
> FOR REC in (select a,b from test1 where a = val_to_update) LOOP
> dbms_output.put_line (rec.a);
> dbms_output.put_line (rec.b);
> END LOOP;
> update test1 set b = 'another'
> where a = val_to_update;
> end;{noformat}
> Doesn't work in Hive:
> {noformat}
> DECLARE
> val_to_update STRING;
> BEGIN
> val_to_update := 'one';
> FOR REC in (select a,b from test where a = val_to_update) LOOP
> print (rec.a);
> print (rec.b);
> END LOOP;
> update test set b = 'another test'
> where a = val_to_update;
> end;
> /
> ERROR : FAILED: SemanticException [Error 10004]: Line 2:14 Invalid table
> alias or column reference 'val_to_update': (possible column names are: a, b)
> org.apache.hadoop.hive.ql.parse.SemanticException: Line 2:14 Invalid table
> alias or column reference 'val_to_update': (possible column names are: a, b)
> at
> org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.genAllExprNodeDesc(SemanticAnalyzer.java:13636)
> at
> org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.genExprNodeDesc(SemanticAnalyzer.java:13575)
> ...
> {noformat}
>
> Select (not update) does work in hive:
> {noformat}
> DECLARE
> val_to_update STRING;
> BEGIN
> val_to_update := 'one';
> FOR REC in (select a,b from test where a = val_to_update) LOOP
> print (rec.a);
> print (rec.b);
> END LOOP;
> select * from test
> where a = val_to_update;
> end;
> /{noformat}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
