Sage Line 50 SQL Query throws column not found error or numeric data is returned

Question

I'm trying to query Sage Line 50 via a .NET app. Connecting via ODBC is working fine. I can open/close the database and run very basic queries but one of my query is slightly more complex and I need to include default hardcoded values but it's behaving very strangely.

The query works perfectly fine when I run it in Microsoft Access and running it against linked tables but when I run it in .NET, it throws a column not found error, well, depending on my values.

1) Numeric data returned

If my query is

Select SELECT PURCHASE_LEDGER.ACCOUNT_REF AS SupplierCode, '10' as OrganizationNumber, ...

Result: 10 is returned as 10.00

2) Missing Columns:

If my query is

Select SELECT PURCHASE_LEDGER.ACCOUNT_REF AS SupplierCode, '' as City, ...

Result: my OdbcDataReader will only return the columns up to the first instance of '' so in this instance, it will only return the ACCOUNT_REF

3) Column not found error:

if my query is

Select SELECT PURCHASE_LEDGER.ACCOUNT_REF AS SupplierCode, 'GB' as Country, ...

it will throw an error that the column is not found. Yet if I use '-' as Country or '--' as Country it will work but it returns 0.00 instead of an empty string

So my questions are:

1) Why does MS Access behave differently and allows me to return an empty value when my query includes '' as City for example but it fails to return it when querying via .NET

2) Why doesn't it allow my to use Alphanumeric value such as 'GB' as harcoded values without throwing a column not found.

3) Why does it allow me to use '-' as Country but returns 0.00

Are there any work-around for these? I really need to return hardcoded empty values for some fields, harcoded alphanumeric values for some other fields and I can't have values being returned with decimal places when they should not have any.

It's been driving mad for hours and I need to keep this generic and while I'm having other problems such as a query can't concatenate columns, I've got work-around for this but I'm trying to keep my app as generic as possible so introducing weird parameters in my xml definition just doesn't seem to be making sense. I've been thinking of introducing a 'default' value which would be technically acceptable when defining the field mappings but what about values being returned as 0.00 when it should be an empty string, and what about values that should be 1,2,3 being returned as 1.00, 2.00, etc...

So I'm hoping someone will know how to fix this at the query level rather than having to put dodgy work-around via code.

Thanks.

UPDATE:

Here's my full query if it helps:

SELECT PURCHASE_LEDGER.ACCOUNT_REF AS SupplierCode, 
       PURCHASE_LEDGER.NAME AS SupplierName, 
       PURCHASE_LEDGER.ADDRESS_1, 
       PURCHASE_LEDGER.ADDRESS_2, 
       PURCHASE_LEDGER.ADDRESS_3, 
       PURCHASE_LEDGER.ADDRESS_4, 
       PURCHASE_LEDGER.ADDRESS_5,
       'London' AS City,
       COUNTRY_CODE.Name AS Country, 
       '' AS PostCode,
       '-' AS PostCode1,
       PURCHASE_LEDGER.VAT_REG_NUMBER AS TaxRegistrationNumber, 
       '10' AS CorporateGroupId,
       COUNTRY_CODE.Name AS Location, 
       CURRENCY.CODE AS CurrencyCode
  FROM (PURCHASE_LEDGER 
       INNER JOIN CURRENCY ON PURCHASE_LEDGER.CURRENCY = CURRENCY.NUMBER) 
       INNER JOIN COUNTRY_CODE ON PURCHASE_LEDGER.COUNTRY_CODE = COUNTRY_CODE.CODE

1. City will throw an error i.e. Column not found
2. PostCode will cause the rest of the fields not to be returned
3. PostCode1 will return 0.00 instead of '-'
4. OrganizationNumber will return 10.00 instead of 10