I'm using Developer Studio for WebFOCUS 8105. The report usually has 70,000+ rows. The SQL below works fine in Oracle SQL Developer but when ran in WebFOCUS some of the left joins which should occasionally have no records associated instead incorrectly retrieves random record values to populate those row cells which should be left blank. Also I can rerun the same report and get other random inconsistent results. Occasionally the main record that I've been testing against correctly displays it's Nulls as blank cells. These incorrect results does not happen when running directly against Oracle.

SET NODATA = ' ' 
-DEFAULT &Excludes = '''ALL'',''NEM'',''NME'',''NES''';

ENGINE SQLORA SET DEFAULT_CONNECTION PRODODS
SQL SQLORA PREPARE SQL_CAMPUSCALLS FOR

SELECT
 CASE WHEN cnst.PERSON_UID              IS NULL THEN ' ' ELSE TO_CHAR(cnst.PERSON_UID)  END AS PIDM
,CASE WHEN cnst.ID                      IS NULL THEN ' ' ELSE cnst.ID                   END AS ID
,CASE WHEN cnst.PREF_LAST_NAME          IS NULL THEN ' ' ELSE cnst.PREF_LAST_NAME       END AS PREF_LAST_NAME
,CASE WHEN cnst.PREF_FIRST_NAME         IS NULL THEN ' ' ELSE cnst.PREF_FIRST_NAME      END AS PREF_FIRST_NAME
,CASE WHEN cnst.PREF_MIDDLE_INITIAL     IS NULL THEN ' ' ELSE cnst.PREF_MIDDLE_INITIAL  END AS PREF_MIDDLE_INITIAL
,CASE WHEN cnst.NICKNAME                IS NULL THEN ' ' ELSE cnst.NICKNAME             END AS NICKNAME
,CASE WHEN cnst.NAME_SUFFIX             IS NULL THEN ' ' ELSE cnst.NAME_SUFFIX          END AS NAME_SUFFIX
,CASE WHEN cnst.SPOUSE_NAME             IS NULL THEN ' ' ELSE cnst.SPOUSE_NAME          END AS SPOUSE_NAME

,CASE WHEN spouse.RELATED_FIRST_NAME    IS NULL THEN ' ' ELSE spouse.RELATED_FIRST_NAME END AS SPOUSE_FIRST
,CASE WHEN addrs.STREET_LINE1           IS NULL THEN ' ' ELSE addrs.STREET_LINE1        END AS PREF_STREET_LINE1
,CASE WHEN addrs.STREET_LINE2           IS NULL THEN ' ' ELSE addrs.STREET_LINE2        END AS PREF_STREET_LINE2
,CASE WHEN addrs.CITY                   IS NULL THEN ' ' ELSE addrs.CITY                END AS PREF_CITY
,CASE WHEN addrs.STATE_PROVINCE         IS NULL THEN ' ' ELSE addrs.STATE_PROVINCE      END AS PREF_STATE
,CASE WHEN addrs.POSTAL_CODE            IS NULL THEN ' ' ELSE addrs.POSTAL_CODE         END AS PREF_ZIP
,CASE WHEN addrs.COUNTY                 IS NULL THEN ' ' ELSE addrs.COUNTY              END AS PREF_COUNTY_CODE
,CASE WHEN addrs.PHONE_NUMBER_COMBINED  IS NULL THEN ' ' ELSE addrs.PHONE_NUMBER_COMBINED END AS PREF_PHONE_NUMBER_COMBINED
,CASE WHEN phn.PHONE_NUMBER_COMBINED    IS NULL THEN ' ' ELSE phn.PHONE_NUMBER_COMBINED END AS CELL_PHONE_NUMBER_COMBINED

,CASE WHEN cnst.EMAIL_PREFERRED_ADDRESS IS NULL THEN ' ' ELSE cnst.EMAIL_PREFERRED_ADDRESS END AS EMAIL_PREFERRED_ADDRESS
,CASE WHEN cnst.GENDER                  IS NULL THEN ' ' ELSE cnst.GENDER               END AS GENDER
,CASE WHEN cnst.CURRENT_AGE             IS NULL THEN ' ' ELSE TO_CHAR(cnst.CURRENT_AGE) END AS CURRENT_AGE
,CASE WHEN cnst.MAIDEN_LAST_NAME        IS NULL THEN ' ' ELSE cnst.MAIDEN_LAST_NAME     END AS MAIDEN_LAST_NAME
,CASE WHEN cnst.MARITAL_STATUS          IS NULL THEN ' ' ELSE cnst.MARITAL_STATUS       END AS MARITAL_STATUS
,CASE WHEN cnst.PREF_CLASS              IS NULL THEN ' ' ELSE cnst.PREF_CLASS           END AS PREF_CLASS
,CASE WHEN cnst.PREF_DONOR_CATEGORY     IS NULL THEN ' ' ELSE cnst.PREF_DONOR_CATEGORY  END AS PREF_DONOR_CATEGORY

,CASE WHEN emp.POSITION_TITLE           IS NULL THEN ' ' ELSE emp.POSITION_TITLE        END AS EMPLOYMENT_POSITION_TITLE
,CASE WHEN emp.EMPLOYER_NAME            IS NULL THEN ' ' ELSE emp.EMPLOYER_NAME         END AS EMPLOYER_NAME
,CASE WHEN emp.MATCHING_GIFT_COMPANY_NAME IS NULL THEN ' ' ELSE emp.MATCHING_GIFT_COMPANY_NAME END AS MATCHING_GIFT_COMPANY_NAME
,CASE WHEN emp.STREET_LINE1             IS NULL THEN ' ' ELSE emp.STREET_LINE1          END AS EMPLOYER_STREET_LINE1
,CASE WHEN emp.STREET_LINE2             IS NULL THEN ' ' ELSE emp.STREET_LINE2          END AS EMPLOYER_STREET_LINE2
,CASE WHEN emp.CITY                     IS NULL THEN ' ' ELSE emp.CITY                  END AS EMPLOYER_CITY
,CASE WHEN emp.STATE_PROVINCE           IS NULL THEN ' ' ELSE emp.STATE_PROVINCE        END AS EMPLOYER_STATE
,CASE WHEN emp.POSTAL_CODE              IS NULL THEN ' ' ELSE emp.POSTAL_CODE           END AS EMPLOYER_ZIP
,CASE WHEN emp.COUNTY                   IS NULL THEN ' ' ELSE emp.COUNTY                END AS EMPLOYER_COUNTY
,CASE WHEN emp.PHONE_NUMBER_COMBINED    IS NULL THEN ' ' ELSE emp.PHONE_NUMBER_COMBINED END AS EMPLOYER_PHONE_NUMBER_COMBINED
,CASE WHEN busnEm.BUSN_EMAIL            IS NULL THEN ' ' ELSE busnEm.BUSN_EMAIL         END AS BUSN_EMAIL

,CASE WHEN spEmp.EMPLOYER_NAME              IS NULL THEN ' ' ELSE spEmp.EMPLOYER_NAME               END AS SPOUSE_EMPLOYER_NAME
,CASE WHEN spEmp.EMPLOYMENT_STATUS_DESC     IS NULL THEN ' ' ELSE spEmp.EMPLOYMENT_STATUS_DESC      END AS SPOUSE_EMPLOYMENT_STATUS_DESC
,CASE WHEN spEmp.MATCHING_GIFT_COMPANY_NAME IS NULL THEN ' ' ELSE spEmp.MATCHING_GIFT_COMPANY_NAME  END AS SPOUSE_MATCHING_GFT_CMPNY_NAM

