SQL Query help

Tej Patil

SQL Query help
Hi,

Please look at the query below , could anybody please tell me is this a
valid query. When I execute this via spufi I get -4700 sql code. could
anybody help me in understand this. Thanks.
SELECT
MAX (EFF_DT) FROM PNY.VGEN_CD_DET B
WHERE B.MKT_ID = ?
AND B.GRP_CD = ?
AND B.VAL_CD = ? )
AND ((SELECT SUM(PAY_AMT) FROM
PNY.VPAYMENT WHERE MKT_ID ='RNY')>=1)
OPTIMIZE FOR 1 ROW;

Thanks,
TSP

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Rob Adams-Kane

Re: SQL Query help
(in response to Tej Patil)
If you've copied this accurately, it may simply be a matter of
mis-matched parenthesis.


Rob Adams-Kane


W.A. Hynes & Company, Inc.
(800) 823-1470
(707) 586-2222
fax (888) 562-1471
[login to unmask email]
www.waHco-it.com

This email, and any files attached, contains information intended only
for the use of the addressee and may include information that is
privileged, confidential or exempt from other disclosure under
applicable law. If you are not the intended recipient, you are hereby
notified that any dissemination, copying, printing or other use of this
email is prohibited. If you have received this email in error, please
delete it from you computer. A reply email notifying the sender of the
error would be greatly appreciated. Thank you.





________________________________

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of tsp l
Sent: Tuesday, January 16, 2007 4:49 PM
To: [login to unmask email]
Subject: [DB2-L] SQL Query help



Hi,

Please look at the query below , could anybody please tell me is this a
valid query. When I execute this via spufi I get -4700 sql code. could
anybody help me in understand this. Thanks.
SELECT

MAX (EFF_DT) FROM PNY.VGEN_CD_DET B
WHERE B.MKT_ID = ?
AND B.GRP_CD = ?
AND B.VAL_CD = ? )
AND ((SELECT SUM(PAY_AMT) FROM
PNY.VPAYMENT WHERE MKT_ID ='RNY')>=1)
OPTIMIZE FOR 1 ROW;

Thanks,
TSP
------------------------------------------------------------------------
--------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the
archives and home page at http://www.idugdb2-l.org/archives/db2-l.html.
From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Tej Patil

Re: SQL Query help
(in response to Rob Adams-Kane)
I am sorry, it was a typo. Below is the query again and I am getting the
same sql code as -4700


SELECT
MAX (EFF_DT) FROM PNY.VGEN_CD_DET B
WHERE B.MKT_ID = ?
AND B.GRP_CD = ?
AND B.VAL_CD = ?
AND ((SELECT SUM(PAY_AMT) FROM
PNY.VPAYMENT WHERE MKT_ID ='RNY')>=1)
OPTIMIZE FOR 1 ROW;

Thanks,
TSP


