Forum Stats

  • 3,838,748 Users
  • 2,262,397 Discussions
  • 7,900,749 Comments

Discussions

Index creation question

Murray Sobol
Murray Sobol Member Posts: 254
edited Oct 5, 2015 3:35PM in SQLとPL/SQL

In SQL Server, I can create an index as follows:

CREATE nonclustered INDEX unique_scale_ticket_ndx1

ON s1_quantity_entry (scale_ticket_id,plc_id,ship_mode_code,direct_ship_flag,scale_ticket_inbound_outbound)

WHERE scale_ticket_id IS NOT NULL

go

When I try this in Oracle (11g and 12c):

CREATE INDEX unique_scale_ticket_id

ON s1_quantity_entry (scale_ticket_id, plc_id, ship_mode_code, direct_ship_flag, scale_ticket_inbound_outbound)

WHERE scale_ticket_id IS NOT NULL

/

the index will not create due to the "where" clause.

I rewrote the SQL as follows:

CREATE UNIQUE INDEX unique_scale_ticket_id ON s1_quantity_entry

((CASE WHEN scale_ticket_id IS NOT NULL

           THEN scale_ticket_id

      ELSE null

  END

)

(CASE WHEN scale_ticket_id IS NOT NULL

           THEN plc_id

     ELSE null

  END

)

(CASE WHEN scale_ticket_id IS NOT NULL

           THEN ship_mode_code

     ELSE null

  END

)

(CASE WHEN scale_ticket_id IS NOT NULL

           THEN direct_ship_flag

     ELSE null

  END

)

(CASE WHEN scale_ticket_id IS NOT NULL

           THEN scale_ticket_inbound_outbound

     ELSE null

  END

)

)

/

but it give me this error:

Error report -

SQL Error: ORA-03001: unimplemented feature

03001. 00000 -  "unimplemented feature"

*Cause:    This feature is not implemented.

*Action:   None.

Is there another way to create this index in an Oracle environment?

Murray

  I then tried this SQL:

CREATE UNIQUE INDEX unique_scale_ticket_id ON s1_quantity_entry

((CASE WHEN scale_ticket_id IS NOT NULL

           THEN scale_ticket_id

           ELSE CASE WHEN scale_ticket_id IS NOT NULL

                        THEN plc_id

                        ELSE CASE WHEN scale_ticket_id IS NOT NULL

                                      THEN ship_mode_code

                                      ELSE CASE WHEN scale_ticket_id IS NOT NULL

                                                    THEN direct_ship_flag

                                                    ELSE CASE WHEN scale_ticket_id IS NOT NULL

                                                                  THEN scale_ticket_inbound_outbound

                                                                  ELSE null

                                                         END

                                           END

                             END

                END

  END

)

)

/

which produced this error:

Error report -

SQL Error: ORA-00932: inconsistent datatypes: expected NUMBER got CHAR

00932. 00000 -  "inconsistent datatypes: expected %s got %s"

*Cause:   

*Action:

The columns specified in the index creation SQL are not all of the same data type; some are varchar, char,number.

This discussion has been closed.