SQL Question

Jobi M B225 Augustine

SQL Question
Hi List,

One of my developers has a very interesting problem as follows. We use OS
390 / DB2 V6.

His program runs in DB2O region and the following SQL goes against a table
(AYNS.ACCOUNT) in DB2P which is defined as an Alias in DB2O as
AYNS_A.ACCOUNT.

The SQL returns -811 since there are duplicate rows in the table with the
same Master No (2221335) . But the SQL returns only nulls to all the DCLGEN
variables in the SQL. The DCLGEN is declared against the table AYNS.ACCOUNT
which is in DB2P (shown below). Whether this could be because of cross
platform access using the ALIAS or something else !! Any comments will be
really appreciated.

Thanks in advance.

EXEC SQL
SELECT HMO_CD,
ACCT_NO,
ACCT_TYP_CD,
MASTER_NO,
SUB_MASTER_NO,
ACCT_END_DT2
INTO :DCLACCOUNT.HMO-CD
,:DCLACCOUNT.ACCT-NO
,:DCLACCOUNT.ACCT-TYP-CD
,:DCLACCOUNT.MASTER-NO
,:DCLACCOUNT.SUB-MASTER-NO
,:DCLACCOUNT.ACCT-END-DT2
FROM AYNS_A.ACCOUNT
WHERE MASTER_NO = :WS40-SRC-AGS-ACCT-7
END-EXEC.

DISPLAY 'MASTER-NO:' , WS40-SRC-AGS-ACCT-7
DISPLAY 'HMO-CD:', HMO-CD OF DCLACCOUNT
DISPLAY 'ACCT-NO:', ACCT-NO OF DCLACCOUNT
DISPLAY 'SUB-MASTER-NO:', SUB-MASTER-NO OF DCLACCOUNT
DISPLAY 'ACCT-END-DT2:', ACCT-END-DT2 OF DCLACCOUNT
DISPLAY 'ACCT-TYP-CD:', ACCT-TYP-CD OF DCLACCOUNT

MOVE SQLCODE TO WS10-SQL-CODE.

EVALUATE TRUE
WHEN SQL-OKAY
DISPLAY 'WHEN SQL-OKAY'
DISPLAY 'HMO-CD:', HMO-CD OF DCLACCOUNT
DISPLAY 'ACCT-NO:', ACCT-NO OF DCLACCOUNT
DISPLAY 'SUB-MASTER-NO:', SUB-MASTER-NO OF DCLACCOUNT
DISPLAY 'ACCT-END-DT2:', ACCT-END-DT2 OF DCLACCOUNT
DISPLAY 'ACCT-TYP-CD:', ACCT-TYP-CD OF DCLACCOUNT
CONTINUE
WHEN SQL-DUP
DISPLAY 'WHEN SQL-DUP'
DISPLAY 'HMO-CD:', HMO-CD OF DCLACCOUNT
DISPLAY 'ACCT-NO:', ACCT-NO OF DCLACCOUNT
DISPLAY 'SUB-MASTER-NO:', SUB-MASTER-NO OF DCLACCOUNT
DISPLAY 'ACCT-END-DT2:', ACCT-END-DT2 OF DCLACCOUNT
DISPLAY 'ACCT-TYP-CD:', ACCT-TYP-CD OF DCLACCOUNT
CONTINUE
WHEN SQL-NOT-FOUND
SET WS30-NATIONAL-NF-Y TO TRUE
DISPLAY 'AGS IS NOT CHMO ACCT '
DISPLAY ' CDS CLIENT ID = ' WS26-CLIENT-ID
DISPLAY ' MED-ACCT-NUM = ' WS05-CMF-ACCT-NUM
DISPLAY ' AGS-NUM = ' WS05-SRC-AGS-ACCT
MOVE SPACES TO WS26-ERR-PROSE
MOVE 'AGS IS NOT CHMO MASTER#' TO WS26-ERR-PROSE
PERFORM 8300-BUILD-WS26 THRU 8300-EXIT
PERFORM 9900-PROCESS-ERROR THRU 9900-EXIT
WHEN OTHER
MOVE WS01-PARA-7000 TO WS10-ERR-PARA
PERFORM 9990-SQL-ABEND THRU 9990-EXIT
END-EVALUATE.

