Suggestion for select statement

[roberto]

I have this kind of information on a db table

COMPANY USER MYDATA
______________________________
AA
01 AA01
USER AAUS
01 USER AA01US

BB
02 BB02
GROUP BBGR
02 GROUP BB02GR
USER is a "logical" reference to another table like this one:

USER GROUP
____________________
USER GROUP
USER2 GROUP2
USER3 GROUP

I want retrieve column (MYDATA) according company and user/group.
For example:

If I have COMPANY = 01 and USSER = USER, I want retrieve row with
MYDATA value = AA01US
If I have COMPANY = KK and USSER = USER, I want retrieve row with
MYDATA value = AAUS
If I have COMPANY = 01 and USSER = USER2, I want retrieve row with
MYDATA value = AA01
If I have COMPANY = KK and USSER = USER2, I want retrieve row with
MYDATA value = AA
If I have COMPANY = KK and USSER = USER3, I want retrieve row with
MYDATA value = BBGR (because USER3 is part of GROUP)
What is the best way (for performance purpose) to retrieve it?
Maybe I can also change my table structure.

I prefer to avoi many select statements to retrive this information.

I'm using db2 8 and 9 on windows platform and DB2/400 on v5r3

Thank You
Roberto

[Brian Tkatch]

roberto wrote:

I have this kind of information on a db table

COMPANY USER MYDATA
______________________________
AA
01 AA01
USER AAUS
01 USER AA01US

BB
02 BB02
GROUP BBGR
02 GROUP BB02GR
USER is a "logical" reference to another table like this one:

USER GROUP
____________________
USER GROUP
USER2 GROUP2
USER3 GROUP

I want retrieve column (MYDATA) according company and user/group.
For example:

If I have COMPANY = 01 and USSER = USER, I want retrieve row with
MYDATA value = AA01US
If I have COMPANY = KK and USSER = USER, I want retrieve row with
MYDATA value = AAUS
If I have COMPANY = 01 and USSER = USER2, I want retrieve row with
MYDATA value = AA01
If I have COMPANY = KK and USSER = USER2, I want retrieve row with
MYDATA value = AA
If I have COMPANY = KK and USSER = USER3, I want retrieve row with
MYDATA value = BBGR (because USER3 is part of GROUP)
What is the best way (for performance purpose) to retrieve it?
Maybe I can also change my table structure.

I prefer to avoi many select statements to retrive this information.

I'm using db2 8 and 9 on windows platform and DB2/400 on v5r3

Thank You
Roberto

With the current setup, i'd suggest either a stored PROCEDURE.:

DECLARE GLOBAL TEMPORARY TABLE T1(Company VARCHAR(2), MyUser
VARCHAR(6), MyData VARCHAR(6))
INSERT INTO SESSION.T1 VALUES (NULL, NULL, 'AA'), ('01', NULL, 'AA01'),
(NULL, 'USER', 'AAUS'), ('01', 'USER', 'AA01US')
INSERT INTO SESSION.T1 VALUES (NULL, NULL, 'BB'), ('02', NULL, 'BB02'),
(NULL, 'GROUP', 'BBGR'), ('02', 'GROUP', 'BB02GR')

DECLARE GLOBAL TEMPORARY TABLE T2(MyUser VARCHAR(6), Group VARCHAR(6))
INSERT INTO SESSION.T2 VALUES ('USER', 'GROUP'), ('USER2', 'GROUP2'),
('USER3', 'GROUP')

DROP PROCEDURE Get_MyData
CREATE PROCEDURE Get_MyData
(
IN IN_Company VARCHAR(0002),
IN IN_MyUser VARCHAR(0006)
)
SPECIFIC Get_MyData
BEGIN

DECLARE List CURSOR WITH RETURN TO CLIENT FOR
SELECT
Company,
MyUser,
MyData
FROM
SESSION.T1
WHERE
NULLIF(Company, IN_Company) IS NULL
AND
(
NULLIF(MyUser, IN_MyUser) IS NULL
OR MyUser IN
(
SELECT
MyUser
FROM
SESSION.T2
WHERE
GROUP = IN_MyUser
)
)
ORDER BY
Company,
MyUser
FETCH FIRST ROW ONLY;

OPEN List;

END

