Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2012
    Posts
    16

    Unanswered: Help: Adding a column from another table

    I need to add a new COLUMN to this query which is in another database "rdn_fac_full". It should be able to INNER JOIN with table "pos_header" as both have the column .fac_idu

    The column I need to display from "rdn_fac_full" is "rdn_fac_full.fac_nm".

    I am getting an error when trying to add

    Select ....... rdn_fac_full.fac_nm
    Where .....
    INNER JOIN rdn_fac_full
    ON rdn_fac_full.fac_idu = pos_header.fac_idu


    to the query


    Any advice would be great!
    Last edited by SQLNoob8; 06-26-13 at 20:39.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Does every error have the same fix/solution?
    Might the answer depend upon the specific error you have?

    my car has an error.
    tell me how to make my car go.

    I don't know what you have.
    I don't know what you do.
    I don't know what you see.
    It is really, Really, REALLY difficult to fix a problem that can not be seen.
    use COPY & PASTE so we can see what you do & how Oracle responds.
    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
    Feb 2008
    Location
    Japan
    Posts
    3,483
    First of all.
    use COPY & PASTE so we can see what you do & how Oracle responds.
    But, at least INNER JOIN must be between FROM and WHERE keywords.
    Select ....... rdn_fac_full.fac_nm AS Sitename
    Where .....
    INNER JOIN rdn_fac_full
    ON rdn_fac_full.fac_idu = pos_ticket_header.fac_idu

  4. #4
    Join Date
    Jun 2012
    Posts
    16
    This is what I added:

    ... rdn_fac_full.fac_nm, count(pos_header),
    count(distinct(to_char(pos_ticket_header.audt_modi fy_dt,'MM-DD-YYYY'))) as NumDays_CashVoidNoReplace
    FROM pos_ticket_header pos_ticket_header, pos_ticket_detail pos_ticket_detail
    LEFT OUTER JOIN POS_MATERIAL POS_MAT ON POS_MATERIAL.MATERIAL_ID = POS_TICKET_DETAIL.MATERIAL_ID_INTRN
    LEFT OUTER JOIN POS_MATL_CAT POS_MAT_CAT ON POS_MAT_CAT.MATERIAL_CAT_ID = POS_MAT.MAT_ID,
    pos_customer_org pos_customer_org,
    INNER JOIN rdn_fac_full
    ON rdn_fac_full.fac_idu = pos_ticket_header.fac_idu





    and I get Error "ORA-0904:"POS_HEADER"."FAC_IDU": invalid idenifier"
    Last edited by SQLNoob8; 06-26-13 at 20:39.

  5. #5
    Join Date
    Jun 2012
    Posts
    16
    This query ran fine when adding the same statement for the extra column:
    Last edited by SQLNoob8; 06-26-13 at 20:34.

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    In ON conditions, you can't refer the columns in a table beyond comma.

    You may have (at least) two options.
    (1) Rewrite all joins with new join synax(i.e. INNER/LEFT OUTER/RIGHT OUTER/ JOIN)
    I want to recommend this way.

    (2) Move the join, like...
    FROM pos_ticket_header pos_ticket_header
    INNER JOIN rdn_fac_full
    ON rdn_fac_full.fac_idu = pos_ticket_header.fac_idu

    , pos_ticket_detail pos_ticket_detail
    LEFT OUTER JOIN POS_MATERIAL POS_MATERIAL ON POS_MATERIAL.MATERIAL_ID = POS_TICKET_DETAIL.MATERIAL_ID_INTRN
    LEFT OUTER JOIN POS_MATERIAL_CATEGORY POS_MATERIAL_CATEGORY ON POS_MATERIAL_CATEGORY.MATERIAL_CATEGORY_ID = POS_MATERIAL.MATERIAL_CATEGORY_ID,
    pos_customer_org pos_customer_org,
    pos_payment_method_lkp pos_payment_method_lkp,
    pos_uom_lkp pos_uom_lkp

  7. #7
    Join Date
    Jun 2012
    Posts
    16
    Thanks Tonkuma!

    I tried the 2nd option of just moving the statement and it works!

Posting Permissions

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