"Luigi" <ciu***@inwind.it> wrote in message
news:1155895547.420523.121480@75g2000cwc.googlegroups.com...
> Hi all,
> I have a query that insert in a table some values find in another
> table.
> The problem is that one field (marked on a code) is always null (is it
> impossible, for my data).
> This is the query.
>
>
> INSERT INTO MOVIMENTI
> SELECT  newid(), --1
> cast(convert(varchar, getdate(),112) as int), --2
>        'ME', --3
>        MT.PianoConti, --4
>       CC.COD_CONTO_CONTABILE, --5
>        CC.DSC_CONTO_CONTABILE, --6
>        newid(), --7
>        CASE substring(MT.Conto,12,2) --8 (COD_CENTRO_COSTO)
>        WHEN 'DR' THEN ''
>            ELSE
> CASE PianoConti
> WHEN 'CB' THEN substring(MT.Conto,12,5)
>                    WHEN 'IS' THEN GCC.COD_GERARCHIA_CENTRO_COSTO
> END
>        END,
>
> -- This is always null. Why?
> CASE MT.PianoConti  --9 (COD_CENTRO_COSTO_FINALE)
>         WHEN 'CB' then
>         CASE CC.TIP_CENTRO_COSTO
>    WHEN 'A' THEN isnull(substring(MT.Conto,12,5), 'nullo')
>                    WHEN 'M' THEN '99999'
>                    WHEN 'D' THEN
> isnull(GCC.COD_GERARCHIA_CENTRO_COSTO, 'Nullo')
>     WHEN null THEN '99999'
> END
> WHEN 'IS' then
> CASE CC.TIP_CENTRO_COSTO
> WHEN 'M' THEN '99999'
>                 WHEN 'D' THEN isnull(GCC.COD_GERARCHIA_CENTRO_COSTO,
> 'Nullo')
> WHEN null THEN '99999'
> END
> else '_'
> END,
>
>
>        MT.Protocollo + '-' + convert(varchar, MT.NumeroMovimento),
> --10
>        MT.DescrizioneMovimento, --11
>        cast(MT.DataRegistrazione as datetime), --12
>        MT.Importo, --13 (CUR_IMPORTO)
>       getdate(), --14  (D_ACQUISIZIONE)
>       newid(), --15 (COD_ANAGRAFICA_GENERALE)
>        @dataInizioAnnoFiscale,  --16
>       CASE PianoConti --17  (FLG_DRIVER)
> when 'CB' THEN
> CASE substring(MT.Conto,12,2)
> WHEN 'DR' THEN 1
> ELSE 0
> END
> WHEN 'IS' THEN 0
>       END,
>        MT.Stat1,  --18  (ID_STATISTICO_1)
>         MT.Stat2,  --19  (COD_NDG_STATISTICO_2)
>       CASE PianoConti --20 (COD_DRIVER)
>       WHEN 'CB' THEN
>      CASE substring(MT.Conto,12,2)
> WHEN 'DR' THEN substring(MT.Conto,12,5)
> ELSE ''
> END
> WHEN 'IS' THEN ''
>       END,
>        null --21 DG.COD_PS
>
> FROM MovTemp MT
> inner join CONTI_CONTABILI CC
> on CC.COD_CONTO_CONTABILE = substring(MT.Conto,1,10)
> inner join CONTI_CONTABILI_GERARCHIE_CENTRI_COSTO GCC
> on CC.COD_SOCIETA = GCC.COD_SOCIETA
> and CC.COD_PIANO_CONTABILE = GCC.COD_PIANO_CONTABILE
> and substring(CC.COD_CONTO_CONTABILE,1,7) = GCC.COD_CONTO_CONTABILE
>
>
> The field 9 is always Null, and it must not be so.
> Is correct the syntax of this query?
>
> Thanks a lot.
>

> > -- This is always null. Why?
> > CASE MT.PianoConti  --9 (COD_CENTRO_COSTO_FINALE)
> >         WHEN 'CB' then
> >         CASE CC.TIP_CENTRO_COSTO
> >    WHEN 'A' THEN isnull(substring(MT.Conto,12,5), 'nullo')
> >                    WHEN 'M' THEN '99999'
> >                    WHEN 'D' THEN
> > isnull(GCC.COD_GERARCHIA_CENTRO_COSTO, 'Nullo')
> >     WHEN null THEN '99999'
> > END
>
> To test for NULL, you need to use the searched CASE syntax instead of the
> simple CASE syntax.  Untested example:
>
> CASE MT.PianoConti  --9 (COD_CENTRO_COSTO_FINALE)
>     WHEN 'CB' THEN
>         CASE
>             WHEN CC.TIP_CENTRO_COSTO = 'A' THEN
>                 isnull(substring(MT.Conto,12,5), 'nullo')
>             WHEN CC.TIP_CENTRO_COSTO = 'M' THEN
>                 '99999'
>             WHEN CC.TIP_CENTRO_COSTO = 'D' THEN
>                 isnull(GCC.COD_GERARCHIA_CENTRO_COSTO, 'Nullo')
>             WHEN CC.TIP_CENTRO_COSTO IS NULL THEN
>                 '99999'
>         END
>     WHEN 'IS' THEN
>         CASE
>             WHEN CC.TIP_CENTRO_COSTO = 'M' THEN '99999'
>             WHEN CC.TIP_CENTRO_COSTO = 'D' THEN
>                 isnull(GCC.COD_GERARCHIA_CENTRO_COSTO, 'Nullo')
>             WHEN CC.TIP_CENTRO_COSTO IS NULL THEN '99999'
>       END
>     ELSE
>         '_'
> END
>
> --
> Hope this helps.
>
> Dan Guzman
> SQL Server MVP
>
> "Luigi" <ciu***@inwind.it> wrote in message
> news:1155895547.420523.121480@75g2000cwc.googlegroups.com...
> > Hi all,
> > I have a query that insert in a table some values find in another
> > table.
> > The problem is that one field (marked on a code) is always null (is it
> > impossible, for my data).
> > This is the query.
> >
> >
> > INSERT INTO MOVIMENTI
> > SELECT  newid(), --1
> > cast(convert(varchar, getdate(),112) as int), --2
> >        'ME', --3
> >        MT.PianoConti, --4
> >       CC.COD_CONTO_CONTABILE, --5
> >        CC.DSC_CONTO_CONTABILE, --6
> >        newid(), --7
> >        CASE substring(MT.Conto,12,2) --8 (COD_CENTRO_COSTO)
> >        WHEN 'DR' THEN ''
> >            ELSE
> > CASE PianoConti
> > WHEN 'CB' THEN substring(MT.Conto,12,5)
> >                    WHEN 'IS' THEN GCC.COD_GERARCHIA_CENTRO_COSTO
> > END
> >        END,
> >
> > -- This is always null. Why?
> > CASE MT.PianoConti  --9 (COD_CENTRO_COSTO_FINALE)
> >         WHEN 'CB' then
> >         CASE CC.TIP_CENTRO_COSTO
> >    WHEN 'A' THEN isnull(substring(MT.Conto,12,5), 'nullo')
> >                    WHEN 'M' THEN '99999'
> >                    WHEN 'D' THEN
> > isnull(GCC.COD_GERARCHIA_CENTRO_COSTO, 'Nullo')
> >     WHEN null THEN '99999'
> > END
> > WHEN 'IS' then
> > CASE CC.TIP_CENTRO_COSTO
> > WHEN 'M' THEN '99999'
> >                 WHEN 'D' THEN isnull(GCC.COD_GERARCHIA_CENTRO_COSTO,
> > 'Nullo')
> > WHEN null THEN '99999'
> > END
> > else '_'
> > END,
> >
> >
> >        MT.Protocollo + '-' + convert(varchar, MT.NumeroMovimento),
> > --10
> >        MT.DescrizioneMovimento, --11
> >        cast(MT.DataRegistrazione as datetime), --12
> >        MT.Importo, --13 (CUR_IMPORTO)
> >       getdate(), --14  (D_ACQUISIZIONE)
> >       newid(), --15 (COD_ANAGRAFICA_GENERALE)
> >        @dataInizioAnnoFiscale,  --16
> >       CASE PianoConti --17  (FLG_DRIVER)
> > when 'CB' THEN
> > CASE substring(MT.Conto,12,2)
> > WHEN 'DR' THEN 1
> > ELSE 0
> > END
> > WHEN 'IS' THEN 0
> >       END,
> >        MT.Stat1,  --18  (ID_STATISTICO_1)
> >         MT.Stat2,  --19  (COD_NDG_STATISTICO_2)
> >       CASE PianoConti --20 (COD_DRIVER)
> >       WHEN 'CB' THEN
> >      CASE substring(MT.Conto,12,2)
> > WHEN 'DR' THEN substring(MT.Conto,12,5)
> > ELSE ''
> > END
> > WHEN 'IS' THEN ''
> >       END,
> >        null --21 DG.COD_PS
> >
> > FROM MovTemp MT
> > inner join CONTI_CONTABILI CC
> > on CC.COD_CONTO_CONTABILE = substring(MT.Conto,1,10)
> > inner join CONTI_CONTABILI_GERARCHIE_CENTRI_COSTO GCC
> > on CC.COD_SOCIETA = GCC.COD_SOCIETA
> > and CC.COD_PIANO_CONTABILE = GCC.COD_PIANO_CONTABILE
> > and substring(CC.COD_CONTO_CONTABILE,1,7) = GCC.COD_CONTO_CONTABILE
> >
> >
> > The field 9 is always Null, and it must not be so.
> > Is correct the syntax of this query?
> >
> > Thanks a lot.
> >