I'm getting an "single-row subquery returns more than one row" error when running my routine. Looking through the table I'm querying, it is listing a date twice for some reason. Since I'm not able to remove this one row, is there a way to return the first instance of the date? If so, how?
I'm using a case statement where the routine is crashing. It reads as:
case when a01.COUNTRY_NAME in ('CANADA') then a01.REV_TY * (select CADRATE.CANADIAN_EXCHANGE.CONV_RATE from CADRATE.CANADIAN_EXCHANGE where a01.STAY_DATE = CADRATE.CANADIAN_EXCHANGE.CONV_DATE) else a01.REV_TY end as CONV_REV_TY,
FYI: The CADRATE.CANADIAN_EXCHANGE has two instances of the March 1, 2015 date.
Are there thoughts on how to do this?
Thanks for your help.