[V9 - z/OS] SQL Question

Satish Srikakulapu

[V9 - z/OS] SQL Question
Dear Esteemed Listers:

Environment:
z/OS - V 1.10
DB2 - V9.1 NFM


We have a requirement to write a SQL. The user wants to count the distinct number of customers present in each area.

Output will be like below:

REGION SUB-REGION COUNT (This is count of distinct customers present in the combination of sub-region)
REG-A SUB-1 10
REG-A SUB-2 20
REG-A SUB-1 13

The output is coming from only one table. The table structure is:

REGION CHAR(20)
SUB-REGION CHAR(20)
ID_1 INTEGER
ID_2 INTEGER
YEAR SMALLINT
MONTH SMALLINT

The distinct customer is a combination of ID_1 and ID_2.

Can somebody help me in writing this SQL?

Thanks in Advance.

Regards,
Satish Srikakulapu

--- NOTICE ---

This message is for the designated recipient only and may contain confidential, privileged or proprietary information. If you have received it in error, please notify the sender immediately and delete the original and any copy or printout. Unintended recipients are prohibited from making any other use of this e-mail. Although we have taken reasonable precautions to ensure no viruses are present in this e-mail, we accept no liability for any loss or damage arising from the use of this e-mail or attachments, or for any delay or errors or omissions in the contents which result from e-mail transmission.

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
** The best DB2 technical sessions in the world
** Independent, not-for-profit, User Run - the IDUG difference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Larry Kintisch

Re: [V9 - z/OS] SQL Question
(in response to Satish Srikakulapu)
Hi Satish
The Solution I'd try is:
SELECT REGION, SUB_REGION , Count(*) as "Count of Customers"
FROM...
WHERE ...
GROUP BY DIGITS(ID_1) Concat DIGITS(ID_2)
ORDER BY....;

Try it and let us know if that works for you.

Larry Kintisch, Pres. ABLE Information Services 845-353-0885

At 14:23 2011-02-11, you wrote:
>Dear Esteemed Listers:
>
>Environment:
>z/OS - V 1.10
>DB2 - V9.1 NFM
>
>
>We have a requirement to write a SQL. The user wants to count the
>distinct number of customers present in each area.
>
>Output will be like below:
>
>REGION SUB-REGION COUNT (This is count of distinct customers present
>in the combination of sub-region)
>REG-A SUB-1 10
>REG-A SUB-2 20
>REG-A SUB-1 13
>
>The output is coming from only one table. The table structure is:
>
>REGION CHAR(20)
>SUB-REGION CHAR(20)
>ID_1 INTEGER
>ID_2 INTEGER
>YEAR SMALLINT
>MONTH SMALLINT
>
>The distinct customer is a combination of ID_1 and ID_2.
>
>Can somebody help me in writing this SQL?
>
>Thanks in Advance.
>
>Regards,
>Satish Srikakulapu
>
>--- NOTICE ---
>
>This message is for the designated recipient only and may contain
>confidential, privileged or proprietary information. If you have
>received it in error, please notify the sender immediately and
>delete the original and any copy or printout. Unintended recipients
>are prohibited from making any other use of this e-mail. Although
>we have taken reasonable precautions to ensure no viruses are
>present in this e-mail, we accept no liability for any loss or
>damage arising from the use of this e-mail or attachments, or for
>any delay or errors or omissions in the contents which result from
>e-mail transmission.
>
>No virus found in this message.
>Checked by AVG - < http://www.avg.com > www.avg.com
>Version: 10.0.1204 / Virus Database: 1435/3436 - Release Date: 02/11/11
>
>
> < http://www.idug.org >
>Independent, not-for-profit, User Run - the IDUG difference!
>
>
>The IDUG DB2-L Listserv is only part of your membership in IDUG. If
>you are not already an IDUG member,
> < http://www.idug.org/register > please register here.

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
** The best DB2 technical sessions in the world
** Independent, not-for-profit, User Run - the IDUG difference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Larry Kintisch

Re: [V9 - z/OS] SQL Question
(in response to Larry Kintisch)
Hi Satish
The --corrected-- Solution I'd try is:
SELECT REGION, SUB_REGION , Count(*) as "Count of Customers"
FROM...
WHERE ...
GROUP BY REGION, SUB_REGION , DIGITS(ID_1) Concat DIGITS(ID_2)
ORDER BY....;

Try it and let us know if that works for you.

Larry Kintisch, Pres. ABLE Information Services 845-353-0885

At 14:23 2011-02-11, you wrote:
>Dear Esteemed Listers:
>
>Environment:
>z/OS - V 1.10
>DB2 - V9.1 NFM
>
>
>We have a requirement to write a SQL. The user wants to count the
>distinct number of customers present in each area.
>
>Output will be like below:
>
>REGION SUB-REGION COUNT (This is count of distinct customers present
>in the combination of sub-region)
>REG-A SUB-1 10
>REG-A SUB-2 20
>REG-A SUB-1 13
>
>The output is coming from only one table. The table structure is:
>
>REGION CHAR(20)
>SUB-REGION CHAR(20)
>ID_1 INTEGER
>ID_2 INTEGER
>YEAR SMALLINT
>MONTH SMALLINT
>
>The distinct customer is a combination of ID_1 and ID_2.
>
>Can somebody help me in writing this SQL?
>
>Thanks in Advance.
>
>Regards,
>Satish Srikakulapu
>
>--- NOTICE ---
>
>This message is for the designated recipient only and may contain
>confidential, privileged or proprietary information. If you have
>received it in error, please notify the sender immediately and
>delete the original and any copy or printout. Unintended recipients
>are prohibited from making any other use of this e-mail. Although
>we have taken reasonable precautions to ensure no viruses are
>present in this e-mail, we accept no liability for any loss or
>damage arising from the use of this e-mail or attachments, or for
>any delay or errors or omissions in the contents which result from
>e-mail transmission.
>
>No virus found in this message.
>Checked by AVG - < http://www.avg.com > www.avg.com
>Version: 10.0.1204 / Virus Database: 1435/3436 - Release Date: 02/11/11
>
>
> < http://www.idug.org >
>Independent, not-for-profit, User Run - the IDUG difference!
>
>
>The IDUG DB2-L Listserv is only part of your membership in IDUG. If
>you are not already an IDUG member,
> < http://www.idug.org/register > please register here.

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
** The best DB2 technical sessions in the world
** Independent, not-for-profit, User Run - the IDUG difference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Dinesh .

Re: [V9 - z/OS] SQL Question
(in response to Larry Kintisch)
Hello Satish:

SELECT REGION, SUB_REGION, COUNT(*)
FROM TABLE_NAME
GROUP BY REGION, SUB_REGION;

My boss isn't right, there are folks out there who have surnames similar to mine. It is not as 'Hawaiian' as he thinks it is.

-Dinesh Srikakolapu
DB2 DBA for z/OS
UCONN

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Satish Srikakulapu
Sent: Friday, February 11, 2011 2:23 PM
To: [login to unmask email]
Subject: [DB2-L] [V9 - z/OS] SQL Question

Dear Esteemed Listers:

Environment:
z/OS - V 1.10
DB2 - V9.1 NFM


We have a requirement to write a SQL. The user wants to count the distinct number of customers present in each area.

Output will be like below:

REGION SUB-REGION COUNT (This is count of distinct customers present in the combination of sub-region)
REG-A SUB-1 10
REG-A SUB-2 20
REG-A SUB-1 13

The output is coming from only one table. The table structure is:

REGION CHAR(20)
SUB-REGION CHAR(20)
ID_1 INTEGER
ID_2 INTEGER
YEAR SMALLINT
MONTH SMALLINT

The distinct customer is a combination of ID_1 and ID_2.

Can somebody help me in writing this SQL?

Thanks in Advance.

Regards,
Satish Srikakulapu

--- NOTICE ---

This message is for the designated recipient only and may contain confidential, privileged or proprietary information. If you have received it in error, please notify the sender immediately and delete the original and any copy or printout. Unintended recipients are prohibited from making any other use of this e-mail. Although we have taken reasonable precautions to ensure no viruses are present in this e-mail, we accept no liability for any loss or damage arising from the use of this e-mail or attachments, or for any delay or errors or omissions in the contents which result from e-mail transmission.

________________________________

