SELECT With Subselect

Gary Hassani

SELECT With Subselect

Hello.   May I ask a question to the list please.

Could you give me some guidance why this fails and the subsequent SELECTS do not fail? 

 

***INPUT STATEMENT: 
 SELECT * 
FROM $TRAVERS
WHERE DEPTH_FIRST LIKE
(SELECT (DEPTH_FIRST || '%') AS DERIVED_WILD
FROM $TRAVERS
WHERE ATRNODN = 'GROUP1');
SQLERROR ON SELECT COMMAND, PREPARE FUNCTION
RESULT OF SQL STATEMENT:
DSNT408I SQLCODE = -199, ERROR: ILLEGAL USE OF KEYWORD AS. TOKEN OVER CONCAT || ) WAS EXPECTED
DSNT418I SQLSTATE = 42601 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNHPARS SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 2 0 0 -1 76 506 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'00000002' X'00000000' X'00000000' X'FFFFFFFF' X'0000004C' X'000001FA' SQL DIAGNOSTIC
INFORMATION

But this works:
 SELECT (DEPTH_FIRST || '%') AS DERIVED_WILD 
FROM $TRAVERS
WHERE ATRNODN = 'GROUP1';
+-------------------------------------------------------------------------------------------------------+
| DERIVED_WILD |
+-------------------------------------------------------------------------------------------------------+
1_| 1-000239% |
+-------------------------------------------------------------------------------------------------------+
SUCCESSFUL RETRIEVAL OF 1 ROW(S)
And this works:
 SELECT * 
FROM $TRAVERS
WHERE DEPTH_FIRST LIKE '1-000239%';
+------------------------------------------------------------------------------------------------------------------------
| DERIVED_LEVEL | DEPTH_FIRST |
+------------------------------------------------------------------------------------------------------------------------
1_| 2 | 1-000239 |
2_| 3 | 1-000239-000241 |
3_| 4 | 1-000239-000241-000245 |
4_| 4 | 1-000239-000241-000246 |
5_| 4 | 1-000239-000241-000247 |
6_| 4 | 1-000239-000241-000248 |
7_| 3 | 1-000239-000242 |
8_| 3 | 1-000239-000243 |
9_| 3 | 1-000239-000244 |
+------------------------------------------------------------------------------------------------------------------------

Thank you for your consideration

Isaac Yassin

SELECT With Subselect
(in response to Gary Hassani)
Missing bracket ?


(SELECT (DEPTH_FIRST || '%') AS DERIVED_WILD
should be

(SELECT (DEPTH_FIRST || '%') ) AS DERIVED_WILD


*Isaac *



On Tue, Jan 23, 2018 at 11:37 PM, Gary Hassani <[login to unmask email]> wrote:

> Hello. May I ask a question to the list please.
>
> Could you give me some guidance why this fails and the subsequent SELECTS
> do not fail?
>
>
>
> ***INPUT STATEMENT:
>
> SELECT *
> FROM $TRAVERS
> WHERE DEPTH_FIRST LIKE
> ​​
> (SELECT (DEPTH_FIRST || '%') AS DERIVED_WILD
> FROM $TRAVERS
> WHERE ATRNODN = 'GROUP1');
> SQLERROR ON SELECT COMMAND, PREPARE FUNCTION
> RESULT OF SQL STATEMENT:
> DSNT408I SQLCODE = -199, ERROR: ILLEGAL USE OF KEYWORD AS. TOKEN OVER CONCAT || ) WAS EXPECTED
> DSNT418I SQLSTATE = 42601 SQLSTATE RETURN CODE
> DSNT415I SQLERRP = DSNHPARS SQL PROCEDURE DETECTING ERROR
> DSNT416I SQLERRD = 2 0 0 -1 76 506 SQL DIAGNOSTIC INFORMATION
> DSNT416I SQLERRD = X'00000002' X'00000000' X'00000000' X'FFFFFFFF' X'0000004C' X'000001FA' SQL DIAGNOSTIC
> INFORMATION
>
> But this works:
>
> SELECT (DEPTH_FIRST || '%') AS DERIVED_WILD
> FROM $TRAVERS
> WHERE ATRNODN = 'GROUP1';
> +-------------------------------------------------------------------------------------------------------+
> | DERIVED_WILD |
> +-------------------------------------------------------------------------------------------------------+
> 1_| 1-000239% |
> +-------------------------------------------------------------------------------------------------------+
> SUCCESSFUL RETRIEVAL OF 1 ROW(S)
>
> And this works:
>
> SELECT *
> FROM $TRAVERS
> WHERE DEPTH_FIRST LIKE '1-000239%';
> +------------------------------------------------------------------------------------------------------------------------
> | DERIVED_LEVEL | DEPTH_FIRST |
> +------------------------------------------------------------------------------------------------------------------------
> 1_| 2 | 1-000239 |
> 2_| 3 | 1-000239-000241 |
> 3_| 4 | 1-000239-000241-000245 |
> 4_| 4 | 1-000239-000241-000246 |
> 5_| 4 | 1-000239-000241-000247 |
> 6_| 4 | 1-000239-000241-000248 |
> 7_| 3 | 1-000239-000242 |
> 8_| 3 | 1-000239-000243 |
> 9_| 3 | 1-000239-000244 |
> +------------------------------------------------------------------------------------------------------------------------
>
> *Thank you for your consideration*
>
> -----End Original Message-----
>

