Help on SQL syntax

Question

I am trying to pass the sql query to pass to dynamic SQL. I keep getting error again and again. Please anyone who have idea please help me.

thank you

select * from openquery(DWH_medw1,	'SELECT activity.the_date AS Activity_Date, LOWER(tag.name) AS tag_name, count(activity.id) AS record_count FROM agencies.activity_activity AS activity INNER JOIN agencies.activity_activity_tags AS connection    ON activity.id = connection.activity_id INNER JOIN agencies.activity_activitytag AS tag     ON connection.activitytag_id = tag.id WHERE activity.franchisor_id IN (48, 61)   AND the_date >= DATE'2023-01-01'   AND (LOWER(tag.name) LIKE '%end of life care%'    OR LOWER(tag.name) LIKE '%hospital discharge%'    OR LOWER(tag.name) LIKE '%decline in mental ability%'    OR LOWER(tag.name) LIKE '%fall prevention%'    OR LOWER(tag.name) LIKE '%non-medical companion%')  GROUP BY activity.the_date,  LOWER(tag.name)'

Answer 1

Hi @Kalbubu

There is no DATE function in T-SQL. You may use CONVERT or CAST to convert an expression of one data type to another.

Regarding the quotes, try this:

select * from openquery
(DWH_medw1,	
'SELECT activity.the_date AS Activity_Date, LOWER(tag.name) AS tag_name, 
        count(activity.id) AS record_count 
 FROM agencies.activity_activity AS activity 
 INNER JOIN agencies.activity_activity_tags AS connection    
   ON activity.id = connection.activity_id 
 INNER JOIN agencies.activity_activitytag AS tag     
   ON connection.activitytag_id = tag.id 
 WHERE activity.franchisor_id IN (48, 61)   
   AND the_date >= ''2023-01-01''   
   AND (
        LOWER(tag.name) LIKE ''%end of life care%''    
        OR LOWER(tag.name) LIKE ''%hospital discharge%''    
        OR LOWER(tag.name) LIKE ''%decline in mental ability%''    
        OR LOWER(tag.name) LIKE ''%fall prevention%''    
        OR LOWER(tag.name) LIKE ''%non-medical companion%''
	   )  
 GROUP BY activity.the_date,  LOWER(tag.name)')

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Syntax highlight already shows you the failure; you have to quote the apostrophe.

And what is DATE? The is no T-SQL with this name

Answer 3

I am trying to pull data from linked amazon athena. could you please send me the correct queted one Please. tried to do that also.

Answer 4

The problem is that your query string from Athena includes single quotes. But as Olaf's screenshot shows, the first single quote in the query string, terminates the string literal you are passing to OPENQUERY, and that results in a syntax error.

No matter which language you work in, you always need to escape nested quotes in some way. In some languages do do this with the backslash, but in SQL you simply double it, as in this example:

SELECT 'Brian O''Brien'