I have the following SQL passthrough request with the following error resulting from it:

ENGINE SQLORA SET DEFAULT_CONNECTION DW
SQL SQLORA
SELECT
dc.customer_id,
dc.customer_name account,
dc.source_acct_nbr,
dp.full_nm,
bf.calendar_id INSERT_DATE_A,
da.source_ad_nbr ad_nbr,
da.transaction_nbr ||'-'||da.sub_transaction_nbr1 insertion_seq_nbr,
DECODE(bf.transaction_type_cd,1,'Insertion',2,'Credit',3,'Charge',4,'Adjustment',5,'Offset','Insertion') rf_tran_type,
dpr.property_desc,
SUM(bf.amount) revenue,
SUM(bf.linage) linage,
DECODE(da.ad_type_cd,'GR','Display','CL','Classified',NULL) ad_type,
DECODE(bf.exception_id,NULL,DECODE(SUBSTR(dpr.property_cd,1,2),'BG','CPC','CMC'),'Non-Comm') is_commissionable,
bf.person_id,
dp.first_nm,
dp.last_nm,
bf.customer_agency_id,
bf.ad_id,
bf.amount,
da.transaction_nbr insertion_nbr,
da.sub_transaction_nbr1 insertion_seq_nbr,
dpr.product_section_desc
FROM
rpmdm.book_of_business_fact bf,
rpmdm.dim_customer dc,
rpmdm.dim_advertisement da,
rpmdm.dim_product dpr,
rpmdm.dim_person dp
WHERE 0=0
AND bf.customer_id = dc.customer_id
AND bf.ad_id = da.ad_id
AND bf.person_id = dp.person_id
AND bf.person_start_id = dp.effective_start_id
AND bf.product_id = dpr.product_id (+)
-*AND bf.customer_id = &TST_ACCOUNT
AND bf.calendar_id BETWEEN 20080630.00 AND 20081228.00
AND bf.person_id = 8293.00
GROUP BY
dc.customer_id,
dc.customer_name,
dc.source_acct_nbr,
dp.full_nm,
bf.calendar_id,
da.source_ad_nbr,
da.transaction_nbr ||'-'||da.sub_transaction_nbr1,
DECODE(bf.transaction_type_cd,1,'Insertion',2,'Credit',3,'Charge',4,'Adjustment',5,'Offset','Insertion'),
dpr.property_desc,
DECODE(da.ad_type_cd,'GR','Display','CL','Classified',NULL),
DECODE(bf.exception_id,NULL,DECODE(SUBSTR(dpr.property_cd,1,2),'BG','CPC','CMC'),'Non-Comm'),
bf.person_id,
dp.first_nm,
dp.last_nm,
bf.customer_agency_id,
bf.ad_id,
bf.amount,
da.transaction_nbr,
da.sub_transaction_nbr1,
dpr.product_section_desc;
? HOLD SQLOUT
TABLE FILE SQLOUT
PRINT *
WHERE RECORDLIMIT EQ 100
ON TABLE HOLD AS PREDEF
END
-RUN
0DEFINITION OF HOLD FILE: SQLOUT
0FIELDNAME ALIAS FORMAT
CUSTOMER_ID E01 D20.2
ACCOUNT E02 A240 MISSING = ON
SOURCE_ACCT_NBR E03 A30 MISSING = ON
FULL_NM E04 A100 MISSING = ON
INSERT_DATE_A E05 D20.2
AD_NBR E06 A50 MISSING = ON
INSERTION_SEQ_NBR E07 A101 MISSING = ON
RF_TRAN_TYPE E08 A10 MISSING = ON
PROPERTY_DESC E09 A50 MISSING = ON
REVENUE E10 D20.2 MISSING = ON
LINAGE E11 D20.2 MISSING = ON
AD_TYPE E12 A10 MISSING = ON
IS_COMMISSIONABLE E13 A8 MISSING = ON
PERSON_ID E14 D20.2
FIRST_NM E15 A40 MISSING = ON
LAST_NM E16 A40 MISSING = ON
CUSTOMER_AGENCY_ID E17 D20.2 MISSING = ON
AD_ID E18 D20.2
AMOUNT E19 D20.2 MISSING = ON
INSERTION_NBR E20 A50 MISSING = ON
INSERTION_SEQ_NBR E21 A50 MISSING = ON
PRODUCT_SECTION_DESC E22 A50 MISSING = ON
(FOC1400) SQLCODE IS -1 (HEX: FFFFFFFF)
(FOC1407) SQL FETCH CURSOR ERROR. : SQLOUT
-IF 1 EQ 0 THEN GOTO ENDRPT;

Now, if I run this report for Sep 2008 all is peachy. When I run it for Jul-Dec it fails. The amount of data being returned is not bone-crushing (maybe 1200 records). Anyone have any insight on this mystery error?

J.

This message has been edited. Last edited by: Kerry,

We normally get a 1400 on no permissions to look at data, but we get an oracle error with it. Check your adapter setting are passing back the error message

ENGINE SQLORA ? SET

* ---------------------------------------------------------------
 * Oracle interface settings
 * ---------------------------------------------------------------
 (FOC1450) CURRENT ORACLE INTERFACE SETTINGS ARE :
 (FOC1697) DEFAULT CONNECTION NAME             -  : WF_IFS
 (FOC1656) DEFAULT SERVER NAME                 -  : WF_IFS
 (FOC1502) USERID AND PASSWORD ARE             -  : <Password passthru>
 (FOC1496) AUTODISCONNECT OPTION IS            -  : ON FIN
 (FOC1499) AUTOCOMMIT OPTION IS                -  : ON COMMAND
 (FOC1491) FETCH BUFFERING FACTOR              -  : 5000
 (FOC1531) INSERT BUFFERING FACTOR             -  : 500
 (FOC1652) CHARACTER TYPE FOR INSERT OR UPDATE -  : VAR
 (FOC1653) CONVERSION STYLE FOR ORACLE NUMBERS -  : COMPAT
 (FOC1654) ORACLE HOME                         -  : Not set
 (FOC1655) ORACLE SID                          -  : Not set
 (FOC1441) WRITE FUNCTIONALITY IS              -  : ON
 (FOC1445) OPTIMIZATION OPTION IS              -  : ON
 (FOC1763) IF-THEN-ELSE OPTIMIZATION IS        - : ON
 (FOC1484) SQL ERROR MESSAGE TYPE IS           -  : DBMS
 (FOC1552) INTERFACE DEFAULT DATE TYPE         -  : NEW
 (FOC1446) DEFAULT DBSPACE IS                  -  :
 *

Think it is setting under 1484

Yeah, but the same report runs fine for a subset of the data.

J.

Since I know nothing about your data, this is just speculation: Maybe there is data level security, try another time period July to November for example, perhaps December is not available because it isn't over. Is the syntax of the selection criteria for the calendar_id column correct?

John

You should get an error message from Oracle when you run with the wrong subset of the data.

What is it?

Either run the proc through sqlplus or switch the error message passing facility on so you can see what is happening in wf.

As best I can tell it's just a really bad message being returned from Oracle for a plain-old error. This doesn't make a lot of sense since the routine would run fine for some selects but not others.

I had a need to make some changes due to shifting requirements, and when I nuked a section of the code it started working properly. In short, the problem is gone now for some reason I don't understand. For this reason I highly recommend putting off fixing things until the very last possible moment.


J.