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_FIRSTNAM E VARCHAR2(40)
RECEIVER_CUST_I D NUMBER(2)
RECEIVER_FIRSTN AME VARCHAR2(40)

im looking to populate table calls with
CALLER_CUST_ID = CUST_ID, CALLER_FIRSTNAM E = FirstName
and
RECEIVER_CUST_I D = CUST_ID, RECEIVER_FIRSTN AME = FIRSTNAME.

the code ive been playing around with is
[code=oracle]
INSERT INTO calls (
caller_cust_ID , caller_firstnam e,receiver_cust _ID , receiver_firstn ame)

SELECT cust_ID , firstname, cust_ID , firstname FROM
(SELECT cust_ID , firstname, cust_ID , firstname FROM custumer
ORDER BY DBMS_RANDOM.VAL UE
)
WHERE ROWNUM = 1;[/code]


Im getting an Column Ambigiguosly Defined error

[mafaisal]

Hello

Try This
[code=oracle]
INSERT INTO calls (
caller_cust_ID , caller_firstnam e,receiver_cust _ID , receiver_firstn ame)

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.VAL UE
)
WHERE ROWNUM = 1;
[/code]
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_FIRSTNAM E RECEIVER_CUST_I D RECEIVER_FIRSTN AME
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_FIRSTNAM E RECEIVER_CUST_I D RECEIVER_FIRSTN AME
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:

[code=oracle]

INSERT INTO calls (
caller_cust_ID , caller_firstnam e,receiver_cust _ID , receiver_firstn ame)
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

[/code]

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

I hope this helps!!

[niz182]

Try this:

[code=oracle]

INSERT INTO calls (
caller_cust_ID , caller_firstnam e,receiver_cust _ID , receiver_firstn ame)
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

[/code]

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