single-row subquery returns more than one row

[Papa_Don]

Hi group,

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.

Don

[pritaeas]

Am not sure about Oracle but SQL Server uses TOP 1. MySQL has LIMIT 1. Firebird has FIRST 1. If that doesn't work maybe DISTINCT can help.

[Papa_Don]

As it turns out, I had a table listed twice which may have been causing the issue. I took it out, change to a different table and it worked correctly. Here's the final code:

case
          WHEN EXTRACT(YEAR from a19.FULL_DATE) IN ('2015') and a15.COUNTRY_NAME in ('CANADA')
          then sum((A11.RM_NT_QTY * A11.RM_RATE_USD_AMT) * (SELECT  d_cur_exchng.cur_exchng_from_usd_rate
                                                            FROM    d_cur_exchng,
                                                                    crmmart.d_date_period
                                                            WHERE   d_cur_exchng.date_key = a11.STAY_DATE_KEY  and
                                                                    crmmart.d_date_period.full_date = a19.full_date and
                                                                    cur_key = 113 and 
                                                                    rcrd_sts_cd = 'A'))