• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 401
  • Last Modified:

WHERE Clause issues

I built this access database that pulls CQ's from a Clear Quest Defect system and allows the user to track what fixes for what bugs have been deployed to any one of our production or test systems ...Its been working great but the company decided to move to another system...Quality Center.  They are migrating all CQ's that have not been closed into the new system...after pulling my hair out re-mapping the fields I seem to have everything working again but one small issue

Some of the user_names that have been imported into the new system does not actualy exist in the new systems user table.  I noticed that my query will not return the record if any of the last three "AND' statements does not find a matching USER_NAME in the uses table..which I expect because its not satisfying the AND.  

After having a long heated discussion with the DBA of the new system he is only willing to agree to create a single user in the user table with a USER_NAME and FULL_NAME of "Non Existant" that can replace the old usernames carried over from the old system (virtualy).  We need to preserve the original values so overwriting them is not an option.

What I need to know is if there is a way to pass the "Non Existant" username in replacement of the actual value for the USER_NAME in the last three AND statements IF it does not find a match against teh records actual value so I can (a) get the query to return a result and (b) pull the FULL_NAME of "Non Existant" in the select statement indicating to us that the fields actual value is one from the previous system.

FYI...the first AND statement was prepopulted with the DBA's account or else the GUI would have issues since the system normally auto inserts the user generating the CQ.
SELECT 
        B.BG_BUG_ID, B.BG_USER_01, B.BG_STATUS, 
        B.BG_PROJECT, B.BG_SUMMARY AS HEADLINE, B.BG_DESCRIPTION AS DESCRIPTION, 
        B.BG_DEV_COMMENTS AS COMMENTS, B.BG_SEVERITY AS SEVERITY, B.BG_DETECTION_DATE, 
        B.BG_PLANNED_CLOSING_VER AS BUILD_OPEN, B.BG_CLOSING_VERSION AS BUILD_CLOSE, B.BG_USER_03 AS RESOLUTION, 
        B.BG_USER_04 AS DEFECT_TYPE, B.BG_USER_09 AS TEST_TYPE, B.BG_VTS AS QUAL_LAST_UPDATED_DATE, 
        B.BG_USER_25 AS REJECT_NOTES, B.BG_USER_26 AS RESOLUTION_NOTES, U.FULL_NAME AS SUBMITTER, 
        UA.FULL_NAME AS OWNER, UB.FULL_NAME AS ASSIGNED_BY, UC.FULL_NAME AS TESTER
 
FROM 
        (((QC_TEST_ICOBWDEMO_DB_BUG_L AS B LEFT JOIN QCSITEADMIN_DB_USERS_L AS U ON B.BG_DETECTED_BY = U.USER_NAME) 
        LEFT JOIN QCSITEADMIN_DB_USERS_L AS UA ON B.BG_RESPONSIBLE = UA.USER_NAME) 
        LEFT JOIN QCSITEADMIN_DB_USERS_L AS UB ON B.BG_USER_06 = UB.USER_NAME) 
        LEFT JOIN QCSITEADMIN_DB_USERS_L AS UC ON B.BG_USER_08 = UC.USER_NAME
 
WHERE 
        (((B.BG_BUG_ID)=intVal) 
        AND ((B.BG_DETECTED_BY)=[U].[USER_NAME])
        AND ((B.BG_RESPONSIBLE)=[UA].[USER_NAME]) 
        AND ((B.BG_USER_06)=[UB].[USER_NAME])
        AND ((B.BG_USER_08)=[UC].[USER_NAME]));

Open in new window

0
dkmcgowan
Asked:
dkmcgowan
  • 3
  • 2
1 Solution
 
pcelbaCommented:
You are using LEFT JOINs, so if you remove the last four conditions from the WHERE then all uknown users should obtain NULL values and these NULL values will indicate "Non existant" users.

Ïf you need to display the exact text instead of NULL value, you could use

IIF(ISNULL(UA.FULL_NAME), "Non existant", UA.FULL_NAME) AS OWNER

or something similar (I hope this will work in Acces, I don't know its syntax and functions, sorry)
0
 
dkmcgowanAuthor Commented:
We need to return the full name assigned to the user_name for that column.  Since alot of this data was migrated over from a previous system  th eUSER_NAME is not going to match up to a user_name in the users table.  so instead of returning nothing because it didnt find a match.  I would prefer it return the "Non Existant".  There will be many cases where a match will be found so the solution im looking for is just to catch the small percentage that do not match up.  The query im building is going to be the recordsource for a form and the values being returned will populate form fields.
0
 
pcelbaCommented:
If there is no match the IIF() function should work as you need. Even without IIF all the existing matches should appear because of LEFT JOINs definitions.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Chuck WoodCommented:
I think this will work for you.

WHERE 
        (((B.BG_BUG_ID)=intVal) 
        AND (((B.BG_DETECTED_BY)=[U].[USER_NAME]) OR (B.BG_DETECTED_BY)='Nonexistent')
        AND (((B.BG_RESPONSIBLE)=[UA].[USER_NAME]) OR (B.BG_RESPONSIBLE)='Nonexistent') 
        AND (((B.BG_USER_06)=[UB].[USER_NAME]) OR (B.BG_USER_06)='Nonexistent')
        AND (((B.BG_USER_08)=[UC].[USER_NAME]) OR (B.BG_USER_08)='Nonexistent')));

Open in new window

0
 
dkmcgowanAuthor Commented:
Hi everyone....So far your solutions are not working.  The query is stil not returning the record.  I tried this but to no avail:
AND IIF((B.BG_USER_08)<>[UC].[USER_NAME], (((B.BG_USER_08)='Nonexistent') AND (B.BG_USER_08)=[UC].[USER_NAME]), (B.BG_USER_08)=[UC].[USER_NAME]));

I though that would return the Nonexistant FULL_NAME value from the user table but the full query returned nothing again.
0
 
pcelbaCommented:
Did you try my suggestions? How many rows is returned  by following query?
SELECT 
        B.BG_BUG_ID, B.BG_USER_01, B.BG_STATUS, 
        B.BG_PROJECT, B.BG_SUMMARY AS HEADLINE, B.BG_DESCRIPTION AS DESCRIPTION, 
        B.BG_DEV_COMMENTS AS COMMENTS, B.BG_SEVERITY AS SEVERITY, B.BG_DETECTION_DATE, 
        B.BG_PLANNED_CLOSING_VER AS BUILD_OPEN, B.BG_CLOSING_VERSION AS BUILD_CLOSE, B.BG_USER_03 AS RESOLUTION, 
        B.BG_USER_04 AS DEFECT_TYPE, B.BG_USER_09 AS TEST_TYPE, B.BG_VTS AS QUAL_LAST_UPDATED_DATE, 
        B.BG_USER_25 AS REJECT_NOTES, B.BG_USER_26 AS RESOLUTION_NOTES, 
IIF(ISNULL(U.FULL_NAME), "Non existant", U.FULL_NAME) AS SUBMITTER, 
IIF(ISNULL(UA.FULL_NAME), "Non existant", UA.FULL_NAME) AS OWNER,
IIF(ISNULL(UB.FULL_NAME), "Non existant", UB.FULL_NAME) AS ASSIGNED_BY, 
IIF(ISNULL(UC.FULL_NAME), "Non existant", UC.FULL_NAME) AS TESTER
 
FROM 
        (((QC_TEST_ICOBWDEMO_DB_BUG_L AS B LEFT JOIN QCSITEADMIN_DB_USERS_L AS U ON B.BG_DETECTED_BY = U.USER_NAME) 
        LEFT JOIN QCSITEADMIN_DB_USERS_L AS UA ON B.BG_RESPONSIBLE = UA.USER_NAME) 
        LEFT JOIN QCSITEADMIN_DB_USERS_L AS UB ON B.BG_USER_06 = UB.USER_NAME) 
        LEFT JOIN QCSITEADMIN_DB_USERS_L AS UC ON B.BG_USER_08 = UC.USER_NAME
 
WHERE 
        (B.BG_BUG_ID=intVal) ;

Open in new window

0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now