Problem with Create Index...

26 Aug 2005 5:46 PM

erobles75

Hi.
When I create an Index, the following message appears me

Server: Msg 8662, Level 16, State 1, Line 1
An index cannot be created on the view 'VW_CAM' because the view definition
includes an unknown value (the sum of a nullable expression).

The view doesn't contain any value in Null, attach the query

SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING,ANSI_WARNINGS,CONCAT_NULL_YIELDS_NULL,ARITHABORT,QUOTED_IDENTIFIER,ANSI_NULLS
ON
GO
CREATE VIEW VW_CAM WITH SCHEMABINDING
AS
SELECT
       id_mon , id_uni , id_cta_con , id_tip_cta , id_prc , id_cpt, id_scn,
       SUM(CASE WHEN ms_sdo_pes IS NULL THEN 0 ELSE ms_sdo_pes END) AS
ms_sdo_tot_pes,
       SUM(CASE WHEN ms_mto_ori IS NULL THEN 0 ELSE ms_mto_ori END) AS ms_mto,
       SUM(CASE WHEN ms_mto_ori_pes IS NULL THEN 0 ELSE ms_mto_ori_pes END)
AS ms_mto_pes,
       SUM(CASE WHEN ms_mto_ori IS NULL OR ms_tas_int IS NULL THEN 0 ELSE
ms_mto_ori * ms_tas_int END) AS ms_mto_tas,
       SUM(CASE WHEN ms_mto_ori_pes IS NULL OR ms_tas_int IS NULL THEN 0
ELSE ms_mto_ori_pes * ms_tas_int END) AS ms_mto_pes_tas,
       SUM(CASE WHEN ms_sdo_pes IS NULL OR ms_tas_int IS NULL THEN 0 ELSE
ms_sdo_pes * ms_tas_int END) AS ms_sdo_tas,
       count_big(*) as ms_cnt
FROM
      FT_OPERACIONES
GROUP BY id_mon, id_uni, id_cta_con, id_tip_cta, id_prc, id_cpt, id_scn
GO

CREATE UNIQUE CLUSTERED INDEX PI_VW_CAM
ON VW_CAM(id_mon, id_uni, id_cta_con, id_tip_cta, id_prc, id_cpt, id_scn)

Any help is appreciated.
Thanks,
ERobles

26 Aug 2005 6:25 PM

Alejandro Mesa

Try,

SELECT
    id_mon, id_uni, id_cta_con, id_tip_cta, id_prc, id_cpt, id_scn,
    SUM(isnull(ms_sdo_pes, 0)) AS ms_sdo_tot_pes,
    SUM(isnull(ms_mto_ori, 0)) AS ms_mto,
    SUM(isnull(ms_mto_ori_pes)) AS ms_mto_pes,
    SUM(isnull(ms_mto_ori, 0) * isnull(ms_tas_int, 0)) AS ms_mto_tas,
    SUM(isnull(ms_mto_ori_pes, 0) * isnull(ms_tas_int, 0)) AS ms_mto_pes_tas,
    SUM(isnull(ms_sdo_pes, 0) * isnull(ms_tas_int, 0)) AS ms_sdo_tas,
    count_big(*) as ms_cnt
FROM
    FT_OPERACIONES
GROUP BY
    id_mon, id_uni, id_cta_con, id_tip_cta, id_prc, id_cpt, id_scn
go

AMB

Show quote

"erobles75" wrote:

> Hi.
> When I create an Index, the following message appears me
>
> Server: Msg 8662, Level 16, State 1, Line 1
> An index cannot be created on the view 'VW_CAM' because the view definition
> includes an unknown value (the sum of a nullable expression).
>
> The view doesn't contain any value in Null, attach the query
>
> SET NUMERIC_ROUNDABORT OFF
> GO
> SET
> ANSI_PADDING,ANSI_WARNINGS,CONCAT_NULL_YIELDS_NULL,ARITHABORT,QUOTED_IDENTIFIER,ANSI_NULLS
> ON
> GO
> CREATE VIEW VW_CAM WITH SCHEMABINDING
> AS
> SELECT
>        id_mon , id_uni , id_cta_con , id_tip_cta , id_prc , id_cpt, id_scn,
>        SUM(CASE WHEN ms_sdo_pes IS NULL THEN 0 ELSE ms_sdo_pes END) AS
> ms_sdo_tot_pes,
>        SUM(CASE WHEN ms_mto_ori IS NULL THEN 0 ELSE ms_mto_ori END) AS ms_mto,
>        SUM(CASE WHEN ms_mto_ori_pes IS NULL THEN 0 ELSE ms_mto_ori_pes END)
> AS ms_mto_pes,
>        SUM(CASE WHEN ms_mto_ori IS NULL OR ms_tas_int IS NULL THEN 0 ELSE
> ms_mto_ori * ms_tas_int END) AS ms_mto_tas,
>        SUM(CASE WHEN ms_mto_ori_pes IS NULL OR ms_tas_int IS NULL THEN 0
> ELSE ms_mto_ori_pes * ms_tas_int END) AS ms_mto_pes_tas,
>        SUM(CASE WHEN ms_sdo_pes IS NULL OR ms_tas_int IS NULL THEN 0 ELSE
> ms_sdo_pes * ms_tas_int END) AS ms_sdo_tas,
>        count_big(*) as ms_cnt
> FROM
>       FT_OPERACIONES
> GROUP BY id_mon, id_uni, id_cta_con, id_tip_cta, id_prc, id_cpt, id_scn
> GO
>
> CREATE UNIQUE CLUSTERED INDEX PI_VW_CAM
> ON VW_CAM(id_mon, id_uni, id_cta_con, id_tip_cta, id_prc, id_cpt, id_scn)
>
>
> Any help is appreciated.
> Thanks,
> ERobles