Gary Hassani

RE: SELECT With Subselect
(in response to Isaac Yassin)

Thank you, but looking at the subselect:

 (SELECT (DEPTH_FIRST || '%') AS DERIVED_WILD 
FROM $TRAVERS
WHERE ATRNODN = 'GROUP1');

Doesn't this end the bracket sir? ....   

 WHERE ATRNODN = 'GROUP1'); 

Isaac Yassin

SELECT With Subselect
(in response to Gary Hassani)
Correct.
It's missing apostrophes before and after the generated string.


*Isaac *



On Tue, Jan 23, 2018 at 11:56 PM, Gary Hassani <[login to unmask email]> wrote:

> Thank you, but looking at the subselect:
>
> (SELECT (DEPTH_FIRST || '%') AS DERIVED_WILD
> FROM $TRAVERS
> WHERE ATRNODN = 'GROUP1');
>
> Doesn't this end the bracket sir? ....
>
> WHERE ATRNODN = 'GROUP1'*)*;
>
>
> -----End Original Message-----
>

Kal Sub

RE: SELECT With Subselect
(in response to Gary Hassani)

From memory, that sort of query using LIKE on subquery seems to work on LUW but not on z/OS.

An alternative could be (may not be efficient depending on how big your table is) :

 

 SELECT A.*
 FROM $TRAVERS A

, $TRAVERS B

WHERE B.ATRNODN = 'GROUP1'

and substr(A.DEPTH_FIRST,1,LENGTH(B.DEPTH_FIRST)) = B.DEPTH_FIRST;

Regards

Kals

In Reply to Gary Hassani:

Hello.   May I ask a question to the list please.

Could you give me some guidance why this fails and the subsequent SELECTS do not fail? 

 

***INPUT STATEMENT: 
 SELECT * 
FROM $TRAVERS
WHERE DEPTH_FIRST LIKE
(SELECT (DEPTH_FIRST || '%') AS DERIVED_WILD
FROM $TRAVERS
WHERE ATRNODN = 'GROUP1');
SQLERROR ON SELECT COMMAND, PREPARE FUNCTION
RESULT OF SQL STATEMENT:
DSNT408I SQLCODE = -199, ERROR: ILLEGAL USE OF KEYWORD AS. TOKEN OVER CONCAT || ) WAS EXPECTED
DSNT418I SQLSTATE = 42601 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNHPARS SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 2 0 0 -1 76 506 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'00000002' X'00000000' X'00000000' X'FFFFFFFF' X'0000004C' X'000001FA' SQL DIAGNOSTIC
INFORMATION

But this works:
 SELECT (DEPTH_FIRST || '%') AS DERIVED_WILD 
FROM $TRAVERS
WHERE ATRNODN = 'GROUP1';
+-------------------------------------------------------------------------------------------------------+
| DERIVED_WILD |
+-------------------------------------------------------------------------------------------------------+
1_| 1-000239% |
+-------------------------------------------------------------------------------------------------------+
SUCCESSFUL RETRIEVAL OF 1 ROW(S)
And this works:
 SELECT * 
FROM $TRAVERS
WHERE DEPTH_FIRST LIKE '1-000239%';
+------------------------------------------------------------------------------------------------------------------------
| DERIVED_LEVEL | DEPTH_FIRST |
+------------------------------------------------------------------------------------------------------------------------
1_| 2 | 1-000239 |
2_| 3 | 1-000239-000241 |
3_| 4 | 1-000239-000241-000245 |
4_| 4 | 1-000239-000241-000246 |
5_| 4 | 1-000239-000241-000247 |
6_| 4 | 1-000239-000241-000248 |
7_| 3 | 1-000239-000242 |
8_| 3 | 1-000239-000243 |
9_| 3 | 1-000239-000244 |
+------------------------------------------------------------------------------------------------------------------------

Thank you for your consideration

Gary Hassani

RE: SELECT With Subselect
(in response to Kal Sub)

Kal,

Thank You sir...

This indeed is Z/os DB2.

Your alternative certainly does work

Do you, or others  :)    know what z/os rules I am breaking?   I just assumed I was missing some aspect of SQL syntax.

 

thanks again   

James Campbell

SELECT With Subselect
(in response to Gary Hassani)
Because you can't LIKE a calculated expression - only a string expression

https://www.ibm.com/support/knowledgecenter/en/SSEPEK_11.0.0/sqlref/src/tpc/db2z_likepr
edicate.html

Even
LIKE 'ABC' || '%'
will have problems.

James Campbell

On 23 Jan 2018 at 14:37, Gary Hassani wrote:

>
> Hello.   May I ask a question to the list please.
> Could you give me some guidance why this fails and the subsequent SELECTS do not fail? 
>  
>
> ***INPUT STATEMENT:
>
>
> SELECT *
> FROM $TRAVERS
> WHERE DEPTH_FIRST LIKE
> (SELECT (DEPTH_FIRST || '%') AS DERIVED_WILD
>
> FROM $TRAVERS
> WHERE ATRNODN = 'GROUP1');
> SQLERROR ON SELECT COMMAND, PREPARE FUNCTION
> RESULT OF SQL STATEMENT:
> DSNT408I SQLCODE = -199, ERROR: ILLEGAL USE OF KEYWORD AS. TOKEN OVER CONCAT || ) WAS EXPECTED
> DSNT418I SQLSTATE = 42601 SQLSTATE RETURN CODE
>
> DSNT415I SQLERRP = DSNHPARS SQL PROCEDURE DETECTING ERROR
> DSNT416I SQLERRD = 2 0 0 -1 76 506 SQL DIAGNOSTIC INFORMATION
> DSNT416I SQLERRD
> = X'00000002' X'00000000' X'00000000' X'FFFFFFFF' X'0000004C' X'000001FA' SQL DIAGNOSTIC
> INFORMATION
>
>
> But this works:
>
> SELECT (DEPTH_FIRST || '%') AS DERIVED_WILD
> FROM $TRAVERS
> WHERE ATRNODN = 'GROUP1';
> +-------------------------------------------------------------------------------------------------------+
> | DERIVED_WILD
> |
> +-------------------------------------------------------------------------------------------------------+
> 1_| 1-000239% |
> +-------------------------------------------------------------------------------------------------------+
> SUCCESSFUL
> RETRIEVAL OF 1 ROW(S)
>
> And this works:
>
> SELECT *
> FROM $TRAVERS
> WHERE DEPTH_FIRST LIKE '1-000239%';
> +------------------------------------------------------------------------------------------------------------------------
> | DERIVED_LEVEL
> | DEPTH_FIRST |
> +------------------------------------------------------------------------------------------------------------------------
> 1_| 2 | 1-000239 |
> 2_| 3 | 1-000239-000241 |
> 3_| 4 |
> 1-000239-000241-000245 |
> 4_| 4 | 1-000239-000241-000246 |
> 5_| 4 | 1-000239-000241-000247 |
> 6_| 4 | 1-000239-000241-000248 |
> 7_| 3 | 1-000239-000242 |
> 8_| 3 | 1-000239-000243
> |
> 9_| 3 | 1-000239-000244 |
> +------------------------------------------------------------------------------------------------------------------------
>
> Thank you for your consideration
>
>

Gary Hassani

RE: SELECT With Subselect
(in response to James Campbell)

Ah I see that now:  

  • An expression that concatenates (using CONCAT or ||) any of the above

 

