I am trying to union results from 2 tables that return uneven columns.
I am missing exit_date, school_year and reason from the 2nd results. and filling in with '' as column_name or null as column_name isn't working.
What would be the syntax?
Error Message: [HY000][-20081] [DataDirect][OpenEdge JDBC Driver][OpenEdge] No of expressions projected on either side of set-op don't match (7582)
select "student_id,"student_tags_type","entry_date","exit_date","school_year","exit_reason"
FROM
((SELECT
TO_CHAR(sc."student-id") as "student_id",
TO_CHAR(sc."rec_type") as "student_tags_type",
TO_CHAR(sc."str-date",'YYYY-MM-DD') as "entry_date",
TO_CHAR(sc."end-date",'YYYY-MM-DD') as "exit_date",
TO_CHAR(sc."dec-2") as "school_year",
TO_CHAR(sc."char-2") as "exit_reason"
FROM dbo."STUDENT-CLASSIFICATIONS" sc)
UNION
(SELECT
TO_CHAR(t."student-id") as "student_id",
TO_CHAR(lc."lun_code_type") as "student_tags_type",
TO_CHAR(t."effective_date",'YYYY-MM-DD') as "entry_date"
FROM dbo."TRANS" t
JOIN dbo."LUN-CODE" lc ON t."LUN-CODE-ID" = lc."LUN-CODE-ID"
WHERE lc."LUN-CODE-TYPE" in ('F','P','N')))
When I add: '' in the 2nd query for exit_date, school_year and exit_reason.
Or if I add: "" as "exit_date","" as "school_year","" as "exit_reason"
I get the following error message: [42000][-20003] [DataDirect][OpenEdge JDBC Driver][OpenEdge] Syntax error (7587)
I am missing exit_date, school_year and reason from the 2nd results. and filling in with '' as column_name or null as column_name isn't working.
What would be the syntax?
Error Message: [HY000][-20081] [DataDirect][OpenEdge JDBC Driver][OpenEdge] No of expressions projected on either side of set-op don't match (7582)
select "student_id,"student_tags_type","entry_date","exit_date","school_year","exit_reason"
FROM
((SELECT
TO_CHAR(sc."student-id") as "student_id",
TO_CHAR(sc."rec_type") as "student_tags_type",
TO_CHAR(sc."str-date",'YYYY-MM-DD') as "entry_date",
TO_CHAR(sc."end-date",'YYYY-MM-DD') as "exit_date",
TO_CHAR(sc."dec-2") as "school_year",
TO_CHAR(sc."char-2") as "exit_reason"
FROM dbo."STUDENT-CLASSIFICATIONS" sc)
UNION
(SELECT
TO_CHAR(t."student-id") as "student_id",
TO_CHAR(lc."lun_code_type") as "student_tags_type",
TO_CHAR(t."effective_date",'YYYY-MM-DD') as "entry_date"
FROM dbo."TRANS" t
JOIN dbo."LUN-CODE" lc ON t."LUN-CODE-ID" = lc."LUN-CODE-ID"
WHERE lc."LUN-CODE-TYPE" in ('F','P','N')))
When I add: '' in the 2nd query for exit_date, school_year and exit_reason.
Or if I add: "" as "exit_date","" as "school_year","" as "exit_reason"
I get the following error message: [42000][-20003] [DataDirect][OpenEdge JDBC Driver][OpenEdge] Syntax error (7587)
Last edited: