Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2011
    Posts
    82

    Unanswered: How do I reference a field from a table without a link

    Hello

    Could someone help me with a problem I have.

    I have a table which I have named rb. rb has a field which needs to be inlcuded in select statement but has no unique identifier to link into other tables.

    How do I get the field to appear without any unique identifiers, and I wanto be able to use the term "left outer join" for all other tables

    Any ideas where I can classify the rb table:

    SELECT
    TO_CHAR(ADD_MONTHS(eoa.Date_seen,-3),'YYYY') AS YEAR,
    TO_CHAR(ADD_MONTHS(eoa.Date_seen,-3),'MM') AS MONTH,
    eoa.TREATMENT_FUNC AS Treatment_Function,
    eoa.Local_Subspecialty AS Local_Specialty,
    ee.DOCTOR_NO AS cons_code,
    rb.Code AS Agegroup

    FROM
    Op_Attendance eoa
    left outer join Enc_Epi ee on
    eoa.Enc_No = ee.Enc_No
    and eoa.Episode_No = ee.Episode_No

    WHERE eoa.Date_Seen BETWEEN to_date ('01/11/2010','DD/MM/YYYY')
    AND to_date ('30/11/2010','DD/MM/YYYY')
    AND FLOOR((eoa.Date_Seen - ee.Date_Of_Birth)/365.25) BETWEEN rb.Low_Value
    AND rb.High_Value
    AND rb.Domain = 'CIMTAGE2'

    FROM(SELECT
    Code
    FROM cfis_data.Ref_Bands) rb

    GROUP BY
    TO_CHAR(ADD_MONTHS(eoa.Date_seen,-3),'YYYY'),
    TO_CHAR(ADD_MONTHS(eoa.Date_seen,-3),'MM'),
    ee.DOCTOR_NO,
    rb.Code
    Anyone able to help me?

    Kind regards

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    It appears that you have two FROM clauses in your statement, which is incorrect - only one FROM should be there.

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    This might be one possibility.

    But, it might be completely wrong(not meet with your requirement),
    because of your too little published information.
    At least, sample/test data of three tables(with enough number of rows)
    and expected result from the data should be provided.

    Example 1: one possibility. might be completely wrong.
    Code:
    SELECT TO_CHAR(ADD_MONTHS(eoa.Date_seen , -3) , 'YYYY') AS YEAR
         , TO_CHAR(ADD_MONTHS(eoa.Date_seen , -3) , 'MM'  ) AS MONTH
         , eoa.TREATMENT_FUNC      AS Treatment_Function
         , eoa.Local_Subspecialty  AS Local_Specialty
         , ee .DOCTOR_NO           AS cons_code
         , rb .Code                AS Agegroup 
     FROM  Op_Attendance         eoa
     LEFT  OUTER JOIN
           Enc_Epi               ee
       ON  ee .Enc_No     = eoa.Enc_No
       AND ee .Episode_No = eoa.Episode_No
     LEFT  OUTER JOIN
           cfis_data.Ref_Bands   rb
       ON  FLOOR( (eoa.Date_Seen - ee.Date_Of_Birth) / 365.25 )
              BETWEEN rb.Low_Value
                  AND rb.High_Value
       AND rb .Domain = 'CIMTAGE2' 
     WHERE eoa.Date_Seen
              BETWEEN to_date('01/11/2010' , 'DD/MM/YYYY')
                  AND to_date('30/11/2010' , 'DD/MM/YYYY')
    ;

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >I have a table which I have named rb. rb has a field which needs to be inlcuded in select statement but has no unique identifier to link into other tables.

    I assume that table RB has more than 1 row.
    Which single row is supposed to be returned by SELECT?
    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
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Helen,

    Please try my Example 1 and let me know the result.

    Did you got error message(s)?
    or the result was diffrent from your requirement?

Posting Permissions

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