Issue on a SQL Function in SCRIPT QVD

Wed, 12 May 2010 09:10:43 GMT

Hello,

I have a request in SQL to create a QVD.

In SQL server this request works fine but in QLikview I Have a error :

This is the request:


LOAD
 '$(BusinessName)' as "Business name",
 "admin_id" as "Admin id",
 dayname(date) as "History date",
 libelle as "Segment Lib",
 /*num(Hour(date)) as "History Hour",
 num(minute(date)) as "History Minute",
 num(second(date)) as "History Second",*/
 refid,
 users_file_id,
 Tp;
SQL
SELECT SEGMENT.libelle,"admin_id",date,refid,users_file_id,Tp
FROM (SELECT h.id,
 "admin_id",
 date,
 refid,
 lastvalue,
 newvalue,
 u.user_id as users_file_id,
 case when lastvalue = '9' and newvalue = '2' then 1
 when lastvalue = '2' and newvalue = '9' then 2
 when lastvalue = '0' and newvalue = '2' then 3
 END as Tp
FROM [$(DBName)].[$(DBOwner)].history h
INNER JOIN [$(DBName)].[$(DBOwner)].users_file u ON refid = u.id
where date >= convert(datetime,'$(startdate)',103) and date < convert(datetime,'$(enddate)',103)
AND [table] = 'users_file'
AND [column] = 'statut'
AND (([lastvalue]='9' and [newvalue] = '2')
 OR ([lastvalue]='2' and [newvalue] = '9')
 OR ([lastvalue]='0' and [newvalue] = '2'))) as t
INNER JOIN [$(DBName)].[$(DBOwner)].users_file WITH(NOLOCK) ON t.refid = users_file.id
 INNER JOIN [$(DBName)].[$(DBOwner)].users WITH(NOLOCK) ON users_file.user_id = users.id
 INNER JOIN [$(DBName)].[$(DBOwner)].[site] WITH(NOLOCK) ON users.site_code = site.code
 INNER JOIN [$(DBName)].[$(DBOwner)].[admin] WITH(NOLOCK) ON t.admin_id = admin.id
 LEFT OUTER JOIN (SELECT code, libelle FROM dbo.segment_list(0, 0, DEFAULT) AS segment_list_1) AS SEGMENT ON dbo.users.segment = SEGMENT.code;

the error is:


SQL Error:[Microsoft][SQL Native Client][SQL Server]Procedure or function dbo.segment_list has too many arguments specified.
SQL Scriptline:
SQL State:37000
SQL
SELECT SEGMENT.libelle,"admin_id",date,refid,users_file_id,Tp
FROM (SELECT h.id,
 "admin_id",
 date,
 refid,
 lastvalue,
 newvalue,
 u.user_id as users_file_id,
 case when lastvalue = '9' and newvalue = '2' then 1
 when lastvalue = '2' and newvalue = '9' then 2
 when lastvalue = '0' and newvalue = '2' then 3
 END as Tp
FROM [Bruno].[dbo].history h
INNER JOIN [Bruno].[dbo].users_file u ON refid = u.id
where date >= convert(datetime,'01/01/2009',103) and date < convert(datetime,'01/02/2009',103)
AND [table] = 'users_file'
AND [column] = 'statut'
AND (([lastvalue]='9' and [newvalue] = '2')
 OR ([lastvalue]='2' and [newvalue] = '9')
 OR ([lastvalue]='0' and [newvalue] = '2'))) as t
INNER JOIN [betclick].[dbo].users_file WITH(NOLOCK) ON t.refid = users_file.id
 INNER JOIN [Bruno].[dbo].users WITH(NOLOCK) ON users_file.user_id = users.id
 INNER JOIN [Bruno].[dbo].[site] WITH(NOLOCK) ON users.site_code = site.code
 INNER JOIN [Bruno].[dbo].[admin] WITH(NOLOCK) ON t.admin_id = admin.id
 LEFT OUTER JOIN (SELECT code, libelle FROM dbo.segment_list(0, 0, DEFAULT) AS segment_list_1) AS SEGMENT ON dbo.users.segment = SEGMENT.code

Can you help me to find the error please?

King regards,

Bruno

Issue on a SQL Function in SCRIPT QVD

Wed, 12 May 2010 11:06:01 GMT

It's a problem in the SQL server sorry 🙂

topic Issue on a SQL Function in SCRIPT QVD in QlikView

Issue on a SQL Function in SCRIPT QVD

Issue on a SQL Function in SCRIPT QVD