Here is the issue,
I was given the task of creating a datamart by combining the information from several different database servers. While doing this I ran into an interesting issue that I can't seem to figure out.
I have two tables, one table has a code value in it pointing to the corresponding lookup table. The lookup table for whatever reason (I didn't build the application or the database, yay legacy support! ) has two lookup columns, one is the standard incrementing numbers and the other is a series of letters. The problem lies in the fact that the code in the first table is a char(1) and can either be a letter or a number. The look up has type int and char so I run into a simple conversion problem. Here is my datamart select statement that fails because it can not convert a char to an int. My question is what would be an easy way to test which value type the char(1) is and use the appropriate left outer join.. I tried several different approaches but just can't seem to figure it out.
SELECT *
FROM PERMIT
LEFT OUTER JOIN PERMIT_STATUS_LU
ON
(
(PERMIT.endorse_status_cd = PERMIT_STATUS_LU.PSLU_PERMIT_STATUS_NM) OR
(PERMIT.endorse_status_cd = PERMIT_STATUS_LU.pslu_cd)
)
the above of course fails due to type conversion. status_cd is the char(1), status_nm is the char and pslu_cd is the int. I have no clue why they set it up this way, but I get to figure out how to deal with it.
Confused,
-Andrew
Try to use ISNUMERIC:
declare @.c char(1)
set @.c='5'
select isnumeric(@.c)
Result: 1
declare @.c2 char(1)
set @.c2='a'
select isnumeric(@.c2)
Result: 0
|||
yeah I thought about that but then the question is, how do you use IF ELSE Logic inside a JOIN statement
Can you even do something like this? ( I just tried it and got parse errors) So I guess the question is now how do you put conditional logic inside a JOIN ON ?
LEFT OUTER JOIN PERMIT_STATUS_LU
ON
(
IF ( isnumeric(PERMIT.endorse_status_cd) = 1)
BEGIN
(PERMIT.endorse_status_cd = PERMIT_STATUS_LU.PSLU_PERMIT_STATUS_NM)
END
ELSE
BEGIN
(PERMIT.endorse_status_cd = PERMIT_STATUS_LU.pslu_cd)
END
)
|||
Use CASE instead.
Also, isnumeric, under some situations, can be unreliable. Refer to this article for details.
isnumeric -What is wrong?
http://www.aspfaq.com/show.asp?id=2390
Good idea using the CASE. I am still getting errors though, I am still unsure if its even possible to put a CASE statement inside the JOIN on clause. For example I tried
....
FROM PERMIT
CASE isnumeric(PERMIT.endorse_status_cd)
WHEN 0 THEN LEFT OUTER JOIN PERMIT_STATUS_LU ON PERMIT.endorse_status_cd = PERMIT_STATUS_LU.PSLU_PERMIT_STATUS_NM
WHEN 1 THEN LEFT OUTER JOIN PERMIT_STATUS_LU ON PERMIT.endorse_status_cd = PERMIT_STATUS_LU.pslu_cd
END
That didnt work so I tried
....
FROM PERMIT
LEFT OUTER JOIN PERMIT_STATUS_LU ON
(
CASE isnumeric(PERMIT.endorse_status_cd)
WHEN 0 THEN PERMIT.endorse_status_cd = PERMIT_STATUS_LU.PSLU_PERMIT_STATUS_NM
WHEN 1 THEN PERMIT_STATUS_LU ON PERMIT.endorse_status_cd = PERMIT_STATUS_LU.pslu_cd
END
)
that failed as well
Does anyone have a definative answer to the above question?
|||I created 2 separate queries: One that contains only the rows that have a numeric value for endorse_status_cd and another for the non numeric values. I used a union to merge them into one singe result set. Here is the query:
Code Snippet
SELECT *
FROM PERMIT
LEFT OUTER JOIN PERMIT_STATUS_LU
ON PERMIT.endorse_status_cd = PERMIT_STATUS_LU.PSLU_PERMIT_STATUS_NM
WHERE IsNumeric(PERMIT.endorse_status_cd) = 0
UNION
SELECT *
FROM PERMIT
LEFT OUTER JOIN PERMIT_STATUS_LU
ON PERMIT.endorse_status_cd = PERMIT_STATUS_LU.pslu_cd
WHERE IsNumeric(PERMIT.endorse_status_cd) = 1
Based on the article that Arnie included in his reply, it might be better to create your own function (check the article) instead of IsNumeric.
I hope this answers your question.
Best regards,
Sami Samir
No comments:
Post a Comment