Is it possible lnExec to be 0?
If you connection Asynchronous?


>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'))
>) ))
>