Select and min() problem 2

[dickiebird]

Hi
I'm working in Sybase 12.0
I have two tables, MemberPromotion has 5 test cardnumbers.
Transactions has several rows, of varying Trandate for the same 5 test cardnumbers
I need to find the earliest Trandate row on Transactions for each cardnumber.

select *
from Transactions lct, MemberPromotion ba
where ba.CardNumber = lct.CardNumber
and ba.PointsAwarded =0
and lct.SettlementCurrency = "GBP"
and lct.TranDate = (select min(lct.TranDate)
from Transactions lct, MemberPromotion ba
where ba.CardNumber = lct.CardNumber
and ba.PointsAwarded =0
and lct.SettlementCurrency = "GBP&quot

This only returns one row, the one with the min(Trandate) from the table.
In my test table MemberPromotion, I expect all 5 rows to match and display
I need the min(Trandate) for each cardnumber that exists on both tables.
Where have I gone wrong ?
Any thoughts ?
TIA


Dickie Bird (-)))

 

[r937]

you need to make it a correlated subquery

i.e. the subquery has to refer to a row in the outer query

[tt]select *
from Transactions lct
, MemberPromotion ba
where ba.CardNumber = lct.CardNumber
and ba.PointsAwarded =0
and lct.SettlementCurrency = 'GBP'
and lct.TranDate
= ( select min(TranDate)
from Transactions
where CardNumber = lct.CardNumber
and SettlementCurrency = 'GBP'
) [/tt]

rudy

http://r937.com/

SQL Consulting

 

[dickiebird]

Of Course - how silly of me !
Thanks Rudy - Have a star !

Dickie Bird (-)))

 

[dickiebird]

Hi Rudy ( or anyone else that's got an idea )
Another problem's occurred - that perhaps you can help with
Those 5 rows are now found OK - thanks, but I want to insert new rows based on those found rows. I am trying to
increment TransactionID as below - but it returns
'Duplicate row error'
(I assume it's that that is the problem - TransactionID is part of primary key, along with a timestamp and an identity column)
insert Transactions(
TransactionID,
TillID,
SiteID,
CardNumber,
SettlementValue,
SettlementCurrency,
DateImported,
select
1 + (select max(TransactionID) from Transactions where TransactionSourceId = 2),
99,
"1045",
lct.Cardnumber,
lct.SettlementValue,
lct.SettlementCurrency,
getdate
from Transactions lct, MemberPromotion ba
where ba.CardNumber = lct.CardNumber
and ba.PointsAwarded =0
and lct.CardTypeId = "BA"
and lct.SettlementValue > 400
and lct.SettlementCurrency = "GBP"
and lct.TranDate = (select min(TranDate)
from Transactions
where CardNumber = lct.CardNumber
and CardTypeId = "BA"
and TranDate between "20 Jan 2004" and "1 Jul 2004"
and SettlementValue > 400
and SettlementCurrency = "GBP&quot

Your help is appreciated
TIA

Dickie Bird (-)))

 

[r937]

this scares the crap outta me --

1 + (select max(TransactionID)
from Transactions
where TransactionSourceId = 2)

i cannot imagine what will happen in a multi-user environment

good luck



rudy

http://r937.com/

SQL Consulting