We help IT Professionals succeed at work.

SQL synthax problem count union like

frippel
frippel asked
on
i can't get the following to work. I gues it's because of the like % but that's what i really need.

I always get error Maximum lenght is 128...


sqlstmt = "SELECT SUM(totcon) as totcona from (Select count(*) As totcon FROM customer1 WHERE bedrijf LIKE '%" & zoekwoord & "%' UNION select count(*) As totcon FROM customer1 WHERE roepnaam LIKE '%" & zoekwoord & "%')"

Open in new window

Comment
Watch Question

Commented:
did you allow enough space on the variable "sqlstmt".
If that isn't it, then try just running the SQL statement by itself and see you get an error. The LIKE % part shouldn't be the issue.
Senior DBA
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:
For SQL Server, you *must* specify an alias name on a derived table ("from (...").  So try adding "AS derived" to the sqlstmt:

sqlstmt = "SELECT SUM(totcon) as totcona from (Select count(*) As totcon FROM customer1 WHERE bedrijf LIKE '%" & zoekwoord & "%' UNION select count(*) As totcon FROM customer1 WHERE roepnaam LIKE '%" & zoekwoord & "%') AS derived"

Author

Commented:
It works but what does the derived do in this case? Never used it like that but only in a derived table or is it the same in the end?

Author

Commented:
*DUH* got it sorry just was confused with the word Derived is here the table of the derived table it could as well be 'abc'. Thanx