Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2011
    Posts
    3

    Question Unanswered: query help: additional join to Table residing on a different connection

    note: the topic that I posted was not described correctly. To clarify, all the tables referenced reside on a different connection and the "XREF" alias table resides on the current connection i'm running the query against.


    Hello~

    I have a query that runs just fine until I add a join to a table and an alias to one of the column references of "myResults". I highlight the offending code. I'm pretty new to SQL and cannot seem to pinpoint what is wrong. Any help would be appreciated.

    Thanks!


    with eidList as
    -- this provides the list of EIDs that are linked for SOURCE CODE 100
    (
    select eid as EID
    from
    (
    select
    entid.curentrecno as EID,
    --entid.memrecno as MEMRECNO,
    count (entid.curentrecno) as LinkCount
    from empi.mpi_entlink_id entid
    where curentrecno in
    (select entid1.curentrecno
    from empi.mpi_entlink_id entid1
    where entid1.srcrecno = '100') -- source code 100 = HTP
    group by entid.curentrecno --, entid.memrecno
    having count (entid.curentrecno) > 1 ) dataA
    --left outer join mpi_entlink_id meid
    --on (meid.curentrecno=dataA.eid and meid.srcrecno=100) --change to = 1 for unlinked
    ),
    myResults as
    (
    select-- *
    -- 'HTP', --||'|'||
    mem.memrecno,
    mem.memidnum,--,-- ||'|'||
    -- ent.memrecno,-- ||'|'||
    ent.eid as EIDA,-- ||'|'||
    ent.srcrecno,--||'|'||
    ent.srcrecno ||'|'|| mem.memidnum as DATA
    --'Linked' --changed as necessary
    -- as data
    from
    (
    select
    mpi_entlink_id.curentrecno as eid,
    mpi_entlink_id.srcrecno,
    mpi_entlink_id.memrecno
    from empi.mpi_entlink_id
    inner join eidList
    on(mpi_entlink_id.curentrecno = eidList.eid)
    -- check if non-survivor y.memrecno = x.curentrecno
    -- on(y.curentrecno = eidList.curentrecno and y.memrecno = eidList.curentrecno)
    ) ent
    inner join empi.mpi_memhead mem
    on(ent.memrecno = mem.memrecno and mem.memstat = 'A') -- source code 100 = HTP
    --- on(ent.memrecno = mem.memrecno and mem.memstat = 'A' and mem.srcrecno = 100) -- source code 100 = HTP
    LEFT OUTER JOIN empi.mpi_entlink_id meid2 on (meid2.curentrecno=ent.eid and meid2.srcrecno=100)
    )

    LEFT OUTER JOIN htpn_xref_mrn_081711 XREF on(XREF.ECW_MRN=myResults.ecw_mrn)
    )

    select myResults.EIDA,myResults.srcrecno,myResults.memidn um [COLOR="rgb(139, 0, 0)"]as ecw_mrn[/COLOR], listagg(B.DATA,'|') within group ( order by myResults.EIDA) as data
    from myResults
    left outer join myResults B on (B.EIDA=myResults.EIDA)
    where B.srcrecno<>'100' and myResults.srcrecno='100'
    group by myResults.EIDA,myResults.srcrecno,myResults.memidn um

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Since we don't have your table or data, we can't begin to run posted SQL.
    Since we can't run SQL & you decided we did not need to see actual error,
    I can not begin to guess what is wrong.
    Do you have an error of omission or commission?
    I have no idea.
    In the future please indent & FORMAT code to increase readibility
    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
    Sep 2011
    Posts
    3
    Quote Originally Posted by anacedent View Post
    Since we don't have your table or data, we can't begin to run posted SQL.
    Since we can't run SQL & you decided we did not need to see actual error,
    I can not begin to guess what is wrong.
    Do you have an error of omission or commission?
    I have no idea.
    In the future please indent & FORMAT code to increase readibility
    My apologies...please bear with me. I just copied and pasted from my notepad editor.

    Below is the error when running it in SQL:

    ORA-00928: missing SELECT keyword
    00928. 00000 - "missing SELECT keyword"
    *Cause:
    *Action:
    Error at Line: 40 Column: 8

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    use <code_tags> is explained in #1 STICKY post (URL below)
    http://www.dbforums.com/oracle/10316...s-posters.html

    >Error at Line: 40 Column: 8
    since I have NO idea which is line 40, this is duly underwhelming.
    Also realize that at time the actual line in error may be slightly above/before the indicated line number.
    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.

  5. #5
    Join Date
    Sep 2011
    Posts
    3
    Quote Originally Posted by anacedent View Post
    use <code_tags> is explained in #1 STICKY post (URL below)
    http://www.dbforums.com/oracle/10316...s-posters.html

    >Error at Line: 40 Column: 8
    since I have NO idea which is line 40, this is duly underwhelming.
    Also realize that at time the actual line in error may be slightly above/before the indicated line number.
    This is the reference line the SQL DEVELOPER is reporting:

    ====>>>> inner join eidList

Posting Permissions

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