Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Issue with Query generation from Masterfile.
Go
New
Search
Notify
Tools
Reply
  
[CLOSED] Issue with Query generation from Masterfile.
 Login/Join
 
Member
posted
Hi,
I have noticed an issue with the SQL that is being generated from a Master file.
Info: We have a Masterfile which will use few tables to fetch some data from DB. This is working as expected in Dev and stage environments with Oracle as the DB.
Of late we had a new data source (Exadata) and when we run the same report/master file it is getting broken with an SQL error. We noticed that all the table names are same in both the DB's and there is a missing parenthesis.
Need your assistance here in understanding what would be the problem. Thanks.

This message has been edited. Last edited by: <Kathryn Henning>,


WebFOCUS 7.7.05 and 8.x
Windows, All Outputs
 
Posts: 25 | Registered: December 13, 2012Reply With QuoteReport This Post
Expert
posted Hide Post
What are the ORA messages ?

And can you post the Pre and Post generated SQL ?


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.06OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Know The Code

 
Posts: 6124 | Location: 33.8688° S, 151.2093° E | Registered: October 31, 2006Reply With QuoteReport This Post
Member
posted Hide Post
Below is the error message i am getting from the caster log.
Task error: SQLCODE IS 942 (HEX: 000003AE)
No report to distribute.

When I take the query and run in from TOAD it is missing a parenthesis. When I manually keep it, query is getting executed in TOAD.
My undertanding is that the SQL will be created from Masterfile when the report is executed. If so, why is that missing parenthesis.
But in other dev environments it is preparing the right query with our any issues.


WebFOCUS 7.7.05 and 8.x
Windows, All Outputs
 
Posts: 25 | Registered: December 13, 2012Reply With QuoteReport This Post
Master
posted Hide Post
Did you try creating master file using Synonym editor by directing fetching it from Exadata instead of reusing master files created from Oracle.

Thanks,
Ram
 
Posts: 542 | Location: Dearborn, MI | Registered: June 03, 2009Reply With QuoteReport This Post
Master
posted Hide Post
Technically Prabhakar, the SQL is generated by the adapter.

Can you provide a copy of your FEX and run a SQL trace and provide that as well. This will help us point you in the correct direction as your message is pretty vague. There are usually ways to tweak your FOCUS code so that SQL can be generated more efficiently as not everything translates properly into Oracle. This might be in your procedure or ultimately this could be in your metadata, depending how much you tweak your metadata and whether you include any define fields or not. But the best way for us to be able to move forward though is to get the ORA message, the procedure, and the SQL output.


Eric Woerle
8.1.05M Gen 913- Reporting Server Unix
8.1.05 Client Unix
Oracle 11.2.0.2
 
Posts: 750 | Location: Warrenville, IL | Registered: January 08, 2013Reply With QuoteReport This Post
Member
posted Hide Post
Here is the masterfile.


-SET &ECHO = ALL;
TABLE FILE FINTXNBYACCT
PRINT
FINTXNBYACCT.ACCT_VW.NAM_LN_1_TXT
FINTXNBYACCT.ACCT_VW.NAM_LN_2_TXT
FINTXNBYACCT.ACCT_VW.ACCT_NUM
FINTXNBYACCT.ACCT_HIER_ADDR.ADDR_LN_1_TXT
FINTXNBYACCT.ACCT_HIER_ADDR.ADDR_LN_2_TXT
FINTXNBYACCT.ACCT_HIER_ADDR.ADDR_LN_3_TXT
FINTXNBYACCT.ACCT_HIER_ADDR.ADDR_LN_4_TXT
FINTXNBYACCT.ACCT_HIER_ADDR.CITY_NAM
FINTXNBYACCT.FIN_TXN_AND_SPLIT_TXN_VW.REVIEWED_SW AS 'REVIEWED_SW'
FINTXNBYACCT.FIN_TXN_AND_SPLIT_TXN_VW.APPROVED_SW AS 'APPROVED_SW'
FINTXNBYACCT.CARD_ACCPTR.CARD_ACCPTR_NAM AS 'CARD_ACCPTR_NAM'
FINTXNBYACCT.CARD_ACCPTR.CARD_ACCPTR_CITY_NAM AS 'CARD_ACCPTR_CITY_NAM'
FINTXNBYACCT.CARD_ACCPTR.CARD_ACCPTR_ST_PROV_CD AS 'CARD_ACCPTR_ST_PROV_CD'
FINTXNBYACCT.CARD_ACCPTR.CARD_ACCPTR_LOC_PSTL_CD AS 'CARD_ACCPTR_LOC_PSTL_CD'
FINTXNBYACCT.FIN_TXN_AND_SPLIT_TXN_VW.TXN_DESC AS 'TXN_DESC'
FINTXNBYACCT.ACCT_HIER_ADDR.ST_PROV_CD
FINTXNBYACCT.ACCT_HIER_ADDR.PSTL_CD
FINTXNBYACCT.ACCT_HIER_ADDR.CTY_CD
FINTXNBYACCT.FIN_TXN_AND_SPLIT_TXN_VW.POSTING_DT
FINTXNBYACCT.FIN_TXN_AND_SPLIT_TXN_VW.TXN_DT
FINTXNBYACCT.ACCT_VW.CORP_ID
FINTXNBYACCT.ACCT_VW.ISS_ID
FINTXNBYACCT.FIN_TXN_AND_SPLIT_TXN_VW.TXN_TYPE_CD
FINTXNBYACCT.FIN_TXN_AND_SPLIT_TXN_VW.TRANSACTION_AMT
FINTXNBYACCT.FIN_TXN_AND_SPLIT_TXN_VW.EXPENSE_DESC
FINTXNBYACCT.CARD_ACCPTR.CARD_ACCPTR_NAM
FINTXNBYACCT.FIN_TXN_AND_SPLIT_TXN_VW.DR_CR_CD
-*FINTXNBYACCT.ACCTNG_CD_FIN_TXN.ACCTNG_CD_SCHEME_ID
FINTXNBYACCT.FIN_TXN_AND_SPLIT_TXN_VW.SPLIT_TXN_ID
FINTXNBYACCT.ACCT_VW.INTRL_ACCTNG_CD
FINTXNBYACCT.FIN_TXN_AND_SPLIT_TXN_VW.TXN_DESC
FINTXNBYACCT.FIN_TXN_AND_SPLIT_TXN_VW.BILL_DT
FINTXNBYACCT.FIN_TXN_AND_SPLIT_TXN_VW.PRCSSNG_DT
FINTXNBYACCT.ACCT_VW.STAT_CD
FINTXNBYACCT.FIN_TXN_AND_SPLIT_TXN_VW.CORP_ID
FINTXNBYACCT.FIN_TXN_AND_SPLIT_TXN_VW.INCLUDE_SPLIT
FINTXNBYACCT.ACCT_VW.ACCT_ID
FINTXNBYACCT.ACCT_VW.DECRYPTED_ACCT_NUM
FINTXNBYACCT.FIN_TXN_AND_SPLIT_TXN_VW.POSTING_DT AS 'POSTING_DT1'
FINTXNBYACCT.FIN_TXN_AND_SPLIT_TXN_VW.TXN_DT AS 'TXN_DT1'
FINTXNBYACCT.FIN_TXN_AND_SPLIT_TXN_VW.PRCSSNG_DT AS 'PRCSSNG_DT1'
FINTXNBYACCT.FIN_TXN_AND_SPLIT_TXN_VW.BILL_DT AS 'BILL_DT1'
FINTXNBYACCT.FIN_TXN_AND_SPLIT_TXN_VW.PRIMARY_TRANSACTION_AMT AS 'PRIMARY_TRANSACTION_AMT'
FINTXNBYACCT.FIN_TXN_AND_SPLIT_TXN_VW.TRANSACTION_AMT AS 'TRANSACTION_AMT'
-* FINTXNBYACCT.ACCTNG_CD.FIELD_NAM
-* FINTXNBYACCT.ACCTNG_CD.SEQ_NUM
FIN_TXN_ID
ACCTNG_CD_FIN_TXN.ACCTNG_CD_DESC_1
ACCTNG_CD_FIN_TXN.ACCTNG_CD_DATA_VAL_1
ACCTNG_CD_FIN_TXN.ACCTNG_CD_DESC_2
ACCTNG_CD_FIN_TXN.ACCTNG_CD_DATA_VAL_2
ACCTNG_CD_FIN_TXN.ACCTNG_CD_DESC_3
ACCTNG_CD_FIN_TXN.ACCTNG_CD_DATA_VAL_3
ACCTNG_CD_FIN_TXN.ACCTNG_CD_DESC_4
ACCTNG_CD_FIN_TXN.ACCTNG_CD_DATA_VAL_4
ACCTNG_CD_FIN_TXN.ACCTNG_CD_DESC_5
ACCTNG_CD_FIN_TXN.ACCTNG_CD_DATA_VAL_5
ACCTNG_CD_FIN_TXN.ACCTNG_CD_DESC_6
ACCTNG_CD_FIN_TXN.ACCTNG_CD_DATA_VAL_6
ACCTNG_CD_FIN_TXN.ACCTNG_CD_DESC_7
ACCTNG_CD_FIN_TXN.ACCTNG_CD_DATA_VAL_7
ACCTNG_CD_FIN_TXN.ACCTNG_CD_DESC_8
ACCTNG_CD_FIN_TXN.ACCTNG_CD_DATA_VAL_8
ACCTNG_CD_FIN_TXN.ACCTNG_CD_DESC_9
ACCTNG_CD_FIN_TXN.ACCTNG_CD_DATA_VAL_9
ACCTNG_CD_FIN_TXN.ACCTNG_CD_DESC_10
ACCTNG_CD_FIN_TXN.ACCTNG_CD_DATA_VAL_10
ACCTNG_CD_FIN_TXN.ACCTNG_CD_DESC_11
ACCTNG_CD_FIN_TXN.ACCTNG_CD_DATA_VAL_11
ACCTNG_CD_FIN_TXN.ACCTNG_CD_DESC_12
ACCTNG_CD_FIN_TXN.ACCTNG_CD_DATA_VAL_12
ACCTNG_CD_FIN_TXN.ACCTNG_CD_DESC_13
ACCTNG_CD_FIN_TXN.ACCTNG_CD_DATA_VAL_13
ACCTNG_CD_FIN_TXN.ACCTNG_CD_DESC_14
ACCTNG_CD_FIN_TXN.ACCTNG_CD_DATA_VAL_14
ACCTNG_CD_FIN_TXN.ACCTNG_CD_DESC_15
ACCTNG_CD_FIN_TXN.ACCTNG_CD_DATA_VAL_15
ACCTNG_CD_FIN_TXN.ACCTNG_CD_DESC_16
ACCTNG_CD_FIN_TXN.ACCTNG_CD_DATA_VAL_16
ACCTNG_CD_FIN_TXN.ACCTNG_CD_DESC_17
ACCTNG_CD_FIN_TXN.ACCTNG_CD_DATA_VAL_17
ACCTNG_CD_FIN_TXN.ACCTNG_CD_DESC_18
ACCTNG_CD_FIN_TXN.ACCTNG_CD_DATA_VAL_18
ACCTNG_CD_FIN_TXN.ACCTNG_CD_DESC_19
ACCTNG_CD_FIN_TXN.ACCTNG_CD_DATA_VAL_19
ACCTNG_CD_FIN_TXN.ACCTNG_CD_DESC_20
ACCTNG_CD_FIN_TXN.ACCTNG_CD_DATA_VAL_20
ACCTNG_CD_FIN_TXN.ACCTNG_CD_SCHEME_ID AS 'SCHEME_ID'
&POSTCOMM.EVAL WHERE FINTXNBYACCT.FIN_TXN_AND_SPLIT_TXN_VW.POSTING_DT IS-FROM &START_DT.QUOTEDSTRING TO &END_DT.QUOTEDSTRING ;
&TXNCOMM.EVAL WHERE FINTXNBYACCT.FIN_TXN_AND_SPLIT_TXN_VW.TXN_DT IS-FROM &START_DATE.QUOTEDSTRING TO &END_DATE.QUOTEDSTRING ;
&PROCCOMM.EVAL WHERE FINTXNBYACCT.FIN_TXN_AND_SPLIT_TXN_VW.PRCSSNG_DT IS-FROM &START_DATE.QUOTEDSTRING TO &END_DATE.QUOTEDSTRING ;
&BILLCOMM.EVAL WHERE FINTXNBYACCT.FIN_TXN_AND_SPLIT_TXN_VW.BILL_DT IS-FROM &START_DATE.QUOTEDSTRING TO &END_DATE.QUOTEDSTRING ;
WHERE FINTXNBYACCT.FIN_TXN_AND_SPLIT_TXN_VW.CORP_ID EQ &CORP_ID.EVAL;
WHERE FINTXNBYACCT.ACCTNG_CD_FIN_TXN.ACCTNG_CD_SCHEME_ID EQ &SCHEME_ID.EVAL;
&COMMENT_ACCOUNT_STATUS.EVAL WHERE FINTXNBYACCT.ACCT_VW.STAT_CD IN &ACCOUNT_STATUS_LIST ;
&ACCTCOMM.EVAL WHERE FINTXNBYACCT.ACCT_VW.ACCT_ID IN (&FOR_ENTITY_ID.EVAL ) ;
&CORPCOMM.EVAL WHERE FINTXNBYACCT.ACCT_VW.CORP_ID IN (&FOR_ENTITY_ID.EVAL ) ;
&ISSUCOMM.EVAL WHERE FINTXNBYACCT.ACCT_VW.ISS_ID IN (&FOR_ENTITY_ID.EVAL ) ;
&ACCTGRCOMM.EVAL WHERE FINTXNBYACCT.ACCT_GRP_DTL.ACCT_GRP_ID IN (&FOR_ENTITY_ID.EVAL ) ;
&ORGCOMM.EVAL WHERE FINTXNBYACCT.ORG_POINT_FLATND_HIER_VW.PARENT_ORG_POINT_ID IN (&FOR_ENTITY_ID.EVAL );
&CGRPCOMM.EVAL WHERE FINTXNBYACCT.ACCT_VW.CORP_GRP_ID IN (&FOR_ENTITY_ID.EVAL ) ;
&IGRPCOMM.EVAL WHERE FINTXNBYACCT.ACCT_VW.ISS_GRP_ID IN (&FOR_ENTITY_ID.EVAL ) ;

WHERE FINTXNBYACCT.FIN_TXN_AND_SPLIT_TXN_VW.INCLUDE_SPLIT EQ &INCL_SPLIT.QUOTEDSTRING OR 'NY';
&FILTER_F_FINANCIAL_APPROVED
&FILTER_F_FINANCIAL_REVIEWED
&FILTER_F_FINANCIAL_TXN_TYPE
ON TABLE HOLD AS HOLD1
END
-RUN
-EXIT

After passing the valid parameters it is erroring out and below is the sql trace. TABLE FILE FINTXNBYACCT
PRINT
FINTXNBYACCT.ACCT_VW.NAM_LN_1_TXT
FINTXNBYACCT.ACCT_VW.NAM_LN_2_TXT
FINTXNBYACCT.ACCT_VW.ACCT_NUM
FINTXNBYACCT.ACCT_HIER_ADDR.ADDR_LN_1_TXT
FINTXNBYACCT.ACCT_HIER_ADDR.ADDR_LN_2_TXT
FINTXNBYACCT.ACCT_HIER_ADDR.ADDR_LN_3_TXT
FINTXNBYACCT.ACCT_HIER_ADDR.ADDR_LN_4_TXT
FINTXNBYACCT.ACCT_HIER_ADDR.CITY_NAM
FINTXNBYACCT.FIN_TXN_AND_SPLIT_TXN_VW.REVIEWED_SW AS 'REVIEWED_SW'
FINTXNBYACCT.FIN_TXN_AND_SPLIT_TXN_VW.APPROVED_SW AS 'APPROVED_SW'
-* FINTXNBYACCT.ACCTNG_CD.SEQ_NUM AS 'ACCTNG_CD_SEQ_NUM'
FINTXNBYACCT.CARD_ACCPTR.CARD_ACCPTR_NAM AS 'CARD_ACCPTR_NAM'
FINTXNBYACCT.CARD_ACCPTR.CARD_ACCPTR_CITY_NAM AS 'CARD_ACCPTR_CITY_NAM'
FINTXNBYACCT.CARD_ACCPTR.CARD_ACCPTR_ST_PROV_CD AS 'CARD_ACCPTR_ST_PROV_CD'
FINTXNBYACCT.CARD_ACCPTR.CARD_ACCPTR_LOC_PSTL_CD AS 'CARD_ACCPTR_LOC_PSTL_CD'
FINTXNBYACCT.FIN_TXN_AND_SPLIT_TXN_VW.TXN_DESC AS 'TXN_DESC'
FINTXNBYACCT.ACCT_HIER_ADDR.ST_PROV_CD
FINTXNBYACCT.ACCT_HIER_ADDR.PSTL_CD
FINTXNBYACCT.ACCT_HIER_ADDR.CTY_CD
FINTXNBYACCT.FIN_TXN_AND_SPLIT_TXN_VW.POSTING_DT
FINTXNBYACCT.FIN_TXN_AND_SPLIT_TXN_VW.TXN_DT
FINTXNBYACCT.ACCT_VW.CORP_ID
FINTXNBYACCT.ACCT_VW.ISS_ID
FINTXNBYACCT.FIN_TXN_AND_SPLIT_TXN_VW.TXN_TYPE_CD
FINTXNBYACCT.FIN_TXN_AND_SPLIT_TXN_VW.TRANSACTION_AMT
FINTXNBYACCT.FIN_TXN_AND_SPLIT_TXN_VW.EXPENSE_DESC
FINTXNBYACCT.CARD_ACCPTR.CARD_ACCPTR_NAM
FINTXNBYACCT.FIN_TXN_AND_SPLIT_TXN_VW.DR_CR_CD
-*FINTXNBYACCT.ACCTNG_CD_FIN_TXN.ACCTNG_CD_SCHEME_ID
FINTXNBYACCT.FIN_TXN_AND_SPLIT_TXN_VW.SPLIT_TXN_ID
FINTXNBYACCT.ACCT_VW.INTRL_ACCTNG_CD
FINTXNBYACCT.FIN_TXN_AND_SPLIT_TXN_VW.TXN_DESC
FINTXNBYACCT.FIN_TXN_AND_SPLIT_TXN_VW.BILL_DT
FINTXNBYACCT.FIN_TXN_AND_SPLIT_TXN_VW.PRCSSNG_DT
FINTXNBYACCT.ACCT_VW.STAT_CD
FINTXNBYACCT.FIN_TXN_AND_SPLIT_TXN_VW.CORP_ID
FINTXNBYACCT.FIN_TXN_AND_SPLIT_TXN_VW.INCLUDE_SPLIT
FINTXNBYACCT.ACCT_VW.ACCT_ID
FINTXNBYACCT.ACCT_VW.DECRYPTED_ACCT_NUM
FINTXNBYACCT.FIN_TXN_AND_SPLIT_TXN_VW.POSTING_DT AS 'POSTING_DT1'
FINTXNBYACCT.FIN_TXN_AND_SPLIT_TXN_VW.TXN_DT AS 'TXN_DT1'
FINTXNBYACCT.FIN_TXN_AND_SPLIT_TXN_VW.PRCSSNG_DT AS 'PRCSSNG_DT1'
FINTXNBYACCT.FIN_TXN_AND_SPLIT_TXN_VW.BILL_DT AS 'BILL_DT1'
FINTXNBYACCT.FIN_TXN_AND_SPLIT_TXN_VW.PRIMARY_TRANSACTION_AMT AS 'PRIMARY_TRANSACTION_AMT'
FINTXNBYACCT.FIN_TXN_AND_SPLIT_TXN_VW.TRANSACTION_AMT AS 'TRANSACTION_AMT'
-* FINTXNBYACCT.ACCTNG_CD.FIELD_NAM
-* FINTXNBYACCT.ACCTNG_CD.SEQ_NUM
FIN_TXN_ID
ACCTNG_CD_FIN_TXN.ACCTNG_CD_DESC_1
ACCTNG_CD_FIN_TXN.ACCTNG_CD_DATA_VAL_1
ACCTNG_CD_FIN_TXN.ACCTNG_CD_DESC_2
ACCTNG_CD_FIN_TXN.ACCTNG_CD_DATA_VAL_2
ACCTNG_CD_FIN_TXN.ACCTNG_CD_DESC_3
ACCTNG_CD_FIN_TXN.ACCTNG_CD_DATA_VAL_3
ACCTNG_CD_FIN_TXN.ACCTNG_CD_DESC_4
ACCTNG_CD_FIN_TXN.ACCTNG_CD_DATA_VAL_4
ACCTNG_CD_FIN_TXN.ACCTNG_CD_DESC_5
ACCTNG_CD_FIN_TXN.ACCTNG_CD_DATA_VAL_5
ACCTNG_CD_FIN_TXN.ACCTNG_CD_DESC_6
ACCTNG_CD_FIN_TXN.ACCTNG_CD_DATA_VAL_6
ACCTNG_CD_FIN_TXN.ACCTNG_CD_DESC_7
ACCTNG_CD_FIN_TXN.ACCTNG_CD_DATA_VAL_7
ACCTNG_CD_FIN_TXN.ACCTNG_CD_DESC_8
ACCTNG_CD_FIN_TXN.ACCTNG_CD_DATA_VAL_8
ACCTNG_CD_FIN_TXN.ACCTNG_CD_DESC_9
ACCTNG_CD_FIN_TXN.ACCTNG_CD_DATA_VAL_9
ACCTNG_CD_FIN_TXN.ACCTNG_CD_DESC_10
ACCTNG_CD_FIN_TXN.ACCTNG_CD_DATA_VAL_10
ACCTNG_CD_FIN_TXN.ACCTNG_CD_DESC_11
ACCTNG_CD_FIN_TXN.ACCTNG_CD_DATA_VAL_11
ACCTNG_CD_FIN_TXN.ACCTNG_CD_DESC_12
ACCTNG_CD_FIN_TXN.ACCTNG_CD_DATA_VAL_12
ACCTNG_CD_FIN_TXN.ACCTNG_CD_DESC_13
ACCTNG_CD_FIN_TXN.ACCTNG_CD_DATA_VAL_13
ACCTNG_CD_FIN_TXN.ACCTNG_CD_DESC_14
ACCTNG_CD_FIN_TXN.ACCTNG_CD_DATA_VAL_14
ACCTNG_CD_FIN_TXN.ACCTNG_CD_DESC_15
ACCTNG_CD_FIN_TXN.ACCTNG_CD_DATA_VAL_15
ACCTNG_CD_FIN_TXN.ACCTNG_CD_DESC_16
ACCTNG_CD_FIN_TXN.ACCTNG_CD_DATA_VAL_16
ACCTNG_CD_FIN_TXN.ACCTNG_CD_DESC_17
ACCTNG_CD_FIN_TXN.ACCTNG_CD_DATA_VAL_17
ACCTNG_CD_FIN_TXN.ACCTNG_CD_DESC_18
ACCTNG_CD_FIN_TXN.ACCTNG_CD_DATA_VAL_18
ACCTNG_CD_FIN_TXN.ACCTNG_CD_DESC_19
ACCTNG_CD_FIN_TXN.ACCTNG_CD_DATA_VAL_19
ACCTNG_CD_FIN_TXN.ACCTNG_CD_DESC_20
ACCTNG_CD_FIN_TXN.ACCTNG_CD_DATA_VAL_20
ACCTNG_CD_FIN_TXN.ACCTNG_CD_SCHEME_ID AS 'SCHEME_ID'
WHERE FINTXNBYACCT.FIN_TXN_AND_SPLIT_TXN_VW.POSTING_DT IS-FROM '2014/07/01' TO '2014/08/01' ;
-* WHERE FINTXNBYACCT.FIN_TXN_AND_SPLIT_TXN_VW.TXN_DT IS-FROM &START_DATE.QUOTEDSTRING TO &END_DATE.QUOTEDSTRING ;
-* WHERE FINTXNBYACCT.FIN_TXN_AND_SPLIT_TXN_VW.PRCSSNG_DT IS-FROM &START_DATE.QUOTEDSTRING TO &END_DATE.QUOTEDSTRING ;
-* WHERE FINTXNBYACCT.FIN_TXN_AND_SPLIT_TXN_VW.BILL_DT IS-FROM &START_DATE.QUOTEDSTRING TO &END_DATE.QUOTEDSTRING ;
WHERE FINTXNBYACCT.FIN_TXN_AND_SPLIT_TXN_VW.CORP_ID EQ 821;
WHERE FINTXNBYACCT.ACCTNG_CD_FIN_TXN.ACCTNG_CD_SCHEME_ID EQ _FOC_NULL;
-* WHERE FINTXNBYACCT.ACCT_VW.STAT_CD IN &ACCOUNT_STATUS_LIST ;
-* WHERE FINTXNBYACCT.ACCT_VW.ACCT_ID IN (821 ) ;
WHERE FINTXNBYACCT.ACCT_VW.CORP_ID IN (821 ) ;
-* WHERE FINTXNBYACCT.ACCT_VW.ISS_ID IN (821 ) ;
-* WHERE FINTXNBYACCT.ACCT_GRP_DTL.ACCT_GRP_ID IN (821 ) ;
-* WHERE FINTXNBYACCT.ORG_POINT_FLATND_HIER_VW.PARENT_ORG_POINT_ID IN (821 );
-* WHERE FINTXNBYACCT.ACCT_VW.CORP_GRP_ID IN (821 ) ;
-* WHERE FINTXNBYACCT.ACCT_VW.ISS_GRP_ID IN (821 ) ;
WHERE FINTXNBYACCT.FIN_TXN_AND_SPLIT_TXN_VW.INCLUDE_SPLIT EQ 'N' OR 'NY';
_FOC_NULL
_FOC_NULL
_FOC_NULL
ON TABLE HOLD AS HOLD1
END
-RUN
FOC2525 - FOREIGN KEY IS NOT A SUPERSET OF PRIMARY KEY FOR SEGMENT: HIER_ADDR
FOC2509 - RDBMS-MANAGED JOIN SELECTED BUT RESULTS MAY BE INACCURATE
FOC2524 - JOIN TO A UNIQUE SEGMENT DOES NOT COVER ITS PRIMARY KEY
FOC2590 - AGGREGATION NOT DONE FOR THE FOLLOWING REASON:
FOC2594 - AGGREGATION IS NOT APPLICABLE TO THE VERB USED
SELECT
SDNG_DBA.DECRYPT_DATA(T1."ACCT_NUM"),
NVL(T9."SPLIT_TXN_ID",-1),
T1."ACCT_ID",
T1."CORP_ID",
T1."ISS_ID",
T1."ACCT_NUM",
T1."STAT_CD",
T1."NAM_LN_1_TXT",
T1."NAM_LN_2_TXT",
T1."INTRL_ACCTNG_CD",
T4."HIER_POINT_CD",
T4."ENTITY_ID",
T4."PRIM_ADDR_SW",
T4."ADDR_LN_1_TXT",
T4."ADDR_LN_2_TXT",
T4."ADDR_LN_3_TXT",
T4."ADDR_LN_4_TXT",
T4."CITY_NAM",
T4."ST_PROV_CD",
T4."CTY_CD",
T4."PSTL_CD",
T5."CORP_ID",
T5."FIN_TXN_ID",
T5."SPLIT_TXN_ID",
T5."APPROVED_SW",
T5."BILL_DT",
T5."CARD_ACCPTR_ID",
T5."POSTING_DT",
T5."PRCSSNG_DT",
T5."REVIEWED_SW",
T5."TXN_DESC",
T5."TXN_DT",
T5."TXN_TYPE_CD",
T5."DR_CR_CD",
T5."EXPENSE_DESC",
T5."PRIMARY_TRANSACTION_AMT",
T5."TRANSACTION_AMT",
T5."INCLUDE_SPLIT",
T7."CARD_ACCPTR_ID",
T7."CARD_ACCPTR_NAM",
T7."CARD_ACCPTR_CITY_NAM",
T7."CARD_ACCPTR_ST_PROV_CD",
T7."CARD_ACCPTR_LOC_PSTL_CD",
T9."ACCTNG_CD_FIN_TXN_ID",
T9."FIN_TXN_ID",
T9."SPLIT_TXN_ID",
T9."ACCTNG_CD_DATA_VAL_1",
T9."ACCTNG_CD_DESC_1",
T9."ACCTNG_CD_DATA_VAL_2",
T9."ACCTNG_CD_DESC_2",
T9."ACCTNG_CD_DATA_VAL_3",
T9."ACCTNG_CD_DESC_3",
T9."ACCTNG_CD_DATA_VAL_4",
T9."ACCTNG_CD_DESC_4",
T9."ACCTNG_CD_DATA_VAL_5",
T9."ACCTNG_CD_DESC_5",
T9."ACCTNG_CD_DATA_VAL_6",
T9."ACCTNG_CD_DESC_6",
T9."ACCTNG_CD_DATA_VAL_7",
T9."ACCTNG_CD_DESC_7",
T9."ACCTNG_CD_DATA_VAL_8",
T9."ACCTNG_CD_DESC_8",
T9."ACCTNG_CD_DATA_VAL_9",
T9."ACCTNG_CD_DESC_9",
T9."ACCTNG_CD_DATA_VAL_10",
T9."ACCTNG_CD_DESC_10",
T9."ACCTNG_CD_DATA_VAL_11",
T9."ACCTNG_CD_DESC_11",
T9."ACCTNG_CD_DATA_VAL_12",
T9."ACCTNG_CD_DESC_12",
T9."ACCTNG_CD_SCHEME_ID",
T9."ACCTNG_CD_DATA_VAL_13",
T9."ACCTNG_CD_DESC_13",
T9."ACCTNG_CD_DATA_VAL_14",
T9."ACCTNG_CD_DESC_14",
T9."ACCTNG_CD_DATA_VAL_15",
T9."ACCTNG_CD_DESC_15",
T9."ACCTNG_CD_DATA_VAL_16",
T9."ACCTNG_CD_DESC_16",
T9."ACCTNG_CD_DATA_VAL_17",
T9."ACCTNG_CD_DESC_17",
T9."ACCTNG_CD_DATA_VAL_18",
T9."ACCTNG_CD_DESC_18",
T9."ACCTNG_CD_DATA_VAL_19",
T9."ACCTNG_CD_DESC_19",
T9."ACCTNG_CD_DATA_VAL_20",
T9."ACCTNG_CD_DESC_20"
FROM
( ( ( ( SDNG_DBA.ACCT_VW T1
LEFT OUTER JOIN SDNG_DBA.HIER_ADDR T4
ON T4."ENTITY_ID" = T1."ACCT_ID" AND
((T4."HIER_POINT_CD" = 'A') AND (T4."PRIM_ADDR_SW" = 'Y'))
INNER JOIN SDNG_DBA.FIN_TXN_AND_SPLIT_TXN_VW T5
ON T5."ACCT_ID" = T1."ACCT_ID" )
LEFT OUTER JOIN SDNG_DBA.CARD_ACCPTR T7
ON T7."CARD_ACCPTR_ID" = T5."CARD_ACCPTR_ID" )
LEFT OUTER JOIN SDNG_DBA.ACCTNG_CD_FIN_TXN T9
ON T9."FIN_TXN_ID" = T5."FIN_TXN_ID" AND
(T5."SPLIT_TXN_ID" = NVL(T9."SPLIT_TXN_ID",-1)) )

--THE CLOSING BRACKET SHOWN BELOW IS MISSING WHEN THE QUERY IS PREPARED. I KEPT IT FOR UNDERSTANDING PURPOSE)

WHERE
(T1."CORP_ID" = 821) AND
(T5."INCLUDE_SPLIT" IN('N', 'NY')) AND
(T5."CORP_ID" = 821) AND
(T5."POSTING_DT" BETWEEN TO_DATE('01-07-2014','DD-MM-YYYY') AND
TO_DATE('01-08-2014','DD-MM-YYYY'));
-EXIT
Connection to the Reporting Server EDASERVE closed at 2014-08-27 06:57:38.586-0500 (1,409,140,658,586)
Job ran on the Reporting Server EDASERVE for 3.167 seconds

With that bracket in place the report will execute successfully.
When i run this fex on other servers it is working fine.
Please guide and assist. Thanks.


WebFOCUS 7.7.05 and 8.x
Windows, All Outputs
 
Posts: 25 | Registered: December 13, 2012Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Issue with Query generation from Masterfile.

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.