CODEWITHSUNDEEP
sqloracle-database
HiDayurie Dave
HiDayurie Dave

Reputation: 1807

Oracle query data with same id column name on different table

I tried to run my Oracle data query with below code:

SELECT
    S.FORM_NO,
    S.ARTICLEID_FK AS S_ARTICLEID_FK,
    S.BOX_SERIAL_NO,
    S.ACTUAL_WEIGHT,
    TO_CHAR(S.DATEADDED, 'DD-MON-YYYY HH24:MI:SS') AS DATEADDEDS,
    S.ADDEDBY,
    B.ARTICLEID_FK AS B_ARTICLEID_FK,
    B.DATE_CODE
FROM
    WA_LA_TBL_ARTICLES_SCAN S,
    WA_LA_TBL_ARTICLES_BOX_SN B
WHERE
    S.S_ARTICLEID_FK = B.B_ARTICLEID_FK

And now getting error below:

Warning: oci_execute(): ORA-00904: "S"."S_ARTICLEID_FK": invalid identifier in...

I suspect maybe from this side: S.ARTICLEID_FK and B.ARTICLEID_FK

but I don't know how to solve the query.

Upvotes: 0

Views: 119

Answers (3)

virtualdvid
virtualdvid

Reputation: 2421

Try:

WHERE S.ARTICLEID_FK = B.ARTICLEID_FK

It seems you are trying to use the new fields S.S_ARTICLEID_FK = B.B_ARTICLEID_FK as ID for the join and that is the problem...

Upvotes: 1

saravanatn
saravanatn

Reputation: 630

SELECT
    S.FORM_NO,
    S.ARTICLEID_FK AS S_ARTICLEID_FK,
    S.BOX_SERIAL_NO,
    S.ACTUAL_WEIGHT,
    TO_CHAR(S.DATEADDED, 'DD-MON-YYYY HH24:MI:SS') AS DATEADDEDS,
    S.ADDEDBY,
    B.ARTICLEID_FK AS B_ARTICLEID_FK,
    B.DATE_CODE
FROM
    WA_LA_TBL_ARTICLES_SCAN S,
    WA_LA_TBL_ARTICLES_BOX_SN B
WHERE
    S.ARTICLEID_FK = B.ARTICLEID_FK

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1271241

Learn to use proper JOIN syntax. Then, you cannot use a column alias in either the ON or the WHERE:

SELECT . . .
FROM WA_LA_TBL_ARTICLES_SCAN S JOIN
     WA_LA_TBL_ARTICLES_BOX_SN B
     ON S.ARTICLEID_FK = B.ARTICLEID_FK;

Upvotes: 0

Related Questions