cancel
Showing results for 
Search instead for 
Did you mean: 

Query Error

Former Member
0 Kudos

I used to have the following query in version 2004a and it work fine (and it still does on that version) , know we did the RamUp to the version 2005A and it gives me this error : Incorrect Syntax Error on 'Value of the parameter that I typed'

The query is the following:

SELECT T0.ItemCode, T1.Substitute, T0.ItemName,T2.CardName, T0.OnHand

FROM OITM T0 LEFT JOIN OSCN T1 ON T0.ItemCode = T1.ItemCode LEFT JOIN OCRD T2 ON T1.CardCode = T2.CardCode WHERE T1.Substitute like '[%0]%%' OR T0.ItemCode like '[%0]%%'

Any Ideas?? Did something change on Version 2005??

Accepted Solutions (1)

Accepted Solutions (1)

barend_morkel2
Active Contributor
0 Kudos

Try this:

SELECT T0.ItemCode, T1.Substitute, T0.ItemName,T2.CardName, T0.OnHand

FROM OITM T0 LEFT JOIN OSCN T1 ON T0.ItemCode = T1.ItemCode LEFT JOIN OCRD T2 ON T1.CardCode = T2.CardCode WHERE T1.Substitute like '<b>%</b>[%0]<b>%</b>' OR T0.ItemCode like '<b>%</b>[%0]<b>%</b>'

-> In "Normal" SQL the like operator string must have the "%" before and after the actual string

ex

WHERE T1.Substitute like '%ActualString%'

Answers (5)

Answers (5)

Former Member
0 Kudos

Thank You Everyone for all your answers, but It looks that the real problem is the PL 2.0 of 2005 A Version, because I'm running the query in 2005 A PL 1.0 and others several Versions of 2004 and it run great.

I guess that now I'll have to make a "Downgrade" , until SAP release a new PL.

I'll let you know if it solve the problem

Once Again Thank you and let me know if you didn' get the rewards points

Cesar Aviles

Former Member
0 Kudos

DECLARE @ITEMCODE NVARCHAR(20)

SET @ITEMCODE = (SELECT DISTINCT LEFT(T2.ITEMCODE,LEN('[%2]')) FROM [DBO].[OITM] T2 WHERE T2.ITEMCODE LIKE '[%2]%')

SELECT BLABLABLA

WHERE ITEMCODE LIKE @ITEMCODE + '%'

->USING DBO.TABLE IN YOUR QUERY AS SHOWN ABOVE MAY MAKE IT WORK

Message was edited by: Chaonas

Former Member
0 Kudos

Thank you Alexey but is not working , it keep displaying: "Incorrect Syntax near 'string value that I typed '.

the Catalog as well as the Item Number are created, becuase if I use the equal clause it will work

former_member201110
Active Contributor
0 Kudos

Hi Cesar,

Why are you using %% as a wildcard? The % wildcard in SQL matches any string of zero or more characters so 'AB%' equals 'begins with AB'. Don't know if that's your problem though...

SELECT T0.ItemCode, T1.Substitute, T0.ItemName,T2.CardName, T0.OnHand

FROM OITM T0 LEFT JOIN OSCN T1 ON T0.ItemCode = T1.ItemCode LEFT JOIN OCRD T2 ON T1.CardCode = T2.CardCode WHERE T1.Substitute like '[%0]%' OR T0.ItemCode like '[%1]%'

This works for me everytime in 2004A (...sorry, I don't have 2005 to test on but I have heard some people mention that queries that worked in 2004 are no longer working in 2005 )

If you've got access to SQL Profiler then you could start a trace and see what statement is actually being submitted to the SQL Server (if any).

Kind Regards,

Owen

Former Member
0 Kudos

The funny thing is that I run the Query using an static value instead of a parameter with the like cluase and it run well, so I thought that was a problem with the [%0] sign , but if I use the Equal clause with the [%0] run fine too.

On version 2004 run good with the Like clause and the [%0]%% so If theres any change on the Query Generator on Version 2005 PL2 ?

Can Someone run this query on 2005 V to see if its my Version that need a fix ?

SELECT T0.ItemCode, T1.Substitute, T0.ItemName,T2.CardName, T0.OnHand

FROM OITM T0 LEFT JOIN OSCN T1 ON T0.ItemCode = T1.ItemCode LEFT JOIN OCRD T2 ON T1.CardCode = T2.CardCode WHERE T1.Substitute like '[%0]%%' OR T0.ItemCode like '[%0]%%'

Thank you very much for all your aswers

AlexGrebennikov
Active Contributor
0 Kudos

Hi Cesar! I don't have 2005 version but just try the following trick:

...
WHERE T0.ItemCode like (N'[%0]' + '%%') OR T1.Substitute like (N'[%0]' + '%%')

Former Member
0 Kudos

Thank you Barend for your answer , but I did run the query on the SQL Query Analyzer with the % sign at the end and it works fine, also I used to have the query with the % at the end on the 2004 Version A and works good. For the SQL if you type the %Actual String% it means 'Contains' but if you type ActualString%% it means 'Start With'.

It looks that the problem is the LIKE clause with a parameter, because if I use the equal (T1.Substitute = [%0] ) works fine too.

I'm gonna try giving the Query an static instead of a parameter on SBO to see if it run

Any susgestion I 'll appreciate it!!

barend_morkel2
Active Contributor
0 Kudos

Another thing,

Not all SQL query "functions" are available when you run the SQL script through SBO. This is VERY unfortunate. It might be that the SBO SQL Parser does not recognize the "LIKE" operator...

Former Member
0 Kudos

The query works 100% in my SBO 2004A PL 18