Forum Stats

  • 3,723,932 Users
  • 2,244,654 Discussions
  • 7,850,763 Comments

Discussions

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

need help with the query

curious_mind
curious_mind Member Posts: 252 Bronze Badge

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:

Tagged:

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 40,207 Red Diamond

    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

    curious_mind
  • JonWat
    JonWat Member Posts: 523 Silver Badge

    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?

Sign In or Register to comment.