Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a SQL script that I need to run in Qlik Sense is this possible?
CREATE PROCEDURE cu_calc_qty_bcydok_work( p_datum DATE )
DEFINE v_retvalue VARCHAR(255) ;
DEFINE v_von_uzeit INTEGER ;
DEFINE v_bis_uzeit INTEGER ;
DEFINE v_etinr INTEGER ;
DEFINE v_uzeit INTEGER ;
DEFINE v_status INTEGER ;
DEFINE v_ref_etinr INTEGER ;
DEFINE v_ref_uzeit INTEGER ;
DEFINE v_ref_status INTEGER ;
DEFINE v_key INTEGER ;
DEFINE v_bmnr INTEGER ;
DEFINE v_schicht INTEGER ;
ON EXCEPTION IN (-206)
END EXCEPTION WITH RESUME
DROP TABLE tt_bcydok ;
DROP TABLE tt_bcydok_single ;
DROP TABLE tt_bcydok_tmp ;
LET v_von_uzeit = cu_calc_unixtime(p_datum , '05:59:59') ;
LET v_bis_uzeit = cu_calc_unixtime(p_datum + 1 , '06:00:00') ;
SELECT auftrnr, lfdpos, bmnr, glasid, etinr, schicht, status,
uzeit, rowid bcy_key,
CASE WHEN status = 501000000 THEN 1 ELSE -1 END stk
FROM bcydok
WHERE uzeit > v_von_uzeit
AND uzeit < v_bis_uzeit
AND etinr > 0
INTO TEMP tt_bcydok WITH NO LOG ;
SELECT bmnr, etinr
FROM tt_bcydok
GROUP BY bmnr, etinr
HAVING COUNT(*) = 1
INTO TEMP tt_bcydok_single WITH NO LOG ;
SELECT rowid key, * FROM tt_bcydok
WHERE bmnr||'-'||etinr NOT IN (
SELECT s.bmnr||'-'||s.etinr
FROM tt_bcydok_single s )
INTO TEMP tt_bcydok_tmp WITH NO LOG ;
FOREACH
SELECT DISTINCT etinr, bmnr
INTO
v_etinr, v_bmnr
FROM tt_bcydok_tmp
LET v_ref_etinr = v_etinr ;
LET v_ref_status = 0 ;
LET v_ref_uzeit = 0 ;
FOREACH
SELECT uzeit, status, key
INTO
v_uzeit, v_status, v_key
FROM tt_bcydok_tmp
WHERE etinr = v_etinr
AND bmnr = v_bmnr
ORDER BY uzeit
IF v_ref_uzeit = 0 --erste Lesung !!!
THEN
LET v_ref_status = v_status ;
LET v_ref_uzeit = v_uzeit ;
CONTINUE FOREACH ;
ELSE --Mehrfach-Lesung !!!
IF v_status = v_ref_status
AND v_status = 501000000
THEN --Mehrfach-GUT-Lesung !!!
DELETE FROM tt_bcydok
WHERE rowid = v_key
;
ELSE --neuer Status !
LET v_ref_status = v_status ;
END IF
END IF
END FOREACH
END FOREACH
END PROCEDURE
Hi, the stored procedure should be created in DB, and from qlik you can call that stored procedure with:
SQL exec [schema].[storedprocedure]
Like: SQL exec dbo.getshare
Not possible with this one sorry, I would of done this.