SQL PUZZLE - CONCAT USE

ROGER SMITH

SQL PUZZLE - CONCAT USE
Hello:

Trying to construct SELECT statements and surround CHAR columns with quotes using CONCAT.
Best attempt gives ?CHARVAL?. Have to edit and change ? to '.
Any other way?

For example this is desired result but can't use ' with CONCAT so use ?

SELECT * FROM PDB2DBA.PART_TB WHERE PART = 123 AND SUB_PART = 1 AND REGION = 'REG1' AND GROUP = 'GRP1234' ;

Have to edit this string:
SELECT * FROM PDB2DBA.PART_TB WHERE PART = 123 AND SUB_PART = 1 AND REGION = ?REG1? AND GROUP = ?GRP1234? ;



--TEST CONCAT SQL
SELECT 'SELECT * FROM PDB2DBA.PART_TB WHERE PART = ', LTRIM(RTRIM(PART)), 'AND SUB_PART = ', LTRIM(RTRIM(SUB_PART)),
'AND REGION = ' CONCAT '?' CONCAT LTRIM(RTRIM(REGION)) CONCAT '?' CONCAT ' AND GROUP = ' CONCAT '?' CONCAT LTRIM(RTRIM(GROUP)) CONCAT '? ;'
FROM PDB2DBA.PART_TB ;

RESULTS
1 2 3 4 5
------------------------------------------- ------- --------------- - -------------------------------------------
SELECT * FROM PDB2DBA.PART_TB WHERE PART = 123 AND SUB_PART = 1 AND REGION = ?REG1? AND GROUP = ?GRP1234? ;
SELECT * FROM PDB2DBA.PART_TB WHERE PART = 22345 AND SUB_PART = 2 AND REGION = ?REG12? AND GROUP = ?GRP123? ;
SELECT * FROM PDB2DBA.PART_TB WHERE PART = 333456 AND SUB_PART = 3 AND REGION = ?REG123? AND GROUP = ?GRP12? ;
SELECT * FROM PDB2DBA.PART_TB WHERE PART = 4444567 AND SUB_PART = 4 AND REGION = ?REG1234? AND GROUP = ?GRP1? ;

PDB2DBA.PART_TB DATA:

REGION GROUP PART SUB_PART
REG1 GRP1234 123 1
REG12 GRP123 22345 2
REG123 GRP12 333456 3
REG1234 GRP1 4444567 4


CREATE TABLE
PDB2DBA.PART_TB
(
REGION CHAR(8) NOT NULL
FOR SBCS DATA
,"GROUP" CHAR(8) NOT NULL
FOR SBCS DATA
,"PART" INTEGER NOT NULL
,SUB_PART INTEGER
)



The information contained in this e-mail, and any attachment, is confidential and is intended solely for the use of the intended recipient. Access, copying or re-use of the e-mail or any attachment, or any information contained therein, by any other person is not authorized. If you are not the intended recipient please return the e-mail to the sender and delete it from your computer. Although we attempt to sweep e-mail and attachments for viruses, we do not guarantee that either are virus-free and accept no liability for any damage sustained as a result of viruses.

Please refer to https://disclaimer.bnymellon.com/eu.htm for certain disclosures relating to European legal entities. We take our data protection and privacy responsibilities seriously and our privacy notice explains how we collect, use and share personal information in the course of our business activities. It can be accessed at the privacy section of www.bnymellon.com.

Sam Baugh

SQL PUZZLE - CONCAT USE
(in response to ROGER SMITH)
Try using hex equivalent for the quote mark, x'7D' for EBCDIC, such as ..
'AND REGION = '||x'7D'||REG1234||x'7D' ...

On Wed, Jun 13, 2018 at 12:20 PM, Smith, Roger <[login to unmask email]> wrote:

> Hello:
>
>
>
> Trying to construct SELECT statements and surround CHAR columns with
> quotes using CONCAT.
>
> Best attempt gives ?CHARVAL?. Have to edit and change ? to ‘.
>
> Any other way?
>
>
>
> For example this is desired result but can’t use *‘* with CONCAT so
> use ?
>
>
>
> *SELECT * FROM PDB2DBA.PART_TB WHERE PART = 123 AND SUB_PART = 1 AND
> REGION = ‘REG1’ AND GROUP = ‘GRP1234’ ;*
>
>
>
> *Have to edit this string:*
>
> SELECT * FROM PDB2DBA.PART_TB WHERE PART = 123 AND SUB_PART = 1 AND
> REGION = ?REG1? AND GROUP = ?GRP1234? ;
>
>
>
>
>
>
>
> *--TEST CONCAT SQL *
>
> SELECT 'SELECT * FROM PDB2DBA.PART_TB WHERE PART = ', LTRIM(RTRIM(PART)), 'AND
> SUB_PART = ', LTRIM(RTRIM(SUB_PART)),
>
> 'AND REGION = ' CONCAT '?' CONCAT LTRIM(RTRIM(REGION)) CONCAT '?' CONCAT '
> AND GROUP = ' CONCAT '?' CONCAT LTRIM(RTRIM(GROUP)) CONCAT '? ;'
>
> FROM PDB2DBA.PART_TB ;
>
>
>
> *RESULTS*
>
> 1 2 3 4 5
>
> ------------------------------------------- ------- --------------- -
> -------------------------------------------
>
> SELECT * FROM PDB2DBA.PART_TB WHERE PART = 123 AND SUB_PART = 1 AND
> REGION = ?REG1? AND GROUP = ?GRP1234? ;
>
> SELECT * FROM PDB2DBA.PART_TB WHERE PART = 22345 AND SUB_PART = 2 AND
> REGION = ?REG12? AND GROUP = ?GRP123? ;
>
> SELECT * FROM PDB2DBA.PART_TB WHERE PART = 333456 AND SUB_PART = 3 AND
> REGION = ?REG123? AND GROUP = ?GRP12? ;
>
> SELECT * FROM PDB2DBA.PART_TB WHERE PART = 4444567 AND SUB_PART = 4 AND
> REGION = ?REG1234? AND GROUP = ?GRP1? ;
>
>
>
> *PDB2DBA.PART_TB DATA:*
>
>
>
> REGION GROUP PART SUB_PART
>
> REG1 GRP1234 123 1
>
> REG12 GRP123 22345 2
>
> REG123 GRP12 333456 3
>
> REG1234 GRP1 4444567 4
>
>
>
>
>
> CREATE TABLE
>
> PDB2DBA.PART_TB
>
> (
>
> REGION CHAR(8) NOT NULL
>
> FOR SBCS DATA
>
> ,"GROUP" CHAR(8) NOT NULL
>
> FOR SBCS DATA
>
> ,"PART" INTEGER NOT NULL
>
> ,SUB_PART INTEGER
>
> )
>
>
>
>
>
> The information contained in this e-mail, and any attachment, is
> confidential and is intended solely for the use of the intended recipient.
> Access, copying or re-use of the e-mail or any attachment, or any
> information contained therein, by any other person is not authorized. If
> you are not the intended recipient please return the e-mail to the sender
> and delete it from your computer. Although we attempt to sweep e-mail and
> attachments for viruses, we do not guarantee that either are virus-free and
> accept no liability for any damage sustained as a result of viruses.
>
> Please refer to https://disclaimer.bnymellon.com/eu.htm for certain
> disclosures relating to European legal entities. We take our data
> protection and privacy responsibilities seriously and our privacy notice
> explains how we collect, use and share personal information in the course
> of our business activities. It can be accessed at the privacy section of
> www.bnymellon.com.
>
> -----End Original Message-----
>

ROGER SMITH

SQL PUZZLE - CONCAT USE
(in response to Sam Baugh)
Thank you – got it. Hex.

--TEST CONCAT
SELECT 'SELECT * FROM PDB2DBA.PART_TB WHERE PART = ', LTRIM(RTRIM(PART)), 'AND SUB_PART = ', LTRIM(RTRIM(SUB_PART)),
'AND REGION = ',x'7D' CONCAT LTRIM(RTRIM(REGION))CONCAT x'7D',
'AND GROUP = ',x'7D' CONCAT LTRIM(RTRIM(GROUP))CONCAT x'7D' CONCAT ';'
FROM PDB2DBA.PART_TB ;

