October 5, 2006 at 7:41 am
I am creating a view to pull some information together...to replace a DTS that was created for the same purpose.
The DTS had a view which I have expaneded on...the DTS used "lookups" with Active X script to do a seperate look up to set some of the field values which is the portion I would like to replace and just use a view.
The logic deals with two fields which I will name (Loc_1_id and Loc_2_id).
The Look up basically used Loc_1_id to do a look up on a table, if it was present, it set a value back....if it was not available it went to a second table and did a lookup and if the value was there it set the value.
Here is the active X code snippet that shows the logic of the Lookup.
'LOCATION
'Assigns the locations using lookups for translation and verification
sLocation = DTSSource("LOCATION")
sHRLocation = DTSLookups("lkpLocations").Execute(sLocation)
sHRLocation = ReplaceNull(sHRLocation, "N/A")
'HR LOCATION
IF sHRLocation = "N/A" THEN
sHRLocation = DTSLookups("lkpHRLocation").Execute(sLocation)
sHRLocation = ReplaceNull(sHRLocation, "N/A")
END IF
DTSDestination("HRLocationCode") = RTrim(sHRLocation)
'PHYSICAL LOCATION
sLocation = DTSSource("B_PHY_LOCATION")
sPHYLocation = DTSLookups("lkpLocations").Execute(sLocation)
sPHYLocation = ReplaceNull(sPHYLocation, "N/A")
IF sPHYLocation = "N/A" THEN
sPHYLocation = DTSLookups("lkpPHYLocation").Execute(sLocation)
sPHYLocation = ReplaceNull(sPHYLocation, "N/A")
END IF
DTSDestination("PhysicalLocationCode") = RTrim(sPHYLocation)
SO there are two look ups for each field...which I don't think I could embed in a SELECT statement...(not sure of the logic to do multiple select statements on the same field).
SELECT
e.Name,
e.Address,
Location = ( IF N/A(select LocationCode_1 from Location_1 where e.Location = LocationCode_1)
ELSE
IF N/A(select LocationCode_2 from Location_2 where e.Location = LocationCode_2)
FROM
e.Employee
I know the syntax is not correct at all but that is what I was wondering if it is possible to do something like that???
OR
Should I just use a FUNCTION and pass in the location value from Employees and do the logic in a Function?
Hopefully this makes a little bit of sense...
October 5, 2006 at 8:04 am
Here is the function I came up with that would do the logic I need...
DECLARE @location VARCHAR(3),
@LocID VARCHAR(5),
@LocType VARCHAR(3)
SET @LocID = '91A'
SET @LocType = 'HR'
SELECT @location = PlantCode
FROM DBA.dbo.tb_ci_Plants
WHERE PlantCode = @LocID
--If the look up returns no results we need to
--set the variable to a value to perform the second
--lookup to assign the location code for either
--HR or PHY Location
SET @location = ISNULL(@location, 'N/A')
IF @location = 'N/A' AND @LocType = 'HR'
BEGIN
--HRLocation
--DS071020
-----------------------------
SELECT @location = B_COMN_SITE_NO
FROM PS_B_HR_COMN_SITE
WHERE LOCATION = @LocID
END
IF @LocID = 'N/A' AND @LocType = 'PHY'
BEGIN
--PHYLocation
--DS071020
-----------------------------
SELECT @location = B_COMN_SITE_NO
FROM PS_B_PHY_COMN_SITE
WHERE B_PHY_LOCATION = @LocID
END
SELECT @location
October 5, 2006 at 8:18 am
When you create a function in a database, there is a permission issue, only sysadmin, db_owner and ddl_admin can use it but sysadmin can grant execute permission to other logon. So I would suggest a subquery.
Can you tell me what is the result that you want so maybe I can try to write the query ? Looking at your Active X script, I had no idea what you want to get.
October 5, 2006 at 8:24 am
I am not sure what you are trying to do, but a brief look suggests that you may be able to get away with a select along the lines of:
SELECT E.[Name]
,E.Address
,COALESCE(L.PlantCode, H2.B_COMN_SITE_NO, 'N/A') as HRLocation
,COALESCE(L.PlantCode, P2.B_COMN_SITE_NO, 'N/A') as PHYLocation
FROM Employee E
LEFT JOIN DS098072.ComInfo.dbo.tb_ci_Plants L on E.Location = L.PlantCode
LEFT JOIN DS071020.[PROFILE].dbo.PS_B_HR_COMN_SITE H2 on E.Location = H2.Location
LEFT JOIN DS071020.[PROFILE].dbo.PS_B_PHY_COMN_SITE P2 on E.B_Phy_Location = P2.B_PHY_LOCATION
Edit: I originally made a mistake in having E.Location instead of L.PlantCode as the first parameter in COALESCE.
October 5, 2006 at 8:28 am
I know the active X script doesn't really show much...the post would have been longer and more confusing if I would have posted the code for each look up.
The general premise is as follows...
In the general SELECT statement I have...there are two fields
HRLocationCode AND PhysicalLocationCode
Originally these two field values were used to do a look up in a different table to assign a HR location...if the field was not found in TABLE ONE the look up in the DTS would move on the TABLE TWO and perform the look up.
If there is a match in TABLE ONE then it assigns the matching value to HRLocationCode. If it is not found in TABLE ONE, it moves on to the second lookup in TABLE TWO
If it was found in TABLE TWO (which appears to be a catch all) then it assigns the value of HRLocationCode = to the TABLE TWO value.
The problem I couldn't get my head around is how to do the look up logic within the actual select statement for each field, because in my mind you would have to do two look ups per HRLocationCode AND PhysicalLocationCode for each row of the query...
October 5, 2006 at 8:40 am
I am working with this at the moment, I will let you know what come of it...quickly
October 5, 2006 at 9:10 am
Thanks for the code but it did not work...it basically returned the same value...
For what ever reason my boss does not seem like he wants to have me use a FUNCTION, I have it complete and working...he mentioned something about using a stored procedure to complete the task.
Here is the FUNCTION that works...
CREATE FUNCTION bf_Location_Lookup(@LocID VARCHAR(5),
@LocType VARCHAR(3))
RETURNS VARCHAR(3)
AS
BEGIN
DECLARE @location VARCHAR(3)
--testing stuff
-- @LocID VARCHAR(5),
-- @LocType VARCHAR(3)
-- SET @LocID = '91A'
-- SET @LocType = 'PHY'
SELECT @location = PlantCode
FROM DBA.dbo.tb_ci_Plants
WHERE PlantCode = @LocID
--If the look up returns no results we need to
--set the variable to a value to perform the second
--lookup to assign the location code for either
--HR or PHY Location
SET @location = ISNULL(@location, 'N/A')
IF @location = 'N/A' AND @LocType = 'HR'
BEGIN
--HRLocation
--DS071020
-----------------------------
SELECT @location = B_COMN_SITE_NO
FROM PS_B_HR_COMN_SITE
WHERE LOCATION = @LocID
END
IF @location = 'N/A' AND @LocType = 'PHY'
BEGIN
--PHYLocation
--DS071020
-----------------------------
SELECT @location = B_COMN_SITE_NO
FROM PS_B_PHY_COMN_SITE
WHERE B_PHY_LOCATION = @LocID
END
--SELECT @location
RETURN @location
END
Would doing this in a stored procedure be any better? I know someone mentioned the issue about permissions...
How would I fit the logic into a stored procedure?
Thanks for all the tips and answers so far guys...much appreciated.
Leeland
October 5, 2006 at 9:30 am
Can you put SELECT statements inside that COALACSE statement???
I don't think you can but this is what I would love to have, it would evaluate the first select statement, if it were NULL, move to the second and return that value...
SELECT
E.EMPLID,
COALESCE(SELECT L.PlantCode FROM DBA.dbo.tb_ci_Plants L WHERE E.LOCATION = L.PlantCode COLLATE database_default, SELECT P.B_COMN_SITE_NO FROM PS_B_HR_COMN_SITE P WHERE P.LOCATION = E.LOCATION)
FROM
PS_EMPLOYEES E
LEFT JOIN DBA.dbo.tb_ci_Plants L on E.LOCATION = L.PlantCode COLLATE database_default
LEFT JOIN PS_B_HR_COMN_SITE H2 on E.LOCATION = H2.LOCATION
LEFT JOIN PS_B_PHY_COMN_SITE P2 on E.B_PHY_LOCATION = P2.B_PHY_LOCATION
WHERE E.EMPLID = '006126'
October 5, 2006 at 9:49 am
>> Can you put SELECT statements inside that COALACSE statement???
You can, but the outer joins should produce the NULLs for you. Try:
SELECT E.EMPID
,COALESCE(L.PlantCode, H2.B_COMN_SITE_NO, 'N/A') AS HRLocation
,COALESCE(L.PlantCode, P2.B_COMN_SITE_NO, 'N/A') AS PHYLocation
FROM PS_EMPLOYEES E
LEFT JOIN DBA.dbo.tb_ci_Plants L ON E.LOCATION = (L.PlantCode COLLATE database_default)
LEFT JOIN PS_B_HR_COMN_SITE H2 ON E.LOCATION = H2.LOCATION
LEFT JOIN PS_B_PHY_COMN_SITE P2 ON E.B_PHY_LOCATION = P2.B_PHY_LOCATION
October 5, 2006 at 11:05 am
OK You are the man...I had to make one slight modification but after reviewing your logic it makes total sense and I love it...way to go man...
My changes are in RED/Bold...I had to alias one table to differenciate between LOCATION and B_PHY_LOCATION. You had it going to the same table which was using the same field for the comparison...which made the fields the same...
I am posting the entire Select...
SELECT
E.EMPLID as EmployeeID,
COALESCE(L1.PlantCode COLLATE database_default, HR.B_COMN_SITE_NO, 'N/A') as HRLocation,
COALESCE(L2.PlantCode COLLATE database_default, PHY.B_COMN_SITE_NO, 'N/A') as PHYLocation,
P.NAME as EmployeeName,
E.PREFERRED_NAME as EmpPreferredName,
E.JOBCODE as JobCode,
JC.DESCR as JobDescription,
E.BUSINESS_TITLE as BusinessTitle,
E.EMPL_STATUS as EmployeeStatus,
E.EMPL_TYPE as EmployeeType,
E.DEPTID as DeptID,
E.DEPTNAME as DeptName,
E.WORK_PHONE as WorkPhone,
MAX(CASE WHEN WORK2.PHONE_TYPE = 'BUSN' THEN WORK2.PHONE ELSE '' END) as WorkPhone2,
MAX(CASE WHEN FAX.PHONE_TYPE = 'FAX' THEN FAX.PHONE ELSE '' END) as Fax,
MAX(CASE WHEN CELL.PHONE_TYPE = 'CELL' THEN CELL.PHONE ELSE '' END) as CellPhone,
'PROFILE' as CategoryCode,
E.B_DIRECT_REPORT_ID as SupervisorID,
E.B_DIRECT_REPORT_NM as SupervisorName
FROM
PS_EMPLOYEES E (NOLOCK)
LEFT JOIN PS_PERSONAL_DATA P (NOLOCK) ON E.EMPLID = P.EMPLID
LEFT JOIN PS_JOBCODE_TBL JC (NOLOCK) ON E.JOBCODE = JC.JOBCODE
LEFT JOIN DBA.dbo.tb_ci_Plants L1 (NOLOCK) ON E.LOCATION = (L1.PlantCode COLLATE database_default)
LEFT JOIN DBA.dbo.tb_ci_Plants L2 (NOLOCK) ON E.B_PHY_LOCATION = (L2.PlantCode COLLATE database_default)
LEFT JOIN PS_B_HR_COMN_SITE HR (NOLOCK) ON E.LOCATION = HR.LOCATION
LEFT JOIN PS_B_PHY_COMN_SITE PHY (NOLOCK) ON E.B_PHY_LOCATION = PHY.B_PHY_LOCATION
LEFT JOIN PS_PERSONAL_PHONE WORK2 (NOLOCK) ON E.EMPLID = WORK2.EMPLID
AND WORK2.PHONE_TYPE = 'BUSN'
LEFT JOIN PS_PERSONAL_PHONE FAX (NOLOCK) ON E.EMPLID = FAX.EMPLID
AND FAX.PHONE_TYPE = 'FAX'
LEFT JOIN PS_PERSONAL_PHONE CELL (NOLOCK) ON E.EMPLID = CELL.EMPLID
AND CELL.PHONE_TYPE = 'CELL'
GROUP BY
E.EMPLID,
L1.PlantCode,
L2.PlantCode,
HR.B_COMN_SITE_NO,
PHY.B_COMN_SITE_NO,
E.LOCATION,
E.B_PHY_LOCATION,
P.NAME,
E.PREFERRED_NAME,
E.JOBCODE,
JC.DESCR,
E.BUSINESS_TITLE,
E.EMPL_STATUS,
E.EMPL_TYPE,
E.DEPTID,
E.DEPTNAME,
E.WORK_PHONE,
E.B_DIRECT_REPORT_ID,
E.B_DIRECT_REPORT_NM
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply