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

Helen Pippard · #1 (**permalink**) 01-17-12, 07:22

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

n_i · #2 (**permalink**) 01-17-12, 09:21

It appears that you have two FROM clauses in your statement, which is incorrect - only one FROM should be there.

tonkuma · #3 (**permalink**) 01-17-12, 10:27

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')
;

anacedent · #4 (**permalink**) 01-17-12, 10:48

>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?

tonkuma · #5 (**permalink**) 01-17-12, 12:52

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?

Internet Services

Web Development

Digital Marketing

Consumer Tech