I am writing a web site in ASP .NET (Oracle 9.2.4) and it is going to be used in 3 different organizations within our company. I need to get the org_id from the person's username so I can display only the data for their organization. I'm not sure I am looking at the correct table(s). My current query is:
SELECT loc.inventory_organization_id FROM
WHERE (loc.location_id = paa.location_id) AND
(pap.person_id = paa.person_id) AND
(pap.person_id = fu.employee_id) AND
(fu.user_name = '" + userName + "');
The problem with this is I get a value from my test instance but when I run it in the production instance the inventory_organization_id field is not filled in so I am wondering if I am in the right place. Any insight for me? Thank you.
Yes I am getting back a null record, and I was wondering if that was the correct place to look in for the Org_ID but no one filled it in, or if I should be looking in another table. So that's my real question, from what table does one determine organization id?
Guessing here, but because of your equi-joins, you are coming back with a NULL record. If it's not that, then there's no username which matches the one you passed in.
Deconstructing the SQL statement, and adding back in one table at a time will help you figure out which of the 2 above scenarios are possible. If it's the former (equi-joins), at first you'll see records returned until you finally add the table and it's equi-join which foils the request. If it's the latter (no user name), then you'll know it immediately when you run
SELECT fu.user_name FROM
WHERE (fu.user_name = 'SMITHD');
Then, you can decide what to do, use outer-joins, a union all, etc.
Well the user absolutely exists, because it is me. :-) Plus that query works in my test instance, so I'm sure the query is not the problem.
I am using Toad and I can see in the table (in the new instance) that the inventory_organization_id column contains no values, so I don't know if someone didn't fill it in correctly or if I am looking in the wrong place. And long story short, I don't have anyone in my company that I can ask, so I am trying to find out for myself if I need to somehow populate this column or if I am just barking up the wrong tree to begin with.
Hi, dreamaway. It seems that the data in the test database is not the same as the data in the production database. For example, the inventory_organization_id field of the hr_locations_all table of the test database contain data but the inventory_organization_id field of the hr_locations_all table of the production database does not contain data. It may help if you make the data in the test database the same as or a subset of the data in the production database.