On 1/16/07, Rob Adams-Kane <[login to unmask email]> wrote:
>
> If you've copied this accurately, it may simply be a matter of
> mis-matched parenthesis.
>
>
> *Rob Adams-Kane**
> *
>
> W.A. Hynes & Company, Inc.
> (800) 823-1470
> (707) 586-2222
> fax (888) 562-1471
> [login to unmask email]
> www.waHco-it.com
>
> This email, and any files attached, contains information intended only for
> the use of the addressee and may include information that is privileged,
> confidential or exempt from other disclosure under applicable law. If you
> are not the intended recipient, you are hereby notified that any
> dissemination, copying, printing or other use of this email is prohibited.
> If you have received this email in error, please delete it from you
> computer. A reply email notifying the sender of the error would be greatly
> appreciated. Thank you.
>
>
>
>
> ------------------------------
> *From:* DB2 Data Base Discussion List [mailto:[login to unmask email] *On
> Behalf Of *tsp l
> *Sent:* Tuesday, January 16, 2007 4:49 PM
> *To:* [login to unmask email]
> *Subject:* [DB2-L] SQL Query help
>
>
>
> Hi,
>
> Please look at the query below , could anybody please tell me is this a
> valid query. When I execute this via spufi I get -4700 sql code. could
> anybody help me in understand this. Thanks.
> SELECT
> MAX (EFF_DT) FROM PNY.VGEN_CD_DET B
> WHERE B.MKT_ID = ?
> AND B.GRP_CD = ?
> AND B.VAL_CD = ? )
> AND ((SELECT SUM(PAY_AMT) FROM
> PNY.VPAYMENT WHERE MKT_ID ='RNY')>=1)
> OPTIMIZE FOR 1 ROW;
>
> Thanks,
> TSP
> ---------------------------------------------------------------------------------
> Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home
> page at http://www.idugdb2-l.org/archives/db2-l.html. From that page
> select "Join or Leave the list". The IDUG DB2-L FAQ is at
> http://www.idugdb2-l.org. The IDUG List Admins can be reached at
> [login to unmask email] Find out the latest on IDUG conferences
> at http://conferences.idug.org/index.cfm
> ---------------------------------------------------------------------------------
> Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home
> page at http://www.idugdb2-l.org/archives/db2-l.html. From that page
> select "Join or Leave the list". The IDUG DB2-L FAQ is at
> http://www.idugdb2-l.org. The IDUG List Admins can be reached at
> [login to unmask email] Find out the latest on IDUG conferences
> at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Dave Nance

Re: SQL Query help
(in response to Tej Patil)
I can't find any reference to a -4700 or even a 4700 in the manuals, so can't help you with that, but your query presents a few questions. I don't understand the need for that subselect as it has nothing to do with the rest of the query. Is some column from your VGEN_CD_DET table supposed to be joined to it? Is it possible that subselect would return more than 1 row? And could that query return a null value?

Dave Nance

tsp l <[login to unmask email]> wrote:
I am sorry, it was a typo. Below is the query again and I am getting the same sql code as -4700


SELECT
MAX (EFF_DT) FROM PNY.VGEN_CD_DET B
WHERE B.MKT_ID = ?
AND B.GRP_CD = ?
AND B.VAL_CD = ?
AND ((SELECT SUM(PAY_AMT) FROM
PNY.VPAYMENT WHERE MKT_ID ='RNY')>=1)
OPTIMIZE FOR 1 ROW;

Thanks,
TSP


On 1/16/07, Rob Adams-Kane <[login to unmask email]> wrote: If you've copied this accurately, it may simply be a matter of mis-matched parenthesis.

Rob Adams-Kane

W.A. Hynes & Company, Inc.
(800) 823-1470
(707) 586-2222
fax (888) 562-1471
[login to unmask email] -it.com
www.wa Hco-it.com
This email, and any files attached, contains information intended only for the use of the addressee and may include information that is privileged, confidential or exempt from other disclosure under applicable law. If you are not the intended recipient, you are hereby notified that any dissemination, copying, printing or other use of this email is prohibited. If you have received this email in error, please delete it from you computer. A reply email notifying the sender of the error would be greatly appreciated. Thank you.





---------------------------------
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf Of tsp l
Sent: Tuesday, January 16, 2007 4:49 PM
To: [login to unmask email]
Subject: [DB2-L] SQL Query help



Hi,
Please look at the query below , could anybody please tell me is this a valid query. When I execute this via spufi I get -4700 sql code. could anybody help me in understand this. Thanks.
SELECT
MAX (EFF_DT) FROM PNY.VGEN_CD_DET B
WHERE B.MKT_ID = ?
AND B.GRP_CD = ?
AND B.VAL_CD = ? )
AND ((SELECT SUM(PAY_AMT) FROM
PNY.VPAYMENT WHERE MKT_ID ='RNY')>=1)
OPTIMIZE FOR 1 ROW;

Thanks,
TSP

--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] . Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm
--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm


