Stored Procedure returns incorrect column list

Mark Stone

Stored Procedure returns incorrect column list
I have a Stored procedure that executes a SQL statement, it's a little
unusual in that the last column of the select is another select. The
query executes fine using control center, but when executed from
within the stored procedure the last column name replaces the column
name of what should be the 1st column, almost like it's overlapping,
and substitutes a generic column # for what should be a column name.
This looks like a stored procedure result set bug. Has anyone
experienced this or have any ideas if any ptf's may exist.

Thanks

Mark.

The specific piece that is problematic is

(SELECT PRESCRIPTION_TRANSACTIONS.CREATE_DATE + (SELECT HRA_HOLD_DAYS
FROM FDBHRA.HRA_HOLD) DAYS FROM sysibm.SYSDUMMY1) as
"EST_CHECK_MAIL_DATE"

The entire query is below.

SELECT PRESCRIPTION_TRANSACTIONS.TRANS_ID,
PRESCRIPTION_TRANSACTIONS.TRANS_ORIGIN,
PRESCRIPTION_TRANSACTIONS.TRANS_STATUS,
PRESCRIPTION_TRANSACTIONS.PROC_BATCH_NBR,
PRESCRIPTION_TRANSACTIONS.PRESCRIPTION_NBR,
PRESCRIPTION_TRANSACTIONS.PRESCRIPTION_SEQ,
PRESCRIPTION_TRANSACTIONS.REFERENCE_NBR_QUAL,
PRESCRIPTION_TRANSACTIONS.DISPENSE_DATE,
PRESCRIPTION_TRANSACTIONS.PAYMENT_TYPE,
PRESCRIPTION_TRANSACTIONS.PATIENT_PAY_AMT,
PRESCRIPTION_TRANSACTIONS.OTHER_PAYER_AMT,
PRESCRIPTION_TRANSACTIONS.PAY_AMT_TO_DEDUCT,
PRESCRIPTION_TRANSACTIONS.PAT_FIRST_NAME,
PRESCRIPTION_TRANSACTIONS.PAT_LAST_NAME,
PRESCRIPTION_TRANSACTIONS.PAT_BIRTH_DATE,
(CASE WHEN PRESCRIPTION_TRANSACTIONS.PAT_GENDER='1' THEN 'M' WHEN
PRESCRIPTION_TRANSACTIONS.PAT_GENDER='2' THEN 'F' END) PAT_GENDER,
PRESCRIPTION_TRANSACTIONS.PAT_ID,
PRESCRIPTION_TRANSACTIONS.PERSON_CODE,
PRESCRIPTION_TRANSACTIONS.RELATION_CODE,
PRESCRIPTION_TRANSACTIONS.CARD_HOLDER_ID,
PRESCRIPTION_TRANSACTIONS.CH_LAST_NAME,
PRESCRIPTION_TRANSACTIONS.CH_FIRST_NAME,
PRESCRIPTION_TRANSACTIONS.CUSTOMER_LOCATION,
PRESCRIPTION_TRANSACTIONS.FORMULARY_STATUS,
PRESCRIPTION_TRANSACTIONS.PLAN_DRUG_STATUS,
PRESCRIPTION_TRANSACTIONS.GROUP_ID,
PRESCRIPTION_TRANSACTIONS.SSNO,
PRESCRIPTION_TRANSACTIONS.RX_SUBMIT_DATE,
PRESCRIPTION_TRANSACTIONS.RX_CLAIM_NBR,
PRESCRIPTION_TRANSACTIONS.RX_CLAIM_NBR_SEQ,
PRESCRIPTION_TRANSACTIONS.CLAIM_STATUS,
PRESCRIPTION_TRANSACTIONS.PS_TRANS_CODE,
PRESCRIPTION_TRANSACTIONS.FAMILY_ID,
PRESCRIPTION_TRANSACTIONS.RELATION_ID,
PRESCRIPTION_TRANSACTIONS.CREATE_USERID,
PRESCRIPTION_TRANSACTIONS.CREATE_DATE,
PRESCRIPTION_TRANSACTIONS.USERID,
PRESCRIPTION_TRANSACTIONS.LASTUPDT,
PRESCRIPTION_TRANSACTIONS.CHECK_DATE,
PRESCRIPTION_HRA_VALUES.DESCRIPTION AS PRESCRIPTION_HRA_DESCRIPTION,
(SELECT PRESCRIPTION_TRANSACTIONS.CREATE_DATE + (SELECT
HRA_HOLD_DAYS FROM FDBHRA.HRA_HOLD) DAYS FROM sysibm.SYSDUMMY1) as
"EST_CHECK_MAIL_DATE"
FROM FDBHRA.PRESCRIPTION_TRANSACTIONS PRESCRIPTION_TRANSACTIONS INNER JOIN
FDBHRA.PRESCRIPTION_HRA_VALUES PRESCRIPTION_HRA_VALUES ON
PRESCRIPTION_HRA_VALUES.TRANS_VALUE =
PRESCRIPTION_TRANSACTIONS.TRANS_STATUS
WHERE COALESCE(PRESCRIPTION_TRANSACTIONS.RX_SUBMIT_DATE,CURRENT DATE)
BETWEEN vPRESCRIPTIONS_FROMDATE AND vPRESCRIPTIONS_TODATE
AND PRESCRIPTION_TRANSACTIONS.FAMILY_ID = vFAMILY_ID
AND PRESCRIPTION_TRANSACTIONS.RELATION_ID = vRELATION_ID
FOR READ ONLY
WITH UR;

_____________________________________________________________________
* 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! *
** 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

Todd Burch

Re: Stored Procedure returns incorrect column list
(in response to Mark Stone)
Hi Mark.

What flavor stored procedure? Native on DB2 9 or a V8 external SQL
procedure?

(I'm guessing an SQL procedure due to your variable names that do no
have colons and they start with "v", which makes me think you've read
the redbook!)

Todd


On Jan 27, 2011, at 6:30 PM, Mark Stone wrote:

I have a Stored procedure that executes a SQL statement, it's a little
unusual in that the last column of the select is another select. The
query executes fine using control center, but when executed from
within the stored procedure the last column name replaces the column
name of what should be the 1st column, almost like it's overlapping,
and substitutes a generic column # for what should be a column name.
This looks like a stored procedure result set bug. Has anyone
experienced this or have any ideas if any ptf's may exist.

Thanks

Mark.

The specific piece that is problematic is

(SELECT PRESCRIPTION_TRANSACTIONS.CREATE_DATE + (SELECT HRA_HOLD_DAYS
FROM FDBHRA.HRA_HOLD) DAYS FROM sysibm.SYSDUMMY1) as
"EST_CHECK_MAIL_DATE"

The entire query is below.

SELECT PRESCRIPTION_TRANSACTIONS.TRANS_ID,
PRESCRIPTION_TRANSACTIONS.TRANS_ORIGIN,
PRESCRIPTION_TRANSACTIONS.TRANS_STATUS,
PRESCRIPTION_TRANSACTIONS.PROC_BATCH_NBR,
PRESCRIPTION_TRANSACTIONS.PRESCRIPTION_NBR,
PRESCRIPTION_TRANSACTIONS.PRESCRIPTION_SEQ,
PRESCRIPTION_TRANSACTIONS.REFERENCE_NBR_QUAL,
PRESCRIPTION_TRANSACTIONS.DISPENSE_DATE,
PRESCRIPTION_TRANSACTIONS.PAYMENT_TYPE,
PRESCRIPTION_TRANSACTIONS.PATIENT_PAY_AMT,
PRESCRIPTION_TRANSACTIONS.OTHER_PAYER_AMT,
PRESCRIPTION_TRANSACTIONS.PAY_AMT_TO_DEDUCT,
PRESCRIPTION_TRANSACTIONS.PAT_FIRST_NAME,
PRESCRIPTION_TRANSACTIONS.PAT_LAST_NAME,
PRESCRIPTION_TRANSACTIONS.PAT_BIRTH_DATE,
(CASE WHEN PRESCRIPTION_TRANSACTIONS.PAT_GENDER='1' THEN 'M' WHEN
PRESCRIPTION_TRANSACTIONS.PAT_GENDER='2' THEN 'F' END) PAT_GENDER,
PRESCRIPTION_TRANSACTIONS.PAT_ID,
PRESCRIPTION_TRANSACTIONS.PERSON_CODE,
PRESCRIPTION_TRANSACTIONS.RELATION_CODE,
PRESCRIPTION_TRANSACTIONS.CARD_HOLDER_ID,
PRESCRIPTION_TRANSACTIONS.CH_LAST_NAME,
PRESCRIPTION_TRANSACTIONS.CH_FIRST_NAME,
PRESCRIPTION_TRANSACTIONS.CUSTOMER_LOCATION,
PRESCRIPTION_TRANSACTIONS.FORMULARY_STATUS,
PRESCRIPTION_TRANSACTIONS.PLAN_DRUG_STATUS,
PRESCRIPTION_TRANSACTIONS.GROUP_ID,
PRESCRIPTION_TRANSACTIONS.SSNO,
PRESCRIPTION_TRANSACTIONS.RX_SUBMIT_DATE,
PRESCRIPTION_TRANSACTIONS.RX_CLAIM_NBR,
PRESCRIPTION_TRANSACTIONS.RX_CLAIM_NBR_SEQ,
PRESCRIPTION_TRANSACTIONS.CLAIM_STATUS,
PRESCRIPTION_TRANSACTIONS.PS_TRANS_CODE,
PRESCRIPTION_TRANSACTIONS.FAMILY_ID,
PRESCRIPTION_TRANSACTIONS.RELATION_ID,
PRESCRIPTION_TRANSACTIONS.CREATE_USERID,
PRESCRIPTION_TRANSACTIONS.CREATE_DATE,
PRESCRIPTION_TRANSACTIONS.USERID,
PRESCRIPTION_TRANSACTIONS.LASTUPDT,
PRESCRIPTION_TRANSACTIONS.CHECK_DATE,
PRESCRIPTION_HRA_VALUES.DESCRIPTION AS PRESCRIPTION_HRA_DESCRIPTION,
(SELECT PRESCRIPTION_TRANSACTIONS.CREATE_DATE + (SELECT
HRA_HOLD_DAYS FROM FDBHRA.HRA_HOLD) DAYS FROM sysibm.SYSDUMMY1) as
"EST_CHECK_MAIL_DATE"
FROM FDBHRA.PRESCRIPTION_TRANSACTIONS PRESCRIPTION_TRANSACTIONS INNER
JOIN
FDBHRA.PRESCRIPTION_HRA_VALUES PRESCRIPTION_HRA_VALUES ON
PRESCRIPTION_HRA_VALUES.TRANS_VALUE =
PRESCRIPTION_TRANSACTIONS.TRANS_STATUS
WHERE COALESCE(PRESCRIPTION_TRANSACTIONS.RX_SUBMIT_DATE,CURRENT DATE)
BETWEEN vPRESCRIPTIONS_FROMDATE AND vPRESCRIPTIONS_TODATE
AND PRESCRIPTION_TRANSACTIONS.FAMILY_ID = vFAMILY_ID
AND PRESCRIPTION_TRANSACTIONS.RELATION_ID = vRELATION_ID
FOR READ ONLY
WITH UR;

_____________________________________________________________________
* 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! *
** 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

Mark Stone

Re: Stored Procedure returns incorrect column list
(in response to Todd Burch)
Well I've read some sections of the redbook :), but the v's are in
response to a painful conversion to native effort.

We opened a pmr, so here's to a long and painful troubleshooting experience.

On Thu, Jan 27, 2011 at 6:27 PM, Todd Burch <[login to unmask email]> wrote:
> Hi Mark.
>
> What flavor stored procedure?    Native on DB2 9 or a V8 external SQL
> procedure?
>
> (I'm guessing an SQL procedure due to your variable names that do no have
> colons and they start with "v", which makes me think you've read the
> redbook!)
>
> Todd
>
>
> On Jan 27, 2011, at 6:30 PM, Mark Stone wrote:
>
> I have a Stored procedure that executes a SQL statement, it's a little
> unusual in that the last column of the select is another select. The
> query executes fine using control center, but when executed from
> within the stored procedure the last column name replaces the column
> name of what should be the 1st column, almost like it's overlapping,
> and substitutes a generic column # for what should be a column name.
> This looks like a stored procedure result set bug. Has anyone
> experienced this or have any ideas if any ptf's may exist.
>
> Thanks
>
> Mark.
>
> The specific piece that is problematic is
>
> (SELECT PRESCRIPTION_TRANSACTIONS.CREATE_DATE + (SELECT HRA_HOLD_DAYS
> FROM FDBHRA.HRA_HOLD) DAYS FROM sysibm.SYSDUMMY1) as
> "EST_CHECK_MAIL_DATE"
>
> The entire query is below.
>
>        SELECT  PRESCRIPTION_TRANSACTIONS.TRANS_ID,
>                        PRESCRIPTION_TRANSACTIONS.TRANS_ORIGIN,
>                        PRESCRIPTION_TRANSACTIONS.TRANS_STATUS,
>                        PRESCRIPTION_TRANSACTIONS.PROC_BATCH_NBR,
>                        PRESCRIPTION_TRANSACTIONS.PRESCRIPTION_NBR,
>                        PRESCRIPTION_TRANSACTIONS.PRESCRIPTION_SEQ,
>                        PRESCRIPTION_TRANSACTIONS.REFERENCE_NBR_QUAL,
>                        PRESCRIPTION_TRANSACTIONS.DISPENSE_DATE,
>                        PRESCRIPTION_TRANSACTIONS.PAYMENT_TYPE,
>                        PRESCRIPTION_TRANSACTIONS.PATIENT_PAY_AMT,
>                        PRESCRIPTION_TRANSACTIONS.OTHER_PAYER_AMT,
>                        PRESCRIPTION_TRANSACTIONS.PAY_AMT_TO_DEDUCT,
>                        PRESCRIPTION_TRANSACTIONS.PAT_FIRST_NAME,
>                        PRESCRIPTION_TRANSACTIONS.PAT_LAST_NAME,
>                        PRESCRIPTION_TRANSACTIONS.PAT_BIRTH_DATE,
>                        (CASE WHEN PRESCRIPTION_TRANSACTIONS.PAT_GENDER='1'
> THEN 'M' WHEN
> PRESCRIPTION_TRANSACTIONS.PAT_GENDER='2' THEN 'F' END) PAT_GENDER,
>                        PRESCRIPTION_TRANSACTIONS.PAT_ID,
>                        PRESCRIPTION_TRANSACTIONS.PERSON_CODE,
>                        PRESCRIPTION_TRANSACTIONS.RELATION_CODE,
>                        PRESCRIPTION_TRANSACTIONS.CARD_HOLDER_ID,
>                        PRESCRIPTION_TRANSACTIONS.CH_LAST_NAME,
>                        PRESCRIPTION_TRANSACTIONS.CH_FIRST_NAME,
>                        PRESCRIPTION_TRANSACTIONS.CUSTOMER_LOCATION,
>                        PRESCRIPTION_TRANSACTIONS.FORMULARY_STATUS,
>                        PRESCRIPTION_TRANSACTIONS.PLAN_DRUG_STATUS,
>                        PRESCRIPTION_TRANSACTIONS.GROUP_ID,
>                        PRESCRIPTION_TRANSACTIONS.SSNO,
>                        PRESCRIPTION_TRANSACTIONS.RX_SUBMIT_DATE,
>                        PRESCRIPTION_TRANSACTIONS.RX_CLAIM_NBR,
>                        PRESCRIPTION_TRANSACTIONS.RX_CLAIM_NBR_SEQ,
>                        PRESCRIPTION_TRANSACTIONS.CLAIM_STATUS,
>                        PRESCRIPTION_TRANSACTIONS.PS_TRANS_CODE,
>                        PRESCRIPTION_TRANSACTIONS.FAMILY_ID,
>                        PRESCRIPTION_TRANSACTIONS.RELATION_ID,
>                        PRESCRIPTION_TRANSACTIONS.CREATE_USERID,
>                        PRESCRIPTION_TRANSACTIONS.CREATE_DATE,
>                        PRESCRIPTION_TRANSACTIONS.USERID,
>                        PRESCRIPTION_TRANSACTIONS.LASTUPDT,
>                        PRESCRIPTION_TRANSACTIONS.CHECK_DATE,
>                        PRESCRIPTION_HRA_VALUES.DESCRIPTION AS
> PRESCRIPTION_HRA_DESCRIPTION,
>                        (SELECT PRESCRIPTION_TRANSACTIONS.CREATE_DATE +
> (SELECT
> HRA_HOLD_DAYS FROM FDBHRA.HRA_HOLD) DAYS FROM sysibm.SYSDUMMY1) as
> "EST_CHECK_MAIL_DATE"
>        FROM    FDBHRA.PRESCRIPTION_TRANSACTIONS PRESCRIPTION_TRANSACTIONS
> INNER JOIN
>                        FDBHRA.PRESCRIPTION_HRA_VALUES
> PRESCRIPTION_HRA_VALUES ON
> PRESCRIPTION_HRA_VALUES.TRANS_VALUE =
> PRESCRIPTION_TRANSACTIONS.TRANS_STATUS
>        WHERE   COALESCE(PRESCRIPTION_TRANSACTIONS.RX_SUBMIT_DATE,CURRENT
> DATE)
> BETWEEN vPRESCRIPTIONS_FROMDATE AND vPRESCRIPTIONS_TODATE
>        AND             PRESCRIPTION_TRANSACTIONS.FAMILY_ID = vFAMILY_ID
>        AND             PRESCRIPTION_TRANSACTIONS.RELATION_ID = vRELATION_ID
>   FOR READ ONLY
>   WITH UR;
>
> _____________________________________________________________________
> * 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!
> *
> **    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
>

_____________________________________________________________________
* 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! *
** 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