July 05, 2007, 05:21 PM
<Umar Farook S>SQLORA with Focus
New to FOCUS when i execute the below code i get the following errors.please help i am new to FOCUS environment
error:
(FOC1400) SQLCODE IS 911 (HEX: 0000038F)
: ORA-00911: invalid character : Erroneous character: #
(FOC1405) SQL PREPARE ERROR.
CODECode-* File mrx_gldailysumarryderivSQL.fex
-* Default Mode: Report FEX
-SET &ECHO = ALL;
-DEFAULT &ASOFDATE= '04/02/2003';
-DEFAULT &OracleConnect='ora023';
ENGINE SQLORA SET SERVER &OracleConnect
ENGINE SQLORA
SELECT
* INTO #TMPMUREXGLACCT
FROM OPENQUERY(ORALINKMUREXIUAT,'SELECT SUBSTR(LTRIM(M_LABEL_GL),1,9) AS AccountCode,M_DESC AS GLACCOUNTDESC
FROM MUREX.TRN_ACA1_DBF')
;
-*
ENGINE SQLORA SET SERVER &OracleConnect
ENGINE SQLORA
select
* INTO #TMPMUREXGLCOMPANY
FROM OPENQUERY(ORALINKMUREXIUAT,'SELECT DISTINCT LEGAL_ENTITY_NBR AS CompanyCode,LEGAL_ENTITY_NAME
FROM MUREXODS.V_PORTFOLIO_ENTITY
WHERE LEGAL_ENTITY_NAME IS NOT LIKE ''%Embedded%''')
;
-*
ENGINE SQLORA SET SERVER &OracleConnect
ENGINE SQLORA
SELECT
(ltrim(MONTH(gl.EntryDate) + '/' + ltrim(DAY(gl.EntryDate)) + '/' + ltrim(YEAR(gl.EntryDate))) AS Postingdate,
gl.company AS CompanyCode,
tmp2.legal_entity_name AS GLEntity,
gl.Segment AS BusinessArea,
gl.GLAcctNo AS AccountCode,
tmp1.GLACCOUNTDESC AS AccountDescription,
gl.DBInd,
CASE gl.DBInd
WHEN 'D' THEN gl.Amount
END AS D_Amount,
CASE gl.DBInd
WHEN 'C' THEN gl.Amount
END AS C_Amount
FROM dbo.wrkGLDerivExtract gl
LEFT JOIN #TMPMUREXGLACCT tmp1
ON gl.GLAcctNo = tmp1.AccountCode
Left JOIN #TMPMUREXGLCOMPANY tmp2
ON gl.company = tmp2.CompanyCode
;
-*
-********************************************************************
-* SAVE SP CURSOR TO A HOLD FILE TO WORK WITH IN FEX
-********************************************************************
TABLE FILE SQLOUT
PRINT *
BY CompanyCode
BY GLEntity
ON TABLE HOLD AS GLDailyEntries_HOLD
END
-RUN
-********************************************************************
-* DEFINE VARIABLES TO WORK WITH IN FEX
-********************************************************************
DEFINE FILE GLDailyEntries_HOLD
BLANK_LINE/A1 = ' ';
F_D_Amount/D14.2CS = D_Amount;
F_C_Amount/D14.2CS = C_Amount;
END
-********************************************************************
-* BEGIN FORMATTING REPort OUTPUTDEFINE VARIABLES TO WORK WITH IN FEX
-********************************************************************
TABLE FILE GLDAILYENTRIES_HOLD
PRINT
BusinessArea
AccountCode
AccountDescription
F_D_Amount AS 'Debit Amount'
F_C_Amount AS 'Credit Amount'
BY
CompanyCode NOPRINT
BY
GLEntity NOPRINT
BY
AccountCode NOPRINT
BY
BusinessArea NOPRINT
ON CompanyCode NOPRINT PAGE-BREAK
ON AccountCode NOPRINT PAGE-BREAK SUBTOTAL
ON GLEntity NOPRINT PAGE-BREAK SUBTOTAL
HEADING
-*PAGE HEADING
"GL Feed - Daily Entries"
-*"PostingDate: &HEADDATE "
"Company Code:
"GL Entity:
-*ON AccountCode NOPRINT SUBFOOT
-*" <84 ------------- <103 -------------"
-*"<50 *TOTAL AccountCode -*" "
-*" "
-*" "
-*" "
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE PCHOLD AS TEST1 FORMAT PDF
ON TABLE SET STYLE *
UNITS=IN,
PAGESIZE='Letter',
LEFTMARGIN=0.250000,
RIGHTMARGIN=0.000000,
TOPMARGIN=0.250000,
BOTTOMMARGIN=0.250000,
SQUEEZE=ON,
ORIENTATION=LANDSCAPE,
$
TYPE=REPORT,
FONT='COURIER NEW',
SIZE=10,
COLOR=BLACK,
BACKCOLOR=NONE,
STYLE=NORMAL,
$
TYPE=DATA,
COLUMN=N1,
SIZE=9,
$
TYPE=DATA,
COLUMN=N2,
SIZE=9,
$
TYPE=DATA,
COLUMN=N3,
SIZE=9,
$
TYPE=DATA,
COLUMN=N4,
SIZE=9,
$
TYPE=DATA,
COLUMN=N5,
SIZE=9,
$
TYPE=DATA,
COLUMN=N6,
SIZE=9,
$
TYPE=TABHEADING,
LINE=1,
JUSTIFY=CENTER,
$
TYPE=TABHEADING,
LINE=1,
OBJECT=TEXT,
ITEM=1,
SIZE=14,
STYLE=BOLD,
$
TYPE=TABHEADING,
LINE=1,
OBJECT=TEXT,
ITEM=2,
SIZE=14,
STYLE=BOLD,
$
TYPE=TABHEADING,
LINE=1,
OBJECT=FIELD,
ITEM=1,
SIZE=14,
STYLE=BOLD,
$
TYPE=TABHEADING,
LINE=2,
JUSTIFY=CENTER,
$
ENDSTYLE
END
July 05, 2007, 05:27 PM
FrankDutchit's an SQL error not a focus problem in fact.
It says something about the # character. Why is it there?
July 05, 2007, 05:44 PM
<Umar Farook S># are mentioned infront of the temporary table names
July 05, 2007, 05:44 PM
Francis Marianiquote:
SELECT
* INTO #TMPMUREXGLACCT
You're creating a temporary Oracle table, correct? I don't think that all of this can be passed as SQL passthru in one FOCUS request. You may be able to do this as a remote procedure, but I'm not sure of the sayntax. Look for "remote procedure" in the forum and the IBI documentation.
July 05, 2007, 05:48 PM
<Umar Farook S>SQL works fine wehn executed in SQL Query Analyzer but whats the problem when calling into webfocus
July 05, 2007, 05:51 PM
<Umar Farook S>thanks virtuoso
i first wrote it as seperate prcedure in SQl and called it into the webfocus at that time also i had same error,
error attached:
(FOC1400) SQLCODE IS 7300 (HEX: 00001C84) XOPEN: 01000
: Microsoft OLE DB Provider for SQL Server: [01000] OLE DB error trace [OL
: E/DB Provider 'MSDAORA' ITransactionJoin::JoinTransaction returned 0x800
: 4d01b]. [42000] The operation could not be performed because the OLE DB
: provider 'MSDAORA' was unable to begin a distributed transaction.
(FOC1405) SQL PREPARE ERROR.
July 05, 2007, 06:48 PM
Francis MarianiThat's a completely different problem which I can't help you with.
July 05, 2007, 06:57 PM
<Umar Farook S>thanks Francis
BUT in both case i get the same kind of error
FOC1400 and FOC1405
July 05, 2007, 07:02 PM
Francis Marianithe WebFOCUS errors are occurring because of the bigger OLE DB error - you have to solve that one first. Perhaps you're trying to run a remote procedure on a server that webfocus cannot access, or on some kind of multi-tier environment, or it could be a simple config problem, but I don't think it's WebFOCUS.