shani19831 (9/11/2012)
Eugene Elutin (9/11/2012)
One of the column you return in one of your "selects" (I think it's a first one) is of smalldatetime data type (we don't know which column exactly, as you didn't post any ddl and code looks like noodles a bit). The position of this column in this "select" corresponds to position of "character" data type column from second "select", where character data cannot be converter implicitly to datetime.
First thing I would advise you to do is to format your code, so you could read it easily. It can help you to find this column just by comparing to select lists. You can format it to something like:
Select top 10 =
CASE
WHEN a.id_value IS NULL THEN 'Not Company'
ELSE a.id_value
END AS [Company Reg No]
,a.sic_code AS [Industry]
,a.tin AS [Tax ID]
,a.tin_cert_dt AS [Registration Date]
,a.status AS [Company Status]
, ...
etc.
You will see that code formatted as above is easier to maintain and debug.
Eugene, thanks for this hint, the code does look handy in this format. Let me format it and see if i can find anything wrong and i will post back.
Regards,
There's a syntax error in the second query of the union: the last word in the FROM list.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden