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. Donnie28

    Donnie28 Active Member

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

    I am running the following query below but getting the error "A Table maybe joined to at most one other table" error message. The error has just happened when I added the last line of code
    AND stud.org_id = po.org_id(+) but I need these 2 tables to be joined by that column. Is there any way around this?

    Any help on this would be very much appreciated

    Regards
    Donal




    SELECT DISTINCT c.cpnt_typ_id,
    c.cpnt_id,
    c.rev_dte,
    c.cpnt_title,
    c.cpe_hrs,
    d.dmn_id,
    d.dmn_desc,
    cs.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,
    pa_domain d,
    pa_subj s,
    pa_cpnt_src cs,
    pa_rqmt_type rt ,
    pa_cpnt_formula cf,
    pa_currency cur ,
    ps_period_type ip,
    ps_basis_type ib,
    ps_period_type rp,
    ps_basis_type rb ,
    pa_dflt_seg ds,
    pa_locn_type lt,
    pa_dflt_eq de,
    pa_eq_type et ,
    pa_qual_cpnt qc,
    pa_qual q,
    ps_period_type qip,
    ps_basis_type qib,
    ps_period_type qrp,
    ps_basis_type qrb ,
    pa_cpnt_subj cs ,
    pa_doc doc,
    pa_cpnt_doc cdoc,
    pa_task t,
    pa_cpnt_task ct ,
    pa_cpnt_user cu,
    pa_usrrf_cpnt urc,
    pa_usrcl_cpnt uc,
    pa_inst pi,
    pa_student stud,
    pa_org po

    WHERE c.dmn_id = d.dmn_id(+)
    AND c.cpnt_src_id = cs.cpnt_src_id(+)
    AND c.rtyp_id = rt.rtyp_id(+)
    /** and (c.cpnt_typ_id, c.cpnt_id, c.rev_dte) in [ItemSearch] */
    /** and [security:pa_cpnt c] */

    AND 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'
    AND cf.currency_code = cur.currency_code(+)
    AND ip.period_type_id(+) = c.init_period_type_id
    AND ib.basis_type_id(+) = c.init_basis_type_id
    AND rp.period_type_id(+) = c.retrng_period_type_id
    AND rb.basis_type_id(+) = c.retrng_basis_type_id
    AND ds.cpnt_typ_id(+) = c.cpnt_typ_id
    AND ds.cpnt_id(+) = c.cpnt_id
    AND ds.rev_dte(+) = c.rev_dte
    AND de.cpnt_typ_id(+) = ds.cpnt_typ_id
    AND de.cpnt_id(+) = ds.cpnt_id
    AND de.rev_dte(+) = ds.rev_dte
    AND ds.seg_num = de.seg_num(+)
    AND ds.locn_typ_id = lt.locn_typ_id(+)
    AND de.eq_typ_id = et.eq_typ_id(+)
    AND qc.qual_id = q.qual_id(+)
    AND qc.cpnt_typ_id(+) = c.cpnt_typ_id
    AND qc.cpnt_id(+) = c.cpnt_id
    AND qc.rev_dte(+) = c.rev_dte
    AND qip.period_type_id(+) = qc.init_period_type_id
    AND qib.basis_type_id(+) = qc.init_basis_type_id
    AND qrp.period_type_id(+) = qc.retrng_period_type_id
    AND qrb.basis_type_id(+) = qc.retrng_basis_type_id
    AND cs.cpnt_typ_id(+) = c.cpnt_typ_id
    AND cs.cpnt_id(+) = c.cpnt_id
    AND cs.rev_dte(+) = c.rev_dte
    AND cs.subj_id = s.subj_id(+)
    AND cdoc.cpnt_typ_id(+) = c.cpnt_typ_id
    AND cdoc.cpnt_id(+) = c.cpnt_id
    AND cdoc.rev_dte(+) = c.rev_dte
    AND cdoc.doc_id = doc.doc_id(+)
    AND ct.cpnt_typ_id(+) = c.cpnt_typ_id
    AND ct.cpnt_id(+) = c.cpnt_id
    AND ct.rev_dte(+) = c.rev_dte
    AND ct.task_id = t.task_id(+)
    AND cu.cpnt_typ_id (+) = c.cpnt_typ_id
    AND cu.cpnt_id (+) = c.cpnt_id
    AND cu.rev_dte (+) = c.rev_dte
    AND cu.col_num = uc.col_num (+)
    AND cu.col_num = urc.col_num(+)
    AND cu.user_value = urc.user_id(+)
    AND c.dmn_id = pi.dmn_id(+)
    AND pi.org_id = stud.org_id(+)
    AND pi.org_id = po.org_id(+)
    AND stud.org_id = po.org_id(+)
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Hi.
    Rewrite your query using ansi-syntax.

    Pay attention, you in request have repeating aliases of tables :

     
  3. Donnie28

    Donnie28 Active Member

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

    Thanks for the reply. I have added the line left join pa_student stud on stud.org_id = po.org_id as you suggested just before the where clause and commented out the last line /*AND po.org_id(+) = stud.org_id(+)*/ but it gives me a "Old style outter join(+) cannot be used with ANSI joins" error. does this mean I have to change all the joins to the new format? Is this a big operation?

    Regards
    Donal


    SELECT DISTINCT c.cpnt_typ_id,
    c.cpnt_id,
    c.rev_dte,
    c.cpnt_title,
    c.cpe_hrs,
    d.dmn_id,
    d.dmn_desc,
    cs.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,
    pa_domain d,
    pa_subj s,
    pa_cpnt_src cs,
    pa_rqmt_type rt ,
    pa_cpnt_formula cf,
    pa_currency cur ,
    ps_period_type ip,
    ps_basis_type ib,
    ps_period_type rp,
    ps_basis_type rb ,
    pa_dflt_seg ds,
    pa_locn_type lt,
    pa_dflt_eq de,
    pa_eq_type et ,
    pa_qual_cpnt qc,
    pa_qual q,
    ps_period_type qip,
    ps_basis_type qib,
    ps_period_type qrp,
    ps_basis_type qrb ,
    pa_cpnt_subj cs ,
    pa_doc doc,
    pa_cpnt_doc cdoc,
    pa_task t,
    pa_cpnt_task ct ,
    pa_cpnt_user cu,
    pa_usrrf_cpnt urc,
    pa_usrcl_cpnt uc,
    pa_inst pi,
    pa_student stud,
    pa_org po

    left join pa_student stud on stud.org_id = po.org_id

    WHERE c.dmn_id = d.dmn_id(+)
    AND c.cpnt_src_id = cs.cpnt_src_id(+)
    AND c.rtyp_id = rt.rtyp_id(+)
    /** and (c.cpnt_typ_id, c.cpnt_id, c.rev_dte) in [ItemSearch] */
    /** and [security:pa_cpnt c] */

    AND 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'
    AND cf.currency_code = cur.currency_code(+)
    AND ip.period_type_id(+) = c.init_period_type_id
    AND ib.basis_type_id(+) = c.init_basis_type_id
    AND rp.period_type_id(+) = c.retrng_period_type_id
    AND rb.basis_type_id(+) = c.retrng_basis_type_id
    AND ds.cpnt_typ_id(+) = c.cpnt_typ_id
    AND ds.cpnt_id(+) = c.cpnt_id
    AND ds.rev_dte(+) = c.rev_dte
    AND de.cpnt_typ_id(+) = ds.cpnt_typ_id
    AND de.cpnt_id(+) = ds.cpnt_id
    AND de.rev_dte(+) = ds.rev_dte
    AND ds.seg_num = de.seg_num(+)
    AND ds.locn_typ_id = lt.locn_typ_id(+)
    AND de.eq_typ_id = et.eq_typ_id(+)
    AND qc.qual_id = q.qual_id(+)
    AND qc.cpnt_typ_id(+) = c.cpnt_typ_id
    AND qc.cpnt_id(+) = c.cpnt_id
    AND qc.rev_dte(+) = c.rev_dte
    AND qip.period_type_id(+) = qc.init_period_type_id
    AND qib.basis_type_id(+) = qc.init_basis_type_id
    AND qrp.period_type_id(+) = qc.retrng_period_type_id
    AND qrb.basis_type_id(+) = qc.retrng_basis_type_id
    AND cs.cpnt_typ_id(+) = c.cpnt_typ_id
    AND cs.cpnt_id(+) = c.cpnt_id
    AND cs.rev_dte(+) = c.rev_dte
    AND cs.subj_id = s.subj_id(+)
    AND cdoc.cpnt_typ_id(+) = c.cpnt_typ_id
    AND cdoc.cpnt_id(+) = c.cpnt_id
    AND cdoc.rev_dte(+) = c.rev_dte
    AND cdoc.doc_id = doc.doc_id(+)
    AND ct.cpnt_typ_id(+) = c.cpnt_typ_id
    AND ct.cpnt_id(+) = c.cpnt_id
    AND ct.rev_dte(+) = c.rev_dte
    AND ct.task_id = t.task_id(+)
    AND cu.cpnt_typ_id (+) = c.cpnt_typ_id
    AND cu.cpnt_id (+) = c.cpnt_id
    AND cu.rev_dte (+) = c.rev_dte
    AND cu.col_num = uc.col_num (+)
    AND cu.col_num = urc.col_num(+)
    AND cu.user_value = urc.user_id(+)
    AND c.dmn_id = pi.dmn_id(+)
    AND pi.org_id = stud.org_id(+)
    AND pi.org_id = po.org_id(+)
    /*AND po.org_id(+) = stud.org_id(+)*/
     
  4. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    I said that it is necessary to rewrite all request.

     
  5. Donnie28

    Donnie28 Active Member

    Messages:
    43
    Likes Received:
    1
    Trophy Points:
    80
    Okay I will keep an eye out for this, As I am quite new to PLSQL would you mind giving me a code example of how some of the tables are joined maybe for the first 3 tables pa_cpnt c,
    pa_domain d, pa_subj s, and I can then follow this example fo the rest?

    Thanks
    Donal
     
  6. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Partial example :
     
    Donnie28 likes this.
  7. Donnie28

    Donnie28 Active Member

    Messages:
    43
    Likes Received:
    1
    Trophy Points:
    80
    Thanks Sergey its much apprecaited :)
     
  8. DTSIGuy

    DTSIGuy Forum Advisor

    Messages:
    402
    Likes Received:
    58
    Trophy Points:
    410
    Location:
    Texas
    Was the reported error ORA-01417 ??
     
  9. DTSIGuy

    DTSIGuy Forum Advisor

    Messages:
    402
    Likes Received:
    58
    Trophy Points:
    410
    Location:
    Texas
  10. DTSIGuy

    DTSIGuy Forum Advisor

    Messages:
    402
    Likes Received:
    58
    Trophy Points:
    410
    Location:
    Texas
  11. Donnie28

    Donnie28 Active Member

    Messages:
    43
    Likes Received:
    1
    Trophy Points:
    80
    Hi DISIGuy thanks for he reply the error code is actuallt ORA-25156
     
  12. Donnie28

    Donnie28 Active Member

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

    I have changed al the joins to ANSI but am getting a ORA-00920 Invalid Relational Operator error

    Regards
    Donal

    SELECT DISTINCT c.cpnt_typ_id,
    c.cpnt_id,
    c.rev_dte,
    c.cpnt_title,
    c.cpe_hrs,
    d.dmn_id,
    d.dmn_desc,
    cs.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 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_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
    /** and (c.cpnt_typ_id, c.cpnt_id, c.rev_dte) in [ItemSearch] */
    /** and [security:pa_cpnt c] */
     
  13. DTSIGuy

    DTSIGuy Forum Advisor

    Messages:
    402
    Likes Received:
    58
    Trophy Points:
    410
    Location:
    Texas
    I meant the original error...that you're getting a 25156 at the moment is not surprising. I suspect the "CS" alias which Sergey pointed out you were using twice was the actual problem.

    CJ
     
  14. Donnie28

    Donnie28 Active Member

    Messages:
    43
    Likes Received:
    1
    Trophy Points:
    80
    I am a bit confused now.....so are you saying that the ORA-00920 Invalid Relational Operator error is due the the "CS" alias being used twice?
     
  15. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Pay attention, you in request have repeating aliases of tables :
     
  16. DTSIGuy

    DTSIGuy Forum Advisor

    Messages:
    402
    Likes Received:
    58
    Trophy Points:
    410
    Location:
    Texas
    Noooo. The presumption is the original post was prompted by an Oracle error which you never revealed. The post title closely resembles the Oracle error that I found but does not match exactly...so I wondered. If you rerun the original query and change one of the CS aliases to CS2 or something...it would be interesting to see if that fixed the original issue.

    Your current predicament w/ the 00920 has to be related to the further changes you have been making per suggestion by Sergey...and I'm not implying he's wrong either. That said, I believe you have to fix the issue of the CS alias being used twice. That can confuse to the Oracle engine and certainly confounds anyone who would have to maintain this monster in the future.

    In addition, it is not clear whether your all of your alias names follow some naming standard. If not, they should.

    HTH

    CJ
     
    Donnie28 likes this.
  17. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Hi,Donnie28.

    I wrote several times that at you in request are used repeating алиасы for tables :

     
  18. Donnie28

    Donnie28 Active Member

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

    I have correced the ailsing issue and changed the joins to ANSI but am still getting the ORA-00920 Invalid Relational Operator se code below

    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 [security:pa_cpnt c] */
     
  19. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Questions :

    1)
    This and (c.cpnt_typ_id, c.cpnt_id, c.rev_dte) AS and (c.cpnt_typ_id, c.cpnt_id, c.rev_dte) in ((a1,b1),(a2,b2)... etc) ?

    2)

    What is it ???:
     
  20. Donnie28

    Donnie28 Active Member

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

    The database I am working on is a online Human Resource Learning management system.

    pa_cpnt = Component table
    c.cpnt_typ_id = component type id
    c.cpnt_id, = component_id
    c.rev_dte = review_date

    pa_cpnt_user = Component User table( holds details of users of the components)

    pa_inst = Instructor table( holds details of tutors that teach the components)

    dmn_id = domain_id( an organisation label)

    org_id = organisation id( Cost Centre)

    stud_ id = student_id(students are users who take or complete the components)

    The ones you have highlighted in red above are aliases for the tables

    hope this makes things a little clearer

    Regards
    Donal