asked on

Code Help

First Part of my issue:

I have written the following code to view the results seen in the attached report called: .xls. What I am having a problem with is I cannot include the records for “RightScenarioMissing” found in the field STAT_CD seen in the attached First_Run_ReportTEST_ELEMENT_COMPARE_RESULT table.
Here’s the code I'm talking about:

SELECT
TECR.COMPR_RUN_ID as COMPR_RUN_ID,
left.TRAN_ID as LTRAN_ID,
right.TRAN_ID as RTRAN_ID,
left.INTFC_NM as INTFC_NM,
tecr.COMPR_RSULT_STAT_CD as STAT_CD,
left.TEST_SCNRO_ID as TEST_SCNRO_ID,
left.EXPTD_UNIQ_DESCR_VALUE_DESC as TRACKING_NR_LEFT,
right.EXPTD_UNIQ_DESCR_VALUE_DESC as TRACKING_NR_RIGHT

FROM
TEST_ELEMENT_COMPARE_RESULT tecr, TEST_SET_ELEMENT LEFT, TEST_SET_ELEMENT RIGHT

WHERE
LEFT.TEST_SET_ELEM_ID=TECR.TEST_ELEM_COMPR_LEFT_SIDE_ID
AND RIGHT.TEST_SET_ELEM_ID=TECR.TEST_ELEM_COMPR_RIGHT_SIDE_ID
AND TECR.COMPR_RUN_ID = 1120

How can I include those records and the records I already have in my First_Run_Report?
first-run-report.zip

Kevin Cross

Hi. It looks like you need an OUTER JOIN. It looks like the rows for RightScenarioMissing are indeed missing a value in TEST_ELEM_COMPR_RIGHT_SIDE_ID; therefore, with an INNER JOIN, you will exclude those rows every time.

Here is an example with ANSI-compliant JOINs to illustrate the suggestion.

SELECT 
  TECR.COMPR_RUN_ID as COMPR_RUN_ID, 
  l.TRAN_ID as LTRAN_ID, 
  r.TRAN_ID as RTRAN_ID,
  l.INTFC_NM as INTFC_NM, 
  tecr.COMPR_RSULT_STAT_CD as STAT_CD, 
  l.TEST_SCNRO_ID as TEST_SCNRO_ID,
  l.EXPTD_UNIQ_DESCR_VALUE_DESC as TRACKING_NR_l, 
  r.EXPTD_UNIQ_DESCR_VALUE_DESC as TRACKING_NR_r
  
FROM 
  TEST_ELEMENT_COMPARE_RESULT tecr
  LEFT JOIN TEST_SET_ELEMENT l ON l.TEST_SET_ELEM_ID=TECR.TEST_ELEM_COMPR_LEFT_SIDE_ID
  LEFT JOIN TEST_SET_ELEMENT r ON r.TEST_SET_ELEM_ID=TECR.TEST_ELEM_COMPR_RIGHT_SIDE_ID
    
WHERE 
  TECR.COMPR_RUN_ID = 1120

Open in new window

Note: if the LEFT side should only be included on matches, then change that back to an INNER JOIN. As it stands, the current query returns 'LeftTranMissing' and 'RightScenarioMissing', which were both missing from the previous FIRST_RUN_REPORT.

j2911

ASKER

Sorry but would you mind writing this out in the code so I can see what you mean?

Thanks so much.

Kevin Cross

The code above works as typed. Copy and paste in your environment and it should illustrate exactly what I am suggesting. Instead of an INNER JOIN which is what you had just in non-ANSI format, you need a LEFT OUTER JOIN as shown in my code snippet above. I did not have my Oracle instance running, so I had to convert the aliases LEFT and RIGHT to l and r, respectively as LEFT and RIGHT are functions in T-SQL and so were causing errors. If you want to change those back, then go right ahead. It does not change the solution, though, which is the JOIN type change.

j2911

ASKER

SELECT 
	tecr.COMPR_RUN_ID as COMPR_RUN_ID,
	left.TRAN_ID as LTRAN_ID, 
	right.TRAN_ID as RTRAN_ID,
	left.INTFC_NM as INTFC_NM, 
	tecr.COMPR_RSULT_STAT_CD as STAT_CD, 
	left.TEST_SCNRO_ID as TEST_SCNRO_ID,
	left.EXPTD_UNIQ_DESCR_VALUE_DESC as dest_NR_LEFT, 	
	right.EXPTD_UNIQ_DESCR_VALUE_DESC as dest_NR_RIGHT

FROM 
	transaction_descriptions L,transaction_descriptions R, TEST_ELEMENT_COMPARE_RESULT tecr
	LEFT OUTER JOIN left ON tecr.TEST_SET_ELEM_ID=
		tecr.TEST_ELEM_COMPR_LEFT_SIDE_ID
	LEFT OUTER JOIN right.TEST_SET_ELEM_ID= MISC ON right.TEST_SET_ELEM_ID=
		tecr.TEST_ELEM_COMPR_RIGHT_SIDE_ID
WHERE 
	left.EXPTD_UNIQ_DESCR_VALUE_DESC = L.TRACKING_NR 
	AND right.EXPTD_UNIQ_DESCR_VALUE_DESC = R.TRACKING_NR
	and tecr.COMPR_RUN_ID = 1120

Open in new window

I tried the Left outer join before and it still didn't get these records.

Kevin Cross

That code looks much different. can you paste the true original code that was working and I will make the correction. The problem is you have criteria on the right table in the where clause, so this is acting like an inner join .

ASKER CERTIFIED SOLUTION

Kevin Cross

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

j2911

ASKER

Thank you for all of your help. - I thought I closed this the other day but it didn't work.