Discussions
Categories
- 196.7K All Categories
- 2.2K Data
- 235 Big Data Appliance
- 1.9K Data Science
- 449.9K Databases
- 221.6K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 549 MySQL Community Space
- 478 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3K ORDS, SODA & JSON in the Database
- 532 SQLcl
- 4K SQL Developer Data Modeler
- 186.9K SQL & PL/SQL
- 21.3K SQL Developer
- 295.4K Development
- 17 Developer Projects
- 138 Programming Languages
- 292.1K Development Tools
- 104 DevOps
- 3.1K QA/Testing
- 645.9K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 153 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.1K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 17 Java Essentials
- 158 Java 8 Questions
- 85.9K Java Programming
- 79 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.2K Java SE
- 13.8K Java Security
- 203 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 400 LiveLabs
- 37 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.6K Other Languages
- 2.3K Chinese
- 170 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 230 Portuguese
need help with the query

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:
Answers
-
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
-
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?