Hi,


We want 2 create a report by searching for a string in a textfield without a fixed place.

In that string there is a number that we want have filtered out.

After some trial en error we finally got an sql-statement that works:

DECLARE @s1 varchar(8000)

DECLARE @s2 varchar(8000)

SET @s1='V="VAS,' SET @s2=',('

SELECT *,

substring([values],(NULLIF(CHARINDEX(@s1,[values]),0) +LEN(@s1)),(NULLIF(CHARINDEX(@s2,[values],(NULLIF(CHARINDEX(@s1,[values]),0) +LEN(@s1))),0) - (NULLIF(CHARINDEX(@s1,[values]),0)+LEN(@s1))))

FROM Metingen_metingen

where Metingen_metingen.[values] like '%V="VAS,%'


We get the result we want if we use query analyser.

But if I insert this sql-statement in datamigrator. I get the following error:
"(FOC14069) SYNTAX ERROR ON LINE 1 AT 'varchar' -- Expected 'FOR'"

What am I doing wrong here?

Thanx in advance,

Nordin

Hi,

may be you can try and check with varchar2 as data type

hope it helps...

Hi Shruthi,


I tried the varchar2 as data type, but Datamigrator didn't even recognize the Syntax.
I got the same error as I described above.

Thanx anyway

Nordin

The SQL object is designed to process a SELECT statement, so the DECLARE and SET statements are problematic.

Try creating an MS SQL Server stored procedure and then create a synonym for the stored procedure.

hi,

instead of using the declare statements, we can use the global variables..

example
i modify your code as per my knowledge...



SQL SQLMSS SET SERVER CON01
SQL SQLMSS SET ERRORTYPE DBMS
SQL SQLMSS

-SET @@s1='V="VAS,';

-SET @@s2='(';

SELECT *,substring(LASTNAME,(NULLIF(CHARINDEX('@@s1',LASTNAME),0) +LEN('@@s1')),(NULLIF(CHARINDEX('@@s2',LASTNAME,(NULLIF(CHARINDEX('@@s1',LASTNAME),0) +LEN('@@s1'))),0) - (NULLIF(CHARINDEX('@@s1',LASTNAME),0)+LEN('@@s1')))) FROM eg_sql where eg_sql.LASTNAME like '%V="VAS,%';

END



its working fine..

thanks,

thenmozhi

Thanx everyone for all the replies.

Tomorrow I'm going 2 try to make those possible options (that you gave me) work.

I wil keep you informed.

Thanks again,

Nordin

Hi,

Guess what??

It worked.

After some minor adjustement, I got the script working.

Thanx, for everyone that contributed.
I really apreciate it

Nordin

hi,

can you please tell me what modification you done in the script..

thanks

thenmozhi

Hi thenmozhi,

I used the following script as an stored procedure in SQL.

Greetz,

Nordin


"USE [ReportBEZ]
GO
/****** Object: StoredProcedure [dbo].[ZI_vasscore] Script Date: 07/08/2009 15:43:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[ZI_vasscore]
AS
BEGIN
DECLARE @s1 varchar(8000)

DECLARE @s2 varchar(8000)

SET @s1='V="VAS,'
SET @s2=',('

SELECT *, CAST(substring([values],(NULLIF(CHARINDEX(@s1,[values]),0) +LEN(@s1)),(NULLIF(CHARINDEX(@s2,[values],(NULLIF(CHARINDEX(@s1,[values]),0) +LEN(@s1))),0) - (NULLIF(CHARINDEX(@s1,[values]),0)+LEN(@s1)))) AS varchar(20)) AS vasscore
FROM EZIS_Report.dbo.Metingen_metingen
where EZIS_Report.dbo.Metingen_metingen.[values] like '%V="VAS,%'

END"