Error with WHERE in OPEN QUERY Statement

Question

Error with WHERE in OPEN QUERY Statement

Viewing 15 posts - 16 through 30 (of 32 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

Pam Brisjar (3/12/2012)
An implicit conversion of '112' to any numeric datatype would not be a problem.
Not necessarily. I've seen DB2 do some strange things with data type mismatches.

DOH!!! After nearly a dozen posts I forgot this all started with OPENQUERY. :blush:

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Pam Brisjar SSChampion Points: 12094 More actions · Answer 2

No problemo, Sean. But I do agree with you that we need to see the exact query that's producung the error. After all, I could be wrong 😎

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 3

Pam Brisjar (3/12/2012)
No problemo, Sean. But I do agree with you that we need to see the exact query that's producung the error. After all, I could be wrong 😎

I listed the Query on my first post and listed the the error later when requested.

Sorry that I did not list them together. I'm rushing to get a lot of different things done and I'm working oun getting the data from another Data SOurce.

This does not work and I can't remember the systax to indude the WHERE CLause.

SELECT *

FROM OPENQUERY(LSCTLRDAT1, 'SELECT * FROM Company WHERE EFF0YR = '112' AND EFF0MO = '09' AND EFF0DA BETWEEN '01' AND '09'')

Msg 102, Level 15, State 1, Line 2

Incorrect syntax near '112'.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Jared SSC Guru Points: 61793 More actions · Answer 4

Welsh Corgi (3/12/2012)
Pam Brisjar (3/12/2012)
No problemo, Sean. But I do agree with you that we need to see the exact query that's producung the error. After all, I could be wrong 😎
I listed the Query on my first post and listed the the error later when requested.
Sorry that I did not list them together. I'm rushing to get a lot of different things done and I'm working oun getting the data from another Data SOurce.
This does not work and I can't remember the systax to indude the WHERE CLause.
SELECT *
FROM OPENQUERY(LSCTLRDAT1, 'SELECT * FROM Company WHERE EFF0YR = '112' AND EFF0MO = '09' AND EFF0DA BETWEEN '01' AND '09'')
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '112'.

The syntax is still wrong. You cannot call openquery and pass single quotes in the SQL. You have to use 2 single quotes. Period. If the results are not what you expect, then take off the quotes completely or check the data.

Jared
CE - Microsoft

Pam Brisjar SSChampion Points: 12094 More actions · Answer 5

To consolidate and repeat what has been said above, if you are using OPENQUERY, you must use:

SELECT *

FROM OPENQUERY(LSCTLRDAT1, 'SELECT * FROM Company WHERE EFF0YR = ''112'' AND EFF0MO = ''09'' AND EFF0DA BETWEEN ''01'' AND ''09''')

If you have a string inside your query, you have to enclose it within TWO single quotes. Anything else just won't parse at all.

If you are running in DB2, it should be:

SELECT * FROM Company WHERE EFF0YR = '112' AND EFF0MO = '09' AND EFF0DA BETWEEN '01' AND '9'

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 6

SQLKnowItAll (3/12/2012)
What happens when you run this query on the server directly?
SELECT * FROM Company WHERE EFF0YR = '112' AND EFF0MO = '09' AND EFF0DA BETWEEN '01' AND '09'

Thank you. I'm sorry but I was rushing in different directions.

If I wanted to JOIN with a SQL Server Table, I would put the from outside of the Open Query Statement.

Thanks again.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 7

SQLKnowItAll (3/12/2012)
Welsh Corgi (3/12/2012)
Pam Brisjar (3/12/2012)
No problemo, Sean. But I do agree with you that we need to see the exact query that's producung the error. After all, I could be wrong 😎
I listed the Query on my first post and listed the the error later when requested.
Sorry that I did not list them together. I'm rushing to get a lot of different things done and I'm working oun getting the data from another Data SOurce.
This does not work and I can't remember the systax to indude the WHERE CLause.
SELECT *
FROM OPENQUERY(LSCTLRDAT1, 'SELECT * FROM Company WHERE EFF0YR = '112' AND EFF0MO = '09' AND EFF0DA BETWEEN '01' AND '09'')
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '112'.
The syntax is still wrong. You cannot call openquery and pass single quotes in the SQL. You have to use 2 single quotes. Period. If the results are not what you expect, then take off the quotes completely or check the data.

I'm really sorry.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 8

Pam Brisjar (3/12/2012)
To consolidate and repeat what has been said above, if you are using OPENQUERY, you must use:
SELECT *
FROM OPENQUERY(LSCTLRDAT1, 'SELECT * FROM Company WHERE EFF0YR = ''112'' AND EFF0MO = ''09'' AND EFF0DA BETWEEN ''01'' AND ''09''')
If you have a string inside your query, you have to enclose it within TWO single quotes. Anything else just won't parse at all.
If you are running in DB2, it should be:
SELECT * FROM Company WHERE EFF0YR = '112' AND EFF0MO = '09' AND EFF0DA BETWEEN '01' AND '9'

I never had a problem running from DB2 and I made a bonehead mistake on the code that was provided to me. :blush:

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Jared SSC Guru Points: 61793 More actions · Answer 9

Welsh Corgi (3/12/2012)
SQLKnowItAll (3/12/2012)
Welsh Corgi (3/12/2012)
Pam Brisjar (3/12/2012)
No problemo, Sean. But I do agree with you that we need to see the exact query that's producung the error. After all, I could be wrong 😎
I listed the Query on my first post and listed the the error later when requested.
Sorry that I did not list them together. I'm rushing to get a lot of different things done and I'm working oun getting the data from another Data SOurce.
This does not work and I can't remember the systax to indude the WHERE CLause.
SELECT *
FROM OPENQUERY(LSCTLRDAT1, 'SELECT * FROM Company WHERE EFF0YR = '112' AND EFF0MO = '09' AND EFF0DA BETWEEN '01' AND '09'')
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '112'.
The syntax is still wrong. You cannot call openquery and pass single quotes in the SQL. You have to use 2 single quotes. Period. If the results are not what you expect, then take off the quotes completely or check the data.
I'm really sorry.

Nothing to be sorry about 🙂 The lack of results is one issue, the syntax is another. I gather that once 1 is fixed, the other appears. However, I bet fixing the syntax is something that cannot change. If you are getting 0 results, but no error after that change. I would try removing single quotes from the ''112'' first and seeing what happens.

Jared
CE - Microsoft

Pam Brisjar SSChampion Points: 12094 More actions · Answer 10

See my response to your other post (the one in the Data Warehouse group). I posted an example of doing a join between openquery and a SQL Server table.

With the query you have her, keep the WHERE criteria inside the OPENQUERY statement. The more you can filter there the better.

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 11

SQLKnowItAll (3/12/2012)
Welsh Corgi (3/12/2012)
SQLKnowItAll (3/12/2012)
Welsh Corgi (3/12/2012)
Pam Brisjar (3/12/2012)
No problemo, Sean. But I do agree with you that we need to see the exact query that's producung the error. After all, I could be wrong 😎
I listed the Query on my first post and listed the the error later when requested.
Sorry that I did not list them together. I'm rushing to get a lot of different things done and I'm working on getting the data from another Data SOurce.
This does not work and I can't remember the syntax to include the WHERE CLause.
SELECT *
FROM OPENQUERY(LSCTLRDAT1, 'SELECT * FROM Company WHERE EFF0YR = '112' AND EFF0MO = '09' AND EFF0DA BETWEEN '01' AND '09'')
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '112'.
The syntax is still wrong. You cannot call openquery and pass single quotes in the SQL. You have to use 2 single quotes. Period. If the results are not what you expect, then take off the quotes completely or check the data.
I'm really sorry.
Nothing to be sorry about 🙂 The lack of results is one issue, the syntax is another. I gather that once 1 is fixed, the other appears. However, I bet fixing the syntax is something that cannot change. If you are getting 0 results, but no error after that change. I would try removing single quotes from the ''112'' first and seeing what happens.

I must have gone brain dead. I had the month wrong, don't ask me what I was thinking.

I'm Grateful for the code.

I've been here 10 hours so far and we just moved the clocks an hour forward yesterday.

No excuse. It has to be irritating when someone gives you someone and it is correct.

I had to get some financial data that has only every been generated monthly. They came to me this afternoon and said they wanted it to today.

My boss was not too happy about it and she asked me if I would get it done.

Thanks again.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 12

Pam Brisjar (3/12/2012)
See my response to your other post (the one in the Data Warehouse group). I posted an example of doing a join between openquery and a SQL Server table.
With the query you have her, keep the WHERE criteria inside the OPENQUERY statement. The more you can filter there the better.

I can't find it, would you mind posting again?

Thanks.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Pam Brisjar SSChampion Points: 12094 More actions · Answer 13

Data Warehousing | Integration Services

http://www.sqlservercentral.com/Forums/Topic1265061-364-1.aspx

basic pseudocode:

INSERT INTO [table1]

(

col1

, col2

, col3

SELECT oq.c1

, oq.c2

, oq.c3

FROM OPENQUERY(DW_DB,

'

SELECTDISTINCT

c1

,c2

,c3

FROM dw.sourcetable

') oq

LEFT OUTER JOIN SSDB.dbo.table1 AS t1

ON oq.c1 = t1.col1

WHERE t1.col1 IS NULL

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 14

Thanks for all of the help.

I appreciate it very much.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

manoj 8279 SSC Journeyman Points: 96 More actions · Answer 15

SELECT * FROM Company WHERE EFF0YR = '112' AND EFF0MO = '09' AND EFF0DA BETWEEN '01' AND '09'

use str command to give the string values in sql query instead of single quote

select * from openquery(<linked server name>,'<Sql query>')