Load Data using SQL

[jonathanEVS]

Hello everyone, i'm pretty new to QLIK and I need some help because i'm desesperate. 
I created a table manually (i had a csv, and find a way to recreate it in SQL to manipulate) and use it to manipulate datas using other tables from an Oracle data base.I test my script using DBeaver. Here is a part of my code : 

WITH cte_correspondance_grammage AS (

SELECT 'MILANO' AS MACHINE,

'Ligne de prix' AS TYPE_PRODUIT,

'BOISSONS CHAUDES' AS GROUPE,

'CAFE NOIR' AS TYPE,

'CAFE LYOPHILISE' AS FAMILLE,

'NESTUSA' AS CODE,

'NESTLE AMERICANO' AS NOM,

2.1 AS Cafe_grain,

0.0 AS Cafe_Lyo,

0.0 AS Chocolat,

0 AS Lait,

190 AS Eau_ml

FROM dual

UNION ALL

SELECT 'MILANO', 'Ligne de prix', 'BOISSONS CHAUDES', 'BOISSON GOURMANDE', 'CHOCOLAT', 'NESTCACAO', 'NESTLE CACAO',

0.0, 0.0, 24.8, 0, 150

FROM dual

UNION ALL

SELECT 'MILANO', 'Ligne de prix', 'BOISSONS CHAUDES', 'BOISSON GOURMANDE', 'CHOCOLAT', 'NESTVIENNOIS', 'NESTLE CACAO VIENNOIS',

0.0, 0.0, 18.6, 6.3, 150

FROM dual

UNION ALL

SELECT 'MILANO', 'Ligne de prix', 'BOISSONS CHAUDES', 'BOISSON GOURMANDE', 'CAFE LYOPHILISE', 'NESTCAFELAIT', 'NESTLE CAFE AU LAIT',

2.6, 0.0, 0.0, 6, 150

FROM dual

),

PRODUCT_INFOS AS (

SELECT

p.IDPRODUCT,

p.CODE,

p.NAME,

l.CODE AS FAMILLE_p

FROM

PRODUCT p

JOIN

LABEL_HAS_PRODUCT lhp ON p.IDPRODUCT = lhp.IDPRODUCT

JOIN

LABEL l ON lhp.IDLABEL = l.IDLABEL

WHERE l.CODE IN ('LAIT','CHOCOLAT','CAFE GRAIN','CAFE LYOPHILISE') AND p.NAME NOT IN ('NESTLE CAFE 250G','MONBANA CHOCOLAT MAX HAVELAAR 15G','VAN HOUTEN CHOCOLAT 23G','PATURAGES LAIT BIO DEMI ECREME 1L')

),

PRODUCT_AND_MP AS(

SELECT

p.*,

cg.cafe_lyo,

cg.cafe_grain,

cg.chocolat,

cg.lait,

cg.Eau_ml,

CASE

WHEN cg.cafe_lyo IS NULL AND cg.cafe_grain IS NULL AND cg.chocolat IS NULL AND cg.lait IS NULL AND cg.Eau_ml IS NULL THEN 1

ELSE 0

END AS premiere

FROM PRODUCT_INFOS p

LEFT JOIN cte_correspondance_grammage cg ON cg.NOM=P.NAME)
... (i then do other manipulation) etc

But I saw that in Qlik I can't use the "dual" option to load data table. Is there an alternative to achieve t
in QLIK ?
i'd be very grateful if you can help me

PS : My table i create manually is from an csv file and it got 28 lines (i didnt write it all in this post) if it can help

[marksouzacosta]

Before digging into this, can you just create a View in your Database adding all this code there?
Doing that, your Qlik code will be simple as SQL SELECT * FROM MyVew;.
Does that work for you?

[jonathanEVS]

The thing is that in my oracle data base i'm not admin so I can't create a table (if it's what you mean).

But I'm building my own data base on postreSQL by fetching data from the oracle data base, because I don't see any other solutions

[marksouzacosta · Accepted solution]

I have the feeling your problem is not with the DUAL.
In any case, you can definitely rewrite your queries to work in Qlik Load Script.
Load the tables isolated and combine them with Qlik. This will look like this - I was not able to test:

PRODUCT_INFOS:
SQL SELECT
    p.Name AS %ProductLink,
    p.IDPRODUCT AS ProductID,
    p.CODE AS ProductCode,
    p.NAME AS ProductName,
    l.CODE AS FAMILLE_p
FROM
    PRODUCT p
    JOIN LABEL_HAS_PRODUCT lhp ON p.IDPRODUCT = lhp.IDPRODUCT
    JOIN LABEL l ON lhp.IDLABEL = l.IDLABEL
WHERE
    l.CODE IN ('LAIT','CHOCOLAT','CAFE GRAIN','CAFE LYOPHILISE') 
    AND p.NAME NOT IN ('NESTLE CAFE 250G','MONBANA CHOCOLAT MAX HAVELAAR 15G','VAN HOUTEN CHOCOLAT 23G','PATURAGES LAIT BIO DEMI ECREME 1L')
;

LEFT JOIN (PRODUCT_INFOS)
cte_correspondance_grammage:
LOAD 
    NOM AS %ProductLink,
    MACHINE, 
    TYPE_PRODUIT, 
    GROUPE, 
    TYPE, 
    FAMILLE, 
    CODE, 
    NOM, 
    Cafe_grain, 
    Cafe_Lyo, 
    Chocolat, 
    Lait, 
    Eau_ml
INLINE [
MACHINE, TYPE_PRODUIT, GROUPE, TYPE, FAMILLE, CODE, NOM, Cafe_grain, Cafe_Lyo, Chocolat, Lait, Eau_ml
MILANO, Ligne de prix, BOISSONS CHAUDES, CAFE NOIR, CAFE LYOPHILISE, NESTUSA, NESTLE AMERICANO, 2.1, 0.0, 0.0, 0, 190
MILANO, Ligne de prix, BOISSONS CHAUDES, BOISSON GOURMANDE, CHOCOLAT, NESTCACAO, NESTLE CACAO, 0.0, 0.0, 24.8, 0, 150
MILANO, Ligne de prix, BOISSONS CHAUDES, BOISSON GOURMANDE, CHOCOLAT, NESTVIENNOIS, NESTLE CACAO VIENNOIS, 0.0, 0.0, 18.6, 6.3, 150
MILANO, Ligne de prix, BOISSONS CHAUDES, BOISSON GOURMANDE, CAFE LYOPHILISE, NESTCAFELAIT, NESTLE CAFE AU LAIT, 2.6, 0.0, 0.0, 6, 150
];


PRODUCT_AND_MP:
LOAD
    // Fields from PRODUCT_INFOS
    %ProductLink,
    ProductID,
    ProductCode,
    ProductName,
    FAMILLE_p,

    // Fields from cte_correspondance_grammage
    cafe_lyo,
    cafe_grain,
    chocolat,
    lait,
    Eau_ml,
    If(IsNull(cafe_lyo) AND IsNull(cafe_grain) And IsNull(chocolat) AND IsNull(lait) AND IsNull(Eau_ml),1,0) AS premiere
RESIDENT
    PRODUCT_INFOS
;

DROP TABLE PRODUCT_INFOS;

[jonathanEVS]

Thank you for your response Sir. I'm still not really familiar with Qlik script so this help me a lot!