Hello, Sorry this has taken a while I debated on posting again as this is long, but here goes.
On mainframe and midrange platforms that I have previously worked on Files is what you call Tables.
T_SQL is the form of SQL I am using via Microsoft SQL Server Management Studio.
The Server this will run is a SQL SERVER
Below is a much more detail description of the SQL program and the issue. Thanks!
/* Obtain data and populate the initial file (#INITIAL_FASLO) */
IF EXISTS (SELECT * FROM TEMPDB.DBO.SYSOBJECTS WHERE NAME LIKE '#INITIAL_FASLO%')
DROP TABLE #INITIAL_FASLO
SELECT*
INTO#INITIAL_FASLO
FROMOPENQUERY(AURFBD, '
SELECT
L.LOAN_NUMBER AS PURCHASE_ORDER, L.LOAN_NUMBER, L.INVESTOR_ID,
CAST(NULL AS VARCHAR(25)) AS WHOLE_LOAN_STATUS_HOLDER,
CAST(NULL AS VARCHAR(20)) AS FinDelinquencyReason,
L.ZONE, LM.LM_TEMPLATE_ID, LM.LOSS_MIT_SET_UP_DATE, LM.LOSS_MIT_STATUS_CODE,
/* 45+ more lines of various fields continue unitll the FROM statement */
FROMBDE.LOAN_V L
INNER JOIN BDE.LOSS_MITIGATION_V LM ON (L.LOAN_NUMBER = LM.LOAN_NUMBER)
INNER JOIN BDE.BORROWER_V B ON (L.LOAN_NUMBER = B.LOAN_NUMBER)
INNER JOIN BDE.YEAR_END_V YE ON (L.LOAN_NUMBER = YE.LOAN_NUMBER)
/* Continue 12 more inner joins related to the FROM statement, then begin left outer joins with select statements to pull max dates*/
LEFT OUTER JOIN (
SELECTA.*
FROM BDE.ARM_PAYMENT_V A
INNER JOIN(SELECTLOAN_NUMBER, MAX(ARM_PR_EFFECTIVE_DATE) AS MAX_ARMPED
FROMBDE.ARM_PAYMENT_V
WHEREARM_PR_EFFECTIVE_DATE < CURRENT DATE
GROUP BY LOAN_NUMBER) AP_MAXDT
ON (A.LOAN_NUMBER = AP_MAXDT.LOAN_NUMBER AND A.ARM_PR_EFFECTIVE_DATE = AP_MAXDT.MAX_ARMPED)
) AP ON (L.LOAN_NUMBER = AP.LOAN_NUMBER)
/* The WHERE clause of the MAIN Select statement all of the above is within */
WHEREL.LOAN_NUMBER ''0999999998''
AND LM.LM_TEMPLATE_ID = ''FAMOD''
AND L.ZONE NOT IN (''DD'', ''DU'', ''DH'', ''DM'', ''DL'')
AND P.PROPERTY_TYPE_FNMA_CODE IN (''1'',''2'',''3'',''5'',''6'',''7'')
AND P.OCCUPANCY_CODE = ''1''
AND P.OCCUPANCY_CURRENT_STATUS_CODE IN (''0'', ''1'', ''2'', ''7'', ''9'')
AND L.LO_TYPE IN (''3'',''6'')
AND (U.USER_03_POSITION_FIELD_4B IS NULL OR U.USER_03_POSITION_FIELD_4B ''Y'')
FOR FETCH ONLY WITH UR ')
/* Now updates are made to the initial data file #INITIAL_FASLO */
UPDATE#INITIAL_FASLO
SETWHOLE_LOAN_STATUS_HOLDER = UPPER(cPortfolioCode)
FROM#INITIAL_FASLO I
INNER JOIN LTS.dbo.LoanMaster LM ON (I.LOAN_NUMBER = LM.cLoanNo)
LEFT OUTER JOIN LTS.dbo.Investor_Groupings IG ON (LM.cInvestorID = IG.cInvestorID AND LM.cCatCode = IG.cCatCode)
UPDATE#INITIAL_FASLO
SETFinDelinquencyReason = ISNULL(FinancialDelinquencyReason,' ')
FROM#INITIAL_FASLO I
INNER JOIN servicing.dbo.tblSSFinancial SN ON (I.LOAN_NUMBER = SN.LoanNumber)
*/ Now edit the final data ouput and in
IF EXISTS (SELECT * FROM TEMPDB.DBO.SYSOBJECTS WHERE NAME LIKE '#FASLO_FINAL%')
DROP TABLE #FASLO_FINAL
SELECT ISNULL(PURCHASE_ORDER, ' ') AS Purchase_Order,
ISNULL(LOAN_NUMBER, ' ') AS cLoanNo,
ISNULL( INVESTOR_ID, ' ') AS cInvestorID,
CASEWHEN WHOLE_LOAN_STATUS_HOLDER = 'WL' THEN '''WHOLE'''
ELSE ' '
END AS [Whole Loan Status],
/* continue editing data to be written to the Final table # FASL_FINAL another 50 lines */
ISNULL(MORTGAGOR_FIRST_NAME, ' ') as cMortgFirstName
INTO#FASLO_FINAL
FROM#INITIAL_FASLO I
INNER JOIN LTS.dbo.LoanMaster LM ON (I.LOAN_NUMBER = LM.cLoanNo)
LEFT OUTER JOIN SBAURSQL001.DokTrakSQL.dbo.dRecData DT ON (I.LOAN_NUMBER = DT.loanno)
/* ------------------------------------------------------------------------------------------------------------------------------------*/
/* Update product line to equal NULL if not INT, OPT OR LOC */
/* ------------------------------------------------------------------------------------------------------------------------------------*/
UPDATE #FASLO_FINAL
SET cProductLineCode = ' '
WHERE cProductLineCode NOT IN ('INT', 'OPT', 'LOC')
/* ------------------------------------------------------------------------------------------------------------------------------------*/
/* Check for NULLS in the files DATE fields and repllace with appropriate output (space or 0) */
/* ------------------------------------------------------------------------------------------------------------------------------------*/
UPDATE #FASLO_FINAL
SET LossMitSetUpDate = ' ' Where LossMitSetUpDate IS NULL;
UPDATE #FASLO_FINAL
SET dtBankruptcyDate = ' ' Where dtBankruptcyDate IS NULL;
/* Continue another 40 or so lines of Updates to set varous fields to blank from NULL as required by custome */
/* At end of the script, select all rows to write to grid */
SELECT * FROM#FASLO_FINAL
I have abbreviated as much as possible without lossing the gist of the SQL coding. When I try to add enve 1 more fields on the main select statement I get this error:
Msg 170, Level 15, State 1, Line 43
Line 43: Incorrect syntax near '
SELECT
L.LOAN_NUMBER AS PURCHASE_ORDER, L.LOAN_NUMBER, L.INVESTOR_ID,
CAST(NULL AS VARCHAR(25)) AS WHOLE_LOAN_'.
I cannot understand what is causing this error. It matters not where I try to add a field in the select statement I alwaays get this error. The field I am trying to add is CC.COMMENT_ADDED_DATE. I cannot add the the Join for the table this field comes from to the INNER JOINS statemetns as I have tried to add this innter join by itself and get the same error of ‘Incorrect Syntax ner ‘ …
I hope I have not posted to much information but I was not sure how to skinny things down much further. Thank you in advace for your time and trouble.