0

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 :-(

Samuel Lelièvre
  • 3,212
  • 1
  • 14
  • 27
Steve
  • 1
  • 5
    Double quotes are used for identifiers, for literal strings use single quotes: `(INVOICE.ACCOUNT_REF Like 'CHA002'` – HoneyBadger Aug 22 '19 at 09:16
  • Step 1: Remove the WHERE clause. Still getting an error? Step 2: Remove LEFT() from SELECT list. Still getting an error? etc. – jarlh Aug 22 '19 at 09:27
  • Thanks Jarlh. Removing Where clause = still error. Removing the Left () from select list means the query runs, but doesn't give me the first digit (which is what I need) – Steve Aug 22 '19 at 09:44
  • What happens if you use a literal as argument instead, like `LEFT('ABC',1) as c2`? – jarlh Aug 22 '19 at 09:51
  • Still no joy. It's almost like the function "Left" isnt supported. – Steve Aug 22 '19 at 09:55

3 Answers3

1

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%"
Neeraj Agarwal
  • 1,059
  • 6
  • 5
  • 1
    Could you add some explanation to this answer, please? Does this not just remove the (trivial) alias for the table? – Jon Aug 22 '19 at 12:22
  • 1
    @Jon No it does not. Notice the % character required for the like string for the query to work. – Neeraj Agarwal Aug 22 '19 at 14:52
0

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")
DarkRob
  • 3,843
  • 1
  • 10
  • 27
  • thanks for the response DarkRob. Having tried this, I still get the same error. – Steve Aug 22 '19 at 09:25
  • kindly share your sample code, where you are using this query. Although this error is coming because no column name was specified. – DarkRob Aug 22 '19 at 09:26
  • This is the code that works for SAP tablesSELECT OCRD.CardCode, left(OCRD.CardCode,1) FROM SBO_UK_Live.dbo.OCRD OCRD – Steve Aug 22 '19 at 09:49
  • whenever we are using some scalar function then it return a value, and in any query language a column is going to be access by column name only. Since we are not giving any column name to function `left` so this is giving error. You may give some alias name and retry it. BTW updated my ans check now. – DarkRob Aug 22 '19 at 09:56
  • thanks again DarkRob. I've tried it with alias and it appears to make no difference. Also, the code works for SAP tables, just not for SAGE. I've read elsewhere that not all functions are supported by all databases (hence trying the substring method). – Steve Aug 22 '19 at 10:03
  • @Steve: updated my ans as per syntax found for SAGE. Please check may be that works. – DarkRob Aug 22 '19 at 10:17
0

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'
Arulkumar
  • 12,966
  • 14
  • 47
  • 68