1

Topic: Sampling of the conditional data of two tables

Hello
Help to understand please, I struggle with an output of unique fields from one table with their title from another
There are two tables PAYFINPLAN (contains the information on all payments - ten thousand records), CLASSIFY (decryption of the codes - too some thousand).
It is necessary to select the unique codes of a type of payment and its name
I use request of a type:

SELECT DISTINCT CODE8
FROM BUDGET_MASTER.PAYFINPLAN
WHERE ("YEAR" = ' 2017 ')

The unique codes of types of payments quickly enough but when I try to connect two tables that sampling are selected happens very slowly within one minute.

SELECT DISTINCT PAYFINPLAN.CODE8 AS CODE, CLASSIFY.NAME
FROM BUDGET_MASTER.PAYFINPLAN PAYFINPLAN INNER JOIN
BUDGET_MASTER.CLASSIFY CLASSIFY ON PAYFINPLAN.CODE8 = CLASSIFY.CODE AND PAYFINPLAN. "YEAR" = CLASSIFY. "YEAR"
WHERE (PAYFINPLAN. "YEAR" = ' 2017 ') AND (CLASSIFY.TREE = ' 8 ')

Thought to make, something like the virtual table with depositing of the data from the first request, and then to fulfill a sheaf with the table of names, but something and did not achieve working result.
Thanks.

2

Re: Sampling of the conditional data of two tables

Vladimir_S_7178;
Here it is possible to play about long:

SELECT DISTINCT CLASSIFY.CODE AS CODE, CLASSIFY.NAME
FROM BUDGET_MASTER.PAYFINPLAN PAYFINPLAN;
BUDGET_MASTER.CLASSIFY CLASSIFY
WHERE PAYFINPLAN. "YEAR" = ' 2017'
AND PAYFINPLAN.CODE8 = CLASSIFY.CODE
AND PAYFINPLAN. "YEAR" = CLASSIFY. "YEAR"
AND CLASSIFY.TREE = ' 8'
>>> AND CLASSIFY. "YEAR" = ' 2017 ' <<<
SELECT DISTINCT CLASSIFY.CODE AS CODE, CLASSIFY.NAME
FROM BUDGET_MASTER.CLASSIFY CLASSIFY
WHERE CLASSIFY.TREE = ' 8'
AND (CLASSIFY.CODE, CLASSIFY. "YEAR") in (select PAYFINPLAN.CODE8, PAYFINPLAN. "YEAR"
from BUDGET_MASTER.PAYFINPLAN PAYFINPLAN where PAYFINPLAN. "YEAR" = ' 2017 ')
SELECT DISTINCT CLASSIFY.CODE AS CODE, CLASSIFY.NAME
FROM BUDGET_MASTER.CLASSIFY CLASSIFY
WHERE CLASSIFY.TREE = ' 8'
AND exists (select 1
from BUDGET_MASTER.PAYFINPLAN PAYFINPLAN
where PAYFINPLAN. "YEAR" = ' 2017'
AND PAYFINPLAN.CODE8 = CLASSIFY.CODE
AND PAYFINPLAN. "YEAR" = CLASSIFY. "YEAR")

Collected on knees, there can be syntactic errors.
Well and to check up types of fields concerning transmittable constants, i.e. fields TREE and "YEAR" should be char/varchar what to avoid implicit conversion.

3

Re: Sampling of the conditional data of two tables

MaximaXXL;
I do not understand, why at usage

SELECT CLASSIFY.CODE AS CODE, CLASSIFY.NAME
FROM BUDGET_MASTER.CLASSIFY CLASSIFY
WHERE (CLASSIFY.TREE = ' 8 ') AND (CLASSIFY. "YEAR" = ' 2017 ') AND (CLASSIFY.CODE IN (SELECT DISTINCT PAYFINPLAN.CODE8, FROM BUDGET_MASTER.PAYFINPLAN PAYFINPLAN where PAYFINPLAN. "YEAR" = ' 2017 '))

Enclosed SELECT returns all data from PAYFINPLAN, instead of unique.

4

Re: Sampling of the conditional data of two tables

Vladimir_S_7178 wrote:

MaximaXXL;
I do not understand, why at usage

SELECT CLASSIFY.CODE AS CODE, CLASSIFY.NAME
FROM BUDGET_MASTER.CLASSIFY CLASSIFY
WHERE (CLASSIFY.TREE = ' 8 ') AND (CLASSIFY. "YEAR" = ' 2017 ') AND (CLASSIFY.CODE IN (SELECT DISTINCT PAYFINPLAN.CODE8, FROM BUDGET_MASTER.PAYFINPLAN PAYFINPLAN where PAYFINPLAN. "YEAR" = ' 2017 '))

Enclosed SELECT returns all data from PAYFINPLAN, instead of unique.

Can EXISTS?

5

Re: Sampling of the conditional data of two tables

XMLer;
C Exists, runtime, about 25 seconds were really reduced

SELECT CODE, NAME
FROM BUDGET_MASTER.CLASSIFY CLASSIFY
WHERE (TREE = ' 8 ') AND EXISTS
(SELECT 1 AS EXPR1
FROM BUDGET_MASTER.PAYFINPLAN PAYFINPLAN
WHERE ("YEAR" = ' 2017 ') AND (CODE8 = CLASSIFY.CODE) AND ("YEAR" = CLASSIFY. "YEAR"))

6

Re: Sampling of the conditional data of two tables

MaximaXXL;
Thanks for examples, question dared, time to pair seconds was reduced, with it it is already possible to work.

SELECT CODE, NAME
FROM BUDGET_MASTER.CLASSIFY CLASSIFY
WHERE (TREE = ' 8 ') AND ("YEAR" = ' 2017 ') AND (NAME IS NOT NULL) AND EXISTS
(SELECT 1 AS EXPR1
FROM BUDGET_MASTER.PAYFINPLAN PAYFINPLAN
WHERE ("YEAR" = ' 2017 ') AND (CODE8 = CLASSIFY.CODE) AND ("YEAR" = CLASSIFY. "YEAR"))