Sql code conversion

[#1]

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.

[#2]

Can you rewrite the SQL without the temp table ?

using subqueries perhaps ?

[flipside]

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

eg STORED Procedure

flipside

[#4]

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.

[#5]

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!

[flipside]

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