26 Aug 2005 7:39 PM

erobles75

Thank Alejandro, but continue me appearing the same message

Show quote

"Alejandro Mesa" wrote:

> Try,
>
> SELECT
>     id_mon, id_uni, id_cta_con, id_tip_cta, id_prc, id_cpt, id_scn,
>     SUM(isnull(ms_sdo_pes, 0)) AS ms_sdo_tot_pes,
>     SUM(isnull(ms_mto_ori, 0)) AS ms_mto,
>     SUM(isnull(ms_mto_ori_pes)) AS ms_mto_pes,
>     SUM(isnull(ms_mto_ori, 0) * isnull(ms_tas_int, 0)) AS ms_mto_tas,
>     SUM(isnull(ms_mto_ori_pes, 0) * isnull(ms_tas_int, 0)) AS ms_mto_pes_tas,
>     SUM(isnull(ms_sdo_pes, 0) * isnull(ms_tas_int, 0)) AS ms_sdo_tas,
>     count_big(*) as ms_cnt
> FROM
>     FT_OPERACIONES
> GROUP BY
>     id_mon, id_uni, id_cta_con, id_tip_cta, id_prc, id_cpt, id_scn
> go
>
>
> AMB
>
> "erobles75" wrote:
>
> > Hi.
> > When I create an Index, the following message appears me
> >
> > Server: Msg 8662, Level 16, State 1, Line 1
> > An index cannot be created on the view 'VW_CAM' because the view definition
> > includes an unknown value (the sum of a nullable expression).
> >
> > The view doesn't contain any value in Null, attach the query
> >
> > SET NUMERIC_ROUNDABORT OFF
> > GO
> > SET
> > ANSI_PADDING,ANSI_WARNINGS,CONCAT_NULL_YIELDS_NULL,ARITHABORT,QUOTED_IDENTIFIER,ANSI_NULLS
> > ON
> > GO
> > CREATE VIEW VW_CAM WITH SCHEMABINDING
> > AS
> > SELECT
> >        id_mon , id_uni , id_cta_con , id_tip_cta , id_prc , id_cpt, id_scn,
> >        SUM(CASE WHEN ms_sdo_pes IS NULL THEN 0 ELSE ms_sdo_pes END) AS
> > ms_sdo_tot_pes,
> >        SUM(CASE WHEN ms_mto_ori IS NULL THEN 0 ELSE ms_mto_ori END) AS ms_mto,
> >        SUM(CASE WHEN ms_mto_ori_pes IS NULL THEN 0 ELSE ms_mto_ori_pes END)
> > AS ms_mto_pes,
> >        SUM(CASE WHEN ms_mto_ori IS NULL OR ms_tas_int IS NULL THEN 0 ELSE
> > ms_mto_ori * ms_tas_int END) AS ms_mto_tas,
> >        SUM(CASE WHEN ms_mto_ori_pes IS NULL OR ms_tas_int IS NULL THEN 0
> > ELSE ms_mto_ori_pes * ms_tas_int END) AS ms_mto_pes_tas,
> >        SUM(CASE WHEN ms_sdo_pes IS NULL OR ms_tas_int IS NULL THEN 0 ELSE
> > ms_sdo_pes * ms_tas_int END) AS ms_sdo_tas,
> >        count_big(*) as ms_cnt
> > FROM
> >       FT_OPERACIONES
> > GROUP BY id_mon, id_uni, id_cta_con, id_tip_cta, id_prc, id_cpt, id_scn
> > GO
> >
> > CREATE UNIQUE CLUSTERED INDEX PI_VW_CAM
> > ON VW_CAM(id_mon, id_uni, id_cta_con, id_tip_cta, id_prc, id_cpt, id_scn)
> >
> >
> > Any help is appreciated.
> > Thanks,
> > ERobles

27 Aug 2005 6:47 AM

Razvan Socol

Books Online says that an index on a view cannot be created if the view
contains "a SUM function that references a nullable expression". It
seems that SQL Server 2000 considers (in this context) that any
expression that includes a nullable column is a "nullable expression",
even if that expression is "ISNULL(SomeColumn,0)".

In SQL Server 2005 (June CTP), this situation has been changed, i.e. an
"ISNULL(SomeColumn,0)" expression is considered non-nullable; however
"COALESCE(SomeColumn,0)" and "CASE WHEN SomeColumn IS NULL THEN 0 ELSE
SomeColumn END" are still considered nullable expressions (in this
context).

The solution for SQL Server 2000 would be to change the definition of
the column(s) in your table so that they do not allow nulls.

Razvan