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.
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
(((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