Post reply

multi-part identifier problem (better explanation?)

  • March 9, 2006 at 9:30 am

    #365465

    I am trying to query a database with 3 tables:

    PERSON(PERSONID, BIRTHDATE, SEX)

    PROCS(PATID, DATE, CPTCODE, DIAGID1, DIAGID2, DIAGID3, DIAGID4)

    DIAGS(DIAGID, PATID, DATE, ICDCODE)

    DIAGID1, DIAGID2, DIAGID3, DIAGID4 are identified in Table DIAGS by the DIAGID field.

    The following query gives me most of what I want except where I have "PROCS.DIAGIDx" I want to pull the ICDCODE value from the DIAGS Table.

    SELECT PERSON.CHARTNUM, PERSON.BIRTHDATE, PERSON.SEX, PROCS.STARTDATE, PROCS.DIAGID1, PROCS.CPTCODE, PROCS.DIAGID2, PROCS.DIAGID3, PROCS.DIAGID4

    FROM PERSON, PROCS

    WHERE PERSON.PERSONID = PROCS.PATID;

    When I try to substitute DIAGS.IDCODE WHERE PROCS.DIAGIDx = DIAGS.DIAGID, I get an error message that "multi-part identifier PROCS.DIAGIDx cannot be bound." Anyone know how I can make this query work?

    Thanks, Steve

  • March 9, 2006 at 10:07 am

    #625747

    Steve;

    The BEST approach would be to redesign your Procs table to eliminate the repeating DiagID field. 

    PROCS(ID, PATID, DATE, CPTCODE, DIAGID)

    This is accepted best practice (normalization) and will make your query much easier.  If you need to have multiple diagid columns "across" a row in a query, you can lookup how to do a crosstab query.

    Then your query just becomes:

    SELECT PERSON.CHARTNUM, PERSON.BIRTHDATE, PERSON.SEX, 
PROCS.STARTDATE, PROCS.DIAGID, PROCS.CPTCODE, DIAGS.ICDCODE
FROM PERSON INNER JOIN PROCS ON PERSON.PERSONID = PROCS.PATID
INNER JOIN DIAGS ON PROCS.DIAGID = DIAGS.DIAGID

    If you have to keep the table as is, you will have to do something like the following (and assumming you want all 4 icdcodes separate):

    SELECT PERSON.CHARTNUM, PERSON.BIRTHDATE, PERSON.SEX, 
PROCS.STARTDATE, PROCS.CPTCODE, 
PROCS.DIAGID1, (SELECT DIAGS.ICDCODE FROM DIAGS WHERE DIAGS.DIAGID = PROCS.DIAGID1) as ICDCODE1, 
PROCS.DIAGID2, (SELECT DIAGS.ICDCODE FROM DIAGS WHERE DIAGS.DIAGID = PROCS.DIAGID2) as ICDCODE2, 
PROCS.DIAGID3, (SELECT DIAGS.ICDCODE FROM DIAGS WHERE DIAGS.DIAGID = PROCS.DIAGID3) as ICDCODE3, 
PROCS.DIAGID4, (SELECT DIAGS.ICDCODE FROM DIAGS WHERE DIAGS.DIAGID = PROCS.DIAGID4) as ICDCODE4, 
FROM PERSON INNER JOIN PROCS ON PERSON.PERSONID = PROCS.PATID

    Hope this helps



    Mark

  • March 9, 2006 at 10:28 am

    #625756

    Mark, You da man! The latter solution gave me just what I need. Thanks so much. The data are not mine, so I didn't want to change the tables.

    Thanks again.

    Steve

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply