0

The code is as below:

cursor c1 is
select x, y, a.z, b.z "a1" from t3 a, t4 b;

And below fetching the data from c1 as 

insert into t1 (x, y, z, z1) values (c1.x, c1.y, c1.z, c1.a1);

After compilation, I am getting errors as component a1 is not declared. and column not allowed here. New to PLSQL, as I didn't think that aliases need to be declared.

jarlh
  • 42,561
  • 8
  • 45
  • 63
krishb591993
  • 193
  • 1
  • 5
  • 16

2 Answers2

5

When you put an identifier in double-quotes, it becomes a case-sensitive identifier. You would need to refer to it enclosed in double quotes and with the proper casing going forward.

insert into t1 (x, y, z, z1) 
  values (c1.x, c1.y, c1.z, c1."a1");

should work. However, creating case-sensitive identifiers in the first place is generally a really bad idea. It is terribly annoying to have to type a bunch of double quotes every time you want to reference a column name. And having some identifiers case sensitive and others case insensitive is going to drive whoever has to support your code absolutely batty. It would make much more sense to use a normal case insensitive alias

cursor c1 is
  select x, y, a.z, b.z a1
    from t3 a, t4 b;

As an aside, are you really trying to generate a Cartesian product between t3 and t4? That is generally a very bad idea...

Community
  • 1
  • 1
Justin Cave
  • 227,342
  • 24
  • 367
  • 384
  • Thanks for the answer Justin. It worked. I will go for the case insensitive one and got to learn a new thing by the way.But, I am not trying to generate a Cartesian product between t3 and t4. The code was already written and the data generated is useful for other operations. – krishb591993 Sep 14 '16 at 07:48
  • 3
    @krishb591993 - The code you posted generates a Cartesian product between the two tables. If that is not what you intended, I would look seriously at the code to figure out what the join condition was supposed to be. If you do intend to create a Cartesian product, I would at least use a `cross join` operator to make it clear to the next person that this was intentional and not the (much more common) situation where someone botched a join accidentally. – Justin Cave Sep 14 '16 at 07:51
0

Your question is not that clear; if you need to copy data from a table to another, you can simply use:

insert into t1 (x,y,z,z1) select x,y,a.z,b.z "a1" from t3 a, t4 b;
Aleksej
  • 22,443
  • 5
  • 33
  • 38
  • I cannot do that because, its a huge code and I need to first retrieve the data in the first query and then insert it into the table. My question here is about the use of aliases. – krishb591993 Sep 14 '16 at 07:43