Reputation: 281
SELECT
tbl1.PYU_EMAIL,
COUNT(tbl1.PYU_EMAIL) as TOTAL,
( SELECT ROWCONCAT( 'SELECT pyu_id FROM p_survey_invite WHERE pyu_email=' || tbl1.pyu_email ) FROM dual )
FROM p_survey_invite tbl1
GROUP BY tbl1.pyu_id, tbl1.pyu_email
ORDER BY total DESC;
hi all,
I keep getting ORA-04054: database link DIGITALVIDEOSYSTEMS.NET does not exist error
I suspect that when I concat the tbl1.pyu_email within the rowconcat function, the tbl1.pyu_email has @ char on it, which reflect to DB-LINK on oracle.
How do I tell oracle not to think @ is db link?
Upvotes: 1
Views: 487
Reputation: 7803
You should put quotes around your e-mail address:
SELECT
tbl1.PYU_EMAIL,
COUNT(tbl1.PYU_EMAIL) as TOTAL,
( SELECT ROWCONCAT( 'SELECT pyu_id FROM p_survey_invite WHERE pyu_email=''' || tbl1.pyu_email ||'''') FROM dual )
FROM p_survey_invite tbl1
GROUP BY tbl1.pyu_id, tbl1.pyu_email
ORDER BY total DESC;
You are passing a string to the ROWCONCAT() function which executes that string dynamically. Your code concatenates a varchar2 column to the DML string. Unless you include escaped quotes in the string what you pass (and the function tries to execute) is something like this:
SELECT pyu_id FROM p_survey_invite WHERE [email protected]
And that's why it fails.
Upvotes: 4