---------------------------------
Need Mail bonding?
Go to the Yahoo! Mail Q&A for great tips from Yahoo! Answers users.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

ANAND JAYACHANDRAN

SQL Query help
(in response to Dave Nance)
Are you in DB2 V8 New Function Mode? You are attempting to use a NFM
function in CM or ENF mode.

-4700 ATTEMPT TO USE NEW FUNCTION BEFORE NEW FUNCTION MODE



Explanation: Functions that this release of DB2 introduces cannot be
used
before new function mode has been enabled. An attempt was made to
execute
one of these functions. In addition, support for extending the length of
a
VARCHAR (supported in V7) is restricted in V8 until new function mode
has
been enabled.



To use the new functions that have been introduced in this release of
DB2,
new-function mode must first be enabled. This error code will be given

after an attempt to use a new function before new-function mode has been

enabled.



Additionally, the following statements are restricted in DB2 for z/OS

Version 8 until new-function mode is enabled:



o Altering a table to increase the length of a VARCHAR column



o Altering a catalog table to DATA CAPTURE CHANGES

System Action: The statement cannot be executed.



Programmer Response: Either delay running your program until
new-function
mode has been enabled, or restrict your program to functions that are

allowed before new-function mode is enabled.



SQLSTATE: 56038


From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of tsp l
Sent: Tuesday, January 16, 2007 16:49
To: [login to unmask email]
Subject: [DB2-L] SQL Query help

Hi,
Please look at the query below , could anybody please tell me is this a
valid query. When I execute this via spufi I get -4700 sql code. could
anybody help me in understand this. Thanks.
SELECT
MAX (EFF_DT) FROM PNY.VGEN_CD_DET B
WHERE B.MKT_ID = ?
AND B.GRP_CD = ?
AND B.VAL_CD = ? )
AND ((SELECT SUM(PAY_AMT) FROM
PNY.VPAYMENT WHERE MKT_ID ='RNY')>=1)
OPTIMIZE FOR 1 ROW;

Thanks,
TSP

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Tej Patil

Re: SQL Query help
(in response to ANAND JAYACHANDRAN)
We are still in CM mode. But that was the error I got when I try to execute
it.

On 1/16/07, [login to unmask email] <
[login to unmask email]> wrote:
>
> Are you in DB2 V8 New Function Mode? You are attempting to use a NFM
> function in CM or ENF mode.
>
> -4700 ATTEMPT TO USE NEW FUNCTION BEFORE NEW FUNCTION MODE
>
>
>
> Explanation: Functions that this release of DB2 introduces cannot be used
>
> before new function mode has been enabled. An attempt was made to execute
>
> one of these functions. In addition, support for extending the length of a
>
> VARCHAR (supported in V7) is restricted in V8 until new function mode has
>
> been enabled.
>
>
>
> To use the new functions that have been introduced in this release of DB2,
>
> new-function mode must first be enabled. This error code will be given
>
> after an attempt to use a new function before new-function mode has been
>
> enabled.
>
>
>
> Additionally, the following statements are restricted in DB2 for z/OS
>
> Version 8 until new-function mode is enabled:
>
>
>
> o Altering a table to increase the length of a VARCHAR column
>
>
>
> o Altering a catalog table to DATA CAPTURE CHANGES
>
> System Action: The statement cannot be executed.
>
>
>
> Programmer Response: Either delay running your program until new-function
>
> mode has been enabled, or restrict your program to functions that are
>
> allowed before new-function mode is enabled.
>
>
>
> SQLSTATE: 56038
>
> ***From:* DB2 Data Base Discussion List [mailto:[login to unmask email]<[login to unmask email]>
> ]*** On Behalf Of* tsp l
> ***Sent:* Tuesday, January 16, 2007 16:49
> ***To:* [login to unmask email]
> ***Subject:* [DB2-L] SQL Query help
>
> Hi,
>
> Please look at the query below , could anybody please tell me is this a
> valid query. When I execute this via spufi I get -4700 sql code. could
> anybody help me in understand this. Thanks.
> SELECT
>
> MAX (EFF_DT) FROM PNY.VGEN_CD_DET B
> WHERE B.MKT_ID = ?
> AND B.GRP_CD = ?
> AND B.VAL_CD = ? )
> AND ((SELECT SUM(PAY_AMT) FROM
> PNY.VPAYMENT WHERE MKT_ID ='RNY')>=1)
> OPTIMIZE FOR 1 ROW;
>
>
> Thanks,
>
> TSP
>

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Larry Kintisch

Re: SQL Query help
(in response to Tej Patil)
Hi List,
I'd suggest you try reversing the Subselect predicate to be
AND 1<= (SELECT SUM(...)...)
The reason it fails is that as coded it is a "Scalar subselect",
not supported without NFM. Larry Kintisch ABLE Information
Services 845-353-0885 www.DBIndex Design.com


At 08:52 PM 1/16/2007, tsp l wrote:
>We are still in CM mode. But that was the error I got when I try to
>execute it.
>
>On 1/16/07,
><mailto:[login to unmask email]>[login to unmask email]
><<mailto:[login to unmask email]>
>[login to unmask email]> wrote:
>
>Are you in DB2 V8 New Function Mode? You are attempting to use a NFM
>function in CM or ENF mode.
>
>-4700 ATTEMPT TO USE NEW FUNCTION BEFORE NEW FUNCTION MODE
>
>
>
>Explanation: Functions that this release of DB2 introduces cannot be used
>
>before new function mode has been enabled. An attempt was made to execute
>
>one of these functions. In addition, support for extending the length of a
>
>VARCHAR (supported in V7) is restricted in V8 until new function mode has
>
>been enabled.
>
>
>
>To use the new functions that have been introduced in this release of DB2,
>
>new-function mode must first be enabled. This error code will be given
>
>after an attempt to use a new function before new-function mode has been
>
>enabled.
>
>
>
>Additionally, the following statements are restricted in DB2 for z/OS
>
>Version 8 until new-function mode is enabled:
>
>
>
>o Altering a table to increase the length of a VARCHAR column
>
>
>
>o Altering a catalog table to DATA CAPTURE CHANGES
>
>System Action: The statement cannot be executed.
>
>
>
>Programmer Response: Either delay running your program until new-function
>
>mode has been enabled, or restrict your program to functions that are
>
>allowed before new-function mode is enabled.
>
>
>
>SQLSTATE: 56038
>
>From: DB2 Data Base Discussion List [
><mailto:[login to unmask email]>mailto:[login to unmask email] On Behalf Of tsp l
>Sent: Tuesday, January 16, 2007 16:49
>To: <mailto:[login to unmask email]>[login to unmask email]
>Subject: [DB2-L] SQL Query help
>
>Hi,
>
>Please look at the query below , could anybody please tell me is
>this a valid query. When I execute this via spufi I get -4700 sql
>code. could anybody help me in understand this. Thanks.
>SELECT
>
>MAX (EFF_DT) FROM PNY.VGEN_CD_DET B
>WHERE B.MKT_ID = ?
>AND B.GRP_CD = ?
>AND B.VAL_CD = ? )
>AND ((SELECT SUM(PAY_AMT) FROM
>PNY.VPAYMENT WHERE MKT_ID ='RNY')>=1)
>OPTIMIZE FOR 1 ROW;
>
>
>Thanks,
>
>TSP
>
>
>---------------------------------------------------------------------------------
>Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives
>and home page at http://www.idugdb2-l.org/archives/db2-l.html. From
>that page select "Join or Leave the list". The IDUG DB2-L FAQ is at
>http://www.idugdb2-l.org. The IDUG List Admins can be reached at
>[login to unmask email] Find out the latest on IDUG
>conferences at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Tej Patil

Re: SQL Query help
(in response to Larry Kintisch)
Thanks a lot. It works.

