Data type conversion in SQL with blank fields

I am trying to do a join between different tables using acct_id. Table 1 is the main linking table and has this field formatted as varchar(10). All other tables has it as has it as Decimal(10).
Table 1 also has blanks (not nulls) for acct_id. When I tried to use CAST I just get errors. I think it's coming from those blank fields but don't know how to fix it.
I'm using SQL going through a linked server.
Ex:
Select * from openquery(CMFR1,'
Select
mcr.ln_no
,mcr.tran_num
,mcr.acct_id
,prod_dt
from MSP_CR mcr
left join on dly_prod prod on mcr.acct_id = prod.acct_id
')
Then left joins on 4 other tables using the same mcr.acct_id =
DCUnitedAsked:
Who is Participating?
 
Nitin SontakkeDeveloperCommented:
If field in question is nullable, I suggest you update all empty string values with NULL, this doesn't result in any data loss anyway, just makes coding a bit easier.

Otherwise, you can do nullify them on the fly as in nullif([column_name], '') and then apply nullability check on it, if you wish.
0
 
Kyle AbrahamsSenior .Net DeveloperCommented:
if you're looking for the blanks you have to address the join.
If you're not looking for the blanks in the where clause should do:
EG:

(Looking for blanks)
Select 
mcr.ln_no
,mcr.tran_num
,mcr.acct_id
,prod_dt
from MSP_CR mcr
left join on dly_prod prod on case when len(mcr.acct_id) >= 1 then mcr.acct_id else 0 end = prod.acct_id

Open in new window


Excluding Blanks:
Select 
mcr.ln_no
,mcr.tran_num
,mcr.acct_id
,prod_dt
from MSP_CR mcr
left join on dly_prod prod on mcr.acct_id = prod.acct_id
where len(mcr.acc_id) >= 1

Open in new window

0
 
DCUnitedAuthor Commented:
I had tried the len previously & received an error.
I tried what you have ( to include blanks since there are other fields that need to be pulled in from that table) and received an error

OLE DB provider "MSDASQL" for linked server "CMFR1" returned message "[IBM][CLI Driver][DB2/AIX64] SQL0440N  No authorized routine named "LEN" of type "FUNCTION" having compatible arguments was found.  SQLSTATE=42884
0
 
Kyle AbrahamsSenior .Net DeveloperCommented:
use LENGTH(<column>) instead of  len  as the open query is firing on a DB2 server.
https://www.ibm.com/support/knowledgecenter/en/SSBJG3_2.5.0/com.ibm.gen_busug.doc/c_fgl_odiagdb2_042.htm

Essentially openquery is like opening a terminal to that machine and firing a query there.  It has to be written in the syntax of the host DB.
0
 
DCUnitedAuthor Commented:
I can't believe how simple that is. I try to make things more complicated that it should be. It worked like a charm.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.