,CASE WHEN dgre.ACADEMIC_YEAR_1 IS NULL THEN ' ' ELSE dgre.ACADEMIC_YEAR_1  END AS ACADEMIC_YEAR_1
,CASE WHEN dgre.DEGREE_DESC_1   IS NULL THEN ' ' ELSE dgre.DEGREE_DESC_1    END AS DEGREE_DESC_1
,CASE WHEN dgre.MAJOR1_DESC_1   IS NULL THEN ' ' ELSE dgre.MAJOR1_DESC_1    END AS MAJOR1_DESC_1
,CASE WHEN dgre.MAJOR2_DESC_1   IS NULL THEN ' ' ELSE dgre.MAJOR2_DESC_1    END AS MAJOR2_DESC_1
,CASE WHEN dgre.ACADEMIC_YEAR_2 IS NULL THEN ' ' ELSE dgre.ACADEMIC_YEAR_2  END AS ACADEMIC_YEAR_2
,CASE WHEN dgre.DEGREE_DESC_2   IS NULL THEN ' ' ELSE dgre.DEGREE_DESC_2    END AS DEGREE_DESC_2
,CASE WHEN dgre.MAJOR1_DESC_2   IS NULL THEN ' ' ELSE dgre.MAJOR1_DESC_2    END AS MAJOR1_DESC_2
,CASE WHEN dgre.MAJOR2_DESC_2   IS NULL THEN ' ' ELSE dgre.MAJOR2_DESC_2    END AS MAJOR2_DESC_2
,CASE WHEN dgre.ACADEMIC_YEAR_3 IS NULL THEN ' ' ELSE dgre.ACADEMIC_YEAR_3  END AS ACADEMIC_YEAR_3
,CASE WHEN dgre.DEGREE_DESC_3   IS NULL THEN ' ' ELSE dgre.DEGREE_DESC_3    END AS DEGREE_DESC_3
,CASE WHEN dgre.MAJOR1_DESC_3   IS NULL THEN ' ' ELSE dgre.MAJOR1_DESC_3    END AS MAJOR1_DESC_3
,CASE WHEN dgre.MAJOR2_DESC_3   IS NULL THEN ' ' ELSE dgre.MAJOR2_DESC_3    END AS MAJOR2_DESC_3

,CASE WHEN cnst.MOST_RECENT_GIFT_DATE   IS NULL THEN ' ' ELSE TO_CHAR(TRUNC(cnst.MOST_RECENT_GIFT_DATE)) END AS MOST_RECENT_GIFT_DATE
,CASE WHEN cnst.MOST_RECENT_GIFT_AMT    IS NULL THEN ' ' ELSE TO_CHAR(cnst.MOST_RECENT_GIFT_AMT)    END AS MOST_RECENT_GIFT_AMT
,CASE WHEN cnst.MOST_RECENT_PLEDGE_DATE IS NULL THEN ' ' ELSE TO_CHAR(cnst.MOST_RECENT_PLEDGE_DATE) END AS MOST_RECENT_PLEDGE_DATE
,CASE WHEN cnst.MOST_RECENT_PLEDGE_AMT  IS NULL THEN ' ' ELSE TO_CHAR(cnst.MOST_RECENT_PLEDGE_AMT)  END AS MOST_RECENT_PLEDGE_AMT
,CASE WHEN cnst.HIGHEST_GIFT_DATE       IS NULL THEN ' ' ELSE TO_CHAR(cnst.HIGHEST_GIFT_DATE)       END AS HIGHEST_GIFT_DATE
,CASE WHEN cnst.HIGHEST_GIFT_AMOUNT     IS NULL THEN ' ' ELSE TO_CHAR(cnst.HIGHEST_GIFT_AMOUNT)     END AS HIGHEST_GIFT_AMOUNT

,CASE WHEN prspct.PRIMARY_STAFF_ASSIGN_IDEN IS NULL THEN ' ' ELSE prspct.PRIMARY_STAFF_ASSIGN_IDEN  END AS PRIMARY_STAFF_ASSIGN_IDEN
,CASE WHEN prspct.PROSPECT_STATUS           IS NULL THEN ' ' ELSE prspct.PROSPECT_STATUS            END AS PROSPECT_STATUS
,CASE WHEN spouse.COMBINED_MAILING_PRIORITY IS NULL THEN ' ' ELSE spouse.COMBINED_MAILING_PRIORITY  END AS COMBINED_MAILING_PRIORITY

,CASE WHEN actv.NAME            IS NULL THEN ' ' ELSE actv.NAME             END AS NAME
,CASE WHEN actv.ACTIVITY_1      IS NULL THEN ' ' ELSE actv.ACTIVITY_1       END AS ACTIVITY_1
,CASE WHEN actv.ACTIVITY_DESC_1 IS NULL THEN ' ' ELSE actv.ACTIVITY_DESC_1  END AS ACTIVITY_DESC_1
,CASE WHEN actv.ACTIVITY_2      IS NULL THEN ' ' ELSE actv.ACTIVITY_2       END AS ACTIVITY_2
,CASE WHEN actv.ACTIVITY_DESC_2 IS NULL THEN ' ' ELSE actv.ACTIVITY_DESC_2  END AS ACTIVITY_DESC_2
,CASE WHEN actv.ACTIVITY_3      IS NULL THEN ' ' ELSE actv.ACTIVITY_3       END AS ACTIVITY_3
,CASE WHEN actv.ACTIVITY_DESC_3 IS NULL THEN ' ' ELSE actv.ACTIVITY_DESC_3  END AS ACTIVITY_DESC_3
,CASE WHEN actv.ACTIVITY_4      IS NULL THEN ' ' ELSE actv.ACTIVITY_4       END AS ACTIVITY_4
,CASE WHEN actv.ACTIVITY_DESC_4 IS NULL THEN ' ' ELSE actv.ACTIVITY_DESC_4  END AS ACTIVITY_DESC_4
,CASE WHEN actv.ACTIVITY_5      IS NULL THEN ' ' ELSE actv.ACTIVITY_5       END AS ACTIVITY_5
,CASE WHEN actv.ACTIVITY_DESC_5 IS NULL THEN ' ' ELSE actv.ACTIVITY_DESC_5  END AS ACTIVITY_DESC_5
,CASE WHEN actv.ACTIVITY_6      IS NULL THEN ' ' ELSE actv.ACTIVITY_6       END AS ACTIVITY_6
,CASE WHEN actv.ACTIVITY_DESC_6 IS NULL THEN ' ' ELSE actv.ACTIVITY_DESC_6  END AS ACTIVITY_DESC_6
,CASE WHEN actv.ACTIVITY_7      IS NULL THEN ' ' ELSE actv.ACTIVITY_7       END AS ACTIVITY_7
,CASE WHEN actv.ACTIVITY_DESC_7 IS NULL THEN ' ' ELSE actv.ACTIVITY_DESC_7  END AS ACTIVITY_DESC_7
FROM    CONSTITUENT             cnst
LEFT JOIN DEGREE_SLOT           dgre
ON  cnst.PERSON_UID         = dgre.PERSON_UID
LEFT JOIN ADDRESS_PREFERRED addrs
ON  cnst.PERSON_UID         = addrs.ENTITY_UID
LEFT JOIN NW_EMAIL_SLOT         busnEm
ON  cnst.PERSON_UID         = busnEm.ENTITY_UID
LEFT JOIN TELEPHONE_CURRENT     phn
ON  cnst.PERSON_UID         = phn.ENTITY_UID
AND phn.PHONE_TYPE          = 'CELL'
LEFT JOIN PROSPECT_INFO         prspct
ON  cnst.PERSON_UID         = prspct.ENTITY_UID
AND prspct.PROSPECT_STATUS IN('EARLYC','ADVC','STEW','READY','CLOSE','IDQ')
AND prspct.PRIMARY_STAFF_ASSIGN_IDEN != 'ADVGA'
LEFT JOIN RELATIONSHIP          spouse
ON  cnst.PERSON_UID         = spouse.ENTITY_UID
AND spouse.SPOUSE_STATUS    = 'A'
AND spouse.SPOUSE_IND       = 'Y'
LEFT JOIN CURRENT_EMPLOYMENT    emp
ON  emp.PERSON_UID          = cnst.PERSON_UID
AND emp.PRIMARY_EMPLOYMENT_IND = 'Y'
LEFT JOIN CURRENT_EMPLOYMENT    spEmp
ON  spEmp.PERSON_UID        = spouse.RELATED_UID
AND spEmp.PRIMARY_EMPLOYMENT_IND = 'Y'
LEFT JOIN (
SELECT
      ENTITY_UID
     ,MAX(NAME) AS NAME
     ,MAX(CASE WHEN ACTIVITY_CNT = 1 THEN ACTIVITY      ELSE ' ' END) AS ACTIVITY_1
     ,MAX(CASE WHEN ACTIVITY_CNT = 1 THEN ACTIVITY_DESC ELSE ' ' END) AS ACTIVITY_DESC_1
     ,MAX(CASE WHEN ACTIVITY_CNT = 2 THEN ACTIVITY      ELSE ' ' END) AS ACTIVITY_2
     ,MAX(CASE WHEN ACTIVITY_CNT = 2 THEN ACTIVITY_DESC ELSE ' ' END) AS ACTIVITY_DESC_2
     ,MAX(CASE WHEN ACTIVITY_CNT = 3 THEN ACTIVITY      ELSE ' ' END) AS ACTIVITY_3
     ,MAX(CASE WHEN ACTIVITY_CNT = 3 THEN ACTIVITY_DESC ELSE ' ' END) AS ACTIVITY_DESC_3
     ,MAX(CASE WHEN ACTIVITY_CNT = 4 THEN ACTIVITY      ELSE ' ' END) AS ACTIVITY_4
     ,MAX(CASE WHEN ACTIVITY_CNT = 4 THEN ACTIVITY_DESC ELSE ' ' END) AS ACTIVITY_DESC_4
     ,MAX(CASE WHEN ACTIVITY_CNT = 5 THEN ACTIVITY      ELSE ' ' END) AS ACTIVITY_5
     ,MAX(CASE WHEN ACTIVITY_CNT = 5 THEN ACTIVITY_DESC ELSE ' ' END) AS ACTIVITY_DESC_5
     ,MAX(CASE WHEN ACTIVITY_CNT = 6 THEN ACTIVITY      ELSE ' ' END) AS ACTIVITY_6
     ,MAX(CASE WHEN ACTIVITY_CNT = 6 THEN ACTIVITY_DESC ELSE ' ' END) AS ACTIVITY_DESC_6
     ,MAX(CASE WHEN ACTIVITY_CNT = 7 THEN ACTIVITY      ELSE ' ' END) AS ACTIVITY_7
     ,MAX(CASE WHEN ACTIVITY_CNT = 7 THEN ACTIVITY_DESC ELSE ' ' END) AS ACTIVITY_DESC_7
FROM  ( SELECT ENTITY_UID
             ,NAME
             ,DENSE_RANK() OVER (PARTITION BY ENTITY_UID ORDER BY ACTIVITY) AS ACTIVITY_CNT
             ,ACTIVITY
             ,ACTIVITY_DESC
        FROM ACTIVITY
        WHERE ACTIVITY_TYPE != 'SCHOL'
          AND ACTIVITY LIKE '4%'
        ORDER BY ENTITY_UID, ACTIVITY) actvy
GROUP BY ENTITY_UID
ORDER BY ENTITY_UID ) actv
ON  cnst.PERSON_UID         = actv.ENTITY_UID

WHERE   cnst.DECEASED_IND   = 'N'
AND cnst.PERSON_UID     NOT IN( SELECT ENTITY_UID FROM EXCLUSION WHERE EXCLUSION IN(&Excludes) )
ORDER BY cnst.PERSON_UID;

END

TABLE FILE SQL_CAMPUSCALLS
PRINT 
PIDM
ID
PREF_LAST_NAME
PREF_FIRST_NAME
PREF_MIDDLE_INITIAL
NICKNAME
NAME_SUFFIX
SPOUSE_NAME

SPOUSE_FIRST
PREF_STREET_LINE1
PREF_STREET_LINE2
PREF_CITY
PREF_STATE
PREF_ZIP
PREF_COUNTY_CODE
PREF_PHONE_NUMBER_COMBINED
CELL_PHONE_NUMBER_COMBINED

EMAIL_PREFERRED_ADDRESS
GENDER
CURRENT_AGE
MAIDEN_LAST_NAME
MARITAL_STATUS
PREF_CLASS
PREF_DONOR_CATEGORY

EMPLOYMENT_POSITION_TITLE
EMPLOYER_NAME
MATCHING_GIFT_COMPANY_NAME
EMPLOYER_STREET_LINE1
EMPLOYER_STREET_LINE2
EMPLOYER_CITY
EMPLOYER_STATE
EMPLOYER_ZIP
EMPLOYER_COUNTY
EMPLOYER_PHONE_NUMBER_COMBINED
BUSN_EMAIL

SPOUSE_EMPLOYER_NAME
SPOUSE_EMPLOYMENT_STATUS_DESC
SPOUSE_MATCHING_GFT_CMPNY_NAM

ACADEMIC_YEAR_1
DEGREE_DESC_1
MAJOR1_DESC_1
MAJOR2_DESC_1
ACADEMIC_YEAR_2
DEGREE_DESC_2
MAJOR1_DESC_2
MAJOR2_DESC_2
ACADEMIC_YEAR_3
DEGREE_DESC_3
MAJOR1_DESC_3
MAJOR2_DESC_3

MOST_RECENT_GIFT_DATE
MOST_RECENT_GIFT_AMT
MOST_RECENT_PLEDGE_DATE
MOST_RECENT_PLEDGE_AMT
HIGHEST_GIFT_DATE
HIGHEST_GIFT_AMOUNT

PRIMARY_STAFF_ASSIGN_IDEN
PROSPECT_STATUS
COMBINED_MAILING_PRIORITY

NAME
ACTIVITY_1
ACTIVITY_DESC_1
ACTIVITY_2
ACTIVITY_DESC_2
ACTIVITY_3
ACTIVITY_DESC_3
ACTIVITY_4
ACTIVITY_DESC_4
ACTIVITY_5
ACTIVITY_DESC_5
ACTIVITY_6
ACTIVITY_DESC_6
ACTIVITY_7
ACTIVITY_DESC_7
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT EXL2K
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
 INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/endeflt.sty,$
ENDSTYLE
END

This message has been edited. Last edited by: FP Mod Chuck,

Could you test to see if the behavior changes when this SELECT is an Oracle View? It shouldn't be any different, but it might give you some insight into what's happening.

first and foremost I would replace the &Excludes in the SQL with the values and not use & variables to test with. Second, Oracle settings in the profile(s) can have different effects. Third Remove all the ON TABLE commands and add ON TABLE PCHOLD FORMAT WP or ON TABLE PCHOLD FORMAT COM to view the results.

If you still get the same result start breaking up the SQL and see if you can find the exact spot that is causing the problem. I may end up being a data type issue.

It has lost it's inconsistencies and seems to be working now. The person that knows and analyzes the data says it's good to go.
"Oracle View"? When I attempt to open WebFOCUS's SQL Editor on it says, "This adapter is not configured. Please choose a different adapter or configure this adapter." Then when ran from there it just tells me it can't find one of the tables.
Replacing the &Excludes value with a hard value has had no effect and worked the same.

I ended up including...

SET ALL = ON
SET SHORTPATH=SQL

SQL SQLORA SET OPT NOAGGR

... and changing the LIKE condition to substr() equals.