Gestion d'une matrice dans talend ou en sql ==> Managing a matrix in talend

[TDIALLO]

Hi

I come to you to ask for help on data processing with the Talend ETL.

The situation is as follows:

I have a table in Mysql (stop_sale_date) which is populated by a team which contains the information not to be taken into account when loading into the final table (productSale).

I have several possibility in the table (stop_sale_date) that I described in a matrix

Table structure

CREATE TABLE syncto.`stop_sale_date` 

(

 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,

 `tour_operateur_id` smallint(5) unsigned NOT NULL,

 `hebergement_id` int(10) unsigned DEFAULT NULL,

 `produit_id` int(10) unsigned DEFAULT NULL,

 `date_depart` date DEFAULT NULL,

 `pension_id` tinyint(3) unsigned DEFAULT NULL,

 `duree` tinyint(3) unsigned NOT NULL DEFAULT 0,

 `date_debut` date DEFAULT NULL,

 `date_fin` date DEFAULT NULL,

 PRIMARY KEY (`id`),

 UNIQUE INDEX idx_unique_ss (`tour_operateur_id`,`hebergement_id`,`date_depart`,`pension_id`,`duree`,`produit_id`)

 ) ENGINE=InnoDB DEFAULT CHARSET=latin1

 ;

the objective is to take into account all these possibilities of filling the "stop_sale_date" table described in the matrix to ignore them in the output table.

the query that ensured the loading of the final "productSale" table is as follows:

SELECT 

ch.package_id,

ch.date_depart,

ch.hebergement_id,

pa.pension_id,

pa.duree,

ch.date_depart + INTERVAL pa.duree_jour DAY,

ch.prix_vente,

ch.prix_brochure,

ch.disponibilite,

  ch.est_promo,

CASE 

WHEN pr.tour_operateur_id = 626 THEN pr.reference_base

ELSE pr.reference

END AS reference,

ch.calendrier_type_prix_id,

coalesce(if(ch.prix_brochure is not null ,round(100 - (ch.prix_vente / ch.prix_brochure) * 100), 0), 0),

FROM 

xxx_calendrier_hebergement ch

INNER JOIN package pa ON pa.id = ch.package_id

INNER JOIN produit pr ON pr.id = pa.produit_id

WHERE 

pr.statut & 4

the idea is to exclude the different possibilities that we have in the matrix of the table (stop_sale_date) from this query.

how can do this with talend or in SQL please?

Thank you for coming back

[TDIALLO]

nobody has any idea ??????