Cette requête contient une projection et un filtrage et aprés la création de vue et lors de l’exécution du requête j'ai cette erreur :
ERROR 1054 (42S22): Unknown column 'l_quantity' in 'field list'
V1 : Q1_Project_lineitem
Create view Q1_Project_lineitem As select l_returnflag ,l_linestatus, sum(l_quantity) as sum_qty,sum(l_extendedprice) as sum_base_price,sum(l_extendedprice * (1 - l_discount)) as sum_disc_price,sum(l_extendedprice * (1 - l_discount) * (1 +l_tax)) as sum_charge,avg(l_quantity) as avg_qty,avg(l_extendedprice) as avg_price,avg(l_discount) as avg_disc,count(*) as count_order from lineitem group by l_returnflag, l_linestatus order by l_returnflag, l_linestatus;
select l_returnflag, l_linestatus, sum(l_quantity) as sum_qty, sum(l_extendedprice) as sum_base_price, sum(l_extendedprice * (1 - l_discount)) as sum_disc_price, sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge, avg(l_quantity) as avg_qty, avg(l_extendedprice) as avg_price, avg(l_discount) as avg_disc, count(*) as count_order from Q1_Project_lineitem group by l_returnflag, l_linestatus order by l_returnflag, l_linestatus;
V2 :Q1_Filter_lineitem
Create view Q1_Filter_lineitem As select * from lineitem where l_shipdate <= date '1998-12-01' - interval '1' day ;
select * from Q1_Filter_lineitem where l_shipdate <= date '1998-12-01' - interval '1' day ;
Si tu définis une vue, c'est justement pour ne pas avoir besoin de rappeler les fonctions et clauses qui permettent son obtention.
Dans ta v1, tu as défini Q1_Project_lineitem avec les colonnes l_returnflag, l_linestatus, sum_qty, sum_base_price, sum_disc_price, sum_charge, avg_qty, avg_price, avg_disc et count_order
Tous les autres noms de colonnes, ceux issus de la table d'origine, sont inconnus dans la vue. De plus, la clause GROUP BY est déjà incluse, pas besoin de la rajouter.
