tai imt kaip baze reikia LK ir jungt left join L prie jos o tada tu irasu kur nebus L lentoje laukai bus Null ir kiekvienoj vietoj dek IsNull(laukas, reiksme_null_atveju_pvz_0) ir viskas...

"Rimokas" <rimasu@ut.lt> wrote in message news:j1t6ji$na6$1@trimpas.omnitel.net...
>     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 ...
> 
>