SYSIDX system view has a property called unique and in docs its quoted with double quotes unlike other properties. When I select unquoted properties everything is ok, no errors. But when I try to select the quoted one I get the following error. Therefore, I have a question: how to select this quoted properties?

Syntax error near 'property_name_here' on line 1

SYSIDX docs page

UPDATE

Query that causes error:

$query = sasql_query($conn,
    "SELECT st.table_name, stc.column_name, si.index_category, si.unique
    FROM sysidxcol sic
    INNER JOIN systab st ON st.table_id = sic.table_id
    INNER JOIN systabcol stc ON stc.column_id = sic.column_id
    INNER JOIN sysidx si ON si.index_id = sic.index_id
    WHERE st.table_id = stc.table_id
        AND si.table_id = sic.table_id
        AND st.table_id BETWEEN 691 AND 747"
);

Also tried this:

$query = sasql_query($conn,
    "SELECT st.table_name, stc.column_name, si.index_category, si.['unique']
    FROM sysidxcol sic
    INNER JOIN systab st ON st.table_id = sic.table_id
    INNER JOIN systabcol stc ON stc.column_id = sic.column_id
    INNER JOIN sysidx si ON si.index_id = sic.index_id
    WHERE st.table_id = stc.table_id
        AND si.table_id = sic.table_id
        AND st.table_id BETWEEN 691 AND 747"
);

But it gives the next error

sasql_query(): SQLAnywhere: [-143] Column ''unique'' not found

asked 04 Jul, 11:05

ilyas_khametov's gravatar image

ilyas_khametov
31116
accept rate: 100%

edited 04 Jul, 11:26

1

Please show us the exact queries that you are using.

Note that the SYSIDX table has a column called unique but the word 'unique' is a keyword so it must be quoted (using double quotes) when used as an identifier. The table in the SYSIDX docs page is simply adding the double quotes around unique to indicate this fact.

(04 Jul, 11:18) Mark Culp
Replies hidden

Updated my question with queries from the code

(04 Jul, 11:26) ilyas_khametov

Found a solution myself. Thanks for Mark Culp for speicfying direction to search my problem.

In order to select columns, which names are similar to a reserved SQL word, put square brackets around column name:

SELECT [column_name] FROM table_name
permanent link

answered 04 Jul, 11:37

ilyas_khametov's gravatar image

ilyas_khametov
31116
accept rate: 100%

1

Glad you found your solution. Note that you tried to use single quotes (') around the word and this resulted in your error. In SQL single quotes are used to construct a string. To construct an identified you need to use double quotes (") or, as you have discovered, use brackets []. You can read more about SQL identifiers here: http://dcx.sap.com/index.html#sqla170/en/html/8170eb5b6ce21014a7e1a2fd6b4a85fc.html and read about SQL strings here: http://dcx.sap.com/index.html#sqla170/en/html/817b3d006ce21014ad07f050c018135e.html

(04 Jul, 11:51) Mark Culp
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×105
×27

question asked: 04 Jul, 11:05

question was seen: 86 times

last updated: 04 Jul, 11:51