Re: insert subquery problems

From: Ed Prochak <prochak_at_my-dejanews.com>
Date: Tue, 18 May 1999 12:47:39 GMT
Message-ID: <7hrnhb$5t2$1@nnrp1.deja.com>

I'll assume your program is in SQLPLUS (the answer is different if it's in PL/SQL). So earlier in your program you have either a DEFINE or ACCEPT statement that gives you the value of column_b_string. Then your statement would look like:

insert into table_a(column_a,column_b,column_c)   select table_b.column_a, '&column_b_string', table_c.column_c

     from table_b, table_c
     where table_b_join = table_c_join

;

NOTE: the & is how SQLPLUS recognizes you want the value of column_b_string substituted at that point, inside the quotes.

In PL/SQL, your function or procedure just provides the variable name. In this case you DO NOT USE THE QUOTES. So the PL/SQL version would like like this:

insert into table_a(column_a,column_b,column_c)   select table_b.column_a, column_b.string, table_c.column_c

     from table_b, table_c
     where table_b_join = table_c_join

;

Likely you have gotten confused by the use of the DUAL pseudotable. You only need DUAL in SQLPLUS when you are not using any other table. Also this really has nothing to do with subqueries. This same process works in straight selects of inserts also. For example:

insert into table_a(column_a,column_b,column_c)

         values('fixed name', column_b.string, 12345);

which has constant values for columns a and c, and a variable for column b.

Let me know if this helps.

In article <7hpri8$u6o$1_at_nnrp1.deja.com>,   ddreams_at_my-dejanews.com wrote:
>
>
> Hi, I'm trying to do an insertion with a subquery but some of the
> columns I want returned from the subquery are static strings that I
keep
> elsewhere in my program... Basically I want to:
>
> insert into table_a(column_a,column_b,column_c)
> select table_b.column_a, 'column_b.string', table_c.column_c
> from table_b, table_c
> where table_b_join = table_c_join
>
> Ie, I can't get "column_b.string" out of a table anywhere, it's just a
> string passed as a parameter to my program... There must be an easy
way
> to do this that I'm overlooking, so any help is appreciated. I could
> insert that static string into a temporary table, but that's quite a
> kludge for a rather simple problem.....
>
> --== Sent via Deja.com http://www.deja.com/ ==--
> ---Share what you know. Learn what you don't.---
>

--
Ed Prochak
Magic Interface, Ltd.
ORACLE services
440-498-3702

--== Sent via Deja.com http://www.deja.com/ ==-- ---Share what you know. Learn what you don't.--- Received on Tue May 18 1999 - 07:47:39 CDT