1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

A Table maybe joined to at most one other table errror

Discussion in 'SQL PL/SQL' started by Donnie28, Apr 29, 2014.

  1. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    769
    Likes Received:
    147
    Trophy Points:
    830
    Location:
    Russian Federation
    Hi.
    I say that aliases repeated and in attempt to execute request there will be an error.
     
  2. Donnie28

    Donnie28 Active Member

    Messages:
    43
    Likes Received:
    1
    Trophy Points:
    80
    Hi

    Yes but I have corrected the ailising issue but am still getting the the Invalid relational operator error!

    SELECT DISTINCT c.cpnt_typ_id,
    c.cpnt_id,
    c.rev_dte,
    c.cpnt_title,
    c.cpe_hrs,
    d.dmn_id,
    d.dmn_desc,
    pcs.cpnt_src_desc,
    rt.rtyp_desc,
    c.create_dte,
    c.contact,
    c.safety_rel,
    DECODE (c.notactive, 'Y', 'N', 'Y') AS notactive,
    c.les_plan,
    c.cpnt_desc,
    c.comments,
    c.tgt_audnc,
    c.rev_num ,
    cf.formula price,
    cf.is_default,
    cf.currency_code AS currencycode,
    cur.symbol AS currencysymbol,
    c.reviser,
    c.apprvd,
    c.apprvl_dte,
    c.apprvr ,
    ip.label_id AS init_period_type_id,
    c.init_number,
    ib.label_id AS init_basis_type_id,
    rp.label_id AS retrng_period_type_id,
    c.retrng_number,
    rb.label_id AS retrng_basis_type_id ,
    c.cpnt_len,
    c.prep_len,
    c.max_enrl,
    c.min_enrl,
    c.credit_hrs,
    c.contact_hrs,
    c.cpe_hrs ,
    ds.seg_num,
    ds.day_num,
    ds.DURATION,
    lt.locn_typ_desc,
    de.eq_cnt,
    et.eq_typ_desc ,
    qip.label_id AS q_init_period_type_id,
    qc.init_number AS q_init_number,
    qib.label_id AS q_init_basis_type_id,
    qrp.label_id AS q_retrng_period_type_id,
    qc.retrng_number AS q_retrng_number,
    qrb.label_id AS q_retrng_basis_type_id,
    qc.stat_effect_dte AS q_stat_effect_dte,
    q.qual_id AS q_qual_id,
    q.qual_title AS q_qual_title ,
    s.subj_id,
    s.subj_desc,
    doc.doc_id,
    doc.doc_title,
    cdoc.review_flg,
    t.task_id,
    t.task_desc ,
    cu.col_num,
    uc.label,
    cu.user_value,
    urc.user_desc,
    pi.inst_id,
    pi.fname,
    pi.lname,
    pi.org_id,
    po.org_desc


    FROM pa_cpnt c

    left join pa_domain d on d.dmn_id = c.dmn_id

    left join pa_cpnt_src pcs on cs.cpnt_typ_id = c.cpnt_typ_id


    left join pa_subj s on s.subj_id = cs.subj_id

    left join pa_rqmt_type rt on rt.rtyp_id = c.rtyp_id

    left join pa_currency cur on cur on cur.currency_code = cf.currency_code

    left join pa_cpnt_formula cf on cf.cpnt_typ_id = c.cpnt_typ_id
    AND cf.cpnt_id = c.cpnt_id
    AND cf.rev_dte = c.rev_dte
    AND cf.fin_var_id = 'ItemDefaultPublishedPrice'


    left join ps_period_type ip on ip.period_type_id = c.init_period_type_id
    left join ps_basis_type ib on ib.basis_type_id = c.init_basis_type_id
    left join ps_period_type rp on rp.period_type_id = c.retrng_period_type_id
    left join ps_basis_type rb on rb.basis_type_id = c.retrng_basis_type_id
    left join pa_dflt_seg ds on ds.cpnt_typ_id = c.cpnt_typ_id
    AND ds.cpnt_id = c.cpnt_id
    AND ds.rev_dte = c.rev_dte

    left join pa_locn_type lt on lt.locn_typ_id = ds.locn_typ_id

    left join pa_dflt_eq de on de.cpnt_typ_id = ds.cpnt_typ_id
    AND de.cpnt_id = ds.cpnt_id
    AND de.rev_dte = ds.rev_dte
    AND de.seg_num = ds.seg_num


    left join pa_eq_type et on et.eq_typ_id = de.eq_typ_id

    left join pa_qual_cpnt qc on qc.cpnt_typ_id = c.cpnt_typ_id
    AND qc.cpnt_id = c.cpnt_id
    AND qc.rev_dte = c.rev_dte

    left join pa_qual q on q.qual_id = qc.qual_id

    left join ps_period_type qip on qip.period_type_id = qc.init_period_type_id

    left join ps_basis_type qib on qib.basis_type_id = qc.init_basis_type_id

    left join ps_period_type qrp on qrp.period_type_id = qc.retrng_period_type_id

    left join ps_basis_type qrb on qrb.basis_type_id = qc.retrng_basis_type_id

    left join pa_cpnt_subj cs on cs.cpnt_typ_id = c.cpnt_typ_id
    AND cs.cpnt_id = c.cpnt_id
    AND cs.rev_dte = c.rev_dte

    left join pa_cpnt_doc cdoc on cdoc.cpnt_typ_id = c.cpnt_typ_id
    AND cdoc.cpnt_id = c.cpnt_id
    AND cdoc.rev_dte = c.rev_dte

    left join pa_doc doc on doc.doc_id = cdoc.doc_id

    left join pa_cpnt_task ct on ct.cpnt_typ_id = c.cpnt_typ_id
    AND ct.cpnt_id = c.cpnt_id
    AND ct.rev_dte = c.rev_dte

    left join pa_cpnt_task ct on ct.cpnt_typ_id = t.task_id

    Left join pa_cpnt_user cu on cu.cpnt_typ_id = c.cpnt_typ_id
    AND cu.cpnt_id = c.cpnt_id
    AND cu.rev_dte = c.rev_dte

    Left join pa_cpnt_user cu on cu.col_num = uc.col_num

    Left Join pa_cpnt_user cu on cu.col_num = urc.col_num

    Left Join pa_cpnt_user cu on cu.user_value = urc.user_id

    Left Join pa_inst pi on pi.dmn_id = c.dmn_id

    Left join pa_inst pi on pi.org_id = stud.org_id

    Left join pa_inst pi on pi.org_id = po.org_id

    Left join pa_inst pi on po.org_id = stud.org_id

    WHERE
    cf.fin_var_id = 'ItemDefaultPublishedPrice'

    /** and (c.cpnt_typ_id, c.cpnt_id, c.rev_dte) in [ItemSearch] */
    /** and [securitya_cpnt c] */
     
  3. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    769
    Likes Received:
    147
    Trophy Points:
    830
    Location:
    Russian Federation
    Can you show error message
     
  4. Donnie28

    Donnie28 Active Member

    Messages:
    43
    Likes Received:
    1
    Trophy Points:
    80
    Hi Sergey

    See attached a screen shot of the error, thanks for sticking with this its much appreciated :)

    Regards
    Donal
     

    Attached Files:

  5. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    769
    Likes Received:
    147
    Trophy Points:
    830
    Location:
    Russian Federation
    Check clause WHERE in your sql query :


    Pay attention :
    Here part of your request.
    Using repeating aliases is will lead to the nonoptimal execution plan, and to errors.

     
  6. Donnie28

    Donnie28 Active Member

    Messages:
    43
    Likes Received:
    1
    Trophy Points:
    80
    Hi Sergey

    Thanks for te tips on the ORA-00920 error. I noticed a repetition in one of the lines of code "left join pa_currency cur on cur on cur.currency_code = cf.currency_code".....cur on is repeated twice so I removed this and am troubleshooting some other issues regarding sequencing now

    Regards
    Donal
     
  7. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    769
    Likes Received:
    147
    Trophy Points:
    830
    Location:
    Russian Federation
    Hi,Donal.
    Now your sql request works?
     
  8. Donnie28

    Donnie28 Active Member

    Messages:
    43
    Likes Received:
    1
    Trophy Points:
    80
    Hi Sergey

    No Unfortunately I am still getting a ORA-00936 Missing Expression Error! Having done a bit of research on the error I understand that it is caused by have not listing some of the columns in the Select statement but I have checked and rechecked the select columns with the from values and they all math up so I am stumped!!! Any help on this would be very much apprecaited, I have posted the last code I am using below!

    Regards
    Donal

    http://www.techonthenet.com/oracle/errors/ora00936.php



    SELECT DISTINCT
    c.cpnt_typ_id,
    c.cpnt_id,
    c.rev_dte,
    c.cpnt_title,
    c.cpe_hrs,
    c.create_dte,
    c.contact,
    c.safety_rel,
    c.les_plan,
    c.cpnt_desc,
    c.comments,
    c.tgt_audnc,
    c.rev_num,
    c.reviser,
    c.apprvd,
    c.apprvl_dte,
    c.apprvr ,
    c.init_number,
    c.retrng_number,
    c.cpnt_len,
    c.prep_len,
    c.max_enrl,
    c.min_enrl,
    c.credit_hrs,
    c.contact_hrs,
    c.cpe_hrs ,
    d.dmn_id,
    d.dmn_desc,
    pcs.cpnt_src_desc,
    rt.rtyp_desc,
    DECODE (c.notactive, 'Y', 'N', 'Y') AS notactive,
    cf.formula price,
    cf.is_default,
    cf.currency_code AS currencycode,
    cur.symbol AS currencysymbol,
    ip.label_id AS init_period_type_id,
    ib.label_id AS init_basis_type_id,
    rp.label_id AS retrng_period_type_id,
    rb.label_id AS retrng_basis_type_id ,
    ds.seg_num,
    ds.day_num,
    ds.DURATION,
    lt.locn_typ_desc,
    de.eq_cnt,
    et.eq_typ_desc,
    qc.init_number AS q_init_number,
    qc.retrng_number AS q_retrng_number,
    qc.stat_effect_dte AS q_stat_effect_dte,
    q.qual_id AS q_qual_id,
    q.qual_title AS q_qual_title,
    qip.label_id AS q_init_period_type_id,
    qib.label_id AS q_init_basis_type_id,
    qrp.label_id AS q_retrng_period_type_id,
    qrb.label_id AS q_retrng_basis_type_id,
    s.subj_id,
    s.subj_desc,
    cdoc.review_flg,
    doc.doc_id,
    doc.doc_title,
    t.task_id,
    t.task_desc,
    cu.col_num,
    cu.user_value,
    uc.label,
    urc.user_desc


    FROM
    pa_cpnt c,
    left join pa_domain d on d.dmn_id = c.dmn_id
    left join pa_cpnt_src pcs on pcs.cpnt_src_id = c.cpnt_src_id
    left join pa_rqmt_type rt on rt.rtyp_id = c.rtyp_id

    left join pa_cpnt_formula cf on cf.cpnt_typ_id = c.cpnt_typ_id
    AND cf.cpnt_id = c.cpnt_id
    AND cf.rev_dte = c.rev_dte
    AND cf.fin_var_id = 'ItemDefaultPublishedPrice'

    left join pa_currency cur on cur.currency_code = cf.currency_code

    left join ps_period_type ip on ip.period_type_id = c.init_period_type_id
    left join ps_basis_type ib on ib.basis_type_id = c.init_basis_type_id
    left join ps_period_type rp on rp.period_type_id = c.retrng_period_type_id
    left join ps_basis_type rb on rb.basis_type_id = c.retrng_basis_type_id
    left join pa_dflt_seg ds on ds.cpnt_typ_id = c.cpnt_typ_id
    AND ds.cpnt_id = c.cpnt_id
    AND ds.rev_dte = c.rev_dte

    left join pa_locn_type lt on lt.locn_typ_id = ds.locn_typ_id

    left join pa_dflt_eq de on de.cpnt_typ_id = ds.cpnt_typ_id
    AND de.cpnt_id = ds.cpnt_id
    AND de.rev_dte = ds.rev_dte
    AND de.seg_num = ds.seg_num

    left join pa_eq_type et on et.eq_typ_id = de.eq_typ_id

    left join pa_qual_cpnt qc on qc.cpnt_typ_id = c.cpnt_typ_id
    AND qc.cpnt_id = c.cpnt_id
    AND qc.rev_dte = c.rev_dte

    left join pa_qual q on q.qual_id = qc.qual_id

    left join ps_period_type qip on qip.period_type_id = qc.init_period_type_id

    left join ps_basis_type qib on qib.basis_type_id = qc.init_basis_type_id

    left join ps_period_type qrp on qrp.period_type_id = qc.retrng_period_type_id

    left join ps_basis_type qrb on qrb.basis_type_id = qc.retrng_basis_type_id

    left join pa_cpnt_subj cs on cs.cpnt_typ_id = c.cpnt_typ_id
    AND cs.cpnt_id = c.cpnt_id
    AND cs.rev_dte = c.rev_dte

    left join pa_subj s on s.subj_id = cs.subj_id

    left join pa_cpnt_doc cdoc on cdoc.cpnt_typ_id = c.cpnt_typ_id
    AND cdoc.cpnt_id = c.cpnt_id
    AND cdoc.rev_dte = c.rev_dte

    left join pa_doc doc on doc.doc_id = cdoc.doc_id

    left join pa_cpnt_task ct on ct.cpnt_typ_id = c.cpnt_typ_id
    AND ct.cpnt_id = c.cpnt_id
    AND ct.rev_dte = c.rev_dte

    left join pa_task t on ct.task_id = t.task_id

    Left join pa_cpnt_user cu on cu.cpnt_typ_id = c.cpnt_typ_id
    AND cu.cpnt_id = c.cpnt_id
    AND cu.rev_dte = c.rev_dte

    Left join pa_usrcl_cpnt uc on cu.col_num = uc.col_num

    Left join pa_usrrf_cpnt urc on cu.col_num = urc.col_num
    AND cu.user_value = urc.user_id
     
  9. DTSIGuy

    DTSIGuy Forum Advisor

    Messages:
    401
    Likes Received:
    58
    Trophy Points:
    410
    Location:
    Texas
    ANSI SQL is not my strong suit, I prefer the Oracle notation...but where is your WHERE?
     
  10. Donnie28

    Donnie28 Active Member

    Messages:
    43
    Likes Received:
    1
    Trophy Points:
    80
    Hi Sergey

    Below is the where clause be even when I add it in affer the FROM statement I still get the ORA-00936 Missing Expression Error!!

    WHERE
    cf.fin_var_id = 'ItemDefaultPublishedPrice'

    /** and (c.cpnt_typ_id, c.cpnt_id, c.rev_dte) in [ItemSearch] */
    /** and [securitya_cpnt c] */
     
  11. DTSIGuy

    DTSIGuy Forum Advisor

    Messages:
    401
    Likes Received:
    58
    Trophy Points:
    410
    Location:
    Texas
    You have a slew of AND statements mixed in w/ your LEFT JOINS that I suspect should all be moved to the WHERE portion.
     
    Donnie28 likes this.
  12. Donnie28

    Donnie28 Active Member

    Messages:
    43
    Likes Received:
    1
    Trophy Points:
    80
    Hi Sergey

    Yessss I did what you said and placed all the ANDs in the Where section and it worked :) :)...thanks so much for your help and for sticking with it. Have a great weekend. below is the code the worked

    Donal




    SELECT DISTINCT
    c.cpnt_typ_id,
    c.cpnt_id,
    c.rev_dte,
    c.cpnt_title,
    c.cpe_hrs,
    c.create_dte,
    c.contact,
    c.safety_rel,
    c.les_plan,
    c.cpnt_desc,
    c.comments,
    c.tgt_audnc,
    c.rev_num,
    c.reviser,
    c.apprvd,
    c.apprvl_dte,
    c.apprvr,
    c.init_number,
    c.retrng_number,
    c.cpnt_len,
    c.prep_len,
    c.max_enrl,
    c.min_enrl,
    c.credit_hrs,
    c.contact_hrs,
    c.cpe_hrs,
    d.dmn_id,
    d.dmn_desc,
    pcs.cpnt_src_desc,
    rt.rtyp_desc,
    DECODE (c.notactive, 'Y', 'N', 'Y') AS notactive,
    cf.formula price,
    cf.is_default,
    cf.currency_code AS currencycode,
    cur.symbol AS currencysymbol,
    c.reviser,
    c.apprvd,
    c.apprvl_dte,
    c.apprvr ,
    ip.label_id AS init_period_type_id,
    c.init_number,
    ib.label_id AS init_basis_type_id,
    rp.label_id AS retrng_period_type_id,
    c.retrng_number,
    rb.label_id AS retrng_basis_type_id ,
    c.cpnt_len,
    c.prep_len,
    c.max_enrl,
    c.min_enrl,
    c.credit_hrs,
    c.contact_hrs,
    c.cpe_hrs ,
    ds.seg_num,
    ds.day_num,
    ds.DURATION,
    lt.locn_typ_desc,
    de.eq_cnt,
    et.eq_typ_desc,
    qc.init_number AS q_init_number,
    qc.retrng_number AS q_retrng_number,
    qc.stat_effect_dte AS q_stat_effect_dte,
    q.qual_id AS q_qual_id,
    q.qual_title AS q_qual_title,
    qip.label_id AS q_init_period_type_id,
    qib.label_id AS q_init_basis_type_id,
    qrp.label_id AS q_retrng_period_type_id,
    qrb.label_id AS q_retrng_basis_type_id,
    s.subj_id,
    s.subj_desc,
    cdoc.review_flg,
    doc.doc_id,
    doc.doc_title,
    t.task_id,
    t.task_desc,
    cu.col_num,
    cu.user_value,
    uc.label,
    urc.user_desc,

    FROM pa_cpnt c
    left join pa_domain d on d.dmn_id = c.dmn_id
    left join pa_cpnt_src pcs on pcs.cpnt_src_id = c.cpnt_src_id
    left join pa_rqmt_type rt on rt.rtyp_id = c.rtyp_id
    left join pa_cpnt_formula cf on cf.cpnt_typ_id = c.cpnt_typ_id
    left join pa_currency cur on cur.currency_code = cf.currency_code
    left join ps_period_type ip on ip.period_type_id = c.init_period_type_id
    left join ps_basis_type ib on ib.basis_type_id = c.init_basis_type_id
    left join ps_period_type rp on rp.period_type_id = c.retrng_period_type_id
    left join ps_basis_type rb on rb.basis_type_id = c.retrng_basis_type_id
    left join pa_dflt_seg ds on ds.cpnt_typ_id = c.cpnt_typ_id
    left join pa_locn_type lt on lt.locn_typ_id = ds.locn_typ_id
    left join pa_dflt_eq de on de.cpnt_typ_id = ds.cpnt_typ_id
    left join pa_eq_type et on et.eq_typ_id = de.eq_typ_id
    left join pa_qual_cpnt qc on qc.cpnt_typ_id = c.cpnt_typ_id
    left join pa_qual q on q.qual_id = qc.qual_id
    left join ps_period_type qip on qip.period_type_id = qc.init_period_type_id
    left join ps_basis_type qib on qib.basis_type_id = qc.init_basis_type_id
    left join ps_period_type qrp on qrp.period_type_id = qc.retrng_period_type_id
    left join ps_basis_type qrb on qrb.basis_type_id = qc.retrng_basis_type_id
    left join pa_cpnt_subj cs on cs.cpnt_typ_id = c.cpnt_typ_id
    left join pa_subj s on s.subj_id = cs.subj_id
    left join pa_cpnt_doc cdoc on cdoc.cpnt_typ_id = c.cpnt_typ_id
    left join pa_doc doc on doc.doc_id = cdoc.doc_id
    left join pa_cpnt_task ct on ct.cpnt_typ_id = c.cpnt_typ_id
    left join pa_task t on ct.task_id = t.task_id
    Left join pa_cpnt_user cu on cu.cpnt_typ_id = c.cpnt_typ_id
    Left join pa_usrcl_cpnt uc on cu.col_num = uc.col_num
    Left join pa_usrrf_cpnt urc on cu.col_num = urc.col_num

    WHERE
    cf.cpnt_id = c.cpnt_id
    AND cf.rev_dte = c.rev_dte
    AND cf.fin_var_id = 'ItemDefaultPublishedPrice'
    AND ds.cpnt_id = c.cpnt_id
    AND ds.rev_dte = c.rev_dte
    AND de.cpnt_id = ds.cpnt_id
    AND de.rev_dte = ds.rev_dte
    AND de.seg_num = ds.seg_num
    AND qc.cpnt_id = c.cpnt_id
    AND qc.rev_dte = c.rev_dte
    AND cs.cpnt_id = c.cpnt_id
    AND cs.rev_dte = c.rev_dte
    AND cdoc.cpnt_id = c.cpnt_id
    AND cdoc.rev_dte = c.rev_dte
    AND ct.cpnt_id = c.cpnt_id
    AND ct.rev_dte = c.rev_dte
    AND cu.cpnt_id = c.cpnt_id
    AND cu.rev_dte = c.rev_dte
    AND cu.user_value = urc.user_id