topic Re: Sql code conversion in QlikView

Sql code conversion

Tue, 01 Oct 2013 10:28:43 GMT

Hi.

If there's SQL code that needs to be converted into Qlikview but the code is written through temp tables with alter and update statements, how do I do the equivalent in Qlikview? I tried using the similar alter and update statements but Qlikview does not seem to recognise it.

Below is a sample of the code I am trying to convert, bearing in mind that Qlikview is connecting to the Sql server so the syntax needs to gel with both SQL server as well as Qlikview.

select c.CLAIM_NUMBER as ACCOUNT,
       isnull(a.SAGE_ACCOUNT_NO,'') as SAGE_ACCOUNT,
        c.SERVICE_DATE
       '                    ' as FUNDER,
        isnull(cad.BILLING_TYPE,'') as BILLING_TYPE,
       isnull(ca.AGREEMENT,'') as AGREEMENT,
       isnull(ca.MEDICAL_AID,'') as MEDICAL_AID,
        isnull(tp.LOCATION,'') as LOCATION,
        isnull(tp.DESTINATION,'') as DESTINATION,
       '                    ' as LOCATION_GROUP,
       '                    ' as DESTINATION_GROUP
INTO   #TEMP
from sysdba.claims c
left join sysdba.BCX_ACCOUNT_ADD_INFO a on c.accountid = a.ACCOUNTID
left join sysdba.CLAIMS_ACCOUNT_DETAILS cad on c.CLAIMSID = cad.CLAIMSID
left join sysdba.CLAIMS_AUTHORISATION ca on c.CLAIMSID = ca.CLAIMSID
left join sysdba.CLAIMS_TIMEKM tk on c.claimsid = tk.CLAIMSID
left joinsysdba.CLAIMS_TRANSPORT tp on c.CLAIMSID = tp.CLAIMSID
order by c.SERVICE_DATE

update #TEMP
set FUNDER = f.FUNDER
from #TEMP t
inner join sysdba.funder f on t.MEDICAL_AID = f.MEDAID
WHERE t.SERVICE_DATE >= f.EFF_FROM and t.SERVICE_DATE <= EFF_TO

Thank you.

Re: Sql code conversion

Tue, 01 Oct 2013 14:08:57 GMT

Can you rewrite the SQL without the temp table ?

using subqueries perhaps ?

Re: Sql code conversion

flipside — Tue, 01 Oct 2013 14:17:46 GMT

Convert the code into a stored procedure and call that from Qlikview.

eg STORED Procedure

flipside

Re: Sql code conversion

Wed, 02 Oct 2013 07:08:22 GMT

Hi Keith.

I think it's possible but from what I'm gathering, either way I might need to make use of stored procs...

Thanks.

Re: Sql code conversion

Wed, 02 Oct 2013 07:09:25 GMT

Hi!

Thank you for the link!

So I guess I can more or less conclude that a stored proc is needed to achieve this...

I'll have to incorporate the code samples in your link.

Thanks!

Re: Sql code conversion

flipside — Wed, 02 Oct 2013 07:32:57 GMT

Hi,

The stored proc method is quickest as it will use the same code, has been tested (I assume) and keep the processing in SQL server, although if that is problematic, then looking at the query you just need to bring through the top part (before the update code) into a table in Qlikview and then bring through the 'funder' table as a second table and then code logic within QV to join the two.

flipside