[ http://www.idug.org/images/banners/idug_2011.gif ] < http://www.idug.org >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
** The best DB2 technical sessions in the world
** Independent, not-for-profit, User Run - the IDUG difference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Larry Kintisch

Re: [V9 - z/OS] SQL Question
(in response to Dinesh .)
Hi Satish
OOPS! The --re-corrected-- Solution I'd try is:
WITH TEMP1 as
(SELECT DISTINCT REGION, SUB_REGION , DIGITS(ID_1) Concat DIGITS(ID_2) AS CUST
FROM...
WHERE ...
)
SELECT REGION, SUB_REGION , Count(*) as "Count of Customers"
FROM TEMP1
WHERE ...
GROUP BY REGION, SUB_REGION
ORDER BY....;

Try it and let us know if that works for you.

Larry Kintisch, Pres. ABLE Information Services 845-353-0885

At 14:23 2011-02-11, you wrote:
>Dear Esteemed Listers:
>
>Environment:
>z/OS - V 1.10
>DB2 - V9.1 NFM
>
>
>We have a requirement to write a SQL. The user wants to count the
>distinct number of customers present in each area.
>
>Output will be like below:
>
>REGION SUB-REGION COUNT (This is count of distinct customers present
>in the combination of sub-region)
>REG-A SUB-1 10
>REG-A SUB-2 20
>REG-A SUB-1 13
>
>The output is coming from only one table. The table structure is:
>
>REGION CHAR(20)
>SUB-REGION CHAR(20)
>ID_1 INTEGER
>ID_2 INTEGER
>YEAR SMALLINT
>MONTH SMALLINT
>
>The distinct customer is a combination of ID_1 and ID_2.
>
>Can somebody help me in writing this SQL?
>
>Thanks in Advance.
>
>Regards,
>Satish Srikakulapu
>
>--- NOTICE ---
>
>This message is for the designated recipient only and may contain
>confidential, privileged or proprietary information. If you have
>received it in error, please notify the sender immediately and
>delete the original and any copy or printout. Unintended recipients
>are prohibited from making any other use of this e-mail. Although
>we have taken reasonable precautions to ensure no viruses are
>present in this e-mail, we accept no liability for any loss or
>damage arising from the use of this e-mail or attachments, or for
>any delay or errors or omissions in the contents which result from
>e-mail transmission.
>
>No virus found in this message.
>Checked by AVG - < http://www.avg.com > www.avg.com
>Version: 10.0.1204 / Virus Database: 1435/3436 - Release Date: 02/11/11
>
>
> < http://www.idug.org >
>Independent, not-for-profit, User Run - the IDUG difference!
>
>
>The IDUG DB2-L Listserv is only part of your membership in IDUG. If
>you are not already an IDUG member,
> < http://www.idug.org/register > please register here.

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
** The best DB2 technical sessions in the world
** Independent, not-for-profit, User Run - the IDUG difference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Satish Srikakulapu

Re: [V9 - z/OS] SQL Question
(in response to Larry Kintisch)
Hi Larry,

Thank you very much for the solution. Your solution worked perfectly.

Regards,
Satish Srikakulapu

-----Original Message-----
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Larry Kintisch
Sent: Friday, February 11, 2011 1:09 PM
To: [login to unmask email]
Subject: Re: [DB2-L] [V9 - z/OS] SQL Question

Hi Satish
OOPS! The --re-corrected-- Solution I'd try is:
WITH TEMP1 as
(SELECT DISTINCT REGION, SUB_REGION , DIGITS(ID_1) Concat DIGITS(ID_2) AS CUST
FROM...
WHERE ...
)
SELECT REGION, SUB_REGION , Count(*) as "Count of Customers"
FROM TEMP1
WHERE ...
GROUP BY REGION, SUB_REGION
ORDER BY....;

Try it and let us know if that works for you.

Larry Kintisch, Pres. ABLE Information Services 845-353-0885

At 14:23 2011-02-11, you wrote:
>Dear Esteemed Listers:
>
>Environment:
>z/OS - V 1.10
>DB2 - V9.1 NFM
>
>
>We have a requirement to write a SQL. The user wants to count the
>distinct number of customers present in each area.
>
>Output will be like below:
>
>REGION SUB-REGION COUNT (This is count of distinct customers present
>in the combination of sub-region)
>REG-A SUB-1 10
>REG-A SUB-2 20
>REG-A SUB-1 13
>
>The output is coming from only one table. The table structure is:
>
>REGION CHAR(20)
>SUB-REGION CHAR(20)
>ID_1 INTEGER
>ID_2 INTEGER
>YEAR SMALLINT
>MONTH SMALLINT
>
>The distinct customer is a combination of ID_1 and ID_2.
>
>Can somebody help me in writing this SQL?
>
>Thanks in Advance.
>
>Regards,
>Satish Srikakulapu
>
>--- NOTICE ---
>
>This message is for the designated recipient only and may contain
>confidential, privileged or proprietary information. If you have
>received it in error, please notify the sender immediately and
>delete the original and any copy or printout. Unintended recipients
>are prohibited from making any other use of this e-mail. Although
>we have taken reasonable precautions to ensure no viruses are
>present in this e-mail, we accept no liability for any loss or
>damage arising from the use of this e-mail or attachments, or for
>any delay or errors or omissions in the contents which result from
>e-mail transmission.
>
>No virus found in this message.
>Checked by AVG - < http://www.avg.com > www.avg.com
>Version: 10.0.1204 / Virus Database: 1435/3436 - Release Date: 02/11/11
>
>
> < http://www.idug.org >
>Independent, not-for-profit, User Run - the IDUG difference!
>
>
>The IDUG DB2-L Listserv is only part of your membership in IDUG. If
>you are not already an IDUG member,
> < http://www.idug.org/register > please register here.

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
** The best DB2 technical sessions in the world
** Independent, not-for-profit, User Run - the IDUG difference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv
--- NOTICE ---

This message is for the designated recipient only and may contain confidential, privileged or proprietary information. If you have received it in error, please notify the sender immediately and delete the original and any copy or printout. Unintended recipients are prohibited from making any other use of this e-mail. Although we have taken reasonable precautions to ensure no viruses are present in this e-mail, we accept no liability for any loss or damage arising from the use of this e-mail or attachments, or for any delay or errors or omissions in the contents which result from e-mail transmission.

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
** The best DB2 technical sessions in the world
** Independent, not-for-profit, User Run - the IDUG difference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

[login to unmask email]

Re: [V9 - z/OS] SQL Question
(in response to Satish Srikakulapu)
Maybe this is more simple:

SELECT REGION, SUBREGION,
COUNT (DISTINCT CONCAT(CHAR(ID1), CHAR(ID2)))
FROM TABQ
GROUP BY REGION, SUBREGION
ORDER BY REGION, SUBREGION
WITH UR;

Regards

-----Mensaje original-----
De: IDUG DB2-L [mailto:[login to unmask email] En nombre de Satish Srikakulapu
Enviado el: viernes, 11 de febrero de 2011 22:01
Para: [login to unmask email]
Asunto: Re: [DB2-L] [V9 - z/OS] SQL Question

Hi Larry,

Thank you very much for the solution. Your solution worked perfectly.

Regards,
Satish Srikakulapu

-----Original Message-----
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Larry Kintisch
Sent: Friday, February 11, 2011 1:09 PM
To: [login to unmask email]
Subject: Re: [DB2-L] [V9 - z/OS] SQL Question

Hi Satish
OOPS! The --re-corrected-- Solution I'd try is:
WITH TEMP1 as
(SELECT DISTINCT REGION, SUB_REGION , DIGITS(ID_1) Concat DIGITS(ID_2) AS CUST
FROM...
WHERE ...
)
SELECT REGION, SUB_REGION , Count(*) as "Count of Customers"
FROM TEMP1
WHERE ...
GROUP BY REGION, SUB_REGION
ORDER BY....;

Try it and let us know if that works for you.

Larry Kintisch, Pres. ABLE Information Services 845-353-0885

At 14:23 2011-02-11, you wrote:
>Dear Esteemed Listers:
>
>Environment:
>z/OS - V 1.10
>DB2 - V9.1 NFM
>
>
>We have a requirement to write a SQL. The user wants to count the
>distinct number of customers present in each area.
>
>Output will be like below:
>
>REGION SUB-REGION COUNT (This is count of distinct customers present
>in the combination of sub-region)
>REG-A SUB-1 10
>REG-A SUB-2 20
>REG-A SUB-1 13
>
>The output is coming from only one table. The table structure is:
>
>REGION CHAR(20)
>SUB-REGION CHAR(20)
>ID_1 INTEGER
>ID_2 INTEGER
>YEAR SMALLINT
>MONTH SMALLINT
>
>The distinct customer is a combination of ID_1 and ID_2.
>
>Can somebody help me in writing this SQL?
>
>Thanks in Advance.
>
>Regards,
>Satish Srikakulapu
>
>--- NOTICE ---
>
>This message is for the designated recipient only and may contain
>confidential, privileged or proprietary information. If you have
>received it in error, please notify the sender immediately and
>delete the original and any copy or printout. Unintended recipients
>are prohibited from making any other use of this e-mail. Although
>we have taken reasonable precautions to ensure no viruses are
>present in this e-mail, we accept no liability for any loss or
>damage arising from the use of this e-mail or attachments, or for
>any delay or errors or omissions in the contents which result from
>e-mail transmission.
>
>No virus found in this message.
>Checked by AVG - < http://www.avg.com > www.avg.com
>Version: 10.0.1204 / Virus Database: 1435/3436 - Release Date: 02/11/11
>
>
> < http://www.idug.org >
>Independent, not-for-profit, User Run - the IDUG difference!
>
>
>The IDUG DB2-L Listserv is only part of your membership in IDUG. If
>you are not already an IDUG member,
> < http://www.idug.org/register > please register here.

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
** The best DB2 technical sessions in the world
** Independent, not-for-profit, User Run - the IDUG difference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv
--- NOTICE ---

This message is for the designated recipient only and may contain confidential, privileged or proprietary information. If you have received it in error, please notify the sender immediately and delete the original and any copy or printout. Unintended recipients are prohibited from making any other use of this e-mail. Although we have taken reasonable precautions to ensure no viruses are present in this e-mail, we accept no liability for any loss or damage arising from the use of this e-mail or attachments, or for any delay or errors or omissions in the contents which result from e-mail transmission.

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
** The best DB2 technical sessions in the world
** Independent, not-for-profit, User Run - the IDUG difference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv
Banco de España - Aviso legal

Este mensaje, su contenido y cualquier fichero transmitido
con él está dirigido únicamente a su destinatario y es
confidencial. Por ello, se informa a quien lo reciba por error o
tenga conocimiento del mismo sin ser su destinatario, que la
información contenida en él es reservada y su uso no
autorizado. En tal caso le rogamos nos lo comunique
por la misma vía o por teléfono (+ 34 91 338 66 66), así como
que se abstenga de reproducir el mensaje mediante cualquier
medio o remitirlo o entregarlo a otra persona, procediendo a su
borrado de manera inmediata.

El emisor de este mensaje pertenece a la plantilla de una de las
empresas externas que prestan sus servicios para el Banco de
España. Esta empresa y/o, en su caso, el Banco de España se
reservan las acciones legales que les correspondan contra todo
tercero que acceda de forma ilegítima al contenido de cualquier
mensaje externo procedente de los mismos.

El Banco de España se reserva las acciones legales que le
correspondan contra todo tercero que acceda de forma
ilegítima al contenido de cualquier mensaje externo procedente
del mismo.

Para informacion y consultas visite la web
http://www.bde.es


Banco de España - Disclaimer
This message, its content and any file attached thereto is for
the intended recipient only and is confidential. If you have
received this e-mail in error or had access to it, you should
note that the information in it is private and any use thereof
is unauthorised. In such an event please notify us by e-mail or
by telephone (+ 34 91 338 66 66). Any reproduction of this
message by whatsoever means and any transmission or dissemination
thereof to other persons is prohibited. It must be deleted
immediately from your system.

The sender of this message belongs to the staff of one of the
external companies that provide its services to the Banco de
España. This company and/or the Banco de España, as appropriate,
reserves the right to take legal action against any persons
unlawfully gaining access to the content of any external
message it has emitted.

For additional information, please visit our website
http://www.bde.es

_____________________________________________________________________
* IDUG EMEA * Prague, Czech Republic * 14-18 November 2011 * http://IDUG.ORG/EMEA *
* If you are going to attend only one conference this year, this is it! *
_____________________________________________________________________
http://www.IDUG.org/mentor
Mentoring should be a rewarding experience for everyone...
IDUG is offering up to 80% off when you both come to the conference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv