Sean,

Here is the problematic SP:

CREATE PROCEDURE PR_CHECK_HIST (MY_DATE TimeStamp) returns
(PR_CHECK_NBR Integer,
EE_NBR Integer,
PR_NBR Integer,
PR_BATCH_NBR Integer,
CUSTOM_PR_BANK_ACCT_NUMBER VarChar(20),
PAYMENT_SERIAL_NUMBER Integer,
CHECK_TYPE Char(1),
CO_DELIVERY_PACKAGE_NBR Integer,
SALARY Numeric(18,6),
EXCLUDE_DD Char(1),
EXCLUDE_DD_EXCEPT_NET Char(1),
EXCLUDE_TIME_OFF_ACCURAL Char(1),
EXCLUDE_AUTO_DISTRIBUTION Char(1),
EXCLUDE_ALL_SCHED_E_D_CODES Char(1),
EXCLUDE_SCH_E_D_FROM_AGCY_CHK Char(1),
EXCLUDE_SCH_E_D_EXCEPT_PENSION Char(1),
PRORATE_SCHEDULED_E_DS Char(1),
OR_CHECK_FEDERAL_VALUE Numeric(18,6),
OR_CHECK_FEDERAL_TYPE Char(1),
OR_CHECK_OASDI Numeric(18,6),
OR_CHECK_MEDICARE Numeric(18,6),
OR_CHECK_EIC Numeric(18,6),
OR_CHECK_BACK_UP_WITHHOLDING Numeric(18,6),
EXCLUDE_FEDERAL Char(1),
EXCLUDE_ADDITIONAL_FEDERAL Char(1),
EXCLUDE_EMPLOYEE_OASDI Char(1),
EXCLUDE_EMPLOYER_OASDI Char(1),
EXCLUDE_EMPLOYEE_MEDICARE Char(1),
EXCLUDE_EMPLOYER_MEDICARE Char(1),
EXCLUDE_EMPLOYEE_EIC Char(1),
EXCLUDE_EMPLOYER_FUI Char(1),
TAX_AT_SUPPLEMENTAL_RATE Char(1),
TAX_FREQUENCY Char(1),
OVERRIDE_CHECK_NOTES BLOB,
CHECK_STATUS Char(1),
STATUS_CHANGE_DATE TimeStamp,
GROSS_WAGES Numeric(18,6),
NET_WAGES Numeric(18,6),
FEDERAL_TAXABLE_WAGES Numeric(18,6),
FEDERAL_TAX Numeric(18,6),
EE_OASDI_TAXABLE_WAGES Numeric(18,6),
EE_OASDI_TAXABLE_TIPS Numeric(18,6),
EE_OASDI_TAX Numeric(18,6),
EE_MEDICARE_TAXABLE_WAGES Numeric(18,6),
EE_MEDICARE_TAX Numeric(18,6),
EE_EIC_TAX Numeric(18,6),
ER_OASDI_TAXABLE_WAGES Numeric(18,6),
ER_OASDI_TAXABLE_TIPS Numeric(18,6),
ER_OASDI_TAX Numeric(18,6),
ER_MEDICARE_TAXABLE_WAGES Numeric(18,6),
ER_MEDICARE_TAX Numeric(18,6),
ER_FUI_TAXABLE_WAGES Numeric(18,6),
ER_FUI_GROSS_WAGES Numeric(18,6),
ER_FUI_TAX Numeric(18,6),
EXCLUDE_FROM_AGENCY Char(1),
CHECK_COMMENTS BLOB,
FEDERAL_SHORTFALL Numeric(18,6),
CHECK_TYPE_945 Char(1),
FEDERAL_GROSS_WAGES Numeric(18,6),
EE_OASDI_GROSS_WAGES Numeric(18,6),
ER_OASDI_GROSS_WAGES Numeric(18,6),
EE_MEDICARE_GROSS_WAGES Numeric(18,6),
ER_MEDICARE_GROSS_WAGES Numeric(18,6),
FILLER VarChar(512),
EFFECTIVE_DATE TimeStamp,
CHANGED_BY Integer,
CREATION_DATE TimeStamp,
ACTIVE_RECORD Char(1))
AS
declare variable TEMP_NBR INTEGER;
declare variable TEMP_DATE TIMESTAMP;
declare variable TEMP_DATE2 TIMESTAMP;
begin
for select PR_CHECK_NBR, max(EFFECTIVE_DATE), max(CREATION_DATE)
from PR_CHECK
where ACTIVE_RECORD='C' or ACTIVE_RECORD='N' group by PR_CHECK_NBR
into :TEMP_NBR, :TEMP_DATE, :TEMP_DATE2 do
begin
if (TEMP_DATE>MY_DATE) then
begin
select max(EFFECTIVE_DATE) from PR_CHECK
where PR_CHECK_NBR=:TEMP_NBR and EFFECTIVE_DATE<=:MY_DATE
into :TEMP_DATE;
if (TEMP_DATE is not null) then
select max(CREATION_DATE) from PR_CHECK
where PR_CHECK_NBR=:TEMP_NBR and EFFECTIVE_DATE=:TEMP_DATE
into :TEMP_DATE2;
end
if (TEMP_DATE is not null) then
begin
select
PR_CHECK_NBR,
EE_NBR,
PR_NBR,
PR_BATCH_NBR,
CUSTOM_PR_BANK_ACCT_NUMBER,
PAYMENT_SERIAL_NUMBER,
CHECK_TYPE,
CO_DELIVERY_PACKAGE_NBR,
SALARY,
EXCLUDE_DD,
EXCLUDE_DD_EXCEPT_NET,
EXCLUDE_TIME_OFF_ACCURAL,
EXCLUDE_AUTO_DISTRIBUTION,
EXCLUDE_ALL_SCHED_E_D_CODES,
EXCLUDE_SCH_E_D_FROM_AGCY_CHK,
EXCLUDE_SCH_E_D_EXCEPT_PENSION,
PRORATE_SCHEDULED_E_DS,
OR_CHECK_FEDERAL_VALUE,
OR_CHECK_FEDERAL_TYPE,
OR_CHECK_OASDI,
OR_CHECK_MEDICARE,
OR_CHECK_EIC,
OR_CHECK_BACK_UP_WITHHOLDING,
EXCLUDE_FEDERAL,
EXCLUDE_ADDITIONAL_FEDERAL,
EXCLUDE_EMPLOYEE_OASDI,
EXCLUDE_EMPLOYER_OASDI,
EXCLUDE_EMPLOYEE_MEDICARE,
EXCLUDE_EMPLOYER_MEDICARE,
EXCLUDE_EMPLOYEE_EIC,
EXCLUDE_EMPLOYER_FUI,
TAX_AT_SUPPLEMENTAL_RATE,
TAX_FREQUENCY,
OVERRIDE_CHECK_NOTES,
CHECK_STATUS,
STATUS_CHANGE_DATE,
GROSS_WAGES,
NET_WAGES,
FEDERAL_TAXABLE_WAGES,
FEDERAL_TAX,
EE_OASDI_TAXABLE_WAGES,
EE_OASDI_TAXABLE_TIPS,
EE_OASDI_TAX,
EE_MEDICARE_TAXABLE_WAGES,
EE_MEDICARE_TAX,
EE_EIC_TAX,
ER_OASDI_TAXABLE_WAGES,
ER_OASDI_TAXABLE_TIPS,
ER_OASDI_TAX,
ER_MEDICARE_TAXABLE_WAGES,
ER_MEDICARE_TAX,
ER_FUI_TAXABLE_WAGES,
ER_FUI_GROSS_WAGES,
ER_FUI_TAX,
EXCLUDE_FROM_AGENCY,
CHECK_COMMENTS,
FEDERAL_SHORTFALL,
CHECK_TYPE_945,
FEDERAL_GROSS_WAGES,
EE_OASDI_GROSS_WAGES,
ER_OASDI_GROSS_WAGES,
EE_MEDICARE_GROSS_WAGES,
ER_MEDICARE_GROSS_WAGES,
FILLER,
EFFECTIVE_DATE,
CHANGED_BY,
CREATION_DATE,
ACTIVE_RECORD
from PR_CHECK where PR_CHECK_NBR=:TEMP_NBR and
EFFECTIVE_DATE=:TEMP_DATE and
CREATION_DATE=:TEMP_DATE2
into
:PR_CHECK_NBR,
:EE_NBR,
:PR_NBR,
:PR_BATCH_NBR,
:CUSTOM_PR_BANK_ACCT_NUMBER,
:PAYMENT_SERIAL_NUMBER,
:CHECK_TYPE,
:CO_DELIVERY_PACKAGE_NBR,
:SALARY,
:EXCLUDE_DD,
:EXCLUDE_DD_EXCEPT_NET,
:EXCLUDE_TIME_OFF_ACCURAL,
:EXCLUDE_AUTO_DISTRIBUTION,
:EXCLUDE_ALL_SCHED_E_D_CODES,
:EXCLUDE_SCH_E_D_FROM_AGCY_CHK,
:EXCLUDE_SCH_E_D_EXCEPT_PENSION,
:PRORATE_SCHEDULED_E_DS,
:OR_CHECK_FEDERAL_VALUE,
:OR_CHECK_FEDERAL_TYPE,
:OR_CHECK_OASDI,
:OR_CHECK_MEDICARE,
:OR_CHECK_EIC,
:OR_CHECK_BACK_UP_WITHHOLDING,
:EXCLUDE_FEDERAL,
:EXCLUDE_ADDITIONAL_FEDERAL,
:EXCLUDE_EMPLOYEE_OASDI,
:EXCLUDE_EMPLOYER_OASDI,
:EXCLUDE_EMPLOYEE_MEDICARE,
:EXCLUDE_EMPLOYER_MEDICARE,
:EXCLUDE_EMPLOYEE_EIC,
:EXCLUDE_EMPLOYER_FUI,
:TAX_AT_SUPPLEMENTAL_RATE,
:TAX_FREQUENCY,
:OVERRIDE_CHECK_NOTES,
:CHECK_STATUS,
:STATUS_CHANGE_DATE,
:GROSS_WAGES,
:NET_WAGES,
:FEDERAL_TAXABLE_WAGES,
:FEDERAL_TAX,
:EE_OASDI_TAXABLE_WAGES,
:EE_OASDI_TAXABLE_TIPS,
:EE_OASDI_TAX,
:EE_MEDICARE_TAXABLE_WAGES,
:EE_MEDICARE_TAX,
:EE_EIC_TAX,
:ER_OASDI_TAXABLE_WAGES,
:ER_OASDI_TAXABLE_TIPS,
:ER_OASDI_TAX,
:ER_MEDICARE_TAXABLE_WAGES,
:ER_MEDICARE_TAX,
:ER_FUI_TAXABLE_WAGES,
:ER_FUI_GROSS_WAGES,
:ER_FUI_TAX,
:EXCLUDE_FROM_AGENCY,
:CHECK_COMMENTS,
:FEDERAL_SHORTFALL,
:CHECK_TYPE_945,
:FEDERAL_GROSS_WAGES,
:EE_OASDI_GROSS_WAGES,
:ER_OASDI_GROSS_WAGES,
:EE_MEDICARE_GROSS_WAGES,
:ER_MEDICARE_GROSS_WAGES,
:FILLER,
:EFFECTIVE_DATE,
:CHANGED_BY,
:CREATION_DATE,
:ACTIVE_RECORD;
if (ACTIVE_RECORD<>'N') then suspend;
end
end
end;

Thank you,

Andrei


--- In firebird-support@yahoogroups.com, "Leyne, Sean" <sleyne@a...>
wrote:

> Andrei,
>
> > Changing the query to SQL-92 JOIN syntax did not help
>
> Well then start removing the SP from the statement, one at a time,

and

> try each variation.
>
> When you've found the cuplrit/problem, post the DDL for the SP and

well

> see what we can find.
>
>
> Sean