On 1/16/07, Larry Kintisch <[login to unmask email]> wrote:
>
> Hi List,
> I'd suggest you try reversing the Subselect predicate to be
> AND 1<= (SELECT SUM(...)...)
> The reason it fails is that as coded it is a "Scalar subselect",
> not supported without NFM. Larry Kintisch ABLE Information
> Services 845-353-0885 www.DBIndex Design.com
>
>
> At 08:52 PM 1/16/2007, tsp l wrote:
> >We are still in CM mode. But that was the error I got when I try to
> >execute it.
> >
> >On 1/16/07,
> ><mailto:[login to unmask email]>
> [login to unmask email]
> ><<mailto:[login to unmask email]>
> >[login to unmask email]> wrote:
> >
> >Are you in DB2 V8 New Function Mode? You are attempting to use a NFM
> >function in CM or ENF mode.
> >
> >-4700 ATTEMPT TO USE NEW FUNCTION BEFORE NEW FUNCTION MODE
> >
> >
> >
> >Explanation: Functions that this release of DB2 introduces cannot be
> used
> >
> >before new function mode has been enabled. An attempt was made to execute
> >
> >one of these functions. In addition, support for extending the length of
> a
> >
> >VARCHAR (supported in V7) is restricted in V8 until new function mode has
> >
> >been enabled.
> >
> >
> >
> >To use the new functions that have been introduced in this release of
> DB2,
> >
> >new-function mode must first be enabled. This error code will be given
> >
> >after an attempt to use a new function before new-function mode has been
> >
> >enabled.
> >
> >
> >
> >Additionally, the following statements are restricted in DB2 for z/OS
> >
> >Version 8 until new-function mode is enabled:
> >
> >
> >
> >o Altering a table to increase the length of a VARCHAR column
> >
> >
> >
> >o Altering a catalog table to DATA CAPTURE CHANGES
> >
> >System Action: The statement cannot be executed.
> >
> >
> >
> >Programmer Response: Either delay running your program until
> new-function
> >
> >mode has been enabled, or restrict your program to functions that are
> >
> >allowed before new-function mode is enabled.
> >
> >
> >
> >SQLSTATE: 56038
> >
> >From: DB2 Data Base Discussion List [
> ><mailto:[login to unmask email]>mailto:[login to unmask email] On Behalf Of tsp
> l
> >Sent: Tuesday, January 16, 2007 16:49
> >To: <mailto:[login to unmask email]>[login to unmask email]
> >Subject: [DB2-L] SQL Query help
> >
> >Hi,
> >
> >Please look at the query below , could anybody please tell me is
> >this a valid query. When I execute this via spufi I get -4700 sql
> >code. could anybody help me in understand this. Thanks.
> >SELECT
> >
> >MAX (EFF_DT) FROM PNY.VGEN_CD_DET B
> >WHERE B.MKT_ID = ?
> >AND B.GRP_CD = ?
> >AND B.VAL_CD = ? )
> >AND ((SELECT SUM(PAY_AMT) FROM
> >PNY.VPAYMENT WHERE MKT_ID ='RNY')>=1)
> >OPTIMIZE FOR 1 ROW;
> >
> >
> >Thanks,
> >
> >TSP
> >
> >
>
> >---------------------------------------------------------------------------------
> >Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives
> >and home page at http://www.idugdb2-l.org/archives/db2-l.html. From
> >that page select "Join or Leave the list". The IDUG DB2-L FAQ is at
> >http://www.idugdb2-l.org. The IDUG List Admins can be reached at
> >[login to unmask email] Find out the latest on IDUG
> >conferences at http://conferences.idug.org/index.cfm
>
>
> ---------------------------------------------------------------------------------
> Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
> home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page
> select "Join or Leave the list". The IDUG DB2-L FAQ is at
> http://www.idugdb2-l.org. The IDUG List Admins can be reached at
> [login to unmask email] Find out the latest on IDUG conferences
> at http://conferences.idug.org/index.cfm
>

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm