topic Re: How to convert this sql into script in Qliview desctop in QlikView

How to convert this sql into script in Qliview desctop

Stravan — Fri, 15 Nov 2024 23:40:41 GMT

Hi,

I need help converting the following sql to regular script in QV. I have an ODBC connection to the database.

Thanks in advance

SELECT [t0].[Id], [t0].[AccNumber], [t0].[IsU], DATEPART(year, [t0].[AccDateTotal]) as Year, (SELECT STUFF((SELECT ',' + CAST(PlaceAttributeId as varchar) FROM [st].[AccPlaceAttribute] WHERE Id = [t0].[Id] AND ValueSource = 3 FOR XML PATH('')) ,1,1,'')) AS PlaceAttribute

FROM (

SELECT DISTINCT [a].[Id], [a].[AccDateTotal], [a].[AccNumber], [a].[BoatAccId], [a].[IsU]

FROM [st].[Acc] AS [a]

INNER JOIN (

(SELECT [a].[Id] AS [AccId]

FROM [st].[Acc] AS [a]

WHERE ((((EXISTS (

SELECT 1

FROM [st].[Report] AS [r]

WHERE ([a].[Id] = [r].[AccId]) AND (([r].[State] = 4) AND (([r].[AccDate] >= '2017-01-01') AND ([r].[AccDate] <= '2017-03-31')))))

AND [a].[BoatAccId] IS NULL) AND ([a].[Id] <> 0))

))) AS [t] ON [a].[Id] = [t].[AccId]

WHERE [a].[IsU] = CAST(0 AS bit)

) AS [t0]

ORDER BY [t0].[AccNumber], [t0].[Id]

Re: How to convert this sql into script in Qliview desctop

edwin — Thu, 05 Nov 2020 12:49:50 GMT

Have you tried adding this into your script? QV is supposed to be able to send your SQL script as it is to the SQL - based database server through your connection (ODBC in your case). you dont need to translate the script to a QV script.

https://help.qlik.com/en-US/qlikview/April2020/Subsystems/Client/Content/QV_QlikView/Scripting/ScriptRegularStatements/Select.htm

or are you experiencing an error when you add this to your script?

Re: How to convert this sql into script in Qliview desctop

Stravan — Fri, 06 Nov 2020 11:48:32 GMT

Thank you Edwin,

It went very well with running it directly in Qliview script. I should have tested it before I asked, sorry about that.