CODEWITHSUNDEEP
sqloracle-databasealias
user518206
user518206

Reputation: 93

Using aliases leads to some invalid identifiers

I'm attempting to create a table that lists patient identifiers for instances where a patient with a primary tumour of interest (MCC_IDS.TUMOURID) has been diagnosed with another tumour (AV_TUMOUR.TUMOURID) up to six months prior to the tumour of interest.

The columns have the same names in both tables (MCC_IDS and AV_TUMOUR), so I've added some column aliases. SQL Developer doesn't seem to like this as I keep getting 'invalid identifier' errors.

I'm not sure what else to try. Help!

    CREATE TABLE multitumours_hes_baseline
    AS
        ( SELECT mcc_ids.patientid,
                 mcc_ids.tumourid,
                 av_tumour.tumourid AS other_tumourid,
                 av_tumour.diagnosisdatebest AS other_diagnosisdatebest,
                 av_tumour.site_icd10_o2_3char AS other_icd10,
                 av_tumour.morph_icd10_o2 AS other_morph
            FROM mcc_ids left
          JOIN av2016.av_tumour ON mcc_ids.patientid = av_tumour.patientid -- Joining AV_TUMOUR onto MCC_IDS by PATIENTID to ensure that we can count TUMOURIDs other than those contained in MCC_IDS
--WHERE (AV_TUMOUR.OTHER_DATE>'31-DEC-2012' AND AV_TUMOUR.OTHER_DATE<'01-JAN-2016')
           WHERE mcc_ids.tumourid != av_tumour.other_tumourid
           AND ( mcc_ids.diagnosisdatebest >= av_tumour.other_diagnosisdatebest ) -- Only those conditions that occur in HES before the date of cancer diagnosis
           AND ( mcc_ids.diagnosisdatebest - av_tumour.other_diagnosisdatebest ) <= 182.5 -- Only those conditions that occur up to six months prior to the date of diagnosis
           AND av_tumour.dedup_flag = 1
        );

Upvotes: 0

Views: 29

Answers (1)

Alex Poole
Alex Poole

Reputation: 191570

You can't refer to column aliases in the same level of query (except in an order-by clause), but you don't need to here.

You are doing:

WHERE MCC_IDS.TUMOURID!=AV_TUMOUR.OTHER_TUMOURID
AND (MCC_IDS.DIAGNOSISDATEBEST>=AV_TUMOUR.OTHER_DIAGNOSISDATEBEST)
AND (MCC_IDS.DIAGNOSISDATEBEST-AV_TUMOUR.OTHER_DIAGNOSISDATEBEST)<=182.5

The OTHER_ names are the column aliases in the result set, so even if you could use those they wouldn't exist as columns on the original table, which is what the AV_TUMOUR.OTHER_* is trying to find. Use the actual column names:

WHERE MCC_IDS.TUMOURID!=AV_TUMOUR.TUMOURID
AND MCC_IDS.DIAGNOSISDATEBEST>=AV_TUMOUR.DIAGNOSISDATEBEST
AND MCC_IDS.DIAGNOSISDATEBEST-AV_TUMOUR.DIAGNOSISDATEBEST<=182.5

And you can't refer to the column alias without the table prefix - i.e. WHERE MCC_IDS.TUMOURID!=OTHER_TUMOURID - because it is the same level of query, and because of how the query is parsed internally; from the docs:

c_alias
Specify an alias for the column expression. ... The alias effectively renames the select list item for the duration of the query. The alias can be used in the order_by_clause but not other clauses in the query.

Also, not really relevant, but to search back six months you could do:

LEFT JOIN AV_TUMOUR ON AV_TUMOUR.PATIENTID = MCC_IDS.PATIENTID
WHERE AV_TUMOUR.OTHER_TUMOURID != MCC_IDS.TUMOURID
AND AV_TUMOUR.OTHER_DIAGNOSISDATEBEST >= ADD_MONTHS(MCC_IDS.DIAGNOSISDATEBEST, -6)
AND AV_TUMOUR.DEDUP_FLAG=1

You are using a left outer join, but because you have conditions in the WHERE clause as well that is effectively turning it back into an inner join. Unless you want to see MCC_IDS with don't have another tumour in that period, you can remove 'LEFT':

JOIN AV_TUMOUR ON AV_TUMOUR.PATIENTID = MCC_IDS.PATIENTID
WHERE AV_TUMOUR.OTHER_TUMOURID != MCC_IDS.TUMOURID
AND AV_TUMOUR.OTHER_DIAGNOSISDATEBEST >= ADD_MONTHS(MCC_IDS.DIAGNOSISDATEBEST, -6)
AND AV_TUMOUR.DEDUP_FLAG=1

If you do want to see those, which seems unlikely from your description, then make all the conditions part of the ON:

LEFT JOIN AV_TUMOUR ON AV_TUMOUR.PATIENTID = MCC_IDS.PATIENTID
AND AV_TUMOUR.OTHER_TUMOURID != MCC_IDS.TUMOURID
AND AV_TUMOUR.OTHER_DIAGNOSISDATEBEST >= ADD_MONTHS(MCC_IDS.DIAGNOSISDATEBEST, -6)
AND AV_TUMOUR.DEDUP_FLAG=1

Upvotes: 3

Related Questions