Here is the Display from SYSOUT

MASTER-NO:2221335
HMO-CD:...
ACCT-NO:....
SUB-MASTER-NO:.......
ACCT-END-DT2:..........
ACCT-TYP-CD:..
WHEN SQL-DUP
HMO-CD:...
ACCT-NO:....
SUB-MASTER-NO:.......
ACCT-END-DT2:..........
ACCT-TYP-CD:..

DCLGEN of AYNS.ACCOUNT

EXEC SQL DECLARE AYNS.ACCOUNT TABLE
( HMO_CD CHAR(3) NOT NULL,
ACCT_NO CHAR(4) NOT NULL,
ACCT_NM CHAR(24) NOT NULL,
ACCT_TYP_CD CHAR(2) NOT NULL,
ACCT_MKT_REP_NM CHAR(24) NOT NULL,
.
.
.
.
) END-EXEC.
******************************************************************

* COBOL DECLARATION FOR TABLE AYNS.ACCOUNT *

******************************************************************

01 DCLACCOUNT.

10 HMO-CD PIC X(3).

10 ACCT-NO PIC X(4).

10 ACCT-NM PIC X(24).

10 ACCT-TYP-CD PIC X(2).

.
.
.
.

Thanks and best regards,

a Jobi Augustine
CIGNA Systems - ES IM &T
* Tel (860) 226-5466 (6-5466)
* Fax (860) 226-5344 (6-5344)
* Mail [login to unmask email] <mailto:[login to unmask email]>

Confidential, unpublished property of CIGNA. Do not duplicate or distribute.
Use and distribution limited solely to authorized personnel. © Copyright
2002 by CIGNA.




------------------------------------------------------------------------------
CONFIDENTIALITY NOTICE: If you have received this e-mail in error, please immediately notify the sender by e-mail at the address shown. This e-mail transmission may contain confidential information. This information is intended only for the use of the individual(s) or entity to whom it is intended even if addressed incorrectly. Please delete it from your files if you are not the intended recipient. Thank you for your compliance. © Copyright 2002 CIGNA

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++



Richard Simpson

Re: SQL Question
(in response to teldb2kals)
As far as I can tell, you are saying that the host variable contain nulls
after you complete the query. Is that correct ?

If that is the case, please be aware that -811 is an error, and the content
of returned host variable is undefined when an SQL statement ends with an
error (negative SQL code). Thus you are getting garbage back which, in your
case, happens to be nulls.

Regards

Richard Simpson
IBM Global Services Australia
Cumberland Forest, FF12, (02) 9354 4220
[login to unmask email]



"Augustine, Jobi M B225" <[login to unmask email]>@RYCI.COM> on 04/01/2002
11:07:09

Please respond to DB2 Data Base Discussion List <[login to unmask email]>

Sent by: DB2 Data Base Discussion List <[login to unmask email]>


To: [login to unmask email]
cc:
Subject: [DB2-L] SQL Question



Hi List,

One of my developers has a very interesting problem as follows. We use OS
390 / DB2 V6.

His program runs in DB2O region and the following SQL goes against a table
(AYNS.ACCOUNT) in DB2P which is defined as an Alias in DB2O as
AYNS_A.ACCOUNT.

The SQL returns -811 since there are duplicate rows in the table with the
same Master No (2221335) . But the SQL returns only nulls to all the DCLGEN
variables in the SQL. The DCLGEN is declared against the table AYNS.ACCOUNT
which is in DB2P (shown below). Whether this could be because of cross
platform access using the ALIAS or something else !! Any comments will be
really appreciated.

Thanks in advance.

EXEC SQL
SELECT HMO_CD,
ACCT_NO,
ACCT_TYP_CD,
MASTER_NO,
SUB_MASTER_NO,
ACCT_END_DT2
INTO :DCLACCOUNT.HMO-CD
,:DCLACCOUNT.ACCT-NO
,:DCLACCOUNT.ACCT-TYP-CD
,:DCLACCOUNT.MASTER-NO
,:DCLACCOUNT.SUB-MASTER-NO
,:DCLACCOUNT.ACCT-END-DT2
FROM AYNS_A.ACCOUNT
WHERE MASTER_NO = :WS40-SRC-AGS-ACCT-7
END-EXEC.

DISPLAY 'MASTER-NO:' , WS40-SRC-AGS-ACCT-7
DISPLAY 'HMO-CD:', HMO-CD OF DCLACCOUNT
DISPLAY 'ACCT-NO:', ACCT-NO OF DCLACCOUNT
DISPLAY 'SUB-MASTER-NO:', SUB-MASTER-NO OF DCLACCOUNT
DISPLAY 'ACCT-END-DT2:', ACCT-END-DT2 OF DCLACCOUNT
DISPLAY 'ACCT-TYP-CD:', ACCT-TYP-CD OF DCLACCOUNT

MOVE SQLCODE TO WS10-SQL-CODE.

EVALUATE TRUE
WHEN SQL-OKAY
DISPLAY 'WHEN SQL-OKAY'
DISPLAY 'HMO-CD:', HMO-CD OF DCLACCOUNT
DISPLAY 'ACCT-NO:', ACCT-NO OF DCLACCOUNT
DISPLAY 'SUB-MASTER-NO:', SUB-MASTER-NO OF DCLACCOUNT
DISPLAY 'ACCT-END-DT2:', ACCT-END-DT2 OF DCLACCOUNT
DISPLAY 'ACCT-TYP-CD:', ACCT-TYP-CD OF DCLACCOUNT
CONTINUE
WHEN SQL-DUP
DISPLAY 'WHEN SQL-DUP'
DISPLAY 'HMO-CD:', HMO-CD OF DCLACCOUNT
DISPLAY 'ACCT-NO:', ACCT-NO OF DCLACCOUNT
DISPLAY 'SUB-MASTER-NO:', SUB-MASTER-NO OF DCLACCOUNT
DISPLAY 'ACCT-END-DT2:', ACCT-END-DT2 OF DCLACCOUNT
DISPLAY 'ACCT-TYP-CD:', ACCT-TYP-CD OF DCLACCOUNT
CONTINUE
WHEN SQL-NOT-FOUND
SET WS30-NATIONAL-NF-Y TO TRUE
DISPLAY 'AGS IS NOT CHMO ACCT '
DISPLAY ' CDS CLIENT ID = ' WS26-CLIENT-ID
DISPLAY ' MED-ACCT-NUM = ' WS05-CMF-ACCT-NUM
DISPLAY ' AGS-NUM = ' WS05-SRC-AGS-ACCT
MOVE SPACES TO WS26-ERR-PROSE
MOVE 'AGS IS NOT CHMO MASTER#' TO WS26-ERR-PROSE
PERFORM 8300-BUILD-WS26 THRU 8300-EXIT
PERFORM 9900-PROCESS-ERROR THRU 9900-EXIT
WHEN OTHER
MOVE WS01-PARA-7000 TO WS10-ERR-PARA
PERFORM 9990-SQL-ABEND THRU 9990-EXIT
END-EVALUATE.

Here is the Display from SYSOUT

MASTER-NO:2221335
HMO-CD:...
ACCT-NO:....
SUB-MASTER-NO:.......
ACCT-END-DT2:..........
ACCT-TYP-CD:..
WHEN SQL-DUP
HMO-CD:...
ACCT-NO:....
SUB-MASTER-NO:.......
ACCT-END-DT2:..........
ACCT-TYP-CD:..

DCLGEN of AYNS.ACCOUNT

EXEC SQL DECLARE AYNS.ACCOUNT TABLE
( HMO_CD CHAR(3) NOT NULL,
ACCT_NO CHAR(4) NOT NULL,
ACCT_NM CHAR(24) NOT NULL,
ACCT_TYP_CD CHAR(2) NOT NULL,
ACCT_MKT_REP_NM CHAR(24) NOT NULL,
.
.
.
.
) END-EXEC.
******************************************************************

* COBOL DECLARATION FOR TABLE AYNS.ACCOUNT *

******************************************************************

01 DCLACCOUNT.

10 HMO-CD PIC X(3).

10 ACCT-NO PIC X(4).

10 ACCT-NM PIC X(24).

10 ACCT-TYP-CD PIC X(2).

.
.
.
.

Thanks and best regards,

a Jobi Augustine
CIGNA Systems - ES IM &T
* Tel (860) 226-5466 (6-5466)
* Fax (860) 226-5344 (6-5344)
* Mail [login to unmask email] <mailto:[login to unmask email]>

Confidential, unpublished property of CIGNA. Do not duplicate or
distribute.
Use and distribution limited solely to authorized personnel. © Copyright
2002 by CIGNA.




------------------------------------------------------------------------------

CONFIDENTIALITY NOTICE: If you have received this e-mail in error, please
immediately notify the sender by e-mail at the address shown. This e-mail
transmission may contain confidential information. This information is
intended only for the use of the individual(s) or entity to whom it is
intended even if addressed incorrectly. Please delete it from your files
if you are not the intended recipient. Thank you for your compliance. ©
Copyright 2002 CIGNA

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++


visit






Susan A Allen

Re: SQL Question
(in response to Jobi M B225 Augustine)
-----Original Message-----
From: Augustine, Jobi M B225 [mailto:[login to unmask email]
Sent: Thursday, January 03, 2002 4:07 PM
To: [login to unmask email]
Subject: SQL Question


Hi List,

One of my developers has a very interesting problem as follows. We use OS
390 / DB2 V6.

His program runs in DB2O region and the following SQL goes against a table
(AYNS.ACCOUNT) in DB2P which is defined as an Alias in DB2O as
AYNS_A.ACCOUNT.


This may have no bearing on the question; but when I have cross platform
code (say a COBOL program in test; using test data and test tables; but
accessing a production table for DB2 V5 / OS 390) - I use the fully
qualified table name for the table not in the test region.

Susan A



teldb2kals

Re: SQL Question
(in response to Susan A Allen)
Hi Jobi,

When u get duplicate rows (sqlcode -811), then a singleton select (as u
have coded) produces unpredictable o/p. (Usually, the host variables r
not changed). Please refer to the Application and Programming Guide
under "Using Host Variables".

U will have to code a cursor and fetch the rows for multiple rows.

Cheers,
Kals


-----Original Message-----
From: Augustine, Jobi M B225 [SMTP:[login to unmask email]
Sent: Friday, January 04, 2002 11:07 AM
To: [login to unmask email]
Subject: SQL Question

Hi List,

One of my developers has a very interesting problem as follows. We use
OS
390 / DB2 V6.

His program runs in DB2O region and the following SQL goes against a
table
(AYNS.ACCOUNT) in DB2P which is defined as an Alias in DB2O as
AYNS_A.ACCOUNT.

The SQL returns -811 since there are duplicate rows in the table with
the
same Master No (2221335) . But the SQL returns only nulls to all the
DCLGEN
variables in the SQL. The DCLGEN is declared against the table
AYNS.ACCOUNT
which is in DB2P (shown below). Whether this could be because of cross
platform access using the ALIAS or something else !! Any comments will
be
really appreciated.

Thanks in advance.


----------------
Powered by telstra.com



Sanjeev (CTS) S

Re: SQL Question
(in response to Richard Simpson)
Content of host variable can't be relied upon in case of -811. The sqlcode
is thrown to indicate the duplicate records. You should code the cursor and
check for duplicates.

With Best Regards,
Sanjeev

> -----Original Message-----
> From: Augustine, Jobi M B225 [SMTP:[login to unmask email]
> Sent: Friday, January 04, 2002 5:37 AM
> To: [login to unmask email]
> Subject: SQL Question
>
> Hi List,
>
> One of my developers has a very interesting problem as follows. We use OS
> 390 / DB2 V6.
>
> His program runs in DB2O region and the following SQL goes against a table
> (AYNS.ACCOUNT) in DB2P which is defined as an Alias in DB2O as
> AYNS_A.ACCOUNT.
>
> The SQL returns -811 since there are duplicate rows in the table with the
> same Master No (2221335) . But the SQL returns only nulls to all the
> DCLGEN
> variables in the SQL. The DCLGEN is declared against the table
> AYNS.ACCOUNT
> which is in DB2P (shown below). Whether this could be because of cross
> platform access using the ALIAS or something else !! Any comments will be
> really appreciated.
>
> Thanks in advance.
>
> EXEC SQL
> SELECT HMO_CD,
> ACCT_NO,
> ACCT_TYP_CD,
> MASTER_NO,
> SUB_MASTER_NO,
> ACCT_END_DT2
> INTO :DCLACCOUNT.HMO-CD
> ,:DCLACCOUNT.ACCT-NO
> ,:DCLACCOUNT.ACCT-TYP-CD
> ,:DCLACCOUNT.MASTER-NO
> ,:DCLACCOUNT.SUB-MASTER-NO
> ,:DCLACCOUNT.ACCT-END-DT2
> FROM AYNS_A.ACCOUNT
> WHERE MASTER_NO = :WS40-SRC-AGS-ACCT-7
> END-EXEC.
>
> DISPLAY 'MASTER-NO:' , WS40-SRC-AGS-ACCT-7
> DISPLAY 'HMO-CD:', HMO-CD OF DCLACCOUNT
> DISPLAY 'ACCT-NO:', ACCT-NO OF DCLACCOUNT
> DISPLAY 'SUB-MASTER-NO:', SUB-MASTER-NO OF DCLACCOUNT
> DISPLAY 'ACCT-END-DT2:', ACCT-END-DT2 OF DCLACCOUNT
> DISPLAY 'ACCT-TYP-CD:', ACCT-TYP-CD OF DCLACCOUNT
>
> MOVE SQLCODE TO WS10-SQL-CODE.
>
> EVALUATE TRUE
> WHEN SQL-OKAY
> DISPLAY 'WHEN SQL-OKAY'
> DISPLAY 'HMO-CD:', HMO-CD OF DCLACCOUNT
> DISPLAY 'ACCT-NO:', ACCT-NO OF DCLACCOUNT
> DISPLAY 'SUB-MASTER-NO:', SUB-MASTER-NO OF DCLACCOUNT
> DISPLAY 'ACCT-END-DT2:', ACCT-END-DT2 OF DCLACCOUNT
> DISPLAY 'ACCT-TYP-CD:', ACCT-TYP-CD OF DCLACCOUNT
> CONTINUE
> WHEN SQL-DUP
> DISPLAY 'WHEN SQL-DUP'
> DISPLAY 'HMO-CD:', HMO-CD OF DCLACCOUNT
> DISPLAY 'ACCT-NO:', ACCT-NO OF DCLACCOUNT
> DISPLAY 'SUB-MASTER-NO:', SUB-MASTER-NO OF DCLACCOUNT
> DISPLAY 'ACCT-END-DT2:', ACCT-END-DT2 OF DCLACCOUNT
> DISPLAY 'ACCT-TYP-CD:', ACCT-TYP-CD OF DCLACCOUNT
> CONTINUE
> WHEN SQL-NOT-FOUND
> SET WS30-NATIONAL-NF-Y TO TRUE
> DISPLAY 'AGS IS NOT CHMO ACCT '
> DISPLAY ' CDS CLIENT ID = ' WS26-CLIENT-ID
> DISPLAY ' MED-ACCT-NUM = ' WS05-CMF-ACCT-NUM
> DISPLAY ' AGS-NUM = ' WS05-SRC-AGS-ACCT
> MOVE SPACES TO WS26-ERR-PROSE
> MOVE 'AGS IS NOT CHMO MASTER#' TO WS26-ERR-PROSE
> PERFORM 8300-BUILD-WS26 THRU 8300-EXIT
> PERFORM 9900-PROCESS-ERROR THRU 9900-EXIT
> WHEN OTHER
> MOVE WS01-PARA-7000 TO WS10-ERR-PARA
> PERFORM 9990-SQL-ABEND THRU 9990-EXIT
> END-EVALUATE.
>
> Here is the Display from SYSOUT
>
> MASTER-NO:2221335
> HMO-CD:...
> ACCT-NO:....
> SUB-MASTER-NO:.......
> ACCT-END-DT2:..........
> ACCT-TYP-CD:..
> WHEN SQL-DUP
> HMO-CD:...
> ACCT-NO:....
> SUB-MASTER-NO:.......
> ACCT-END-DT2:..........
> ACCT-TYP-CD:..
>
> DCLGEN of AYNS.ACCOUNT
>
> EXEC SQL DECLARE AYNS.ACCOUNT TABLE
> ( HMO_CD CHAR(3) NOT NULL,
> ACCT_NO CHAR(4) NOT NULL,
> ACCT_NM CHAR(24) NOT NULL,
> ACCT_TYP_CD CHAR(2) NOT NULL,
> ACCT_MKT_REP_NM CHAR(24) NOT NULL,
> .
> .
> .
> .
> ) END-EXEC.
> ******************************************************************
>
> * COBOL DECLARATION FOR TABLE AYNS.ACCOUNT *
>
> ******************************************************************
>
> 01 DCLACCOUNT.
>
> 10 HMO-CD PIC X(3).
>
> 10 ACCT-NO PIC X(4).
>
> 10 ACCT-NM PIC X(24).
>
> 10 ACCT-TYP-CD PIC X(2).
>
> .
> .
> .
> .
>
> Thanks and best regards,
>
> a Jobi Augustine
> CIGNA Systems - ES IM &T
> * Tel (860) 226-5466 (6-5466)
> * Fax (860) 226-5344 (6-5344)
> * Mail [login to unmask email] <mailto:[login to unmask email]>
>
> Confidential, unpublished property of CIGNA. Do not duplicate or
> distribute.
> Use and distribution limited solely to authorized personnel. © Copyright
> 2002 by CIGNA.
>
>
>
>
> --------------------------------------------------------------------------
> ----
> CONFIDENTIALITY NOTICE: If you have received this e-mail in error, please
> immediately notify the sender by e-mail at the address shown. This e-mail
> transmission may contain confidential information. This information is
> intended only for the use of the individual(s) or entity to whom it is
> intended even if addressed incorrectly. Please delete it from your files
> if you are not the intended recipient. Thank you for your compliance. ©
> Copyright 2002 CIGNA
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
>
>
>
>



Roger Miller

Re: SQL Question
(in response to Sanjeev (CTS) S)
I have just one point to add to the other statements. With V7 we
implemented an option FETCH FIRST 1 ROW ONLY clause to change this from an
error into a technique that can be supported. It can also improve
performance a bit.

Roger Miller, DB2 for z/OS



Judy Kibler

SQL question
(in response to Roger Miller)
Hello all,

I know I have seen this question out here before, but I can't seem to come up
with the right substring to search on the archive! Forgive me for asking this
again!

I am trying to find the largest value of a certain column in a table. I am
using the order by clause (descending) to have the value come to the top of the
result set, but I only want to return the one value. How can I do this?

TIA,
Judy



Manas Dasgupta

Re: SQL question
(in response to Judy Kibler)
How about using MAX function as in select max(colname) from table;

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On Behalf Of
Judy Kibler
Sent: Monday, January 07, 2002 10:17 AM
To: [login to unmask email]
Subject: SQL question


Hello all,

I know I have seen this question out here before, but I can't seem to come
up
with the right substring to search on the archive! Forgive me for asking
this
again!

I am trying to find the largest value of a certain column in a table. I am
using the order by clause (descending) to have the value come to the top of
the
result set, but I only want to return the one value. How can I do this?

TIA,
Judy








Cindy Andersen

Re: SQL question
(in response to Manas Dasgupta)
Select MAX(column-name) from owner.table-name;

The MAX function will return only one value.
Depending on the size of your table and whether or not there is an index in
descending order on the table, this select can be very quick or very long.

Cindy

> -----Original Message-----
> From: Judy Kibler [SMTP:[login to unmask email]
> Sent: Monday, January 07, 2002 9:17 AM
> To: [login to unmask email]
> Subject: SQL question
>
> Hello all,
>
> I know I have seen this question out here before, but I can't seem to come
> up
> with the right substring to search on the archive! Forgive me for asking
> this
> again!
>
> I am trying to find the largest value of a certain column in a table. I
> am
> using the order by clause (descending) to have the value come to the top
> of the
> result set, but I only want to return the one value. How can I do this?
>
> TIA,
> Judy
>
>
>
>
>



Judy Woodfield

Re: SQL question
(in response to Cindy Andersen)
If I understand your question properly, this can be accomplished by the
following

SELECT MAX(column_name)
FROM table_name;

-----Original Message-----
From: Judy Kibler [mailto:[login to unmask email]
Sent: Monday, January 07, 2002 10:17 AM
To: [login to unmask email]
Subject: SQL question


Hello all,

I know I have seen this question out here before, but I can't seem to come
up
with the right substring to search on the archive! Forgive me for asking
this
again!

I am trying to find the largest value of a certain column in a table. I am
using the order by clause (descending) to have the value come to the top of
the
result set, but I only want to return the one value. How can I do this?

TIA,
Judy








John McKown

Re: SQL question
(in response to Judy Woodfield)
SELECT MAX(column_name)
FROM table
;

Put in any WHERE clause you may need.

----------------------------------------------------------------------
John McKown
Applications and Solutions Team
Healthaxis



> -----Original Message-----
> From: Judy Kibler [SMTP:[login to unmask email]
> Sent: Monday, January 07, 2002 9:17 AM
> To: [login to unmask email]
> Subject: SQL question
>
> Hello all,
>
> I know I have seen this question out here before, but I can't seem to come
> up
> with the right substring to search on the archive! Forgive me for asking
> this
> again!
>
> I am trying to find the largest value of a certain column in a table. I
> am
> using the order by clause (descending) to have the value come to the top
> of the
> result set, but I only want to return the one value. How can I do this?
>
> TIA,
> Judy
>
>
>
>
>



Steen Rasmussen

Re: SQL question
(in response to John McKown)
Remember that from DB2 V7 it does not matter whether you have both an
ASCENDING and DESCENDING index. Now you just need an index on the specific
column as the firstkey in order to get MAX or MIN value in a hurry.

Kind regards
Steen Rasmussen
Computer Associates



-----Original Message-----
From: Andersen, Cindy [mailto:[login to unmask email]
Sent: 7. januar 2002 16:50
To: [login to unmask email]
Subject: Re: SQL question


Select MAX(column-name) from owner.table-name;

The MAX function will return only one value.
Depending on the size of your table and whether or not there is an index in
descending order on the table, this select can be very quick or very long.

Cindy

> -----Original Message-----
> From: Judy Kibler [SMTP:[login to unmask email]
> Sent: Monday, January 07, 2002 9:17 AM
> To: [login to unmask email]
> Subject: SQL question
>
> Hello all,
>
> I know I have seen this question out here before, but I can't seem to come
> up
> with the right substring to search on the archive! Forgive me for asking
> this
> again!
>
> I am trying to find the largest value of a certain column in a table. I
> am
> using the order by clause (descending) to have the value come to the top
> of the
> result set, but I only want to return the one value. How can I do this?
>
> TIA,
> Judy
>
>
>
>
>