1 2 3 4 5 6 7 8
------------------------------------------- ------- --------------- - ------------- --------- ------------- ----------
SELECT * FROM PDB2DBA.PART_TB WHERE PART = 123 AND SUB_PART = 1 AND REGION = 'REG1' AND GROUP = 'GRP1234';
SELECT * FROM PDB2DBA.PART_TB WHERE PART = 22345 AND SUB_PART = 2 AND REGION = 'REG12' AND GROUP = 'GRP123';
SELECT * FROM PDB2DBA.PART_TB WHERE PART = 333456 AND SUB_PART = 3 AND REGION = 'REG123' AND GROUP = 'GRP12';
SELECT * FROM PDB2DBA.PART_TB WHERE PART = 4444567 AND SUB_PART = 4 AND REGION = 'REG1234' AND GROUP = 'GRP1';



From: Sam Baugh <[login to unmask email]>
Sent: Wednesday, June 13, 2018 1:50 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: SQL PUZZLE - CONCAT USE

Try using hex equivalent for the quote mark, x'7D' for EBCDIC, such as .. 'AND REGION = '||x'7D'||REG1234||x'7D' ...

On Wed, Jun 13, 2018 at 12:20 PM, Smith, Roger <[login to unmask email]<mailto:[login to unmask email]>> wrote:
Hello:

Trying to construct SELECT statements and surround CHAR columns with quotes using CONCAT.
Best attempt gives ?CHARVAL?. Have to edit and change ? to ‘.
Any other way?

For example this is desired result but can’t use ‘ with CONCAT so use ?

SELECT * FROM PDB2DBA.PART_TB WHERE PART = 123 AND SUB_PART = 1 AND REGION = ‘REG1’ AND GROUP = ‘GRP1234’ ;

Have to edit this string:
SELECT * FROM PDB2DBA.PART_TB WHERE PART = 123 AND SUB_PART = 1 AND REGION = ?REG1? AND GROUP = ?GRP1234? ;



--TEST CONCAT SQL
SELECT 'SELECT * FROM PDB2DBA.PART_TB WHERE PART = ', LTRIM(RTRIM(PART)), 'AND SUB_PART = ', LTRIM(RTRIM(SUB_PART)),
'AND REGION = ' CONCAT '?' CONCAT LTRIM(RTRIM(REGION)) CONCAT '?' CONCAT ' AND GROUP = ' CONCAT '?' CONCAT LTRIM(RTRIM(GROUP)) CONCAT '? ;'
FROM PDB2DBA.PART_TB ;

RESULTS
1 2 3 4 5
------------------------------------------- ------- --------------- - -------------------------------------------
SELECT * FROM PDB2DBA.PART_TB WHERE PART = 123 AND SUB_PART = 1 AND REGION = ?REG1? AND GROUP = ?GRP1234? ;
SELECT * FROM PDB2DBA.PART_TB WHERE PART = 22345 AND SUB_PART = 2 AND REGION = ?REG12? AND GROUP = ?GRP123? ;
SELECT * FROM PDB2DBA.PART_TB WHERE PART = 333456 AND SUB_PART = 3 AND REGION = ?REG123? AND GROUP = ?GRP12? ;
SELECT * FROM PDB2DBA.PART_TB WHERE PART = 4444567 AND SUB_PART = 4 AND REGION = ?REG1234? AND GROUP = ?GRP1? ;

PDB2DBA.PART_TB DATA:

REGION GROUP PART SUB_PART
REG1 GRP1234 123 1
REG12 GRP123 22345 2
REG123 GRP12 333456 3
REG1234 GRP1 4444567 4


CREATE TABLE
PDB2DBA.PART_TB
(
REGION CHAR(8) NOT NULL
FOR SBCS DATA
,"GROUP" CHAR(8) NOT NULL
FOR SBCS DATA
,"PART" INTEGER NOT NULL
,SUB_PART INTEGER
)



The information contained in this e-mail, and any attachment, is confidential and is intended solely for the use of the intended recipient. Access, copying or re-use of the e-mail or any attachment, or any information contained therein, by any other person is not authorized. If you are not the intended recipient please return the e-mail to the sender and delete it from your computer. Although we attempt to sweep e-mail and attachments for viruses, we do not guarantee that either are virus-free and accept no liability for any damage sustained as a result of viruses.

Please refer to https://disclaimer.bnymellon.com/eu.htm for certain disclosures relating to European legal entities. We take our data protection and privacy responsibilities seriously and our privacy notice explains how we collect, use and share personal information in the course of our business activities. It can be accessed at the privacy section of www.bnymellon.com http://www.bnymellon.com .
-----End Original Message-----

The information contained in this e-mail, and any attachment, is confidential and is intended solely for the use of the intended recipient. Access, copying or re-use of the e-mail or any attachment, or any information contained therein, by any other person is not authorized. If you are not the intended recipient please return the e-mail to the sender and delete it from your computer. Although we attempt to sweep e-mail and attachments for viruses, we do not guarantee that either are virus-free and accept no liability for any damage sustained as a result of viruses.

Please refer to https://disclaimer.bnymellon.com/eu.htm for certain disclosures relating to European legal entities. We take our data protection and privacy responsibilities seriously and our privacy notice explains how we collect, use and share personal information in the course of our business activities. It can be accessed at the privacy section of www.bnymellon.com.

James Campbell

SQL PUZZLE - CONCAT USE
(in response to ROGER SMITH)
I've mostly used double apostrophes
> 'AND REGION = ''' CONCAT LTRIM(RTRIM(REGION))CONCAT '''',
> 'AND GROUP  = ''' CONCAT LTRIM(RTRIM(GROUP))CONCAT ''''
note that there are three apostrophes after the = sign, and four at the end.

James Campbell

On 13 Jun 2018 at 18:57, Smith, Roger wrote:

>
> Thank you - got it. Hex.
>  
> --TEST CONCAT          
> SELECT 'SELECT * FROM PDB2DBA.PART_TB WHERE PART = ',
> LTRIM(RTRIM(PART)), 'AND SUB_PART = ', LTRIM(RTRIM(SUB_PART)),     
> 'AND REGION = ',x'7D' CONCAT LTRIM(RTRIM(REGION))CONCAT x'7D',
> 'AND GROUP  = ',x'7D' CONCAT LTRIM(RTRIM(GROUP))CONCAT x'7D'
> CONCAT ';'
> FROM PDB2DBA.PART_TB ;
>  
> 1                                           2       3               4 5             6         7             8
> ------------------------------------------- ------- --------------- - ------------- --------- ------------- ----------
> SELECT * FROM PDB2DBA.PART_TB WHERE PART =  123     AND SUB_PART =  1 AND REGION = 
> 'REG1'    AND GROUP  =  'GRP1234';
> SELECT * FROM PDB2DBA.PART_TB WHERE PART =  22345   AND SUB_PART =  2 AND REGION
> =  'REG12'   AND GROUP  =  'GRP123';
> SELECT * FROM PDB2DBA.PART_TB WHERE PART =  333456  AND SUB_PART =  3 AND
> REGION =  'REG123'  AND GROUP  =  'GRP12';
> SELECT * FROM PDB2DBA.PART_TB WHERE PART =  4444567 AND SUB_PART =  4 AND
> REGION =  'REG1234' AND GROUP  =  'GRP1';
>  
>  
>  
> From: Sam Baugh <[login to unmask email]>
> Sent: Wednesday, June 13, 2018 1:50 PM
> To: [login to unmask email]
> Subject: [DB2-L] - RE: SQL PUZZLE - CONCAT USE
>  
> Try using hex equivalent for the quote mark, x'7D' for EBCDIC, such as .. 'AND
> REGION = '||x'7D'||REG1234||x'7D' ...
>  
> On Wed, Jun 13, 2018 at 12:20 PM, Smith, Roger <[login to unmask email]> wrote:
> Hello:
>  
> Trying to construct SELECT statements and surround CHAR columns with quotes using
> CONCAT.
> Best  attempt gives  ?CHARVAL?.  Have to edit and change ? to `.
> Any other way?
>  
> For example this is desired result but can´t use  `   with CONCAT so use ?
>  
> SELECT * FROM PDB2DBA.PART_TB WHERE PART =  123     AND SUB_PART =  1 AND REGION =
> `REG1´ AND GROUP = `GRP1234´;
>  
> Have to edit this string:
> SELECT * FROM PDB2DBA.PART_TB WHERE PART =  123     AND SUB_PART =  1 AND REGION =
> ?REG1? AND GROUP = ?GRP1234? ;
>  
>  
>  
> --TEST CONCAT SQL
> SELECT'SELECT * FROM PDB2DBA.PART_TB WHERE PART = ', LTRIM(RTRIM (PART)),
> 'AND SUB_PART = ', LTRIM(RTRIM (SUB_PART)),   
> 'AND REGION = 'CONCAT'?'CONCATLTRIM(RTRIM (REGION)) CONCAT'?'CONCAT' AND
> GROUP = 'CONCAT'?'CONCATLTRIM(RTRIM (GROUP)) CONCAT'? ;'
> FROM PDB2DBA.PART_TB ;
>  
> RESULTS
> 1                                           2       3               4 5
> ------------------------------------------- ------- --------------- - -------------------------------------------
> SELECT * FROM PDB2DBA.PART_TB WHERE PART =  123     AND SUB_PART =  1 AND REGION =
> ?REG1? AND GROUP = ?GRP1234? ;
> SELECT * FROM PDB2DBA.PART_TB WHERE PART =  22345   AND SUB_PART =  2 AND REGION
> = ?REG12? AND GROUP = ?GRP123? ;
> SELECT * FROM PDB2DBA.PART_TB WHERE PART =  333456  AND SUB_PART =  3 AND
> REGION = ?REG123? AND GROUP = ?GRP12? ;
> SELECT * FROM PDB2DBA.PART_TB WHERE PART =  4444567 AND SUB_PART =  4 AND
> REGION = ?REG1234? AND GROUP = ?GRP1? ;
>  
> PDB2DBA.PART_TB DATA:
>  
> REGION  GROUP           PART    SUB_PART
> REG1    GRP1234          123           1
> REG12   GRP123         22345           2
> REG123  GRP12         333456           3
> REG1234 GRP1         4444567           4
>  
>  
> CREATE TABLE
>   PDB2DBA.PART_TB
>    (
>    REGION   CHAR(8) NOT NULL
>     FOR SBCS DATA
>   ,"GROUP"   CHAR(8) NOT NULL
>     FOR SBCS DATA
>   ,"PART"   INTEGER NOT NULL
>   ,SUB_PART   INTEGER
>    )
>  
>  
> The information contained in this e-mail, and any attachment, is confidential and is
> intended solely for the use of the intended recipient. Access, copying or re-use of the
> e-mail or any attachment, or any information contained therein, by any other person is not
> authorized. If you are not the intended recipient please return the e-mail to the sender and
> delete it from your computer. Although we attempt to sweep e-mail and attachments for
> viruses, we do not guarantee that either are virus-free and accept no liability for any
> damage sustained as a result of viruses.
>
> Please refer to https://disclaimer.bnymellon.com/eu.htm for certain disclosures relating to
> European legal entities. We take our data protection and privacy responsibilities seriously
> and our privacy notice explains how we collect, use and share personal information in the
> course of our business activities. It can be accessed at the privacy section of
> www.bnymellon.com.
> -----End Original Message-----
> The information contained in this e-mail, and any attachment, is confidential and is
> intended solely for the use of the intended recipient. Access, copying or re-use of the
> e-mail or any attachment, or any information contained therein, by any other person
> is not authorized. If you are not the intended recipient please return the e-mail to the
> sender and delete it from your computer. Although we attempt to sweep e-mail and
> attachments for viruses, we do not guarantee that either are virus-free and accept no
> liability for any damage sustained as a result of viruses.
>
> Please refer to https://disclaimer.bnymellon.com/eu.htm for certain disclosures
> relating to European legal entities. We take our data protection and privacy
> responsibilities seriously and our privacy notice explains how we collect, use and
> share personal information in the course of our business activities. It can be
> accessed at the privacy section of www.bnymellon.com.
>
>
> Site Links: View post online   View mailing list online   Start new thread via email   Unsubscribe from this mailing list   Manage your subscription  
>
> This email has been sent to: [login to unmask email]
> Faster data refresh is here! The long waits and babysitting of unload/load jobs is over.
> Contact
> ESAi to learn about BCV5 & XDM. Be a hero to users with fast on-demand test/QA data
> provisioning.See
> http://www.ESAIGroup.com/idug
>
> Use of this email content is governed by the terms of service at:
> http://www.idug.org/p/cm/ld/fid=2
>




---
This email has been checked for viruses by AVG.
https://www.avg.com

Michael Hannan

RE: SQL PUZZLE - CONCAT USE
(in response to ROGER SMITH)

In Reply to ROGER SMITH:

Hello:

Trying to construct SELECT statements and surround CHAR columns with quotes using CONCAT.
Best attempt gives ?CHARVAL?. Have to edit and change ? to '.
Any other way?

As James points out you can do literal Quotes. The SQL Reference Manual gives the details I am sure.

It is rather ugly though. So another alternative is to put in some other character as you have done and finally use the REPLACE function on each output row, to change that character to a Quote:

REPLACE(string-expression,  '?', '''')

four single quotes in a row, LOL, representing a literal quote.

Or: REPLACE(expression,'?', Q)

where column Q was already set to '''' somewhere in a nested table expression.
 

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Edited By:
Michael Hannan[Organization Members] @ Jun 14, 2018 - 11:36 AM (Europe/Berlin)
Michael Hannan[Organization Members] @ Jun 14, 2018 - 11:39 AM (Europe/Berlin)

ROGER SMITH

SQL PUZZLE - CONCAT USE
(in response to James Campbell)
Thank you - double apostrophes - passing this downstream to developers - hex may confuse , but I do like it

--TEST CONCAT USE ''''
SELECT 'SELECT * FROM PDB2DBA.PART_TB WHERE PART = ', LTRIM(RTRIM(PART)), 'AND SUB_PART = ', LTRIM(RTRIM(SUB_PART)),
'AND REGION = ''' CONCAT LTRIM(RTRIM(REGION))CONCAT '''',
'AND GROUP = ''' CONCAT LTRIM(RTRIM(GROUP))CONCAT '''' CONCAT ';'
FROM PDB2DBA.PART_TB ;


RESULT:

SELECT * FROM PDB2DBA.PART_TB WHERE PART = 123 AND SUB_PART = 1 AND REGION = 'REG1' AND GROUP = 'GRP1234';
SELECT * FROM PDB2DBA.PART_TB WHERE PART = 22345 AND SUB_PART = 2 AND REGION = 'REG12' AND GROUP = 'GRP123';
SELECT * FROM PDB2DBA.PART_TB WHERE PART = 333456 AND SUB_PART = 3 AND REGION = 'REG123' AND GROUP = 'GRP12';
SELECT * FROM PDB2DBA.PART_TB WHERE PART = 4444567 AND SUB_PART = 4 AND REGION = 'REG1234' AND GROUP = 'GRP1';


Roger Smith
Vice President/ Database Administrator
BNY Mellon Mainframe Database Services
Cell: 914-325-1671
[login to unmask email]<mailto:[login to unmask email]>
- Attitude is a little thing that makes a big difference-


From: James Campbell <[login to unmask email]>
Sent: Thursday, June 14, 2018 2:17 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: SQL PUZZLE - CONCAT USE

I've mostly used double apostrophes
> 'AND REGION = ''' CONCAT LTRIM(RTRIM(REGION))CONCAT '''',
> 'AND GROUP = ''' CONCAT LTRIM(RTRIM(GROUP))CONCAT ''''
note that there are three apostrophes after the = sign, and four at the end.

James Campbell

On 13 Jun 2018 at 18:57, Smith, Roger wrote:

>
> Thank you - got it. Hex.
>
> --TEST CONCAT
> SELECT 'SELECT * FROM PDB2DBA.PART_TB WHERE PART = ',
> LTRIM(RTRIM(PART)), 'AND SUB_PART = ', LTRIM(RTRIM(SUB_PART)),
> 'AND REGION = ',x'7D' CONCAT LTRIM(RTRIM(REGION))CONCAT x'7D',
> 'AND GROUP = ',x'7D' CONCAT LTRIM(RTRIM(GROUP))CONCAT x'7D'
> CONCAT ';'
> FROM PDB2DBA.PART_TB ;
>
> 1 2 3 4 5 6 7 8
> ------------------------------------------- ------- --------------- - ------------- --------- ------------- ----------
> SELECT * FROM PDB2DBA.PART_TB WHERE PART = 123 AND SUB_PART = 1 AND REGION =
> 'REG1' AND GROUP = 'GRP1234';
> SELECT * FROM PDB2DBA.PART_TB WHERE PART = 22345 AND SUB_PART = 2 AND REGION
> = 'REG12' AND GROUP = 'GRP123';
> SELECT * FROM PDB2DBA.PART_TB WHERE PART = 333456 AND SUB_PART = 3 AND
> REGION = 'REG123' AND GROUP = 'GRP12';
> SELECT * FROM PDB2DBA.PART_TB WHERE PART = 4444567 AND SUB_PART = 4 AND
> REGION = 'REG1234' AND GROUP = 'GRP1';
>
>
>
> From: Sam Baugh <[login to unmask email]<mailto:[login to unmask email]>>
> Sent: Wednesday, June 13, 2018 1:50 PM
> To: [login to unmask email]<mailto:[login to unmask email]>
> Subject: [DB2-L] - RE: SQL PUZZLE - CONCAT USE
>
> Try using hex equivalent for the quote mark, x'7D' for EBCDIC, such as .. 'AND
> REGION = '||x'7D'||REG1234||x'7D' ...
>
> On Wed, Jun 13, 2018 at 12:20 PM, Smith, Roger <[login to unmask email]<mailto:[login to unmask email]>> wrote:
> Hello:
>
> Trying to construct SELECT statements and surround CHAR columns with quotes using
> CONCAT.
> Best attempt gives ?CHARVAL?. Have to edit and change ? to '.
> Any other way?
>
> For example this is desired result but can't use ' with CONCAT so use ?
>
> SELECT * FROM PDB2DBA.PART_TB WHERE PART = 123 AND SUB_PART = 1 AND REGION =
> 'REG1' AND GROUP = 'GRP1234';
>
> Have to edit this string:
> SELECT * FROM PDB2DBA.PART_TB WHERE PART = 123 AND SUB_PART = 1 AND REGION =
> ?REG1? AND GROUP = ?GRP1234? ;
>
>
>
> --TEST CONCAT SQL
> SELECT'SELECT * FROM PDB2DBA.PART_TB WHERE PART = ', LTRIM(RTRIM (PART)),
> 'AND SUB_PART = ', LTRIM(RTRIM (SUB_PART)),
> 'AND REGION = 'CONCAT'?'CONCATLTRIM(RTRIM (REGION)) CONCAT'?'CONCAT' AND
> GROUP = 'CONCAT'?'CONCATLTRIM(RTRIM (GROUP)) CONCAT'? ;'
> FROM PDB2DBA.PART_TB ;
>
> RESULTS
> 1 2 3 4 5
> ------------------------------------------- ------- --------------- - -------------------------------------------
> SELECT * FROM PDB2DBA.PART_TB WHERE PART = 123 AND SUB_PART = 1 AND REGION =
> ?REG1? AND GROUP = ?GRP1234? ;
> SELECT * FROM PDB2DBA.PART_TB WHERE PART = 22345 AND SUB_PART = 2 AND REGION
> = ?REG12? AND GROUP = ?GRP123? ;
> SELECT * FROM PDB2DBA.PART_TB WHERE PART = 333456 AND SUB_PART = 3 AND
> REGION = ?REG123? AND GROUP = ?GRP12? ;
> SELECT * FROM PDB2DBA.PART_TB WHERE PART = 4444567 AND SUB_PART = 4 AND
> REGION = ?REG1234? AND GROUP = ?GRP1? ;
>
> PDB2DBA.PART_TB DATA:
>
> REGION GROUP PART SUB_PART
> REG1 GRP1234 123 1
> REG12 GRP123 22345 2
> REG123 GRP12 333456 3
> REG1234 GRP1 4444567 4
>
>
> CREATE TABLE
> PDB2DBA.PART_TB
> (
> REGION CHAR(8) NOT NULL
> FOR SBCS DATA
> ,"GROUP" CHAR(8) NOT NULL
> FOR SBCS DATA
> ,"PART" INTEGER NOT NULL
> ,SUB_PART INTEGER
> )
>
>
> The information contained in this e-mail, and any attachment, is confidential and is
> intended solely for the use of the intended recipient. Access, copying or re-use of the
> e-mail or any attachment, or any information contained therein, by any other person is not
> authorized. If you are not the intended recipient please return the e-mail to the sender and
> delete it from your computer. Although we attempt to sweep e-mail and attachments for
> viruses, we do not guarantee that either are virus-free and accept no liability for any
> damage sustained as a result of viruses.
>
> Please refer to https://disclaimer.bnymellon.com/eu.htm for certain disclosures relating to
> European legal entities. We take our data protection and privacy responsibilities seriously
> and our privacy notice explains how we collect, use and share personal information in the
> course of our business activities. It can be accessed at the privacy section of
> www.bnymellon.com http://www.bnymellon.com .
> -----End Original Message-----
> The information contained in this e-mail, and any attachment, is confidential and is
> intended solely for the use of the intended recipient. Access, copying or re-use of the
> e-mail or any attachment, or any information contained therein, by any other person
> is not authorized. If you are not the intended recipient please return the e-mail to the
> sender and delete it from your computer. Although we attempt to sweep e-mail and
> attachments for viruses, we do not guarantee that either are virus-free and accept no
> liability for any damage sustained as a result of viruses.
>
> Please refer to https://disclaimer.bnymellon.com/eu.htm for certain disclosures
> relating to European legal entities. We take our data protection and privacy
> responsibilities seriously and our privacy notice explains how we collect, use and
> share personal information in the course of our business activities. It can be
> accessed at the privacy section of www.bnymellon.com http://www.bnymellon.com .
>
>
> Site Links: View post online View mailing list online Start new thread via email Unsubscribe from this mailing list Manage your subscription
>
> This email has been sent to: [login to unmask email]<mailto:[login to unmask email]>
> Faster data refresh is here! The long waits and babysitting of unload/load jobs is over.
> Contact
> ESAi to learn about BCV5 & XDM. Be a hero to users with fast on-demand test/QA data
> provisioning.See
> http://www.ESAIGroup.com/idug
>
> Use of this email content is governed by the terms of service at:
> http://www.idug.org/p/cm/ld/fid=2
>



[https://ipmcdn.avast.com/images/icons/icon-envelope-tick-green-avg-v1.png] http://www.avg.com/email-signature?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=emailclient

Virus-free. www.avg.com http://www.avg.com/email-signature?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=emailclient


-----End Original Message-----

The information contained in this e-mail, and any attachment, is confidential and is intended solely for the use of the intended recipient. Access, copying or re-use of the e-mail or any attachment, or any information contained therein, by any other person is not authorized. If you are not the intended recipient please return the e-mail to the sender and delete it from your computer. Although we attempt to sweep e-mail and attachments for viruses, we do not guarantee that either are virus-free and accept no liability for any damage sustained as a result of viruses.

Please refer to https://disclaimer.bnymellon.com/eu.htm for certain disclosures relating to European legal entities. We take our data protection and privacy responsibilities seriously and our privacy notice explains how we collect, use and share personal information in the course of our business activities. It can be accessed at the privacy section of www.bnymellon.com.