CALL Get_MyData('01', 'USER')
CALL Get_MyData('KK', 'USER')
CALL Get_MyData('01', 'USER2')
CALL Get_MyData('KK', 'USER2')
CALL Get_MyData('KK', 'GROUP')

However, the setup itself does not seem logical. Keeping a COLUMN that
can refer to one of two separate COLUMNs is in itself suspect.

B.

[roberto]

Thank You Brian,

Do you suggest another kind of setup (table structure)?

Roberto

[Tonkuma]

roberto wrote:

I have this kind of information on a db table

COMPANY USER MYDATA
______________________________
AA
01 AA01
USER AAUS
01 USER AA01US

BB
02 BB02
GROUP BBGR
02 GROUP BB02GR
USER is a "logical" reference to another table like this one:

USER GROUP
____________________
USER GROUP
USER2 GROUP2
USER3 GROUP

I want retrieve column (MYDATA) according company and user/group.
For example:

If I have COMPANY = KK and USSER = USER2, I want retrieve row with
MYDATA value = AA

Why MYDATA value = BB was not retrieved?
Both of MYDATA value = AA and BB are COMPANY IS NULL and USER IS NULL.

>
I'm using db2 8 and 9 on windows platform and DB2/400 on v5r3

Thank You
Roberto

CREATE TABLE InputData
(Company VARCHAR(2)
,User VARCHAR(6)
);

INSERT INTO InputData
VALUES
('01', 'USER')
,('KK', 'USER')
,('01', 'USER2')
,('KK', 'USER2')
,('KK', 'USER3')
;

I assumed multiple rows satisfy the conditions, take MIN(MYDATA).

------------------------------ Commands Entered
------------------------------
SELECT InD.Company AS In_Company
, InD.User AS In_User
, MIN(MyData) AS MyData
FROM InputData InD
, T1
WHERE
(T1.Company = InD.Company
OR
T1.Company IS NULL
AND NOT EXISTS
(SELECT *
FROM T1 T1N
WHERE T1N.Company = InD.Company
)
)
AND
(T1.User = InD.User
OR
T1.User
= (SELECT T2.Group
FROM T2
WHERE InD.User = T2.User
)
AND NOT EXISTS
(SELECT *
FROM T1 T1N
WHERE T1N.User = InD.User
)
OR
T1.User IS NULL
AND NOT EXISTS
(SELECT *
FROM T1 T1N
LEFT OUTER JOIN
T2 T2N
ON T1N.User = T2N.Group
WHERE InD.User IN (T1N.User, T2N.User)
)
)
GROUP BY InD.Company, InD.User
ORDER BY InD.User, InD.Company;
------------------------------------------------------------------------------

IN_COMPANY IN_USER MYDATA
---------- ------- ------
01 USER AA01US
KK USER AAUS
01 USER2 AA01
KK USER2 AA
KK USER3 BBGR

5 record(s) selected.

[roberto]

I make a mistake...

Company and User are the unique key of the table.

MYDATE = BB is a wrong row.

:) sorry...

[Tonkuma]

roberto wrote:

I make a mistake...

Company and User are the unique key of the table.

MYDATE = BB is a wrong row.

:) sorry...

So, the query can eliminate GROUP BY

------------------------------ Commands Entered
------------------------------
SELECT InD.Company AS In_Company
, InD.User AS In_User
, MyData
FROM InputData InD
, T1
WHERE
(T1.Company = InD.Company
OR
T1.Company IS NULL
AND NOT EXISTS
(SELECT *
FROM T1 T1N
WHERE T1N.Company = InD.Company
)
)
AND
(T1.User = InD.User
OR
T1.User
= (SELECT T2.Group
FROM T2
WHERE InD.User = T2.User
)
AND NOT EXISTS
(SELECT *
FROM T1 T1N
WHERE T1N.User = InD.User
)
OR
T1.User IS NULL
AND NOT EXISTS
(SELECT *
FROM T1 T1N
LEFT OUTER JOIN
T2 T2N
ON T1N.User = T2N.Group
WHERE InD.User IN (T1N.User, T2N.User)
)
);
------------------------------------------------------------------------------

IN_COMPANY IN_USER MYDATA
---------- ------- ------
01 USER AA01US
KK USER AAUS
01 USER2 AA01
KK USER2 AA
KK USER3 BBGR

