I have a bunch of queries, against a couple different databases, that I use to generate delimited reports. I'm trying to create some new reports against a MSSQL database and I'm having trouble replicating our other processes to get consistent results.
When I query against the other sources I get '' and 0 in place of nulls. When I query against MSSQL I get the nulls.
I'm using DSN strings => ODBC => remote database for some queries... and straight connection strings for other queries.
So against a DB2 database:
SELECT OrderID, CustomerName FROM OrderTable LEFT JOIN CustomerTable on OrderCustomerID = CustomerID
Assume the CustomerTable doesn't have a Customer, so for OrderID 1, no customerID the result would be (output as a | delimted list):
0|Joe Schmoe 1|NULL 2|Jane Doe
When using DB2, I get "1|". When using MSSQL, I get "1|null".
Using ODBC, connection strings, server settings... how can I return "" or 0 on nulls to get the output to match our other processes?
I've tried a straight connection (no DSN/ODBC). I've tried different settings in ODBC. All three SQL drivers (SQL Server, SQL Native Client, SQL Server Native Client 10.0). All are coming back with NULL instead of "default" for the data type.
Update/Clarification I'm working with an "in house" ETL program that pulls and transforms data from in-house databases for export to other institutions/customers mainly via delimited text. Previously all of the queries I've used, when hit with nulls, have simply output ""/0. MSSQL obviously doesn't do that, so I'm trying to mimic the other sources at the most generic level I can.
I understand how IsNull/Colesce works, and if need be, I will use these to get the job done. The obvious issue with using IsNull/Colesce is the added code, across what will be many queries. Added code means harder maintenance, more creation time, more points of failure, etc. If I can simply tell the connection level to replace all nulls, I would think it'd be much easier... but that makes the question Is it possible? and if so how? Either in the SQL2005 Server instance, or on the other side of the connection at the ODBC connection or connection string.