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