5 record(s) selected.

[roberto]

Thank you Tonkuma,

but there is some way to avoid "InputData" table?

Roberto

[Brian Tkatch]

roberto wrote:

Thank You Brian,

Do you suggest another kind of setup (table structure)?

Roberto

It would depend on the use of the data. This small snippet is hardly
enough to go on.

Even keeping the current structure, i'd ADD a second COLUMN for group,
so user and group are in separate COLUMNs. Then, ADD a CONSTRAINT
making sure only one is used: CHECK (NOT ((User IS NOT NULL) AND (Group
IS NOT NULL))). That alone would separate user and group as separate
entities.

B.

[Tonkuma]

roberto wrote:

Thank you Tonkuma,

but there is some way to avoid "InputData" table?

Roberto

It depends what environment you are using this query and how to get the
input value of Company and User.
If the statement is embedded in HOST language, replace InD.Company and
InD.User with Host Variable(For example: :v_Company and :v_User)

SELECT :v_Company AS In_Company
, :v_User AS In_User
, MyData
FROM T1
WHERE
(T1.Company = :v_Company
OR
T1.Company IS NULL
AND NOT EXISTS
(SELECT *
FROM T1 T1N
WHERE T1N.Company = :v_Company
)
)
AND
(T1.User = :v_User
OR
T1.User
= (SELECT T2.Group
FROM T2
WHERE :v_User = T2.User
)
AND NOT EXISTS
(SELECT *
FROM T1 T1N
WHERE T1N.User = :v_User
)
OR
T1.User IS NULL
AND NOT EXISTS
(SELECT *
FROM T1 T1N
LEFT OUTER JOIN
T2 T2N
ON T1N.User = T2N.Group
WHERE :v_User IN (T1N.User, T2N.User)
)
);

[roberto]

Thank Very much for the support to all,
I have another case (this is my final requirements....)

Starting from this table structure:

K1 K2 K3 K4 K5 V1 V2
-----------------------------------
AA BB C1 D1 D2
AA KK C1 D1 D2
01 AA KK C1 D101 D201
01 GR AA KK C1 D101GR D201GR
AA KK C2 D1 D2
01 AA KK C2 D101 D2
US AA KK C2 D1US D2
01 US AA KK C2 D101US D2
02 AA KK C2 D102 D2
GR AA KK C2 D1GR D2
02 GR AA KK C2 D102GR D2
01 GR AA KK C2 D101GR D2

where K1, K2, K3, K4 and K5 are the unique primary key of the table

I want retrieve all row where:
K3 = AA
K4 = KK
and for K1 and K2 the same behavior of the previous case so where
K2 is a "logical" reference to another table User/Group

So in this case 2 rows must be returned: one for K5 = C1 and another
one for K5=C2
I don't know how many rows will be returned because i don't know how
many K5 value will be for K1-K4 keys

Thank You !
Roberto

[Tonkuma]

roberto wrote:

Thank Very much for the support to all,
I have another case (this is my final requirements....)

Starting from this table structure:

K1 K2 K3 K4 K5 V1 V2
-----------------------------------
AA BB C1 D1 D2
AA KK C1 D1 D2
01 AA KK C1 D101 D201
01 GR AA KK C1 D101GR D201GR
AA KK C2 D1 D2
01 AA KK C2 D101 D2
US AA KK C2 D1US D2
01 US AA KK C2 D101US D2
02 AA KK C2 D102 D2
GR AA KK C2 D1GR D2
02 GR AA KK C2 D102GR D2
01 GR AA KK C2 D101GR D2

where K1, K2, K3, K4 and K5 are the unique primary key of the table

I want retrieve all row where:
K3 = AA
K4 = KK
and for K1 and K2 the same behavior of the previous case so where
K2 is a "logical" reference to another table User/Group

So in this case 2 rows must be returned: one for K5 = C1 and another
one for K5=C2
I don't know how many rows will be returned because i don't know how
many K5 value will be for K1-K4 keys

Thank You !
Roberto

Is it not enough that you simply add condition K3 = AA AND K4 = KK to
previous query?

[roberto]

Tonkuma wrote:

Is it not enough that you simply add condition K3 = AA AND K4 = KK to
previous query?

No, it's return only 1 row.
This is my SQL

SELECT *
FROM T4
WHERE
( (K1 = '01'
OR
K1 = ''
AND NOT EXISTS
(SELECT *
FROM T4 T4N
WHERE T4N.K1 = '01'
)
)
AND
(K2 = 'US'
OR
K2
= (SELECT Group
FROM T2
WHERE 'US' = MyUser
)
AND NOT EXISTS
(SELECT *
FROM T4 T4N
WHERE K2 = 'US'
)
OR
K2 = ''
AND NOT EXISTS
(SELECT *
FROM T4 T4N
LEFT OUTER JOIN
T2 T2N
ON K2 = Group
WHERE 'US' IN (K2, User)
)
)) AND K3= 'AA' and K4='KK' order by K5;

Its' return only

K1 K2 K3 K4 K5 V1 V2
-----------------------------------
01 US AA KK C2 D101US D2
but i need instead that return 2 elements in this case:

K1 K2 K3 K4 K5 V1 V2
-----------------------------------
01 AA KK C1 D101 D201 < ALSO THIS ONE
01 US AA KK C2 D101US D2

I replace the "IS NULL" with "= '' " because Keys are not nullable
values.

Thanks

[Tonkuma]

So in this case 2 rows must be returned: one for K5 = C1 and another

one for K5=C2
I don't know how many rows will be returned because i don't know how
many K5 value will be for K1-K4 keys

Is this meaning that you want to apply the conditions for each K5
group?

And your input data are followings.
K1 = '01'
K2 = 'US'
K3 = 'AA'
K4 = 'KK'

If so, and Definition and Data of T2 are like followings.

CREATE TABLE T2
(MyUser CHAR(2) NOT NULL
,Group CHAR(2) NOT NULL
)

INSERT INTO T2
VALUES
('US', 'GR')
,('U2', 'G2')
,('U3', 'GR')

Why V1 = D101GR is not selected instead of V1 = D101? (Because 'US' is
part of 'GR')
Or am I misunderstand Data of T2 and/or some other thing?

K1 K2 K3 K4 K5 V1 V2
-- -- -- -- -- ------ ------
01 GR AA KK C1 D101GR D201GR
01 AA KK C1 D101 D201

[roberto]

>
Why V1 = D101GR is not selected instead of V1 = D101? (Because 'US' is
part of 'GR')

Yes, US is a member of group GR so it wins.

I found this statement based on your suggestion.
I testing about it for check if it's cover all my case.

SELECT * FROM T4
WHERE
(T4.K1 = '01'
OR
T4.K1 = ''
AND NOT EXISTS (SELECT k1,k2,k3,k4,k5 FROM T4 T4N WHERE
T4N.K1 = '01' AND T4N.K3='AA' and T4N.K4='KK' AND T4N.K5 = T4.K5 )
)
AND
(T4.K2 = 'User1'
OR
T4.K2 = (SELECT T2.Group FROM T2 WHERE 'User1' = T2.MyUser )
AND NOT EXISTS (SELECT k1,k2,k3,k4,k5 FROM T4 T4N WHERE
T4N.K2 = 'User1' AND T4N.K3='AA' and T4N.K4='KK' AND T4N.K5 = T4.K5)
OR
T4.K2 = ''
AND NOT EXISTS (
SELECT k1,k2,k3,k4,k5 FROM T4 T4N LEFT OUTER JOIN T2 T2N
ON T4N.K2 = T2N.Group
WHERE 'User1' IN (T4N.K2, T2N.MyUser)
AND T4N.K3='AA' and T4N.K4='KK' AND T4N.K5 = T4.K5
)
OR
T4.K2 = ''
AND NOT EXISTS (
SELECT k1,k2,k3,k4,k5 FROM T4 T4N LEFT OUTER JOIN T2 T2N
ON T4N.K2 = T2N.Group
WHERE 'User1' IN (T4N.K2, T2N.MyUser)
AND T4N.K3='AA' and T4N.K4='KK' AND T4N.K5 = T4.K5 and
T4N.K1 = T4.K1
)
) AND K3='AA' and K4='KK' ;

I hope this is correct... and usefull to other people.

I replace SELECT * from inner exists clause to some columns to better
performance.. I think i can also use only 1 column .