When I try to punsih a result set as View or Snapshot I get the following Error: Column scbcrse_eff_term is ambiguosly specified. If an alias is used for the source table of this column, please use that, or otherwise create one for it. Publishing a dataset or script using the same SQL statement to gerenaret/refresh that dataset, or report do work.
The offending column/field is Saturn.scbcrse.scbcrse_eff_term that is not visible but has the follwing custom filter:
(select max(scbcrse_eff_term) from saturn.scbcrse xppd
xppd.scbcrse_subj_code = scbcrse.scbcrse_subj_code
xppd.scbcrse_crse_numb = scbcrse.scbcrse_crse_numb and xppd.scbcrse_eff_term <=ssbsect.ssbsect_term_code
Basically what I try to accomplish is for each “term” I want the highest “eff_term” that is equal to or less than that “term”.
Is there a way to rephrase this custom filter that will allow to generate a view or snapshot?