Dayakar M created HIVE-27491:
--------------------------------
Summary: 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
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)
