Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

need help with the query

curious_mindDec 11 2020

Hi All,
I have a below query which i am using to do the manual mock up of data as i have to go through the Performance test.
select distinct C.eods_grp_id,'DMU_1' PRTT_CDE,(select face_prfl_id from seods02.face_prfl
where face_prfl_id not in
(select face_prfl_id from seods02.grp_face_prfl)
and rownum=1)
from GRP C
where eods_grp_id not in (select eods_grp_id from grp_face_prfl);

here i basically have to mock up the table called grp_face_prfl where the primary key is on the columns EODS_GRP_ID,FACE_PRFL_ID,PRTT_CDE
I am getting EODS_GRP_CDE,PRTT_CDE from GRP which is fine but to get FACE_PRFL_ID i have another table which is in the inner query of the select statement. unfortunately there is no join between FACE_PRFL & GRP table and hence i am writing select statement like this:
I am getting below output from my query where the value of face_prfl_id is not changing with every row:
eods_grp_id prtt_cde face_prfl_id
35016657 DMU_1 5654808
35016807 DMU_1 5654808
35016821 DMU_1 5654808

Please help how i can get the distinct value for the face_prfl_id for every row:

Comments

Frank Kulash

Hi,
Whenever you have a question, please post a little sample data in a usable form (such as CREATE TABLE and INSERT statements), so the people who want to help you car re-create the problem and test their ideas. Also post the exact results you want from the given data, and an explanation (in general terms) of you get those results from that data. Always say which version of Oracle you're using (e.g. 12.2.0.1.0).

See: How to Ask Questions in Developer Community Spaces - oracle-tech

1 person found this helpful
JonWat

I think you need to rethink that "unfortunately there is no join between FACE_PRFL & GRP table " statement, because if that is really true, then there is no reason for the face_profile_id to be different for every row. To give you an analogy, if your query was orders from a grocery store, and this part of the query is supposed to add the vegetables, then your in-line query basically says: add a vegetable that is not celery to the order. There is no other constraint, so the query adds any random vegetable (whichever it finds first in the list of vegetables), say asparagus, to every order. Presumably somewhere there is something somewhere that indicates the person's vegetable choice?

1 - 2

Post Details

Added on Dec 11 2020
#pl-sql, #plsql, #sql
2 comments
134 views