Using Advantage SQL, I have the following query:
SELECT TOP 10 mytable.*
FROM "mytable.ADT" mytable
ORDER BY date DESC
This returns this data set:
INDEX NR NAME Date
---------------------------------------------------
"145443" 115 Bob 19.03.2021 12:26
"23545",1 215 Steve 19.03.2021 12:09
"564543","",0 215 John 19.03.2021 12:09
"456234" 215 Mark 19.03.2021 12:09
What I want to do is work with the data in the INDEX column. But if I run a normal SELECT query with this field name:
SELECT mytable.INDEX etc
it doesn't run. I also cannot add it as an alias or anything.
Are there any workarounds to pull the specific column into a SELECT? My goal is to do text manipulation to remove the quote marks and extract only the pure number that sits in the middle.
This is the first time I've had this issue - I'm guessing the word INDEX is somehow a function name which screws things up. But I also am guessing there is a way around it?
Thanks.
INDEX is a SQL keyword -- and probably reserved, which is the problem.
You need to escape it. I believe Advantage supports both double quotes and square braces:
SELECT mytable."INDEX", mytable.[INDEX]
FROM "mytable.ADT" mytable
order by date desc
Note that capitalization is important in some databases (but not Advantage SQL) when you escape column names.