SQL expression field syntax

[seabubble]

Hi, This is the first time I have used the sql expression field and I am having trouble with the following simple select statement.

select sum("AST_Asset"."Asset_ID_") from table "AST_Asset" where AST_Asset."AST_Asset"."Type" = "ROUTER"

It keeps saying that there is an incorrect syntax near the keyword select. I can't see it ! Can enyone see it.

I bet this is easy once you know. Sorry if I am being dim.

 

[Katy44]

I'm probably not the best person to answer this, but I think a had a similar problem and it was something stupid like putting brackets around the entire SQL expression!

 

[seabubble]

I put ) at the end and now it says incorrect syntax near the work table! it's mad!

 

[bdreed35]

Try putting an open paren "(" before the SELECT keyword, and a closing paren ")" at the end.

~Brian

 

[seabubble]

bdreed35

I have done that

(select sum("AST_Asset"."Asset_ID_") from table "AST_Asset" where AST_Asset."AST_Asset"."Type" = "ROUTER")

still says syntax problem near table.

By the way This is in the expression field under the formulas and not in the main sql statement.

 

[lbass]

SQL expressions are finicky, varying based on database/connectivity, I think. When you add a field in the SQL Expressions editor, does it appear with "" around both table and field names? Use whatever format appears when you double click on a field name. I would set it up like the following, using "AKA" as an alias table name.

(select sum(AKA.`Asset_ID`) from AST_Asset AKA where AKA.`Type` = AST_Asset.`Type` and AST_Asset.`Type` = `ROUTER`)

Try copying this into your SQL Expression editor and see if it works, although you might have to change the quotes as noted earlier. Also, I'm not sure double quotes work with SQL expressions, although this too might vary.

Finally, are you sure you mean to add Asset IDs?

-LB

 

[bdreed35]

Try removing some of the double quotes since they are not consistently used:

(select sum(AST_Asset.Asset_ID_) from table AST_Asset where AST_Asset.Type = "ROUTER")

is this the actual name of the one field: "Asset_ID_"?

~Brian

 

[bdreed35]

Also, what database are you using? I can't remember ever seeing the keyword "table" in a SELECT statement.

(select sum(AST_Asset.Asset_ID_) from AST_Asset where AST_Asset.Type = "ROUTER")

~Brian

 

[seabubble]

Actually I want to do a distinct count of the asset ID's but thought I would start simple by just getting anything back :0)

done the following

(select sum(AST_Asset."Asset_ID_") from table 'AST_Asset' where AST_Asset.'AST_Asset'."Type" = "ROUTER")

syntax near table again.
and this too.
(select sum(AST_Asset.Asset_ID_) from table AST_Asset where AST_Asset.AST_Asset.Type = "ROUTER")
syntax near table again.

Still not working. Mabey sql dosn't work on fridays!

 

[seabubble]

OK I'm getting somewhere ! I have taken out the table bit,

(select sum('AST_Asset'."Asset_ID_") from AST_Asset where 'AST_Asset'."Type" = "ROUTER")

this gives me incorrect syntax at '.'.'. well usefull. I will keep moveing the " and '

Thanks for helping me by the way.

 

[seabubble]

nearly !
(select sum("AST_Asset"."Asset_ID_") from AST_Asset where "AST_Asset"."Type" = "ROUTER")

gives me invalid column name ROUTER.

 

[bdreed35]

Try removing the extra quotes and double quotes:

(select sum(AST_Asset.Asset_ID_) from AST_Asset where AST_Asset.Type = "ROUTER")

~Brian

 

[bdreed35]

Try ROUTER in single quotes:

(select sum("AST_Asset"."Asset_ID_") from AST_Asset where "AST_Asset"."Type" = 'ROUTER')

~Brian

 

[seabubble]

DONE IT !

(select distinct count("AST_Asset"."Asset_ID_") from AST_Asset where "AST_Asset"."Type" = 'ROUTER')

Thanks for help LB and Brian. Have a nice weekend.