UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Converting Field Type In Query, SQL Server 2008 R2    
 
   
JV63
post Nov 29 2017, 11:42 AM
Post#1



Posts: 183
Joined: 16-August 12



Hi. Have a simple query that's giving me "invalid number" error message. I believe it's because in one table the NDC code is defined as varchar and in the other table it's decimal. Can I change that in my where statement somehow so they are the same type? I can't overwrite tables as they are secure.

CODE
SELECT
NDC_CD,
LPAD(IMS_PROD_ID,7,0)||LPAD(IMS_PPK_ID,3,0) AS CMF10,
BRAND_NAME,
GENERIC_NAME

FROM FRMDBA.R_FRM_NDC_IMS_X_REF A, NDCDBA.NDC_CODE B

WHERE A.NDC_CD = B.NDC_CODE


Thanks
Go to the top of the page
 
GroverParkGeorge
post Nov 29 2017, 11:51 AM
Post#2


UA Admin
Posts: 31,197
Joined: 20-June 02
From: Newcastle, WA


Are you doing this in Access, or in SQL Server?

In Access there are conversion functions, such as CDbl(). In SQL Server, you can use the Cast() or Conver() functions.

--------------------
Go to the top of the page
 
JV63
post Nov 29 2017, 11:57 AM
Post#3



Posts: 183
Joined: 16-August 12



SQL, not using Access. I was looking at CAST or CONVERT but wasn't sure of the syntax and if it goes in the WHERE or SELECT?

Thanks
Go to the top of the page
 
GroverParkGeorge
post Nov 29 2017, 12:42 PM
Post#4


UA Admin
Posts: 31,197
Joined: 20-June 02
From: Newcastle, WA


You ought to get some useful guidance here. There are examples at the bottom of the page.

I think you'll need to do it in the JOIN, at least, because that's where the error is coming from.

--------------------
Go to the top of the page
 
MadPiet
post Nov 29 2017, 02:03 PM
Post#5



Posts: 2,257
Joined: 27-February 09



Hold the phone...

that's Oracle syntax ( and really old! ), not SQL Server. Surprised that works at all.
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    12th December 2017 - 06:42 PM