How JustAnswer Works:
  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.
Ask ATLPROG Your Own Question
ATLPROG
ATLPROG, Computer Software Engineer
Category: Programming
Satisfied Customers: 7677
Experience:  MS in IT.Several years of programming experience in Java C++ C C# Python VB Javascript HTML
44910485
Type Your Programming Question Here...
ATLPROG is online now
A new question is answered every 9 seconds

I need some help in writing a sql query, oracle, I have

Customer Question

Hi I need some help in writing a sql query
JA: What programs and versions are you using?
Customer: oracle
JA: What is your ideal outcome? How can we solve this for you?
Customer: I have couple of sql tables
JA: Anything else you want the programmer to know before I connect you?
Customer: and one sql table has permissions column and permissions can use or group permissions
Submitted: 5 months ago.
Category: Programming
Expert:  Bhavik Joshi replied 5 months ago.

Hi, I'm Bhavik. Welcome to JustAnswer. I'm reviewing your question now, and will reply back ASAP.

Expert:  Bhavik Joshi replied 5 months ago.

Please provide me your requirement. I will try to prepare SQL query for it.

Customer: replied 5 months ago.
I have 3 tables DTreeCore. DTREEACL, KUAF
the tables are joined as shown in the below query.
Each Name(ID) from DTreeCore table has user and group permissions setup in DTREEACL, KUAF tables.
If the record from KUAF table has a GroupID assigned then its a user.
If the record from KUAF table has no GroupID assigned then it is a group.
In my select query I want all the users information for that Name(ID -(###) ###-####) from DTreeCore
if its a user then user record and if its a group i want all the user information from KUAF table based on KUAF.GroupID values.Here is my ample query. Let me knowselect dc.DataID as dcID, dc.Name as dcName,
da.DataID as daID, DA.PERMISSIONS as daPermissions,DA.RIGHTID as daRightID,DA.ACLTYPE daACLType,
k.ID as kID, K.NAME as kName,K.GROUPID as kGroupID
from CUSTOM.DTreeCore dc,custom.DTREEACL da, custom.KUAF k
where dc.DATAID = da.DATAID
and DA.RIGHTID = K.ID
and dc.DataID =(###) ###-####
Expert:  Bhavik Joshi replied 5 months ago.

Can you share your database with me or simply the table structure. I want to set up similar environment on my PC

Customer: replied 5 months ago.
Please find my sql table definitions in attachmentCREATE TABLE CUSTOM.DTREECORE
(
OWNERID NUMBER(10) NOT NULL,
PARENTID NUMBER(10) NOT NULL,
DATAID NUMBER(10) NOT NULL,
NAME VARCHAR2(255 CHAR) NOT NULL,
ORIGINOWNERID NUMBER(10) NOT NULL,
ORIGINDATAID NUMBER(10) NOT NULL,
USERID NUMBER(10) NOT NULL,
GROUPID NUMBER(10) NOT NULL,
UPERMISSIONS NUMBER(10) NOT NULL,
GPERMISSIONS NUMBER(10) NOT NULL,
WPERMISSIONS NUMBER(10) NOT NULL,
SPERMISSIONS NUMBER(10) NOT NULL,
ACLCOUNT NUMBER(10) NOT NULL,
DATATYPE NUMBER(10),
CREATEDBY NUMBER(10),
CREATEDATE DATE,
MODIFYDATE DATE,
MAXVERS NUMBER(10),
RESERVED NUMBER(10),
RESERVEDBY NUMBER(10),
RESERVEDDATE DATE,
VERSIONNUM NUMBER(10),
DCOMMENT VARCHAR2(4000 CHAR),
DCATEGORY VARCHAR2(255 CHAR),
SUBTYPE NUMBER(10),
EXATT1 VARCHAR2(255 CHAR),
EXATT2 VARCHAR2(255 CHAR),
ORDERING NUMBER(10),
MAJOR NUMBER(10),
MINOR NUMBER(10),
RELEASEREF NUMBER(10),
CHILDCOUNT NUMBER(10),
DOC_DATE DATE,
DOC_ISAFORM NUMBER(10),
DOC_ISAALERT NUMBER(10),
DOC_ISANEWSBRIEF NUMBER(10),
DOC_TYPE VARCHAR2(50 CHAR),
DOC_AUDIENCE VARCHAR2(10 CHAR),
DOC_AUTHOR VARCHAR2(50 CHAR),
DOC_CLASS VARCHAR2(25 CHAR),
DOC_CONTACT VARCHAR2(50 CHAR),
DOC_ISAANNOUCEMENT NUMBER(10),
DOC_KEYWORD VARCHAR2(254 CHAR),
DOC_SUBTYPE VARCHAR2(50 CHAR),
CATALOG NUMBER(10),
PERMID NUMBER(10),
ASSIGNEDTO NUMBER(10),
DATEASSIGNED DATE,
DATEEFFECTIVE DATE,
DATEEXPIRATION DATE,
DATEDUE DATE,
DATESTARTED DATE,
DATECOMPLETED DATE,
STATUS NUMBER(10),
PRIORITY NUMBER(10),
GIF VARCHAR2(255 CHAR),
EXTENDEDDATA CLOB,
CACHEEXPIRATION NUMBER(10),
DELETED NUMBER(10) NOT NULL,
MODIFIEDBY NUMBER(10),
GUID RAW(16) DEFAULT sys_guid() NOT NULL
)CREATE TABLE CUSTOM.DTREEACL
(
OWNERID NUMBER(10) NOT NULL,
PARENTID NUMBER(10) NOT NULL,
DATAID NUMBER(10) NOT NULL,
RIGHTID NUMBER(10) NOT NULL,
PERMISSIONS NUMBER(10) NOT NULL,
ACLTYPE NUMBER(10),
SEE NUMBER(10)
)
TABLESPACE CUSTOMDATA
RESULT_CACHE (MODE DEFAULT)
PCTUSED 40
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 43680K
NEXT 128K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;4CREATE TABLE CUSTOM.KUAF
(
ID NUMBER(10) NOT NULL,
OWNERID NUMBER(10) NOT NULL,
TYPE NUMBER(10) NOT NULL,
SPACEID NUMBER(10) NOT NULL,
NAME VARCHAR2(255 CHAR) NOT NULL,
USERDATA VARCHAR2(4000 CHAR),
LEADERID NUMBER(10),
DELETED NUMBER(10),
USERPWD VARCHAR2(64 CHAR),
GROUPID NUMBER(10),
USERPRIVILEGES NUMBER(10),
LASTNAME VARCHAR2(64 CHAR),
MIDDLENAME VARCHAR2(32 CHAR),
FIRSTNAME VARCHAR2(64 CHAR),
MAILADDRESS VARCHAR2(255 CHAR),
CONTACT VARCHAR2(128 CHAR),
TITLE VARCHAR2(64 CHAR),
PWDEXPIREDATE DATE,
PWDEXPIREMODE NUMBER(10),
SETTINGSNUM NUMBER(10),
FAX VARCHAR2(64 CHAR),
OFFICELOCATION VARCHAR2(255 CHAR),
TIMEZONE NUMBER(10),
PHOTOID NUMBER(10),
GENDER NUMBER(10),
BIRTHDAY DATE,
PERSONALEMAIL VARCHAR2(255 CHAR),
HOMEADDRESS1 VARCHAR2(255 CHAR),
HOMEADDRESS2 VARCHAR2(255 CHAR),
HOMEPHONE VARCHAR2(64 CHAR),
HOMEFAX VARCHAR2(64 CHAR),
CELLULARPHONE VARCHAR2(64 CHAR),
PAGER VARCHAR2(64 CHAR),
HOMEPAGE VARCHAR2(255 CHAR),
FAVORITES1 VARCHAR2(255 CHAR),
FAVORITES2 VARCHAR2(255 CHAR),
FAVORITES3 VARCHAR2(255 CHAR),
INTERESTS VARCHAR2(4000 CHAR)
)
Expert:  Bhavik Joshi replied 5 months ago.

I tried but I am not getting expected result. I am opting out so that some other expert can help you in it.

Expert:  Bruce Wilner replied 5 months ago.

Hi. I won't opt out on you. Kindly explain what the issue is. That's an awfully lengthy DDL for such a limited, er, scope of commitment.

Customer: replied 5 months ago.
Hi Bruce,I have 3 tables DTreeCore. DTREEACL, KUAF and the tables are joined as shown in the below query.
Each Name(DataID) from DTreeCore table has the user and group permissions setup in DTREEACL, KUAF tables.
If the record in KUAF table has a GroupID assigned then it is the user.
If the record from KUAF table has no GroupID assigned then it is a group.
In my select query I want all the users information for that Name(example - DataID -(###) ###-####) from DTreeCore
If its a user then user record else if its a group then I want to get all the user information from KUAF table based on KUAF.GroupID values.Here is my sample query. I need all the results from one query. Let me knowselect dc.DataID as dcID, dc.Name as dcName,
da.DataID as daID, DA.PERMISSIONS as daPermissions,DA.RIGHTID as daRightID,DA.ACLTYPE daACLType,
k.ID as kID, K.NAME as kName,K.GROUPID as kGroupID
from CUSTOM.DTreeCore dc,custom.DTREEACL da, custom.KUAF k
where dc.DATAID = da.DATAID
and DA.RIGHTID = K.ID
and dc.DataID =(###) ###-####
Expert:  Bruce Wilner replied 5 months ago.

You need the results? If you have the structures and the data, why don't you just run the query and save the results? What am I not understanding?

"Joined as shown in the query below." No, actually, that's how YOU chose to join them for I-know-not-what reason. I need PRECISE table structures that tell me what the key fields are and what the inter-table referential integrity constraints are.

"It's a group" and "based on KUAF.GroupID values" and such are far too vague to make this doable in the least. I need a PRECISE spec of what facts you want to obtain.

Databases are all about precision, not about vague, freewheeling indications of "I just need this" and "like I did below."

Please advise. Thank you for your cooperation.

Customer: replied 5 months ago.
Hi Bruce,Here are my table PK's and FK's and table columns information and definitions.
Let em know if you need anything else.DTreeCore Table:
DataID - PK
UserID - FK to KUAF.ID
GroupID - FK to KUAF.IDDTreeACL:
DataID - PK and FK to DTreeCore.DataID
RightID - PK and FK to KUAF.IDKUAF Table:
ID - PK
GroupID - ID of the users base GroupHere are the Table definitions:
CREATE TABLE CUSTOM.DTREECORE
(
OWNERID NUMBER(10) NOT NULL,
PARENTID NUMBER(10) NOT NULL,
DATAID NUMBER(10) NOT NULL,
NAME VARCHAR2(255 CHAR) NOT NULL,
ORIGINOWNERID NUMBER(10) NOT NULL,
ORIGINDATAID NUMBER(10) NOT NULL,
USERID NUMBER(10) NOT NULL,
GROUPID NUMBER(10) NOT NULL,
UPERMISSIONS NUMBER(10) NOT NULL,
GPERMISSIONS NUMBER(10) NOT NULL,
WPERMISSIONS NUMBER(10) NOT NULL,
SPERMISSIONS NUMBER(10) NOT NULL,
ACLCOUNT NUMBER(10) NOT NULL,
DATATYPE NUMBER(10),
CREATEDBY NUMBER(10),
CREATEDATE DATE,
MODIFYDATE DATE,
MAXVERS NUMBER(10),
RESERVED NUMBER(10),
RESERVEDBY NUMBER(10),
RESERVEDDATE DATE,
VERSIONNUM NUMBER(10),
DCOMMENT VARCHAR2(4000 CHAR),
DCATEGORY VARCHAR2(255 CHAR),
SUBTYPE NUMBER(10),
EXATT1 VARCHAR2(255 CHAR),
EXATT2 VARCHAR2(255 CHAR),
ORDERING NUMBER(10),
MAJOR NUMBER(10),
MINOR NUMBER(10),
RELEASEREF NUMBER(10),
CHILDCOUNT NUMBER(10),
DOC_DATE DATE,
DOC_ISAFORM NUMBER(10),
DOC_ISAALERT NUMBER(10),
DOC_ISANEWSBRIEF NUMBER(10),
DOC_TYPE VARCHAR2(50 CHAR),
DOC_AUDIENCE VARCHAR2(10 CHAR),
DOC_AUTHOR VARCHAR2(50 CHAR),
DOC_CLASS VARCHAR2(25 CHAR),
DOC_CONTACT VARCHAR2(50 CHAR),
DOC_ISAANNOUCEMENT NUMBER(10),
DOC_KEYWORD VARCHAR2(254 CHAR),
DOC_SUBTYPE VARCHAR2(50 CHAR),
CATALOG NUMBER(10),
PERMID NUMBER(10),
ASSIGNEDTO NUMBER(10),
DATEASSIGNED DATE,
DATEEFFECTIVE DATE,
DATEEXPIRATION DATE,
DATEDUE DATE,
DATESTARTED DATE,
DATECOMPLETED DATE,
STATUS NUMBER(10),
PRIORITY NUMBER(10),
GIF VARCHAR2(255 CHAR),
EXTENDEDDATA CLOB,
CACHEEXPIRATION NUMBER(10),
DELETED NUMBER(10) NOT NULL,
MODIFIEDBY NUMBER(10),
GUID RAW(16) DEFAULT sys_guid() NOT NULL
)CREATE TABLE CUSTOM.DTREEACL
(
OWNERID NUMBER(10) NOT NULL,
PARENTID NUMBER(10) NOT NULL,
DATAID NUMBER(10) NOT NULL,
RIGHTID NUMBER(10) NOT NULL,
PERMISSIONS NUMBER(10) NOT NULL,
ACLTYPE NUMBER(10),
SEE NUMBER(10)
)CREATE TABLE CUSTOM.KUAF
(
ID NUMBER(10) NOT NULL,
OWNERID NUMBER(10) NOT NULL,
TYPE NUMBER(10) NOT NULL,
SPACEID NUMBER(10) NOT NULL,
NAME VARCHAR2(255 CHAR) NOT NULL,
USERDATA VARCHAR2(4000 CHAR),
LEADERID NUMBER(10),
DELETED NUMBER(10),
USERPWD VARCHAR2(64 CHAR),
GROUPID NUMBER(10),
USERPRIVILEGES NUMBER(10),
LASTNAME VARCHAR2(64 CHAR),
MIDDLENAME VARCHAR2(32 CHAR),
FIRSTNAME VARCHAR2(64 CHAR),
MAILADDRESS VARCHAR2(255 CHAR),
CONTACT VARCHAR2(128 CHAR),
TITLE VARCHAR2(64 CHAR),
PWDEXPIREDATE DATE,
PWDEXPIREMODE NUMBER(10),
SETTINGSNUM NUMBER(10),
FAX VARCHAR2(64 CHAR),
OFFICELOCATION VARCHAR2(255 CHAR),
TIMEZONE NUMBER(10),
PHOTOID NUMBER(10),
GENDER NUMBER(10),
BIRTHDAY DATE,
PERSONALEMAIL VARCHAR2(255 CHAR),
HOMEADDRESS1 VARCHAR2(255 CHAR),
HOMEADDRESS2 VARCHAR2(255 CHAR),
HOMEPHONE VARCHAR2(64 CHAR),
HOMEFAX VARCHAR2(64 CHAR),
CELLULARPHONE VARCHAR2(64 CHAR),
PAGER VARCHAR2(64 CHAR),
HOMEPAGE VARCHAR2(255 CHAR),
FAVORITES1 VARCHAR2(255 CHAR),
FAVORITES2 VARCHAR2(255 CHAR),
FAVORITES3 VARCHAR2(255 CHAR),
INTERESTS VARCHAR2(4000 CHAR)
)
Expert:  Bruce Wilner replied 5 months ago.

Well, yes, I need to know what information you're trying to retrieve from your query that evidently didn't work.

Can you express it in a precise form, like predicate calculus? Or just be as descriptive as possible:

"For each value of X in T, I want to find the corresponding value of Y in Q, and, for each P that matches that Y, count the # of distinct values of R." That sort of thing.

Customer: replied 5 months ago.
Bruce,Here is my requirement.Each DTreeCore.DataID record(row) - it's the document information. Each document has user and group permissions assigned. Each user and group permissions for that DataID(document) are stored in DTreeACL table.So for each DTreeCore.DataID - it will have more than one record in DTreeACL table.In KUAF table consists of user and group information and if column Type = 0 then it's a User and Type = 1 then it is a Groupwhen a record in DTreeACL table is a user record (column Type = 0 in KUAF table) - I need that user information
and
when a record in DTreeACL table is a group record (column Type = 1 in KUAF table) - then I need all the users for that group from KUAF table (based on the GroupID column).I need all this information from a single query either with a union, join or subquery.DTreeCore Table:
DataID - PK
UserID - FK to KUAF.ID
GroupID - FK to KUAF.IDDTreeACL:
DataID - PK and FK to DTreeCore.DataID
RightID - PK and FK to KUAF.IDKUAF Table:
ID - PK
GroupID - ID of the users base GroupThanks.
Customer: replied 5 months ago.
Hi Bruce, do you have any update. Thanks