asked on

using subquery to get the max(date)

what am i doing wrong? This is supposed to be easy....i am trying to select all the fields of a subquery that has the max(date):

select * fom currency_exchange where effective_date = (select max(effective_date) from currency_exchange)

the table looks like this right now:

2 GBP 2004-01-01 00:00:00.000 1.80000000 .55555556
4 GBP 2006-01-01 00:00:00.000 1.70000000 .58823529

thanks

ASKER CERTIFIED SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

lytung818

ASKER

there is one more problem: that statement works fine stand alone but when its under another subquery it doesnt, i get this error:

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
here is my whole query:

select
case
      when cl.product_code like 'G%' then 'Grinder'
      when cl.product_code like 'C%' then 'Channel Monster'
      when cl.product_code like 'A%' then 'Auger'
      when cl.product_code like 'E%' then 'Eng System'
      when cl.product_code like 'S%' then 'Service'
      when cl.product_code like 'W%' then 'Warranty'
      when cl.product_code like 'M%' then 'Screens'
      else 'Other' end as product_family,
line_no,
cl.cust_order_id, cl.part_id, cl.misc_reference, cl.product_code
, cl.unit_price * cl.order_qty as ext_unit_price
, cl.trade_disc_percent, cl.order_qty
, (cl.unit_price - (cl.unit_price * (cl.trade_disc_percent/100))) * cl.order_qty as ext_price
,(cl.unit_price * (cl.trade_disc_percent/100)) as disc_dollars
,(cl.unit_price * (cl.trade_disc_percent/100)) * cl.order_qty as ext_disc_dollars
, (cl.unit_price - (cl.unit_price * (cl.trade_disc_percent/100))) as net_dollars
, c.currency_id, case when c.currency_id = 'USD' then 1 else (select * from currency_exchange where effective_date = (select max(effective_date) from currency_exchange ce2
where ce2.currency_id ='GBP')) end as conversion_rate
, c.order_date, c.status, cl.service_charge_id, cl.last_shipped_date, a.acct_period, c.territory, cu.name as cust_name, c.entity_id, cu.id as cust_id,
CASE
      when cl.desired_ship_date is null then c.desired_ship_date
      else cl.desired_ship_date
END as desired_ship_date
from cust_order_line cl join customer_order c on cl.cust_order_id = c.id
left outer join inventory_trans i on cl.cust_order_id = i.cust_order_id AND cl.line_no = i.cust_order_line_no
left outer join account_period a on cl.last_shipped_date between a.begin_date and a.end_date
join customer cu on c.customer_id = cu.id
WHERE cl.last_shipped_date is null and ((cl.desired_ship_date < getdate()) or (c.desired_ship_date < getdate()))
and c.status not in ('c','x')

Scott Pletcher

You cannot use "SELECT *" in a subquery in a SELECT list. Instead you would have to do a join to the table in question. For example:

SELECT ...,
ceData.*,
...
FROM cust_order_line cl join customer_order c on cl.cust_order_id = c.id
left outer join inventory_trans i on cl.cust_order_id = i.cust_order_id AND cl.line_no = i.cust_order_line_no
left outer join account_period a on cl.last_shipped_date between a.begin_date and a.end_date
join customer cu on c.customer_id = cu.id
left outer join (
select currency_type, max(effective_date) AS effective_date
FROM currency_exchange
--WHERE currency_type = 'GBP' --if 'GBP' is only one needed
group by currency_type
) AS ceMax ON ceMax.currency_type = c.currency_id
left outer join currency_exchange ceData ON ceData.currency_type = ceMax.currency_type AND ceData.effective_date = ceMax.effective_date
WHERE cl.last_shipped_date is null and ((cl.desired_ship_date < getdate()) or (c.desired_ship_date < getdate()))
and c.status not in ('c','x')

lytung818

ASKER

never mind, i found the problem. I shouldn't use select *, instead is select sell_Rate.