Link to home
Create AccountLog in
Avatar of mroberson
mroberson

asked on

SQL Syntax in SQL Expression Field

Can I get the syntax for SQL in SQL Expression field? I have been trying with a very basic SQl statement & I keep getting an error message of error near Select
SELECT nametable.namefirst
FROM nametable
WHERE nametable.nametype = a
I have try different syntaxs of the above with no luck. Such as  I have added single quotes or double quotes around the table name or just around the field name or both or both but separate so what is the correct syntax for the SQL Expression Field?
 
Avatar of ee_rlee
ee_rlee
Flag of Philippines image

hi

try this

SELECT nametable.namefirst
FROM nametable
WHERE nametable.nametype = 'a'
Avatar of Mike McCracken
Mike McCracken

FOr a Crystal SQL Expression to work it can only return 1 record.  Is there any chance this may return more than 1 record?

mlmcc
Avatar of mroberson

ASKER

Yes, the query would return multiply rows a little over 200K therefore the query to work in crystal it would have to be

SELECT namefirst, namelast
FROM nametable
WHERE nametable.nametype = 'a' and
    nametable.nameid = '123456'

which only returns one record.
Wonder if a SQL query would work in a group footer group  when I only want the max / min of the group
That returns 2 values.  It can only return a single field, not multiple fields even if only 1 record.

It probably would, though it might be better to use a subreport to get it.

mlmcc
This question is not abandoned I am terribly busy with my job & just do not have the time to deal with the problems I currently have open in expert exchange Please do not delete the question.
If there are no comments for 21 days, the question is considered abandoned.  If you wish to keep the questiion alive then make a comment every 10-15 days.

mlmcc
Below is the the query as written in the Formula Workshop - SQL Expression Editor
SELECT "nametable"."namefirst"
FROM "nametable"
WHERE "name_full"."nametype" = 'a'

When checking for errors I get the following error message & googling the error message isn't helping

Error in compiling SQL Expression:
Query Engine Error: '42000:[Microsoft][ODBC SQL Server Driver][SQL Server] Incorrect syntax near the keyword 'SELECT'.'.

The SQL statement should only return one last name for each ID number.
Could this be permissions issue?
I use the same syntax in a command object I do not get any errors
WHy the change to NAME_FULL on the where clause?

Is NameTable, the name of the table in the report?

mlmcc
Name_Full should have been nametable

SELECT "nametable"."namefirst"
FROM "nametable"
WHERE "nametble"."nametype" = 'a'
You might need " " around the a rather than ' '

mlmcc
whether it is a single quote or a double quote or no quotes does not matter I still get the same error message.
I can write a complex SQL query in SQL Server Enterprize Manger but cannot get a simple query to work in Crystal
ASKER CERTIFIED SOLUTION
Avatar of James0628
James0628

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
thanks
You're welcome.  Glad I could help.

 James