Focal Point
SQL statement that doesn't work in Datamigrator
June 23, 2009, 09:09 AM
Nordin Ben AhmedSQL statement that doesn't work in Datamigrator
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
7.7.03
OS is Windows 7
We create al sorts of outputs depending on the demand, HTML (most cases), Excel (some cases) and PDF (some cases)
June 24, 2009, 06:35 AM
Shruthi JoshiHi,
may be you can try and check with varchar2 as data type
hope it helps...
WebFocus 7.6.8
Data Migrator 7.6.8
Windows XP
HTML,Excel
June 24, 2009, 09:59 AM
Nordin Ben AhmedHi 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
7.7.03
OS is Windows 7
We create al sorts of outputs depending on the demand, HTML (most cases), Excel (some cases) and PDF (some cases)
June 25, 2009, 10:00 AM
ClifThe 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.
N/A
June 26, 2009, 09:35 AM
thenmozhihi,
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
June 29, 2009, 06:48 AM
Nordin Ben AhmedThanx 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
7.7.03
OS is Windows 7
We create al sorts of outputs depending on the demand, HTML (most cases), Excel (some cases) and PDF (some cases)
July 03, 2009, 09:41 AM
Nordin Ben AhmedHi,
Guess what??
It worked.
After some minor adjustement, I got the script working.
Thanx, for everyone that contributed.
I really apreciate it
Nordin
7.7.03
OS is Windows 7
We create al sorts of outputs depending on the demand, HTML (most cases), Excel (some cases) and PDF (some cases)
July 06, 2009, 04:56 AM
thenmozhihi,
can you please tell me what modification you done in the script..
thanks
thenmozhi
July 08, 2009, 09:47 AM
Nordin Ben AhmedHi 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"
7.7.03
OS is Windows 7
We create al sorts of outputs depending on the demand, HTML (most cases), Excel (some cases) and PDF (some cases)