April 15, 2003 at 3:24 pm
I have two tables, one is a company information table the other is a NAICS/SIC code table. I'm trying to return a query using the NAICS code as searchable criteria, however, since most companies have more than one NAICS code associated with them, I'm getting duplicate entries.
Here is related table layout:
COMPANY TABLE: PRIVATE(lngCompID)
NAICS/SIC TABLE: PRIV_SIC(lngCompID, lngNAICSCode)
I've tried using the following function fncSICForPRIV (I got from a friendly developer on another forum), but I keep getting nothing back for "custom variable" NAICS.
here is the query:
SELECT PRIVATE.lngCompID, strCompany, strAddress1, strCity, strState, lngZip, dbo.fncFormatPhone(strPhone) as strPhone, 'Private' as CompType, PRIV_SIC.lngNAICSCode, PRIVATE.fltRevenue as lngRevenue, PRIV_EMP.lngFTE, strNet, '' AS strSymbol, txtDescription, dbo.fncSICforPRIV(PRIVATE.lngCompID) AS NAICS
FROM PRIVATE INNER JOIN PRIV_SIC ON PRIVATE.lngCompID = PRIV_SIC.lngCompID
INNER JOIN v_FIN_HIST_LATEST ON PRIVATE.lngCompID=v_FIN_HIST_LATEST.lngCompID
INNER JOIN PRIV_FIN_HIST ON v_FIN_HIST_LATEST.lngCompID=PRIV_FIN_HIST.lngCompID AND v_FIN_HIST_LATEST.dtmLatestUpdate=PRIV_FIN_HIST.lngFYEYR
INNER JOIN v_EMP_LATEST ON PRIVATE.lngCompID = v_EMP_LATEST.lngCompID
INNER JOIN PRIV_EMP ON v_EMP_LATEST.lngCompID = PRIV_EMP.lngCompID AND v_EMP_LATEST.dtmLatestUpdate = PRIV_EMP.dtmDate
WHERE
Here is the function:
FUNCTION dbo.fncSICForPUB (@lngCompID int)
RETURNS varchar (1000)
AS
BEGIN
DECLARE @retVal varchar (1000)
SELECT @retVal = @retVal + CAST (lngNAICSCode AS varchar (30)) + ','
FROM PUB_SIC
WHERE lngCompID = @lngCompID
SELECT @retVal = ISNULL (@retVal, '')
RETURN @retVal
END
Anyone able to wrap their heads around this one and give me any ideas on how to fix this or another approach entirely?
Thank you very much for any help you can give!
Mischa
April 16, 2003 at 8:28 am
Your friendly developer forgot to initialize @retVal in the function. When delcared, its value is NULL, and concatenating anything with NULL always yields NULL. So add the following line right after the DECLARE statement:
SET @retVal = ''
Jay Madren
Jay Madren
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply