SQL
SELECT RTWCURRENT.CLAIM_ID
FROM RTWCURRENT

LEFT OUTER JOIN RTWFIVETWO
ON RTWCURRENT.CLAIM_ID = RTWFIVETWO.CLAIM_ID

LEFT OUTER JOIN RTWSEVENEIGHT
ON RTWCURRENT.CLAIM_ID = RTWSEVENEIGHT.CLAIM_ID

LEFT OUTER JOIN RTWONEOFOUR
ON RTWCURRENT.CLAIM_ID = RTWONEOFOUR.CLAIM_ID

WHERE RTWCURRENT.CLAIM_ID = '21150035190'
END

These objects are FOCUS hold files.
The SQL translator is automatically changing my LEFT OUTER JOINs into INNER JOINs, which is not what LEFT OUTER JOIN states!

In addition to that bug, if i change the SELECT LIST to COUNT(*) (which according to the IBI documentation is suppported), I receive the following cryptic error message

(FOC14006) SQL TRANSLATOR ERROR Type mismatch on temporary node data
CheckTmpType
(FOC14006) SQL TRANSLATOR ERROR Missing DBE link to HELD column PrJnHoldDBE()
(FOC14006) SQL TRANSLATOR ERROR Type mismatch on temporary node data
CheckTmpType
(FOC14006) SQL TRANSLATOR ERROR Missing DBE link to HELD column PrJnHoldDBE()
(FOC14006) SQL TRANSLATOR ERROR Type mismatch on temporary node data
CheckTmpType
(FOC14006) SQL TRANSLATOR ERROR Invalid Enn column tag (0 or negative)
PrJnEColNo()
(FOC14006) SQL TRANSLATOR ERROR Type mismatch on temporary node data
CheckTmpType
(FOC14006) SQL TRANSLATOR ERROR Invalid Enn column tag (0 or negative)
PrJnEColNo()

Can anyone assist?

"ANSI SQL Level 2 compliant" -> ("Creating Reports" documentation for FOCUS 7)
Yeah, right.

This message has been edited. Last edited by: <Emily McAllister>,

Could you look at each master file (e.g. RTWCURRENT.MAS) and share SUFFIX= for the tables you're joining? Join rules depend on the data type you are using in your query. Also, are all tables in the query the same type?

jashwood, you really don't need to open 3 discussions on the same topic. You may want to read the manual where it discusses SQL PASSTHRU. I've never had any issue with it working properly.

Hi Guys,

Apologies for opening multiple threads on this issue.
This isn't actually SQL passthru, I'm attempting to use SQL to join FOCUS hold files instead of having the use FOCUS.
With trace flags on, it's actually interesting to see how FOCUS implements the SQL engine on hold files - but I digress.

Here is the master file for RTWCURRENT - all the other files i'm joining have the same definition

FILENAME=RTWCURRENT, SUFFIX=FOC , $
SEGMENT=SEG01, SEGTYPE=S1, $
FIELDNAME=FOCLIST , ALIAS=E01, USAGE=I5, $
FIELDNAME=CLAIM_ID, ALIAS=E02, USAGE=A11, $
FIELDNAME=SEGMENT_CD, ALIAS=E03, USAGE=A3,
MISSING=ON, $
FIELDNAME=SECTION_CD, ALIAS=E04, USAGE=A4,
MISSING=ON, $
FIELDNAME=OFFICERNAME, ALIAS=E05, USAGE=A42,
MISSING=ON, $
FIELDNAME=WORKERNAME, ALIAS=E06, USAGE=A41,
MISSING=ON, $
FIELDNAME=EMPLOYERSIZE, ALIAS=E07, USAGE=A30,
MISSING=ON, $
FIELDNAME=EMPLOYERNAME, ALIAS=E08, USAGE=A80,
MISSING=ON, $
FIELDNAME=MEDCERTTYPE, ALIAS=E09, USAGE=A36,
MISSING=ON, $
FIELDNAME=AGENTRECEIVEDDATE, ALIAS=E10, USAGE=YYMD,
MISSING=ON, $
FIELDNAME=CLAIMSTATUSDESC, ALIAS=E11, USAGE=A100,
MISSING=ON, $
FIELDNAME=FIRSTCEASEDDT, ALIAS=E12, USAGE=YYMD,
MISSING=ON, $
FIELDNAME=FIRSTRESUMEDDT, ALIAS=E13, USAGE=YYMD,
MISSING=ON, $
FIELDNAME=LASTCEASEDDT, ALIAS=E14, USAGE=YYMD,
MISSING=ON, $
FIELDNAME=LASTRESUMEDDT, ALIAS=E15, USAGE=YYMD,
MISSING=ON, $
FIELDNAME=MEASUREDATE, ALIAS=E16, USAGE=YYMD,
MISSING=ON, $
FIELDNAME=ACTUALMEASUREDATE, ALIAS=E17, USAGE=YYMD,
MISSING=ON, $
FIELDNAME=PUBLICHOLIDAY, ALIAS=E18, USAGE=A1,
MISSING=ON, $
FIELDNAME=DAYOFWEEK, ALIAS=E19, USAGE=A3,
MISSING=ON, $
FIELDNAME=DEATH_DT, ALIAS=E20, USAGE=YYMD,
MISSING=ON, $
FIELDNAME=DEATH_VERIFIED_FL, ALIAS=E21, USAGE=A1,
MISSING=ON, $
FIELDNAME=DERIVED_DAYS, ALIAS=E22, USAGE=D20.2,
MISSING=ON, $
FIELDNAME=FULLWEEKLYSTATUS, ALIAS=E23, USAGE=A28,
MISSING=ON, $
FIELDNAME=PARTIALWEEKLYSTAT, ALIAS=E24, USAGE=A31,
MISSING=ON, $
FIELDNAME=RTWSTATUS, ALIAS=E25, USAGE=A19,
MISSING=ON, $
FIELDNAME=RTWSTSATMEASURE, ALIAS=E26, USAGE=A1,
MISSING=ON, $
FIELDNAME=RTWATMEASURE, ALIAS=E27, USAGE=YYMD,
MISSING=ON, $
FIELDNAME=TERMINATIONFLAG, ALIAS=E28, USAGE=A20,
MISSING=ON, $
FIELDNAME=RTW, ALIAS=E29, USAGE=D20.2,
MISSING=ON, $
FIELDNAME=NUMERATOR, ALIAS=E30, USAGE=D20.2,
MISSING=ON, $
FIELDNAME=RTWREASON, ALIAS=E31, USAGE=A37,
MISSING=ON, $
FIELDNAME=RTWREASONVWA, ALIAS=E32, USAGE=A59,
MISSING=ON, $

Thanks for your responses, and again apologies for spamming with multiple threads - I will behave in future!

Cheers,
Josh

As a start, issue the command:

SET ALL=PASS

before your SQL SELECT.

SUFFIX=FOC files are internal FOCUS datasets. The most efficient method to query FOCUS files is the FOCUS TABLE syntax. I wouldn't use SQL.