2 Replies Latest reply: Apr 5, 2011 9:09 AM by Phil King RSS

    Reference a table before field name with SQL

      Hi Everyone,

      I have two tables, with the same fields in both (I know what you are thinking, but I inherited this from someone else...!). I need to link them as they contain useable data in both tables. For instance SPO2Meas is the field name but I can't work out how to reference this to the first table PE29_AOCResults and not to PE29_AOCScores.

      In other words, how can I make it work like table1.fieldname as newfieldname

      hope this helps, here is the script.

       

       

       

      AOC:

      LOAD

       

       

       

       

       

       

       

      //SQL SELECT

      1

       

      PRFUniqueId as %KEY_PRFUID,

      SPO2Meas;

       

       

      SQL

       

       

      SELECT *

       

      //WHERE IncDate >= DateAdd(month,-48,GETDATE())

      ) prf

      ON

      aor.PRFUniqueId = prf.PRFUniqueId

      inner JOIN

      [TCMPRF].[dbo].[PE29_AOCSCores] aos

      ON

      aos.PRFUniqueId = aor.PRFUniqueId



      FROM TCMPRF.dbo."PE29_AOCResults" aor

      INNER JOIN

      (SELECT PRFUniqueId FROM TCMPRF.dbo."PE29_AOCResults"

      where IncDate between '01-Nov-2008' and getdate()



      as Counter,