Aciu uz patarima . Isbandziau ...

> Tada jai ner kazkokio iraso likuc lentoje visi is tos lentos traukiami 
> laukai bus NULL tai jei toliau darysii agregacija (tipo sum, count, avg ir 
> t.t.. ) su jos laukais tai pradzioj statai funkcija IsNull(likuc.laukas, 
> 0).

   Beda ta , kad paprastai L lentelej nera tokiu irasu , kuriu yra LK . Ir 
butent ju nematau . Ir dar beda ta , kad ta "L" judejimo lentele yra kaip ir 
pagrindine , likuciu lentele jungiama prie jos .

  Stai pilnas selectas :

      SELECT
      DBO.INVENTTrans.ITEMID,
      dbo.INVENTTABLE.ITEMNAME,
      dbo.INVENTTABLE.ALNINVENTGROUP1,
      DBO.INVENTTrans.DATEPHYSICAL,

      SUM ( IsNull( dbo.UTINVENTREMAINMONTH.INVENTREMAIN, 0 ) ) AS LQTY,
      SUM ( IsNull( dbo.UTINVENTREMAINMONTH.COSTAMOUNT, 0 ) ) AS LSUM,

      SUM ( CASE WHEN DBO.INVENTTrans.DIRECTION = 1 and 
DBO.INVENTTrans.DIRECTION <> NULL then DBO.INVENTTrans.QTY else 0 END ) AS 
PQTY,
      SUM ( CASE WHEN DBO.INVENTTrans.DIRECTION = 1 and 
DBO.INVENTTrans.DIRECTION <> NULL then DBO.INVENTTrans.COSTAMOUNTPOSTED else 
0 END ) AS PSUM,
      SUM ( CASE WHEN DBO.INVENTTrans.DIRECTION = 2 and 
DBO.INVENTTrans.DIRECTION <> NULL then DBO.INVENTTrans.QTY else 0 END ) AS 
IQTY,
      SUM ( CASE WHEN DBO.INVENTTrans.DIRECTION = 2 and 
DBO.INVENTTrans.DIRECTION <> NULL then DBO.INVENTTrans.COSTAMOUNTPOSTED else 
0 END ) AS ISUM,

      DBO.INVENTTrans.INVENTDIMID,
      DBO.INVENTDIM.INVENTLOCATIONID,
      DBO.INVENTDIM.CONFIGID,
      DBO.INVENTDIM.INVENTBATCHID,
      DBO.INVENTDIM.INVENTSERIALId

      INTO dbo.INVENTTRANS_AV
      FROM dbo.INVENTTrans

      JOIN dbo.INVENTTABLE WITH (INDEX(itemidx))ON 
dbo.INVENTTRANS.ITEMID=dbo.inventtable.itemid
      JOIN DBO.INVENTDIM WITH (INDEX(INVENTDIMIDX)) ON 
dbo.INVENTTrans.INVENTDIMID=DBO.INVENTDIM.INVENTDIMID

      LEFT JOIN dbo.UTINVENTREMAINMONTH ON
        DBO.INVENTTrans.INVENTDIMID = dbo.UTINVENTREMAINMONTH.INVENTDIMID 
and
        DBO.INVENTTrans.ITEMID = dbo.UTINVENTREMAINMONTH.ITEMID and
        dbo.UTINVENTREMAINMONTH.INVREMAINDATE >= '2011.07.01' and
        dbo.UTINVENTREMAINMONTH.INVREMAINDATE <= '2011.07.31'

      Pagrindine problema , kaip suprantu , cia ... Nes join'as daromas tarp 
"L" ir "LK" .  O "L" neturi tokiu irasu kaip "LK"

      WHERE
      DBO.INVENTTrans.datephysical >='2011.07.01' and
      DBO.INVENTTrans.datephysical <='2011.07.31' and
      dbo.INVENTDIM.INVENTLOCATIONID = 'its'

      GROUP BY
      DBO.INVENTTrans.ITEMID,
      DBO.INVENTTABLE.ITEMNAME,
      dbo.inventtable.ALNINVENTGROUP1,
      DBO.INVENTTrans.DATEPHYSICAL,
      DBO.INVENTTrans.INVENTDIMID,
      DBO.INVENTDIM.INVENTLOCATIONID,
      dbo.INVENTDIM.CONFIGID,
      DBO.INVENTDIM.INVENTBATCHID,
      DBO.INVENTDIM.INVENTSERIALID

  Cia where su datom ir 'its' skyrium laikinai , isbandymui , kad veiktu ... 
:-)

> P.S. Tiesa nepakenciu uzklausu su keliom lentom ir be aliasu tai 
> sualiasinau ;) L - lenta, LK - likuc, tiesiog skaitomumas uzklausos mazeja 
> kada dar visas lentos name prie kiekvieno lauko eina.

  Pilnai suprantu ... :-)) Tik cia toks MS SQL Management Studio yra , tai 
su juo bandau kazka isgaut . Rasosi su pilna sintakse neblogai , tik spek 
nukilnot ... :-))

  Sirdingas dekui uz pagalba ...