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

it's an SQL error not a focus problem in fact.

It says something about the # character. Why is it there?

# are mentioned infront of the temporary table names

quote:

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.

SQL works fine wehn executed in SQL Query Analyzer but whats the problem when calling into webfocus

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.

That's a completely different problem which I can't help you with.

thanks Francis

BUT in both case i get the same kind of error
FOC1400 and FOC1405

the 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.