SSRS and Oracle
Hi can anyone help I am working with SSRS against an Oracle data source which work fine most days but today the following caught me out, if I Run the SQL below against oracle through TOAD SELECT * FROM V_CURRENT_LIVE_MEMBERS LM, (SELECT VLM.CONTACTNO FROM V_CURRENT_LIVE_MEMBERS vLM WHERE VLM.COMPANYCODE = 'EMP' AND VLM.MEMBERSHIPCODE = '350') EM WHERE LM.CONTACTNO = EM.CONTACTNO (+) AND LM.COMPANYCODE = 'NFUSL' AND LM.MEMBERSHIPCODE = '02' AND EM.CONTACTNO is NULL Returns 812 Records But in SSRS it reformats and adds a second outer join to the WHERE clause SELECT LM.CONTACTNO, LM.COMPANYCODE, LM.MEMBERSHIPCODE, LM.MEMBERSHIPDESCRIPTION, LM.STARTDATE, LM.ENDDATE, EM.CONTACTNO AS EXPR1 FROM V_CURRENT_LIVE_MEMBERS LM, (SELECT CONTACTNO FROM V_CURRENT_LIVE_MEMBERS VLM WHERE (COMPANYCODE = 'EMP') AND (MEMBERSHIPCODE = '350')) EM WHERE LM.CONTACTNO = EM.CONTACTNO (+) AND (LM.COMPANYCODE = 'NFUSL') AND (LM.MEMBERSHIPCODE = '02') AND (EM.CONTACTNO (+) IS NULL) Which returns the incorrect 813 Records Any Ideas greatly received
October 28th, 2010 10:14am

Hi Simon, From the 2 SQL scripts, we can't find its difference. However, you should be able to filter out the one additional record with the formated SQL and then analyze the record not meet which restriction in the where clause of the first SQL script. In addition, you can switch the query designer to Text-based UI and utilize the first SQL script directly so that the script will not be reformated by SSRS. thanks, Jerry
Free Windows Admin Tool Kit Click here and download it now
November 3rd, 2010 2:56am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics