Selecting the Same Coloumn Twice

[niz182]

Hey all...first time to the site so excuse me for bein some what of a noob to how to properly ask questions.

Im looking to create a table which selects a cloumn from another table twice.

so i have a table of customers
Name Type
CUST_ID NUMBER
FIRSTNAME VARCHAR2(40)

and a table of calls
Name Type
CALLER_CUST_ID NUMBER(2)
CALLER_FIRSTNAME VARCHAR2(40)
RECEIVER_CUST_ID NUMBER(2)
RECEIVER_FIRSTNAME VARCHAR2(40)

im looking to populate table calls with
CALLER_CUST_ID = CUST_ID, CALLER_FIRSTNAME = FirstName
and
RECEIVER_CUST_ID = CUST_ID, RECEIVER_FIRSTNAME = FIRSTNAME.

the code ive been playing around with is

INSERT INTO calls (
caller_cust_ID , caller_firstname,receiver_cust_ID , receiver_firstname)
 
SELECT cust_ID , firstname, cust_ID , firstname FROM
(SELECT cust_ID , firstname, cust_ID , firstname FROM custumer
ORDER BY DBMS_RANDOM.VALUE
)
WHERE ROWNUM = 1;

Im getting an Column Ambigiguosly Defined error

[mafaisal]

Hello

Try This

INSERT INTO calls (
caller_cust_ID , caller_firstname,receiver_cust_ID , receiver_firstname)
 
SELECT cust_ID , firstname, cust_ID2 , firstname2 FROM
(SELECT cust_ID , firstname, cust_ID as cust_ID2  , firstname as firstname2 FROM custumer
ORDER BY DBMS_RANDOM.VALUE
)
WHERE ROWNUM = 1;
 

ie, Using of Alias Name

Hope this may help, if not Plz ignore

[subashsavji]

use aliase name for tables respective columns

[debasisdas]

You need to qualify the column name by table name.

[niz182]

hey guys thanks for the help, the only problem im gettign with that code is that its selecting the same data for both the caller and the reciever

CALLER_CUST_ID CALLER_FIRSTNAME RECEIVER_CUST_ID RECEIVER_FIRSTNAME
3 David 3 David

where im looking to have 2 diferent values selected.

[amitpatel66]

hey guys thanks for the help, the only problem im gettign with that code is that its selecting the same data for both the caller and the reciever

CALLER_CUST_ID CALLER_FIRSTNAME RECEIVER_CUST_ID RECEIVER_FIRSTNAME
3 David 3 David

where im looking to have 2 diferent values selected.

You mean to say you want to randomly insert two different values each for caller and receiver respectively?

[niz182]

You mean to say you want to randomly insert two different values each for caller and receiver respectively?

yeh, so it selects a caller from cust_details, then it selects a reciever from the same table.

[amitpatel66]

yeh, so it selects a caller from cust_details, then it selects a reciever from the same table.

Try this:

 
INSERT INTO calls (
caller_cust_ID , caller_firstname,receiver_cust_ID , receiver_firstname)
SELECT x.ccid , x.cfn , y.rcid , y.rfn FROM
(SELECT cust_ID ccid , firstname cfn, FROM custumer) x, (SELECT cust_id rcid, firstname rfn FROM customer) y 
WHERE x.custid != y.custid
 
 

The above query will insert many combinations of caller, receiver.

I hope this helps!!

[niz182]

Try this:

 
INSERT INTO calls (
caller_cust_ID , caller_firstname,receiver_cust_ID , receiver_firstname)
SELECT x.ccid , x.cfn , y.rcid , y.rfn FROM
(SELECT cust_ID ccid , firstname cfn, FROM custumer) x, (SELECT cust_id rcid, firstname rfn FROM customer) y 
WHERE x.custid != y.custid
 
 

The above query will insert many combinations of caller, receiver.

I hope this helps!!

when i run that script i get the following error...

WHERE x.custid != y.custid
*

ERROR at line 5:
ORA-06553: PLS-306: wrong number or types of arguments in call to 'OGC_X'

any idea what that means?

[amitpatel66]

when i run that script i get the following error...

WHERE x.custid != y.custid
*

ERROR at line 5:
ORA-06553: PLS-306: wrong number or types of arguments in call to 'OGC_X'

any idea what that means?

it should be x.ccid != x.rcid

add this condition add try to execute the query