SQL Editor Question/ Hotkeys

[razzy]

Hi,

I have connected to a database successfully, and i am now trying to input form the connected database using the input tool and the "choose table or specify query " dialogue box with the option of using the SQL editor. I am specifying the query correctly using the SQL syntax:

SELECT * FROM LIST where USER_NAME="JOHN DOE"; 

which i know is correct but i guess the editor is not accepting the semicolon instead outputs a colon from my keyboard and so is giving me an error that the command is not ended properly, can someone help me with why this is happening and how do i resolve it?

[myastarling · Accepted solution]

I can't tell you why that's the case in Alteryx, but the SQL editor does not accept semicolons. Just remove the semicolon and your code should parse just fine.

[razzy]

Excellent response but i am still having an error of invalid identifier on the message pane even though the query ran, and also how do i save the oracle client downloaded on my  c: drive , because its like every time i start the connection i have to delete it and then install again.  I have still not got any outputs on the query 

[myastarling]

can you share what you are seeing? Deleting the semicolon has always worked for what I am doing, but it sounds like you are doing something different

[razzy]

this is the error message it is giving me:

Error: Input Data (1): DataWrapOCI: Unable to prepare the query: "SELECT count(*) FROM PAT_LIST where LIST_ID= " 330686"" Error: ORA-00904: " 330686": invalid identifier

[myastarling]

I looked up the error message and it is indicating there is most likely a problem with the specified column name. I can't troubleshoot beyond that because I don't have the data you are working from (LIST_ID seems legit according to naming rules), but that is where I would start. Here's a link to what TOTN says -- I hope links like this are allowed in the forum.

https://www.techonthenet.com/oracle/errors/ora00904.php

[Nate1]

In Oracle you should use singe quotes (') for strings, where LIST_ID= '330686' should work. Double quotes are used to identify objects, tables, columns, etc. Double quotes are almost never used in Oracle unless you need to identify a column/table that does not conform to naming conventions.