6b.593. Database Estimated Costs (OCI360_DB_SYSTEMS)

  WHERE  SHAPE LIKE 'VM.%' OR SHAPE LIKE 'BM.%'
                              *

ERROR at line 15:
ORA-00904: "SHAPE": invalid identifier
#: click on a column heading to sort on it
* US$ costs are estimations for Month Flex, not considering ANY account discounts. Values base date: 2018-08-28

 Name                                                                                Null?    Type
 ----------------------------------------------------------------------------------- -------- --------------------------------------------------------
 DATA_STORAGE_SIZE_IN_GBS                                                                     NUMBER(1)
 LIFECYCLE_STATE                                                                              VARCHAR2(1)
 RECO_STORAGE_SIZE_IN_GB                                                                      NUMBER(1)
 ID                                                                                           VARCHAR2(1)


WITH DB_SRV AS
(
  SELECT distinct
         ID,
         CLUSTER_NAME,
         DISPLAY_NAME,
         LICENSE_MODEL,
         SHAPE,
         CPU_CORE_COUNT,
         DATABASE_EDITION,
         SUBSTR(SHAPE,1,INSTR(SHAPE,'.',1,1)-1)                                      SHAPE_TYPE,
         SUBSTR(SHAPE,INSTR(SHAPE,'.',-1,1)+1,LENGTH(SHAPE)-INSTR(SHAPE,'.',-1,1))   SHAPE_MAX_OCPUS
  FROM   OCI360_DB_SYSTEMS X
  WHERE  SHAPE LIKE 'VM.%' OR SHAPE LIKE 'BM.%'
),
DB_COST AS (
SELECT /*+ materialize */
       INST_TYPE,
       PAYG,
       MF,
       SUBSTR(INST_TYPE,1,INSTR(INST_TYPE,'.',1,1)-1) DB_VERS,
       LIC_TYPE DB_LIC_TYPE,
       SUBSTR(INST_TYPE,INSTR(INST_TYPE,'.',1,1)+1) DB_OCPUS_INCL
FROM   "OCI360_PRICING"
WHERE  SUBJECT = 'DATABASE'
AND    SUBSTR(INST_TYPE,INSTR(INST_TYPE,'.',1,1)+1) != 'OCPU'
),
DB_OCPU AS (
SELECT /*+ materialize */
       INST_TYPE,
       PAYG,
       MF,
       SUBSTR(INST_TYPE,1,INSTR(INST_TYPE,'.',1,1)-1) DB_VERS,
       LIC_TYPE DB_LIC_TYPE
FROM   "OCI360_PRICING"
WHERE  SUBJECT = 'DATABASE'
AND    INSTR(INST_TYPE,'.',1,2) = 0
AND    SUBSTR(INST_TYPE,INSTR(INST_TYPE,'.',1,1)+1) = 'OCPU'
)
SELECT t1.CLUSTER_NAME,
       t1.DISPLAY_NAME,
       t1.SHAPE_TYPE,
       t1.CPU_CORE_COUNT,
       t1.LICENSE_MODEL,
       t1.SHAPE,
       (t1.CPU_CORE_COUNT - t2.DB_OCPUS_INCL) EXTRA_USED_OCPUS,
       t1.SHAPE_MAX_OCPUS MAX_OCPUS,
       t1.DATABASE_EDITION,
       t1.ID,
       ROUND(T2.MF * 24 * 30,2) "Database - US$ Cost per month",
       ROUND((t1.CPU_CORE_COUNT - t2.DB_OCPUS_INCL) * T3.MF * 24 * 30,2) "Extra CPU - US$ Cost per month",
       ROUND((T2.MF   + (t1.CPU_CORE_COUNT - t2.DB_OCPUS_INCL) * T3.MF  ) * 24 * 30,2) "Total - US$ Cost per month"
FROM   DB_SRV      t1,
       DB_COST t2,
       DB_OCPU t3
WHERE  t1.SHAPE_TYPE = t2.DB_VERS (+)
AND    DECODE(t1.LICENSE_MODEL,'LICENSE_INCLUDED',t1.DATABASE_EDITION,t1.LICENSE_MODEL) = t2.DB_LIC_TYPE (+)
AND    t1.SHAPE_TYPE = t3.DB_VERS (+)
AND    DECODE(t1.LICENSE_MODEL,'LICENSE_INCLUDED',t1.DATABASE_EDITION,t1.LICENSE_MODEL) = t3.DB_LIC_TYPE (+);


-1 rows selected.