Results 1 to 12 of 12
  1. #1
    Join Date
    Apr 2014
    Posts
    7

    Unanswered: Oracle view error

    Hi All ,

    I am getting error "X0"."CURR_PLN_GI_DT": invalid identifier while executing below query.

    select x0.created_on ,x4.quarter_name ,x0.curr_pln_gi_dt ,

    x0.deliv_block ,x1.distr_channel ,x0.exchange_rate ,x5.fiscal_month_num ,

    x0.forecast_firm_dt ,x6.quarter_name ,x0.hd_deliv_block ,

    x0.item_cat ,x2.mtrl_grp ,(x0.exchange_rate * x0.net_price ) ,

    x0.open_qty ,x0.order_type ,x1.part_num ,x9.name1 ,x0.order_number ,

    x0.item_number ,x0.inco_terms_1 ,x0.inco_terms_2 ,x12.sale_district ,

    CASE WHEN (((x0.order_type IN ('ZOR' ,'ZFD' ,'ZFY' ,'ZFC' ,'ZRS' )) AND (x1.distr_channel IN ('OM' ,'CM' )) ) AND (x1.sale_org = 'EU' ) ) THEN x8.quarter_name ELSE CASE WHEN (((x0.order_type IN ('ZOR' ,'ZFD' ,'ZFY' ,'ZFC' ,'ZRS' )) AND (x1.distr_channel IN ('OM' ,'CM' )) ) AND (x1.sale_org IN ('AP' ,'US' )) ) THEN x5.quarter_name ELSE CASE WHEN (((x0.order_type IN ('ZOR' ,'ZFD' ,'ZFY' ,'ZFC' ,'ZRS' )) AND (x1.distr_channel IN ('OM' ,'CM' )) ) AND (x12.sale_district = 'JP01' ) ) THEN x11.quarter_name ELSE CASE WHEN (((x0.order_type IN ('ZOR' ,'ZFD' ,'ZFY' ,'ZFC' ,'ZRS' )) AND (x1.distr_channel IN ('OM' ,'CM' )) ) AND (x12.sale_district = 'JP02' ) ) THEN x13.quarter_name ELSE x5.quarter_name END END END END ,

    x0.req_deliv_date ,x3.quarter_name ,x1.sale_org ,x0.sch_line_num ,

    ((x0.exchange_rate * x0.net_price ) * x0.open_qty ) ,x5.week_name ,

    x0.end_customer ,CASE WHEN (x0.sch_line_num = '0000' ) THEN 'Unschd' ELSE 'schd' END ,

    CASE WHEN ((x0.deliv_block = 'ZA' ) OR (x0.hd_deliv_block = 'ZA' ) ) THEN 'Hold-Credit' ELSE CASE WHEN ((x0.deliv_block IS NULL ) AND (x0.hd_deliv_block IS NULL ) ) THEN '' ELSE 'Hold-Non-Credit' END END ,

    CASE WHEN ((((x0.order_type = 'ZOR' ) OR (x0.order_type = 'ZFD' ) ) OR (x0.order_type = 'ZFY' ) ) OR (x0.order_type = 'ZRS' ) ) THEN 'Direct' ELSE CASE WHEN ((x0.order_type = 'ZKB' ) AND (((x0.item_cat = 'ZEBN' ) OR (x0.item_cat = 'KBN' ) ) OR (x0.item_cat = 'ZMAN' ) ) ) THEN 'Consignment' ELSE CASE WHEN ((x0.order_type = 'ZKB' ) AND (((x0.item_cat = 'ZKBN' ) OR (x0.item_cat = 'ZKBX' ) ) OR (x0.item_cat = 'ZMBN' ) ) ) THEN 'Forecast_Consignment' ELSE CASE WHEN (x0.order_type = 'ZID' ) THEN 'Interdivision' ELSE CASE WHEN ((x0.order_type = 'ZFC' ) AND (((x0.item_cat = 'ZFC' ) OR (x0.item_cat = 'ZFCX' ) ) OR (x0.item_cat = 'ZFCM' ) ) ) THEN 'Forecast' ELSE CASE WHEN ((x0.order_type = 'ZFC' ) AND (((x0.item_cat = 'TAN' ) OR (x0.item_cat = 'ZEXP' ) ) OR (x0.item_cat = 'ZMAN' ) ) ) THEN 'Direct' ELSE 'Other' END END END END END END ,

    x10.mark_seg ,x10.market_group ,x10.market_family ,x10.market_family_det

    from backlog_fact x0 ,sd_part_sale_dim x1 ,sd_part_dim x2 ,time_dim x3 ,time_dim x4 FULL OUTER JOIN(time_dim x5 )on x0.curr_pln_gi_dt = x5.day_date FULL OUTER JOIN(time_dim x6 ) on x0.forecast_firm_dt = x6.day_date FULL OUTER JOIN(usap_time_dim x7 ) on x0.curr_pln_gi_dt = x7.day_date FULL OUTER JOIN(eu_time_dim x8 ) on x0.curr_pln_gi_dt = x8.day_date ,sd_customer_dim x9 FULL OUTER JOIN(oms_prod x10 ) on x1.part_num = x10.product FULL OUTER JOIN(jp01_time_dim x11 ) on x0.curr_pln_gi_dt = x11.day_date ,sd_cust_sale_dim x12 FULL OUTER JOIN(jp02_time_dim x13 ) on x0.curr_pln_gi_dt = x13.day_date

    where (((((((((((((x0.curr_pln_gi_dt = x5.day_date ) AND (x0.part_sale_key = x1.part_sale_key ) ) AND (x0.part_key = x2.part_key ) ) AND (x0.req_deliv_date = x3.day_date ) ) AND (x0.created_on = x4.day_date ) ) AND (x0.forecast_firm_dt = x6.day_date ) ) AND (x0.curr_pln_gi_dt = x8.day_date ) ) AND (x0.curr_pln_gi_dt = x7.day_date ) ) AND (x0.sold_to_key = x9.customer_key ) ) AND (x1.part_num = x10.product ) ) AND (x0.curr_pln_gi_dt = x11.day_date ) ) AND (x0.cust_sale_key = x12.cust_sale_key ) ) AND (x0.curr_pln_gi_dt = x13.day_date ) )

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    posted SQL is an unreadable mess.
    since we don't have your tables, we can not debug, test or improve post code.

    Instant SQL Formatter
    reports that posted SQL has invalid syntax
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Apr 2014
    Posts
    7
    I am trying to migrate view from informix to oracle,since informix has outer keyword , i just replaced with full outer join and made corrections in oracle.

  4. #4
    Join Date
    Apr 2014
    Posts
    7
    Can someone tell better way to migrate from informix to oracle?

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Can someone tell better way to migrate from informix to oracle?
    Better than what exactly?
    Which metric measures "better"?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  6. #6
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713
    I agree with all that your sql is a mess.
    I formatted the first portion and realized that the nesting of "case" statements may be excessive, unnecessary, not relevant, uncalled-for, extravagant, overloaded and superfluos.

    Maybe something more simple would work:
    Code:
    SELECT
          x0.created_on, x4.quarter_name, x0.curr_pln_gi_dt, x0.deliv_block, x1.distr_channel
        , x0.exchange_rate, x5.fiscal_month_num, x0.forecast_firm_dt, x6.quarter_name
        , x0.hd_deliv_block, x0.item_cat, x2.mtrl_grp, (x0.exchange_rate * x0.net_price )
        , x0.open_qty, x0.order_type, x1.part_num, x9.name1, x0.order_number, x0.item_number
        , x0.inco_terms_1, x0.inco_terms_2, x12.sale_district
        , CASE
            WHEN (((x0.order_type IN ('ZOR','ZFD' ,'ZFY' ,'ZFC' ,'ZRS' ))
               AND (x1.distr_channel IN ('OM','CM' )) )
               AND (x1.sale_org = 'EU' ))
              THEN x8.quarter_name
            WHEN (((x0.order_type IN ('ZOR','ZFD','ZFY','ZFC' ,'ZRS' ))
               AND (x1.distr_channel IN ('OM' ,'CM' )) )
               AND (x1.sale_org IN ('AP','US' )) )
              THEN x5.quarter_name
            WHEN (((x0.order_type IN ('ZOR','ZFD' ,'ZFY' ,'ZFC' ,'ZRS' ))
               AND (x1.distr_channel IN ('OM','CM' )) )
               AND (x12.sale_district = 'JP01' ) )
              THEN x11.quarter_name
            WHEN (((x0.order_type IN ('ZOR','ZFD' ,'ZFY' ,'ZFC' ,'ZRS' ))
             AND (x1.distr_channel IN ('OM' ,'CM' )) )
             AND (x12.sale_district = 'JP02' ) )
            THEN x13.quarter_name
            ELSE x5.quarter_name
         END
        , x0.req_deliv_date,x3.quarter_name,x1.sale_org,x0.sch_line_num
        , ((x0.exchange_rate * x0.net_price ) * x0.open_qty )
        , x5.week_name,x0.end_customer
        ,CASE WHEN (x0.sch_line_num = '0000' ) THEN 'Unschd' ELSE 'schd' END
        ,CASE WHEN ((x0.deliv_block IS NULL ) AND (x0.hd_deliv_block IS NULL ) ) THEN ''
              WHEN ((x0.deliv_block = 'ZA' ) OR (x0.hd_deliv_block = 'ZA' ) ) THEN 'Hold-Credit'
              ELSE 'Hold-Non-Credit'
              END
    --,   E t c  --
    . . .
    I recommend you format your code and make it simple.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  7. #7
    Join Date
    Apr 2014
    Posts
    7
    I am trying to convert an Informix query to Oracle:

    The Informix query looks like this:

    from backlog_fact x0 ,sd_part_sale_dim x1
    ,sd_part_dim x2 ,time_dim x3 ,time_dim x4 ,outer(time_dim x5 ) ,outer(time_dim x6 ) ,outer(usap_time_dim x7 ) ,outer(eu_time_dim x8 ),
    sd_customer_dim x9 ,outer(oms_prod x10 ) ,outer(jp01_time_dim x11 ) ,sd_cust_sale_dim x12 ,outer(jp02_time_dim x13 )

    where (((((((((((((x0.curr_pln_gi_dt = x5.day_date ) AND (x0.part_sale_key = x1.part_sale_key ) )
    AND (x0.part_key = x2.part_key ) ) AND (x0.req_deliv_date = x3.day_date ) )
    AND (x0.created_on = x4.day_date ) ) AND (x0.forecast_firm_dt = x6.day_date ) )
    AND (x0.curr_pln_gi_dt = x8.day_date ) ) AND (x0.curr_pln_gi_dt = x7.day_date ) )
    AND (x0.sold_to_key = x9.customer_key ) ) AND (x1.part_num = x10.product ) )
    AND (x0.curr_pln_gi_dt = x11.day_date ) ) AND (x0.cust_sale_key = x12.cust_sale_key ) )
    AND (x0.curr_pln_gi_dt = x13.day_date ) )


    I am not sure how to join the these tables.

    Can any one help me?

  8. #8
    Join Date
    Dec 2007
    Posts
    253
    My first question would be:
    Do you fully understand the query and what it is trying to do? (What business requirements is it solving)
    If not, that is your first port of call, understand the original query. The next is to learn the basics of Oracle SQL syntax and take those requirements and solve them using Oracle syntax.

  9. #9
    Join Date
    Feb 2005
    Posts
    57
    Seems to me that you are associating the FULL OUTER JOINS to the wrong tables.

    e.g.
    Code:
     ,time_dim x4 FULL OUTER JOIN(time_dim x5 )on x0.curr_pln_gi_dt = x5.day_date
    "x0.curr_pln_gi_dt" does not exist in "time_dim x4"

    try the following:

    Code:
    FROM   backlog_fact x0
           FULL OUTER JOIN(time_dim x5 )
                        ON x0.curr_pln_gi_dt = x5.day_date
           FULL OUTER JOIN(time_dim x6 )
                        ON x0.forecast_firm_dt = x6.day_date
           FULL OUTER JOIN(usap_time_dim x7 )
                        ON x0.curr_pln_gi_dt = x7.day_date
           FULL OUTER JOIN(eu_time_dim x8 )
                        ON x0.curr_pln_gi_dt = x8.day_date
           FULL OUTER JOIN(jp01_time_dim x11 )
                        ON x0.curr_pln_gi_dt = x11.day_date
           FULL OUTER JOIN(jp02_time_dim x13 )
                        ON x0.curr_pln_gi_dt = x13.day_date
           ,sd_part_sale_dim x1
            FULL OUTER JOIN(oms_prod x10 )
                         ON x1.part_num = x10.product
           ,sd_part_dim x2
           ,time_dim x3
           ,time_dim x4
           ,sd_customer_dim x9
           ,sd_cust_sale_dim x12
    WHERE  ( x0.part_key = x2.part_key )
       AND ( x0.req_deliv_date = x3.day_date )
       AND ( x0.created_on = x4.day_date )
       AND ( x0.sold_to_key = x9.customer_key )
       AND ( x0.cust_sale_key = x12.cust_sale_key )

  10. #10
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by amitarkin View Post
    I am trying to convert an Informix query to Oracle
    Maybe, before making any code attempts, it would be useful to understand what you are supposed to do.

    It means, firstly you should understand what the Informix query does, so study it e.g. in the official IBM documentation for version 12.10 here:
    http://pic.dhe.ibm.com/infocenter/in...s_sqs_1035.htm
    http://pic.dhe.ibm.com/infocenter/in...ds_sqt_162.htm
    I am quite puzzled how did you find ANSI FULL join as it is not stated there at all and the Informix OUTER keyword seems to be equivalent to ANSI LEFT join.

    For Oracle, the relevant parts for 11gR2 are stated here:
    http://docs.oracle.com/cd/E11882_01/...6.htm#i2054062

    So, you have two options:
    - use Oracle-style outer join - it is just adding the join operator (+) to all columns of the outer joined tables in the WHERE clause (and remove the OUTER keyword from the FROM clause). It would not be so simple for nested joins, but your query does not contain any.
    - use ANSI join (as you were trying), but for courtesy of all people who will have to use and maintain it, use it for all tables, not only the outer joined. If outrider's query did not fail, strictly using ANSI join (INNER JOIN instead of listing X1, X2, X3, X4, X9 and X12 - by the way I could not find join condition between X0 and X1 in his query) should not fail too.

  11. #11
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    also, you shouldn't really mix implicit/explicit joins. do one or the other.
    Dave

  12. #12
    Join Date
    Apr 2014
    Posts
    7

    Smile

    Thanks to All

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •