Basit bir view yapmak istedim ancak ürüne bağlı o anki stoğu çekmek istediğimde aşağıdaki tabloda gördüğünüz gibi ONHAND null dönderiyor. Biraz araştırdım ama gene de sonuç alamadım. Yardımlarınız için şimdiden TEŞEKKÜRLER.
SELECT
LG_216_01_ORFICHE.FICHENO AS [FİŞ NO],
CLCARD.DEFINITION_ AS [MÜŞTERİ],
ITEMS.CODE AS [MAMUL KODU],
ITEMS.NAME AS [MAMUL ADI],
CONVERT (VARCHAR (11),LG_216_01_ORFICHE.DATE_,103) AS [SİPARİŞ TARİHİ],
CONVERT (VARCHAR (11),ORFLINE.DUEDATE,103) AS [TESLİM TARİHİ],
ORFLINE.AMOUNT AS [MİKTAR],
ORFLINE.SHIPPEDAMOUNT AS [SEVKEDİLEN],
ORFLINE.AMOUNT - ORFLINE.SHIPPEDAMOUNT AS KALAN,
(
SELECT SUM (DEPO.ONHAND) AS MIKTAR FROM LV_216_01_STINVTOT AS DEPO
INNER JOIN LG_216_ITEMS AS STOK ON DEPO.STOCKREF = STOK.LOGICALREF
WHERE (DEPO.INVENNO IN (1,2)) AND (STOK.CODE = ITEMS.CODE)
) AS [MEVCUT STOK]
FROM LG_216_SPECODES AS SPECODES
RIGHT OUTER JOIN LG_216_01_ORFLINE AS ORFLINE ON SPECODES.SPECODE = ORFLINE.SPECODE
LEFT OUTER JOIN LG_216_ITEMS AS ITEMS ON ORFLINE.STOCKREF = ITEMS.LOGICALREF
FULL OUTER JOIN LG_216_CLCARD AS CLCARD
RIGHT OUTER JOIN LG_216_SHIPINFO
RIGHT OUTER JOIN LG_216_01_ORFICHE ON LG_216_SHIPINFO.LOGICALREF = LG_216_01_ORFICHE.SHIPINFOREF ON CLCARD.LOGICALREF = LG_216_01_ORFICHE.CLIENTREF ON ORFLINE.ORDFICHEREF = LG_216_01_ORFICHE.LOGICALREF
WHERE(LG_216_01_ORFICHE.TRCODE = 1)
AND (ORFLINE.AMOUNT - ORFLINE.SHIPPEDAMOUNT > 0)