CREATE VIEW dbo.ViewWithFilter AS
SELECT ITM.CODE, 
       ISNULL((SELECT TOP (1) PRODUCERCODE
               FROM dbo.MALZEME_URETICI_KOD_LISTESI
               WHERE (REF = ITM.REF) AND (SIRA = 1) AND (STS = 1)), '') AS PRODUCERCODE, 
       CMP.DEFINITION_, 
       LST.PRICE, 
       CASE WHEN LST.CURRENCY = 1 THEN 'USD' 
            WHEN LST.CURRENCY = 13 THEN 'CNY' 
            WHEN LST.CURRENCY = 20 THEN 'EUR' 
            WHEN LST.CURRENCY = 160 THEN 'TL' 
            ELSE 'USD' END AS CURR, 
       LST.CREATED_DATETIME, 
       { fn CONCAT({ fn CONCAT(LST.LINEEXP1, ' ') }, LST.LINEEXP2) } AS LEXP, 
       ISNULL((SELECT TOP (1) PRODUCERCODE
               FROM dbo.MALZEME_URETICI_KOD_LISTESI AS MALZEME_URETICI_KOD_LISTESI_1
               WHERE (REF = ITM.REF) AND (SIRA = 1) AND (STS = 2)), '') AS PRODUCERCODE2, 
       LST.REF AS PRCREF, 
       ISNULL((SELECT FICHENO
               FROM dbo.FICHES
               WHERE (REF IN (SELECT FICHEREF
                              FROM dbo.FICHE_LINES
                              WHERE (PRICEREF = LST.REF)))), '') AS FIS
FROM dbo.PRCLIST AS LST 
LEFT OUTER JOIN dbo.ITEMS AS ITM ON ITM.REF = LST.ITEMREF 
LEFT OUTER JOIN dbo.COMPANIES AS CMP ON CMP.REF = LST.COMPANYREF
WHERE (LST.PRICE <> 0) 
  AND (LST.REF IN (SELECT DISTINCT PRICEREF
                   FROM dbo.FICHE_LINES AS FICHE_LINES_1));