I want to know what is the error in this SQL query
Clash Royale CLAN TAG#URR8PPP
I want to know what is the error in this SQL query
I have updated the Query to
SELECT regexp_substr(a.NAME, '[^:]+$') AS emp_req
,a.COMPLETION_STATUS
,b.USER_TYPE
,b.display_name
FROM spt_result a
,spt_identity b
WHERE a.name LIKE 'Join%'
AND emp_req = b.display_name;
I want to fetch the user type from spt_identity table which is having completion status as error or success in spt_result table . the table spt_result contains
spt_result table
spt_result
NAME COMPLETION_STATUS
----------------------------------------------------------------
Add/Remove Role(s) for:kc051363(1503085327459) Error
Joiner:sk080281 Success
and spt_identity contains
spt_identity contains
USER_TYPE DISPLAY_NAME
-----------------------------
Parttime-Regular pt092101
Parttime-Regular rm088923
Vendor wl090918
Parttime-Regular ne091924
Vendor dh090040
You have display_name as alias and actual column name. It may get confused.
– slon
Aug 13 at 3:28
Well I have changed it to :-select a.USER_TYPE,a.display_name,regexp_substr(b.NAME, '[^:]+$') as emp_name from spt_identity a,spt_task_result b where b.Name like 'Join%' and a.DISPLAY_NAME=b.emp_name; still the error showing as ORA-00904: "B"."EMP_NAME": invalid identifier 00904. 00000 - "%s: invalid identifier" *Cause: *Action: Error at Line: 5 Column: 162
– Sambita
Aug 13 at 3:34
When asking SQL related question, you should post SQLs you used to create the tables, and if the question concerns query result, SQLs to insert data. Without these information, you may have made a mistake when creating the table and we will never know. Please edit your question accordingly.
– KC Wong
Aug 13 at 3:54
Bad habits to kick : using old-style JOINs - that old-style comma-separated list of tables style was replaced with the proper ANSI
JOIN syntax in the ANSI-92 SQL Standard (more than 25 years ago) and its use is discouraged+– marc_s
Aug 13 at 4:43
JOIN
1 Answer
1
Join on occurrence of DISPLAY_NAME in NAME
DISPLAY_NAME
NAME
SELECT i.*
FROM spt_identity i
JOIN spt_result r ON INSTR(r.name, i.display_name) > 0
WHERE r.completion_status IN ('Error', 'Success')
By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.
How can we know what the error is in your SQL query if you don't tell us?
– Tim Biegeleisen
Aug 13 at 3:25