CeCiL - CCL Power Tools

CCL

select distinct    ; single-line comment;
                     ; another single-line comment
    sir.item_id,
    ocs.catalog_cd,
    ITEM_PRIMARY = uar_get_code_display(ocir.catalog_cd),
    sir.synonym_id,
    SYN_PRIMARY = uar_get_code_display(ocs.catalog_cd),
    ocs.mnemonic,
    MNEMONIC_TYPE_CD = uar_get_code_display(ocs.mnemonic_type_cd),
    ITEM_DESC = mi.value
  from
    order_catalog_item_r ocir,
    synonym_item_r sir,
    order_catalog_synonym ocs,
    med_identifier mi
  /* CCL-style join plans
     get replaced with SQL-style explicit joins.
  */
  plan sir
  where sir.item_id not in (
    select
      ocir.item_id
    from
      order_catalog_item_r ocir
    group by ocir.item_id
    having count(ocir.catalog_cd) > 1
  ) and "abcdef" = "abc*"
  join ocir
    where ocir.item_id = sir.item_id
  join ocs
    where ocs.synonym_id = sir.synonym_id
    and ocs.catalog_cd != ocir.catalog_cd
  join mi
    where mi.item_id = outerjoin(ocir.item_id)
    and mi.med_product_id = outerjoin(0)
    and mi.primary_ind = outerjoin(1)
    and mi.med_identifier_type_cd = outerjoin(value(uar_get_code_by("MEANING",11000,"DESC")))
  order by sir.item_id

CeCiL - CCL Power Tools

CeCiL performs pattern-based transformations from CCL to Oracle SQL.

Pre-release software. Use at your own risk. Beta

CCL

Oracle SQL

Conversion Report