OK this one has me scratching my head. Time to throw it out to the ether and see if anyone has any suggestions.

I have a query that runs fine in TOAD, so therefore it conforms to acceptable Oracle syntax, yata, yata, yata.

However, when VFP performs a SQLEXEC() using this piece of code in my VCX, SQLEXEC() returns a 1, indicating success, yet does not create a cursor with the result set.

The code runs past the following code

   lnExec = SQLEXEC(.nGen_Sel_Handle, m.lcSql_String, 'Gen_Claims')

   IF lnExec < 0
      .SPT_Message('Claim Query Failure.', 0, 'Write_Detail')
      RETURN .F.
   ENDIF

and fails on the following line

   lcTotal = ALLTRIM(STR(RECCOUNT('GEN_CLAIMS')))

because the alias 'GEN_CLAIMS' does not exist.

Here is the SQL statement which is contained in the variable "lcSql_String" (again, this DOES work in Oracle because it runs fine in TOAD!)

WITH 
CODES_BODY AS (SELECT DISTINCT DESCRIPTION, CODE, CODETYPE FROM ADAM.CODES WHERE CODES.CODETYPE='BODY'), 
CODES_NATURE AS (SELECT DISTINCT DESCRIPTION, CODE, CODETYPE FROM ADAM.CODES WHERE CODES.CODETYPE='NATURE'), 
CASESTAT AS (SELECT * FROM ADAM.CASESTATUS WHERE CURRENTFLAG='Y'), 
ADJLINK AS (SELECT * FROM ADAM."LINK" WHERE "LINK".LINKTABLE='ADJUSTER' AND "LINK".CURRENTFLAG='Y') 
SELECT 
"CASE".PKID AS CASEPKID, 
CODES_BODY.DESCRIPTION AS NCCIBODYPART, 
CODES_NATURE.DESCRIPTION AS NCCINATURE, 
"CASE".CARRIERID, 
"ASSOCIATE"."FIRST" AS ASSOCFIRST, 
"ASSOCIATE"."LAST" AS ASSOCLAST, 
"ASSOCIATE".MI AS ASSOCMI, 
"ASSOCIATE".SSN, 
"ASSOCIATE".BIRTHDATE, 
"ASSOCIATE".GENDER, 
"ASSOCIATE".DAYPHONE, 
"ASSOCIATE".ADDRESS1, 
"ASSOCIATE".ADDRESS2, 
"ASSOCIATE".CITY, 
"ASSOCIATE".STATE, 
"ASSOCIATE".ZIP, 
DECODE(COMPENSIBLE.COMPENSIBLE, 'YES', 'PAY', 'NO', 'DEN', 'UNDETER', 'PEN', ' ') AS COMPNS, 
DECODE(CASESTAT.CASESTATUS, 'OPEN', 'O', 'CLOSE', 'C', 'REOPEN', 'R', 'U') AS BUNCHCASESTS, 
"CASE".INCIDENTDATE, 
"ACCOUNT".ACCOUNTNAME, 
"ACCOUNT".ACCOUNTCODE, 
USERS.LASTNAME AS NCMLAST, 
USERS.FIRSTNAME AS NCMFIRST, 
USERS.PHONE AS NCMPHONE, 
USERS.EMAIL AS NCMEMAIL, 
FACILITY.SUBID1 AS FACILITYID, 
FACILITY.FACILITYNAME AS FACILITYNAME, 
FACILITY.ADDRESS1 AS FACILITYADDRESS1, 
FACILITY.ADDRESS2 AS FACILITYADDRESS2, 
FACILITY.CITY AS FACILITYCITY, 
FACILITY.STATE AS FACILITYSTATE, 
FACILITY.ZIP AS FACILITYZIP, 
"CASE"."BODY", 
"CASE".NATURE, 
"CASE".CAUSE, 
CARRIERSTATUS.CARRIERSTATUS, 
DECODE(CARRIERSTATUS.CARRIERSTATUS, 'OPEN', 'O', 'CLOSE', 'C', 'REOPEN', 'R', 'U') AS CARRIERSTS, 
(CASE CARRIERSTATUS.CARRIERSTATUS WHEN 'CLOSE' THEN CARRIERSTATUS.EFFECTIVEDATE ELSE NULL END) AS CLOSEDATE,   
"CASE".JURISSTATE, 
ADJUSTER."FIRST" AS ADJFIRST, 
ADJUSTER."LAST" AS ADJLAST, 
ADJUSTER.ADJUSTERID, 
ADJUSTER.PHONE1 AS ADJPHONE, 
ADJUSTER.EMAIL AS ADJEMAIL, 
DECODE("ASSOCIATE".DEATHDATE, NULL, 'N', 'Y') AS DECEASED, 
"ASSOCIATE".DEATHDATE 
FROM ADAM."CASE" 
JOIN ADAM."ASSOCIATE" ON "ASSOCIATE".PKID="CASE".ASSOCIATEID 
JOIN ADAM.COMPENSIBLE ON COMPENSIBLE.ICN="CASE".PKID 
JOIN ADAM.CARRIERSTATUS ON CARRIERSTATUS.ICN="CASE".PKID 
JOIN ADAM.CASETYPE ON CASETYPE.ICN="CASE".PKID 
JOIN ADAM.FACILITY ON FACILITY.PKID="CASE".FACILITYID 
JOIN ADAM."ACCOUNT" ON "ACCOUNT".PKID="CASE".ACCOUNTID 
JOIN ADAM.ASSIGNMENTS ON ASSIGNMENTS.ICN="CASE".PKID 
JOIN ADAM.USERS ON USERS.USERNAME=ASSIGNMENTS.ASSIGNEE 
LEFT JOIN CASESTAT ON CASESTAT.ICN="CASE".PKID 
LEFT JOIN ADJLINK ON ADJLINK.LINKEDTO="CASE".PKID  
LEFT JOIN ADAM.ADJUSTER ON ADJUSTER.PKID=ADJLINK."LINK" 
LEFT JOIN CODES_BODY ON CODES_BODY.CODE="CASE"."BODY" 
LEFT JOIN CODES_NATURE ON CODES_NATURE.CODE="CASE".NATURE 
WHERE COMPENSIBLE.CURRENTFLAG='Y' 
AND CARRIERSTATUS.CURRENTFLAG='Y' 
AND CASETYPE.CURRENTFLAG='Y' 
AND ASSIGNMENTS.CURRENTFLAG='Y' 
AND (( "CASE".AccountID = 122 
AND ( ("CASE".MODDATE > TO_DATE('01/01/1900 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM') 
AND "CASE".MODDATE <= TO_DATE('06/12/2008 08:57:57 AM', 'MM/DD/YYYY HH:MI:SS AM')) 
OR (COMPENSIBLE.MODDATE > TO_DATE('01/01/1900 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM') 
AND COMPENSIBLE.MODDATE <= TO_DATE('06/12/2008 08:57:57 AM', 'MM/DD/YYYY HH:MI:SS AM')) 
OR (CARRIERSTATUS.MODDATE > TO_DATE('01/01/1900 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM') 
AND CARRIERSTATUS.MODDATE <= TO_DATE('06/12/2008 08:57:57 AM', 'MM/DD/YYYY HH:MI:SS AM')) 
OR ("ASSOCIATE".MODDATE > TO_DATE('01/01/1900 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM') 
AND "ASSOCIATE".MODDATE <= TO_DATE('06/12/2008 08:57:57 AM', 'MM/DD/YYYY HH:MI:SS AM')) 
OR (CASETYPE.MODDATE > TO_DATE('01/01/1900 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM') 
AND CASETYPE.MODDATE <= TO_DATE('06/12/2008 08:57:57 AM', 'MM/DD/YYYY HH:MI:SS AM')) 
) ))