"Column not found" error when using "left" "mid" or "Substr" in MS Query

Question

and thanks in advance for any help. I'm using Microsoft Query to interrogate Sage 50 tables. I'm trying to create a user defined field containing only the first digit of a customer account reference. I have been able to achieve this result when interrogating SAP tables, by using the Left or Mid functions. When I try exactly the same method with Sage, I get the error "column not found"

The SQL is as follows:

SELECT INVOICE.ACCOUNT_REF, LEFT(INVOICE.ACCOUNT_REF,1)
FROM INVOICE INVOICE
WHERE (INVOICE.ACCOUNT_REF Like "CHA002")

I'd expect the result of this to be "C" but I get

Column Not Found

Please help! I also will need to do similar to isolate the year from a date field - currently I get the same error here as well :-(

Answer 1

You may try this. Try to give some column name alias to your result column.
Also need to change " to '.

SELECT INVOICE.ACCOUNT_REF, LEFT(INVOICE.ACCOUNT_REF,1) as "COLNAME"
FROM INVOICE INVOICE
WHERE (INVOICE.ACCOUNT_REF Like "CHA002")

As You commented about SAGE

In SAGE you need to use mid function mid$(EXP_STRING,EXP_POS,EXP_NB) or may be Instr with same syntax.
For more info on SAGE you may find this LINK.

SELECT INVOICE.ACCOUNT_REF,  mid$(INVOICE.ACCOUNT_REF,1,1)
FROM INVOICE INVOICE
WHERE (INVOICE.ACCOUNT_REF Like "CHA002")

Answer 2

I have a concern about your table alias name INVOICE INVOICE.

Can you please change the table alias name different than the actual table name, something like IV.

So the query below may work:

SELECT IV.ACCOUNT_REF, LEFT(IV.ACCOUNT_REF, 1) AS FirstCharacter
FROM INVOICE IV
WHERE IV.ACCOUNT_REF LIKE 'CHA002'

Answer 3

Please note that you need the % character for the like operator:

SELECT ACCOUNT_REF, LEFT(ACCOUNT_REF,1)
FROM INVOICE
WHERE ACCOUNT_REF Like "CHA002%"