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

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.

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'))
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.