i have a query im doing to display records to oracle reports. the parameter i need as selection is the class_code but when user click on the dropdown value it's a bit messy for them(hard to explain here) so they ask to combine the study_intake and class_code as a word which would be clear to them.
now what i did is use AS for alias of a new column which combines the two, but i cant call it back (since it's not the right syntax)
here's a sample of my query:
SELECT DISTINCT STUDENT_ATTENDENTS.IC_PASSPORT,
STUDENT_ATTENDENTS.NAME,
STUDENT_ATTENDENTS.A_DATE,
STUDENT_ATTENDENTS.A_TYPE,
STUDENT_ATTENDENTS.ATTEN,
TUTORIAL_CLASSES_MASTER.LECTURER_NAME,
STUDENT_ATTENDENTS.COURSE_CODE,
STUDENT_ATTENDENTS.SUBJECT_CODE,
SUBJECT_MASTER_TABLE.CREDIT_POINT,
TUTORIAL_CLASSES_MASTER.STUDY_INTAKE || ' ' || STUDENT_ATTENDENTS.CLASS_CODE AS Study_Intake_Class_Code
FROM STUDENT_ATTENDENTS, TUTORIAL_CLASSES_MASTER, SUBJECT_MASTER_TABLE
WHERE Study_Intake_Class_Code = :CLASS_DODE
AND STUDENT_ATTENDENTS.SUBJECT_CODE = SUBJECT_MASTER_TABLE.SUBJECT_CODE
AND (STUDENT_ATTENDENTS.CLASS_CODE = TUTORIAL_CLASSES_MASTER.CLASS_CODE)
the line WHERE Study_Intake_Class_Code = :CLASS_DODE will return an error which is
ORA-00904: "STUDY_INTAKE_CLASS_CODE": invalid identifier
i cant use back the alias. an idea i came up with is to create a view but this made the report haywire and affecting all the formulas(i didnt create the original report)
how do i adjust this query to make it work? any ideas or other style of query?
Just put the original query part into there where instead of the alias name... i.e.,
WHERE TUTORIAL_CLASSES_MASTER.STUDY_INTAKE || ' ' || STUDENT_ATTENDENTS.CLASS_CODE = :CLASS_DODE