Phantom Result (1 Viewer)

Hi,

I have a query that I run that produces a list of employees that are transfers. If they have transferred inter-division it assigns a 1 if not an inter-division transfer a blank or they do not show up in the query if they were not a transfer.

My HR query outer joins to the transfer query above.

The issue is an employee who is not in the transfer query will show a 1 when joined to the HR query, a phantom result.

This is a new issue as last year it produced correct results. The only change was the dates of transfer.

I can get the new query of the 2 above queries to work if I employ the transfer table into a make table, then it does not assign a 1 when the employee is not found when joining.

Any ideas?

Thank you!

Hi. Welcome to AWF! Are you able to post a sample copy of your db with test data? If the query worked before, then it might be something different with the data.

theDBguy said:

If the query worked before, then it might be something different with the data.

Click to expand...

an expansion on that....more than likely any expert here would have to see the entire sql statement that's causing the problem to give a meaningful answer.

I can post the photos. would that be sufficient?

Transfer table is the one where it assigns the 1 if inter-division or "" if not

Transfer 2 png is showing the example person who is not in the result of the query

link query is taking the hr query and joining to the transfer table

link table result is showing a 1 in LS column where it should be blank.

I'm not totally clear what the issue is but I think the problem may be with your LS_Prorate expression in the query

Change this

to this


Doing so explicitly sets the 3 conditions which give a true value 1.
I've also replaced the empty string with null

Hope that helps

xstine,

this is a little out of my wheelhouse I think because it sounds like you're trying to report to your HR people who is transferring between divisions and who isn't, and they want records of those moves. is that right? If that is right, then I wouldn't do it the way you're doing it. I can't offer you help with regard to your current setup because I'm not understanding why you're doing it. Maybe dbGuy can help you in that regard. However, if you want my viewpoint on how to set something like that up from the beginning so you don't get confused, see the attached images. That's how I would do it so I could query the stuff out easily without the need for any outer joins. Come to think of it, I haven't used a LEFT or RIGHT join in years. I was just telling a hiring manager that last week, who required me to take a proficiency test in querying data from Oracle databases. I hope this does at least some good for you. I would ask the others here for more options for answers though....

Thank you so much! That worked perfectly. Not sure why it stopped working or why this was the only division that had issues (the other 3 are set up the same and flow correctly) other than we upgraded to Win10.

I appreciate everyone's help!

I realised after posting it that you had already filtered the field To_SSD_R for M1 or M2 or M3 in the field criteria.
So would it still work if you just set your expression as LS_ProRate:1