Thank you.... I am grateful to you for pointing that out sir

Michael Hannan

RE: SELECT With Subselect
(in response to James Campbell)

James,

Not quite right. There is no difference between a string expression and a calculated expression (returning Char string). LIKE with restricted constant expressions are allowed.

LIKE is a slightly under-privileged predicate in terms of Optimizer handling, for things like Transitive closure, predicate pushdown, etc. However needs to be a constant value to enable Optimizer to calculate any type of reasonable filter factor at runtime for Dynamic SQLs.

To quote the SQL manual:

"LIKE pattern-expression
An expression that specifies the pattern of characters to be matched. The expression can be specified by any one of the following:

A constant
*
A special register
A variable
*
A scalar function whose arguments are any of the above (though nested function invocations cannot be used)
* An array element specification
*
A CAST specification whose arguments are any of the above
*
An expression that concatenates (using CONCAT or ||) any of the above"
Etc.

So Concatenate is allowed. In fact I have used it often. An Expression based on a Table column or calculated value that varies for the duration of the Query is not allowed.

So if there was a need to do COL1 LIKE COL2 concat '%', the coder could rewrite it as:

COL1 BETWEEN COL2 AND COL2 concat X'FF'
which will work assuming that high value character X'FF" never actually occurs.

Alternative:

AND  COL1 >= COL2
AND COL1 < COL2 concat X'00'
assuming real values don't have X'00'.

In Db2 z there is no point to store a complex LIKE pattern in a table column, unless prepared to read it into a Constant variable first and then use that in a following search SQL, with the single value.


In Reply to James Campbell:

Because you can't LIKE a calculated expression - only a string expression

https://www.ibm.com/support/knowledgecenter/en/SSEPEK_11.0.0/sqlref/src/tpc/db2z_likepr
edicate.html

Even
LIKE 'ABC' || '%'
will have problems.

James Campbell

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Edited By:
Michael Hannan[Organization Members] @ Jan 25, 2018 - 05:03 AM (Europe/Berlin)
Michael Hannan[Organization Members] @ Jan 25, 2018 - 05:07 AM (Europe/Berlin)
Michael Hannan[Organization Members] @ Jan 25, 2018 - 05:10 AM (Europe/Berlin)
Michael Hannan[Organization Members] @ Jan 25, 2018 - 05:11 AM (Europe/Berlin)
Michael Hannan[Organization Members] @ Jan 25, 2018 - 05:16 AM (Europe/Berlin)

Gary Hassani

RE: SELECT With Subselect
(in response to Michael Hannan)

Thank You all (most recently Michael),

Putting shoe leather to these comments may I ask a follow up question? :

Here are the two queries I have tested successfully based upon the above discussion:

Option #1

 SELECT A.* 
FROM $TRAVERS A,
$TRAVERS B
WHERE B.ATRNODN = 'GROUP1'
AND SUBSTR(A.DEPTH_FIRST,1,LENGTH(B.DEPTH_FIRST))
= B.DEPTH_FIRST;

 

Option #2

SELECT A.* 
FROM $TRAVERS A,
$TRAVERS B
WHERE B.ATRNODN = 'GROUP1'
AND A.DEPTH_FIRST BETWEEN B.DEPTH_FIRST AND
B.DEPTH_FIRST CONCAT X'FF';

 

Assuming ATRNODN is the primary key for $TRAVERS.

Which of these queries are likely to perform better?  

OR

can Micheal s suggestion of:  "COL1 BETWEEN COL2 AND COL2 concat X'FF' "

Be incorporated into a subselect instead of using the temporary result tables A & B ?

 

BTW...

ATRNODN is PRIMARY KEY

 

DEPTH_FIRST is UNIQUE

 

I am grateful for the discussion.  thank you 

alain pary

RE: SELECT With Subselect
(in response to Gary Hassani)

Hello, 

you can certainly make an explain of each statement and after that check in the dsn_statemnt_table the estimated cost of each statement. 

or you can execute each query and after check the the number of cpu and getpages in the DSN_STATEMENT_CACHE_TABLE , to be more accurate execute each query 3 or 4 times to eliminates the i/o need to populate the bufferpool.

 

regards 

 

Alain

Michael Hannan

RE: SELECT With Subselect
(in response to Gary Hassani)



In Reply to Gary Hassani:

Here are the two queries I have tested successfully based upon the above discussion:

Option #1

 SELECT A.* 
FROM $TRAVERS A,
$TRAVERS B
WHERE B.ATRNODN = 'GROUP1'
AND SUBSTR(A.DEPTH_FIRST,1,LENGTH(B.DEPTH_FIRST))
= B.DEPTH_FIRST;

 

Option #2

SELECT A.* 
FROM $TRAVERS A,
$TRAVERS B
WHERE B.ATRNODN = 'GROUP1'
AND A.DEPTH_FIRST BETWEEN B.DEPTH_FIRST AND
B.DEPTH_FIRST CONCAT X'FF';

 

Assuming ATRNODN is the primary key for $TRAVERS.

Which of these queries are likely to perform better?  

OR

can Micheal s suggestion of:  "COL1 BETWEEN COL2 AND COL2 concat X'FF' "

Be incorporated into a subselect instead of using the temporary result tables A & B ?

I realised a mistake. Needs to be:

A.DEPTH_FIRST BETWEEN B.DEPTH_FIRST
      AND SUBSTR(STRIP(B.DEPTH_FIRST) concat X'FF', 1, max_length)

Just making sure that X'FF' is added to right place and string is not too long (as can't remember if would be stage 2 when too long). Replace max_length with a literal integer giving the max length of the string DEPTH_FIRST.

Now Option 2 is good, without any subqueries, assuming visit to table B first. Option 1 is not.

If table A visited first in join, access path and performance will not be good. Naturally the Optimizer is not likely to choose the bad way for Option 2, especially as B should only return 1 row according to your primary key. LOL

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Edited By:
Michael Hannan[Organization Members] @ Jan 27, 2018 - 02:26 PM (Europe/Berlin)
Michael Hannan[Organization Members] @ Jan 27, 2018 - 02:29 PM (Europe/Berlin)
Michael Hannan[Organization Members] @ Jan 27, 2018 - 02:33 PM (Europe/Berlin)
Michael Hannan[Organization Members] @ Jan 27, 2018 - 02:34 PM (Europe/Berlin)

Michael Hannan

RE: SELECT With Subselect
(in response to Michael Hannan)

Further correction (which comes from not testing it):

A.DEPTH_FIRST BETWEEN B.DEPTH_FIRST
      AND STRIP(SUBSTR(STRIP(B.DEPTH_FIRST) concat X'FF', 1, max_length))

I had not needed the extra precautions when I used the BETWEEN approach before because situation was a bit different.


 
In Reply to Michael Hannan:



In Reply to Gary Hassani:

Here are the two queries I have tested successfully based upon the above discussion:

Option #1

 SELECT A.* 
FROM $TRAVERS A,
$TRAVERS B
WHERE B.ATRNODN = 'GROUP1'
AND SUBSTR(A.DEPTH_FIRST,1,LENGTH(B.DEPTH_FIRST))
= B.DEPTH_FIRST;

 

Option #2

SELECT A.* 
FROM $TRAVERS A,
$TRAVERS B
WHERE B.ATRNODN = 'GROUP1'
AND A.DEPTH_FIRST BETWEEN B.DEPTH_FIRST AND
B.DEPTH_FIRST CONCAT X'FF';

 

Assuming ATRNODN is the primary key for $TRAVERS.

Which of these queries are likely to perform better?  

OR

can Micheal s suggestion of:  "COL1 BETWEEN COL2 AND COL2 concat X'FF' "

Be incorporated into a subselect instead of using the temporary result tables A & B ?

I realised a mistake. Needs to be:

A.DEPTH_FIRST BETWEEN B.DEPTH_FIRST
      AND SUBSTR(STRIP(B.DEPTH_FIRST) concat X'FF', 1, max_length)

Just making sure that X'FF' is added to right place and string is not too long (as can't remember if would be stage 2 when too long). Replace max_length with a literal integer giving the max length of the string DEPTH_FIRST.

Now Option 2 is good, without any subqueries, assuming visit to table B first. Option 1 is not.

If table A visited first in join, access path and performance will not be good. Naturally the Optimizer is not likely to choose the bad way for Option 2, especially as B should only return 1 row according to your primary key. LOL

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd


Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Edited By:
Michael Hannan[Organization Members] @ Jan 28, 2018 - 06:42 AM (Europe/Berlin)