Bizarre convert varchar to numeric only on Union

Question

Post reply

Bizarre convert varchar to numeric only on Union

ProKelly

SSCommitted

Points: 1867
More actions
October 24, 2010 at 1:14 am

#230352

I have a very strange problem.
I have 2 views on some data, both have equal number of output fields, and the columns are the same types. The SQL is messy because I'm most definately not a sql developer, but the idea is that it each view takes data from one form, and puts it in the same format. Then I can union those views and have one output of both types of data. The views work themselves fine, without error but when introducing the union it fails to work.
Can you help!? Just some background info - the concat is in there only to provide a semi unique classifcation key so that I can link the contact upto a more detailed description. On the old form where the columsn have set 0 values it's because that data cannot be taken from the old form as it probably doesnt exist.
I have included the views below.
OLDForm
SELECT b.SourceID, b.CaseRef, a.CASE_ENQUIRY_ID AS CaseID, CAST(FLOOR(CAST(b.CreationDate AS FLOAT)) AS DATETIME) AS DateID, a.AREA,
a.CLIENT AS CLIENTGROUP, CASE WHEN a.OVER18 = '-18' THEN 1 ELSE 0 END AS Child, CASE WHEN a.OVER18 = '18+' THEN 1 ELSE 0 END AS Adult,
CASE WHEN a.REFERRALREASON IS NULL THEN 0 ELSE 1 END AS IsReferral, CAST('0' AS NUMERIC) AS NumOfReferrals, CAST('0' AS NUMERIC)
AS NumOfRelations, CASE WHEN CONTACTREASON IS NULL THEN REFERRALREASON ELSE CONTACTREASON END AS CONTACTREASON,
a.METHOD AS ChannelID, 'complete' AS FIRSTCOMPLETION, { fn CONCAT(a.CONTACTREASON, a.AREA) } AS ClassificationID, CAST('0' AS numeric)
AS NumOfDbaseChecks, CAST('0' AS NUMERIC) AS ContactsOpen, b.CreationDate, 'NA' AS CONTACT
FROM frontline_live.dbo.FLODS_CRUENQUIRYLOG_E00 AS a INNER JOIN
frontline_live.dbo.LGNCC_ENQUIRY AS b ON a.CASE_ENQUIRY_ID = b.CaseID
WHERE (NOT (b.SourceID = 'lagan')) AND (NOT (b.SourceID = 'ctitest')) AND (NOT (b.SourceID = 'itctitest')) AND (NOT (b.SourceID = 'icti')) AND
(NOT (b.SourceID = 'kbyrne')) AND (NOT (b.SourceID = 'cti'))
NEWForm
SELECT a.CASE_ENQUIRY_ID AS CaseID, CAST(FLOOR(CAST(b.CreationDate AS FLOAT)) AS DATETIME) AS DateID, a.CONTACTREASON, a.CONTACT,
CASE WHEN OVER18 = 'yes' THEN 1 ELSE 0 END AS Adult, CASE WHEN OVER18 = 'no' THEN 1 ELSE 0 END AS Child,
CASE WHEN REFERRAL = 'Yes' THEN 1 ELSE 0 END AS IsReferral, CASE WHEN ISNUMERIC(CONTACTSOPEN)
<> 0 THEN CAST(CONTACTSOPEN AS NUMERIC) ELSE 0 END AS ContactsOpen, CASE WHEN ISNUMERIC(NOOFREFERRALS)
<> 0 THEN CAST(NOOFREFERRALS AS NUMERIC) ELSE 0 END AS NumOfReferrals, CASE WHEN ISNUMERIC(RELATIONS)
<> 0 THEN CAST(RELATIONS AS NUMERIC) ELSE 0 END AS NumOfRelations, a.CLIENTGROUP, a.AREA,
CASE WHEN ISNUMERIC(NODATABASECHECKS) <> 0 THEN CAST(NODATABASECHECKS AS NUMERIC) ELSE 0 END AS NumOfDbaseChecks,
b.CreationDate, b.SourceID, a.METHOD AS ChannelID, b.CaseRef, CASE WHEN a.FIRSTCOMPLETION IS NULL
THEN 'Emergency Parked' ELSE a.FIRSTCOMPLETION END AS FIRSTCOMPLETION, { fn CONCAT(a.CONTACTREASON, a.AREA)
} AS ClassificationID
FROM frontline_live.dbo.FLODS_2901_NEWCRUENQUIRYLOGVIEW_E00 AS a INNER JOIN
frontline_live.dbo.LGNCC_ENQUIRY AS b ON a.CASE_ENQUIRY_ID = b.CaseID
WHERE (NOT (b.SourceID = 'lagan')) AND (NOT (b.SourceID = 'ctitest')) AND (NOT (b.SourceID = 'itctitest')) AND (NOT (b.SourceID = 'icti')) AND
(NOT (b.SourceID = 'kbyrne')) AND (NOT (b.SourceID = 'cti'))
All help is greatly appreciated.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Johan Bijnens SSC Guru Points: 135373 More actions · Answer 1

the columns of both queries are not in the same order.

Keep in mind SQLserver doesn't map your columns by name but by ordinal position.

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

ProKelly SSCommitted Points: 1867 More actions · Answer 2

You, sir - are the saviour of my exponentially frustrated mind. Thankyou so, so much.

Johan Bijnens SSC Guru Points: 135373 More actions · Answer 3

HTH.

Most of us still fall in that trap every once in a while.;-)