May 18, 2015 at 9:52 am
Hi friends,
We are getting an error for the below query via application program, it works from the database. Wondering if it is possible to tweak the SQL to make it work from the application since all the other SQL's in the program work just fine in the application..
SELECT col1 FROM table1 WHERE col2 IN (upper('UMP')) AND col2 = 'TE1' ORDER BY (case when upper(col3) like upper('%htest%') then 0 else 1 end)
Is there an alternative for CASE statement in SQL Server? May be I can try that option... Please help.... THanks a lot
May 18, 2015 at 10:05 am
You can use a subquery or CTE.
SELECT col1
FROM (
SELECT col1,
case when upper(col3) like '%HTEST%' then 0 else 1 end AS ordercol
FROM table1 WHERE col2 IN ('UMP') AND col2 = 'TE1')x
ORDER BY ordercol
May 18, 2015 at 10:22 am
newbieuser (5/18/2015)
Hi friends,We are getting an error for the below query via application program, it works from the database. Wondering if it is possible to tweak the SQL to make it work from the application since all the other SQL's in the program work just fine in the application..
SELECT col1 FROM table1 WHERE col2 IN (upper('UMP')) AND col2 = 'TE1' ORDER BY (case when upper(col3) like upper('%htest%') then 0 else 1 end)
Is there an alternative for CASE statement in SQL Server? May be I can try that option... Please help.... THanks a lot
What is the error? What you posted looks like it should work just fine.
_______________________________________________________________
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/
May 18, 2015 at 10:32 am
Thanks Luis.. We will try it out..
Sean, the query works from SQL Server but not from the application. So we are trying to try alternate SQLs..
Thanks
May 18, 2015 at 10:45 am
newbieuser (5/18/2015)
Thanks Luis.. We will try it out..Sean, the query works from SQL Server but not from the application. So we are trying to try alternate SQLs..
Thanks
Yes you said that. You also said that you are getting an error message. Sharing that error message would let us know what is happening. Otherwise we are left to guess what the problem is.
_______________________________________________________________
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/
May 18, 2015 at 10:49 am
Hi Sean
It is an error from our application.. not sure if will help
“An attempt was made to call a stored procedure that expects a boolean value.”
Thanks
May 18, 2015 at 10:55 am
newbieuser (5/18/2015)
Hi SeanIt is an error from our application.. not sure if will help
“An attempt was made to call a stored procedure that expects a boolean value.”
Thanks
Well that certainly has no relevance if the message is accurate. Why do you suspect it is the case expression in the order by? From the little bit of information posted I don't see the correlation.
_______________________________________________________________
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/
May 18, 2015 at 11:01 am
Sean Lange (5/18/2015)
newbieuser (5/18/2015)
Hi SeanIt is an error from our application.. not sure if will help
“An attempt was made to call a stored procedure that expects a boolean value.”
Thanks
Well that certainly has no relevance if the message is accurate. Why do you suspect it is the case expression in the order by? From the little bit of information posted I don't see the correlation.
I would look at the stored procedure to see what it is expecting to receive versus what the application is sending.
May 18, 2015 at 12:42 pm
newbieuser (5/18/2015)
Hi friends,We are getting an error for the below query via application program, it works from the database. Wondering if it is possible to tweak the SQL to make it work from the application since all the other SQL's in the program work just fine in the application..
SELECT col1 FROM table1 WHERE col2 IN (upper('UMP')) AND col2 = 'TE1' ORDER BY (case when upper(col3) like upper('%htest%') then 0 else 1 end)
Is there an alternative for CASE statement in SQL Server? May be I can try that option... Please help.... THanks a lot
Quick thought, the information you've posted are insufficient for the purpose of any kind of analysis except on your frustration:-D Can you elaborate further on this as all of us want to answer which ever question we can but if you don't ask the complete question then the changes are you're not going to get an answer.
😎
May 19, 2015 at 2:33 pm
Ok..Here is the background.. This SQL runs from our application connected to SQL Server via ODBC. TABLE1 in the SQL is an oracle table, queried via linked server. We are facing this error after the Oracle database(TABLE1 resides here) was migrated to unicode.. Although the SQL works from management studio, it is failing while running the program from the application. Our application connects to SQL Server via ODBC, so not sure if it makes any difference.. Please give me your thoughts.. THank you all so much
May 19, 2015 at 2:37 pm
newbieuser (5/19/2015)
Ok..Here is the background.. This SQL runs from our application connected to SQL Server via ODBC. TABLE1 in the SQL is an oracle table, queried via linked server. We are facing this error after the Oracle database(TABLE1 resides here) was migrated to unicode.. Although the SQL works from management studio, it is failing while running the program from the application. Our application connects to SQL Server via ODBC, so not sure if it makes any difference.. Please give me your thoughts.. THank you all so much
Luis gave you an alternative yesterday. Did that work? If not, can you get the real error message instead of something from your application that has no relevance on what is going on? We would like to help but we can't see your screen and as such can't really do much to help.
_______________________________________________________________
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/
May 19, 2015 at 7:05 pm
We tried that SQL.. same error from the application.. We are planning on running a trace on ODBC tomorrow I will post that hopefully that will give us some clue..Thanks a lot
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply