Home » SQL & PL/SQL » SQL & PL/SQL » Alternative approach for a query run programmatically. (12)
Alternative approach for a query run programmatically. [message #683574] |
Mon, 01 February 2021 18:13 |
|
ritmo2k
Messages: 11 Registered: October 2020
|
Junior Member |
|
|
I have a query that returns a large data set from a table where each row is unique.
This has worked well for years to stream and consume in code. However, an update
is pending that requires associating it with a few other tables which contain one
to zero or more records.
For example:
CREATE TABLE "Users" (
"id" INT,
"FirstName" VARCHAR2(255),
"LastName" VARCHAR2(255) NOT NULL,
constraint USERS_PK PRIMARY KEY ("id")
);
CREATE TABLE "UserData" (
"id" INT NOT NULL,
"user_id" INT NOT NULL,
"ColA" INT NOT NULL,
"ColB" INT NOT NULL,
constraint USERDATA_PK PRIMARY KEY ("id")
);
ALTER TABLE "UserData" ADD CONSTRAINT "UserData_fk0" FOREIGN KEY ("user_id") REFERENCES "Users"("id");
As the UserData table contains from 0 to many rows for each account, this
makes a regular join difficult. I also do not want to consume all the related tables
initially and hold them in memory.
One approach may be to use an ordered union and select null for each column from
the other tables I want, and also select a fixed value that is unique to each table
so as I stream it, I can parse it.
For example:
WITH cte AS (
SELECT 'a' "tb", id, FirstName, LastName, NULL ColA, NULL ColB FROM Users
UNION ALL
SELECT 'b' "tb", user_id id, NULL FirstName, NULL LastName, ColA, ColB FROM UserData
)
SEELCT * FROM cte
ORDER BY tb,id
Does a more elegant facility exist with Oracle to help me with streaming the
related data from these tables without consuming all of the 1 to many related
tables first?
|
|
|
|
|
Re: Alternative approach for a query run programmatically. [message #683578 is a reply to message #683577] |
Tue, 02 February 2021 11:50 |
Solomon Yakobson
Messages: 3275 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
ritmo2k wrote on Tue, 02 February 2021 09:32
Using an outer join would make it ambiguous as to what the actual row count is for the final result set for a given record.
What do you want to count? Just number of users? If so, COUNT(DISTINCT "Users"."id") will give you that. Need count of row for each id? COUNT(*) OVER(PARTITION BY Users"."id") will give you that.
SY.
|
|
|
|
Re: Alternative approach for a query run programmatically. [message #683581 is a reply to message #683580] |
Wed, 03 February 2021 13:30 |
John Watson
Messages: 8938 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
Quote:how can I include a column in the join that indicates what table the result set originates from? This question suggests that you may not be familiar with the concepts behind relational engineering. A row generated by joining two or more rows does not come from one table, it comes from two tables. Looking back at your other posts there are more indications that you are not familiar with some underlying concepts. Your use of "unique" and "duplicate" is rather odd, for example.
If you could describe what you need to do with these rows (this "consumption" process) it might help.
|
|
|
|
|
Re: Alternative approach for a query run programmatically. [message #683586 is a reply to message #683584] |
Thu, 04 February 2021 06:18 |
|
ritmo2k
Messages: 11 Registered: October 2020
|
Junior Member |
|
|
Hi John,
No need to apologize, I will try to be more clear. As I don't have quick access to get a test schema created, I'll use sqlite as an example:
CREATE TABLE Users
(
id INTEGER PRIMARY KEY AUTOINCREMENT,
FirstName TEXT NOT NULL,
LastName TEXT NOT NULL
);
INSERT INTO Users VALUES(1,'John','Doe');
INSERT INTO Users VALUES(2,'Jim','Berry');
INSERT INTO Users VALUES(3,'Bob','Moore');
CREATE TABLE UserDataA
(
id INTEGER PRIMARY KEY AUTOINCREMENT,
user_id INTEGER NOT NULL,
type TEXT NOT NULL,
value TEXT NOT NULL,
FOREIGN KEY(user_id) REFERENCES Users(id)
);
INSERT INTO UserDataA VALUES(1,1,'role','FIN');
INSERT INTO UserDataA VALUES(2,1,'role','DEV');
INSERT INTO UserDataA VALUES(3,3,'role','SHP');
CREATE TABLE UserDataB
(
id INTEGER PRIMARY KEY AUTOINCREMENT,
user_id INTEGER NOT NULL,
type TEXT NOT NULL,
value TEXT NOT NULL,
FOREIGN KEY(user_id) REFERENCES Users(id)
);
INSERT INTO UserDataB VALUES(1,1,'attr','a');
INSERT INTO UserDataB VALUES(2,1,'attr','a');
INSERT INTO UserDataB VALUES(3,1,'attr','b');
I am reading this data in an application, from within compiled code. I do not want to read individual tables entirely, I would prefer to read one statement and process it as it arrives. Given the above criteria, we can see the following points:
- The Users table is single valued, only row relates to one record.
- The UserDataA and UserDataB tables are multi valued, from none to unlimited row relate to one User record.
I would then need to assemble the data programmatically as map of the following:
- Column name to column value for data in the Users table.
- Column name to column values for data in the UserDataA and UserDataB tables.
In the above data set, this yields:
record 0:
FirstName: John
LastName: Doe
role: [FIN, DEV]
attr: [a, a, b]
record 1:
FirstName: Jim
LastName: Berry
role: []
attr: []
record 2:
FirstName: Bob
LastName: Moore
role: [SHP]
attr: []
However, if you simply left join this data, you cannot identify the actual count of values due to the repetition:
SELECT Users.FirstName,Users.LastName,UserDataA.type,UserDataA.value,UserDataB.type,UserDataB.value
FROM Users
LEFT JOIN UserDataA on Users.id = UserDataA.user_id
LEFT JOIN UserDataB on Users.id = UserDataB.user_id
ORDER BY Users.FirstName,Users.LastName,UserDataA.type,UserDataA.value,UserDataB.type,UserDataB.value
which produces the first table in the image.
As a result, one approach is to create a select for each table that returns a column for the combined data set, and a column that indicates what table the data originates from and then perform a union all:
SELECT 'a' AS "tbl", id, FirstName, LastName, NULL AS UserDataAtype, NULL AS UserDataAvalue, NULL AS UserDataBtype, NULL AS UserDataBvalue
FROM Users
UNION ALL
SELECT 'b' AS "tbl", user_id AS "id", NULL, NULL, type, value, NULL, NULL
FROM UserDataA
JOIN Users ON UserDataA.user_id = Users.id
UNION ALL
SELECT 'c' AS "tbl", user_id AS "id", NULL, NULL, NULL, NULL, type, value
FROM UserDataB
JOIN Users ON UserDataB.user_id = Users.id
ORDER BY id,tbl;
which produces the second table in the image that can be read and differentiated within application code.
My question does Oracle provide a facility that accomplishes what I have done in the union all more elegantly?
-
Attachment: samples.png
(Size: 51.87KB, Downloaded 1060 times)
[Updated on: Thu, 04 February 2021 06:20] Report message to a moderator
|
|
|
Re: Alternative approach for a query run programmatically. [message #683587 is a reply to message #683586] |
Thu, 04 February 2021 06:35 |
John Watson
Messages: 8938 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
Do you mean something like this,orclz> select firstname,lastname,
2 (select listagg(value,',') from userdataa where userdataa.user_id=users.id),
3 (select listagg(value,',') from userdatab where userdatab.user_id=users.id)
4 from users;
FIRSTNAME LASTNAME
---------- ----------
(SELECTLISTAGG(VALUE,',')FROMUSERDATAAWHEREUSERDATAA.USER_ID=USERS.ID)
---------------------------------------------------------------------------------------------------------------------------------------
(SELECTLISTAGG(VALUE,',')FROMUSERDATABWHEREUSERDATAB.USER_ID=USERS.ID)
---------------------------------------------------------------------------------------------------------------------------------------
John Doe
FIN,DEV
a,a,b
Jim Berry
Bob Moore
SHP
orclz> Though I am still at a loss to understand what you intend to do with this. What is the "consumption" you are going to do? For example, do you have some process that demands a particular format?
|
|
|
|
Re: Alternative approach for a query run programmatically. [message #683589 is a reply to message #683588] |
Thu, 04 February 2021 10:14 |
|
Michel Cadot
Messages: 68665 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
It's just formatting:
SQL> break on res skip 1
SQL> With
2 roles as (
3 select user_id,
4 listagg(value,', ') within group (order by value) roles
5 from userdataa
6 group by user_id
7 ),
8 attrs as (
9 select user_id,
10 listagg(value,', ') within group (order by value) attrs
11 from userdatab
12 group by user_id
13 )
14 select 'record '||to_char(row_number() over (order by null)-1)||':
15 FirstName: '||firstname||'
16 Lastname: '||lastname||'
17 role: ['||roles||']
18 attr: ['||attrs||']' res
19 from Users u
20 left outer join roles r on r.user_id = u.id
21 left outer join attrs a on a.user_id = u.id
22 /
RES
------------------------------------------------------------------------------
record 0:
FirstName: John
Lastname: Doe
role: [DEV, FIN]
attr: [a, a, b]
record 1:
FirstName: Bob
Lastname: Moore
role: [SHP]
attr: []
record 2:
FirstName: Jim
Lastname: Berry
role: []
attr: []
3 rows selected.
|
|
|
|
|
|
Goto Forum:
Current Time: Mon Jul 01 00:02:57 CDT 2024
|