Re: Dar karta T-SQL select'as [Dream-Colored Bunny @ 2011-08-12 14:35:32]

Tema: Re: Dar karta T-SQL select'as
Autorius: Dream-Colored Bunny
Data: 2011-08-12 14:35:32
Na...
Visu pirma gali sutrumpinti:
>      CASE WHEN INV1.ITEMID is Null then LIK1.ITEMID else INV1.ITEMID END,
i
ISNULL(INV1.ITEMID,LIK1.ITEMID)

O antra didele problema yra chia
>      WHERE
>      INVD.INVENTLOCATIONID = 'its'     and
>      INV1.datephysical >='2011.07.01'  and
>      INV1.datephysical <='2011.07.31'  and
>      LIK1.DATEPHYSICAL >= '2011.07.01' and
>      LIK1.DATEPHYSICAL <= '2011.07.31'
Taigi pridek atitinkamas salygas ish WHERE prie ON cia:
>      FULL OUTER JOIN DBO.UTINVENTREMAINMONTH LIK1 ON INVD.INVENTDIMID = 
> LIK1.INVENTDIMID
arba perrashyk mazhdaug taip:
      WHERE
      INVD.INVENTLOCATIONID = 'its'     and
      isnull(INV1.datephysical,LIK1.DATEPHYSICAL) >='2011.07.01'  and
      isnull(INV1.datephysical,LIK1.DATEPHYSICAL) <='2011.07.31'
arba taip:
      WHERE
      INVD.INVENTLOCATIONID = 'its'     and
      (INV1.datephysical >='2011.07.01' or INV1.datephysical IS NULL)  and
      (INV1.datephysical <='2011.07.31' or INV1.datephysical IS NULL)  and
      (LIK1.DATEPHYSICAL >= '2011.07.01' or INV1.datephysical IS NULL) and
      (LIK1.DATEPHYSICAL <= '2011.07.31' or INV1.datephysical IS NULL)
ar panashiai, priklausomai nuo duomenu.
Turek omenyje, kad jei darai full outer join ir vienoj lentoje duomenu nera, tai is tos lentos duomenis gauni kaip NULL'us.
Bet koks lyginimas su NULL grazhins tau FALSE (pagal nutylejima).

-- 

=^_^=


"Rimokas" <rimasu@ut.lt> wrote in message news:j22u4k$4bl$1@trimpas.omnitel.net...
>> Greiciausiai taip butu buve geriausiai daryti nuo pradziu :) butum jau 
>> senai padares - apie tokia lenta mes ne nezinojom su Bunny :). Tada 
>> tiesiog left/right outer joinais tik dajungi kitas lentas ir viskas ;)
>>
>> P.S. Vakar kai perskaiciau sitai:
>>
>>> Beda ta , kad paprastai L lentelej nera tokiu irasu , kuriu yra LK . Ir 
>>> butent ju nematau .
>>
>> Tai biski isspaude sypsena ;) Na bet visko buna, gyveni ir mokaisi.
> 
>   Prispazinsiu , kad ant sudetingu SQl uzklausu esu zalias . Kol kas teko 
> daryt tik elementarius selectus . Ant MS SQl norim padaryt Data WareHouse .
>   Bandziau kaip ir rasiau , vistiek negaunu ko noriu . Busiu dekingas , 
> jeigu nurodysit , ka blogai darau ...
> 
> GO
> SET ANSI_NULLS ON
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> ALTER PROCEDURE [dbo].[Inventtrans_AV_CREATING] AS
> begin transaction
>      if OBJECT_ID(N'INVENTTRANS_AV',N'U') IS NOT NULL
>         DROP TABLE DBO.INVENTTRANS_AV
>         PRINT 'DROP PABAIGA'
> 
>      SELECT
>      INVD.INVENTLOCATIONID,
>      CASE WHEN INV1.ITEMID is Null then LIK1.ITEMID else INV1.ITEMID END AS 
> ITEMID,
>      CASE WHEN INVT1.ITEMNAME is null then INVT2.ITEMNAME else 
> INVT1.ITEMNAME END AS ITEMNAME,
>      CASE WHEN INVT1.ALNINVENTGROUP1 is null then INVT2.ALNINVENTGROUP1 
> else INVT1.ALNINVENTGROUP1 END AS ALNGROUP,
> 
>      SUM ( CASE WHEN LIK1.QTY is Null then 0 else LIK1.QTY END ) AS LQTY,
>      SUM ( CASE WHEN LIK1.COSTAMOUNTPOSTED is Null then 0 else 
> LIK1.COSTAMOUNTPOSTED END ) AS LSUM,
> 
>      SUM ( CASE WHEN INV1.DIRECTION = 1 then INV1.QTY else 0 END ) AS PQTY,
>      SUM ( CASE WHEN INV1.DIRECTION = 1 then INV1.COSTAMOUNTPOSTED else 0 
> END ) AS PSUM,
>      SUM ( CASE WHEN INV1.DIRECTION = 2 then INV1.QTY else 0 END ) AS IQTY,
>      SUM ( CASE WHEN INV1.DIRECTION = 2 then INV1.COSTAMOUNTPOSTED else 0 
> END ) AS ISUM,
> 
>      INVD.INVENTDIMID,
>      INVD.CONFIGID,
>      INVD.INVENTBATCHID,
>      INVD.INVENTSERIALId,
>      CASE WHEN INV1.DATEPHYSICAL is null THEN LIK1.DATEPHYSICAL else 
> INV1.DATEPHYSICAL end AS DATEPHYSICAL
> 
>      INTO DBO.INVENTTRANS_AV
>      FROM DBO.INVENTDIM INVD
> 
>      FULL OUTER JOIN DBO.INVENTTrans INV1 ON INVD.INVENTDIMID = 
> INV1.INVENTDIMID
>      FULL OUTER JOIN DBO.UTINVENTREMAINMONTH LIK1 ON INVD.INVENTDIMID = 
> LIK1.INVENTDIMID
> 
>      JOIN DBO.INVENTTABLE INVT1 ON INV1.ITEMID = INVT1.ITEMID
>      JOIN DBO.INVENTTABLE INVT2 ON LIK1.ITEMID = INVT2.ITEMID
> 
>      WHERE
>      INVD.INVENTLOCATIONID = 'its'     and
>      INV1.datephysical >='2011.07.01'  and
>      INV1.datephysical <='2011.07.31'  and
>      LIK1.DATEPHYSICAL >= '2011.07.01' and
>      LIK1.DATEPHYSICAL <= '2011.07.31'
> 
>      GROUP BY
>      INVD.INVENTLOCATIONID,
>      CASE WHEN INV1.ITEMID is Null then LIK1.ITEMID else INV1.ITEMID END,
>      CASE WHEN INVT1.ITEMNAME is null then INVT2.ITEMNAME else 
> INVT1.ITEMNAME END,
>      CASE WHEN INVT1.ALNINVENTGROUP1 is null then INVT2.ALNINVENTGROUP1 
> else INVT1.ALNINVENTGROUP1 END,
>      CASE WHEN INV1.DATEPHYSICAL is null THEN LIK1.DATEPHYSICAL else 
> INV1.DATEPHYSICAL END,
>      INVD.CONFIGID,
>      INVD.INVENTBATCHID,
>      INVD.INVENTSERIALID,
>      INVD.INVENTDIMID
> 
> COMMIT TRANSACTION
> 
>      CREATE INDEX INVENTLOCATIONIDIDX
> ON DBO.INVENTTRANS_AV 
> (INVENTLOCATIONID,ALNGROUP,ITEMNAME,ITEMID,DATEPHYSICAL)
> 
>   "18-" ir "19-" grupes item'u nera suvestinej , sios grupes nejudejo , tik 
> likuciuose . Butent ju ir nematau .  Bandziau visaip : ir prie INVD jungti 
> INV1 , o tada prie INV1 jungt LIK1 ir visaip kitaip . Visu tipu join'us - 
> FULL , LEFT , RIGHT ... Niekaip nepasirodo 18- ir 19- grupes prekes . Kaip 
> matot InventDimId yra vienas ir tas pats identifikatorius "00002576_D" . ITS 
> skyrius , be jokiu papildomu pozymiu .
> 
>   Dekui , is anksto , uz pagalba ! ....
> 
> Likuciu duomenys :
> 
>      ItemId InventDimId InventLocationId Qty DatePhysical CostAmountPosted
>      18-1-1-1 00002576_D ITS 2 2011.07.01 241,8
>      18-2-1-1 00002576_D ITS 4 2011.07.01 0,04
>      18-2-1-2 00002576_D ITS 1 2011.07.01 0,02
>      18-2-1-3 00002576_D ITS 19 2011.07.01 0,57
>      19-2-2-1 00002576_D ITS 1 2011.07.01 55
>      19-2-2-6 00002576_D ITS 2 2011.07.01 9
>      19-2-5-1 00002576_D ITS 2 2011.07.01 231,57
>      20-1-1-10 00002576_D ITS 1 2011.07.01 46
>      20-1-1-100 00002576_D ITS 4 2011.07.01 122
>      20-1-1-102 00002576_D ITS 1 2011.07.01 41,42
>      20-1-1-103 00002576_D ITS 1 2011.07.01 6,61
>      20-1-1-11 00002576_D ITS 1 2011.07.01 30
>      20-1-1-12 00002576_D ITS 1 2011.07.01 53
>      20-1-1-14 00002576_D ITS 1 2011.07.01 24,79
>      20-1-1-15 00002576_D ITS 2 2011.07.01
>      20-1-1-19 00002576_D ITS 1 2011.07.01 78,23
>      20-1-1-2 00002576_D ITS 4 2011.07.01 36,03
>      20-1-1-21 00002576_D ITS 3 2011.07.01 231,14
>      20-1-1-22 00002576_D ITS 1 2011.07.01 94,21
>      20-1-1-23 00002576_D ITS 1 2011.07.01 99,17
>      20-1-1-24 00002576_D ITS 2 2011.07.01 175,2
>      20-1-1-3 00002576_D ITS 37 2011.07.01 44,06
>      20-1-1-35 00002576_D ITS 1 2011.07.01 30
>      20-1-1-4 00002576_D ITS 1 2011.07.01
>      20-1-1-5 00002576_D ITS 8 2011.07.01 74,38
>      20-1-1-58 00002576_D ITS 2 2011.07.01 26,8
>      20-1-1-59 00002576_D ITS 5 2011.07.01 12,7
>      20-1-1-6 00002576_D ITS 10 2011.07.01 66,95
>      20-1-1-60 00002576_D ITS 1 2011.07.01 14,05
>      20-1-1-65 00002576_D ITS 4 2011.07.01 52,02
>      20-1-1-68 00002576_D ITS 7 2011.07.01 9,18
>      20-1-1-71 00002576_D ITS 4 2011.07.01 74,08
>      20-1-1-83 00002576_D ITS 1 2011.07.01 60
>      20-1-1-9 00002576_D ITS 2 2011.07.01 13,56
> 
> Judejimo duomenys :
>      ITEMID DATEPHYSICAL QTY COSTAMOUNTPOSTED DIRECTION INVENTDIMID
>      20-1-1-24 2011.07.13 1 88,43 1                     00002576_D
>      20-1-1-21 2011.07.13 1 95,87 1                     00002576_D
>      20-1-1-3 2011.07.13 10 7 1                     00002576_D
>      20-1-1-44 2011.07.26 4 0 1                     00002576_D
>      20-1-1-43 2011.07.26 -21 0 2                     00002576_D
>      20-1-1-44 2011.07.26 -4 0 2                     00002576_D
>      20-1-1-83 2011.07.31 -1 -60 2                     00002576_D
>      20-1-1-36 2011.07.31 -1 -30 2                     00002576_D
>      20-1-1-36 2011.07.31 -1 -30 2                     00002576_D
>      20-1-1-100 2011.07.31 -1 -30,29 2                     00002576_D
>      20-1-1-51 2011.07.31 -1 -99,17 2                     00002576_D
>      20-1-1-36 2011.07.31 -1 -30 2                     00002576_D
>      20-1-1-100 2011.07.11 3 90 1                     00002576_D
>      20-1-1-36 2011.07.11 6 180 1                     00002576_D
>      20-1-1-11 2011.07.11 1 30 1                     00002576_D
>      11-10-1-1 2011.07.19 100 0 1                     00002576_D
>      11-10-1-1 2011.07.19 -100 0 2                     00002576_D
>      11-10-1-2 2011.07.19 -80 0 2                     00002576_D
>      11-10-1-2 2011.07.19 80 0 1                     00002576_D
>      20-1-1-43 2011.07.26 11 0 1                     00002576_D
>      20-1-1-43 2011.07.26 10 0 1                     00002576_D
>      20-1-1-51 2011.07.13 1 99,17 1                     00002576_D
>      20-1-1-22 2011.07.13 1 94,21 1                     00002576_D
>      20-1-1-124 2011.07.14 1 180 1                     00002576_D
>      20-1-1-100 2011.07.31 -1 -30,29 2                     00002576_D
>      20-1-1-36 2011.07.31 -1 -30 2                     00002576_D
>      20-1-1-100 2011.07.31 -1 -30,28 2                     00002576_D
>      20-1-1-100 2011.07.31 -1 -30,29 2                     00002576_D
>      20-1-1-35 2011.07.31 -1 -30 2                     00002576_D
>      20-1-1-24 2011.07.31 -1 -87,88 2                     00002576_D
>      20-1-1-100 2011.07.31 -1 -30,29 2                     00002576_D
>      20-1-1-100 2011.07.31 -1 -30,28 2                     00002576_D
> 
> 
> 
>