SQL – VIEWS

Spread the love

SQL VIEWS – it works as real table  basis on defined and created  script. But dependent object must be available at the time of execution.

Example  – Table A has field name Product it is used in view when created , but after created column is deleted from table, if created view is called in a query, it will through an error.

Note :it does not store the data physically, it always execute the command and shows the result as table.

ITEM_TYPE TABLE

S_NOPRODUCTTYPE
1NIKESHOES
2REEBOKTSHIRT
3FILASHOES
4PUMARUNNING SHOES
5ADIDASSHOES

BRAND TABLE

S_NOPRODUCTMONTHYEARQTYSALES
1NIKEDEC2016107599
2REEBOKJAN2014181200
3FILAFEB201786000
4PUMAJAN2015165000
5NULLAPR201252500

 

CREATE VIEW DATAVIEW
as
BEGIN

DECLARE @DM INT
SELECT A.S_NO , A.PRODUCT , B.TYPE from BRAND AS A
LEFT JOIN
ITEM_TYPES B
ON A.PRODUCT = B.PRODUCT
WHERE A.YEAR = @DM

END

 

Create View