Re: How make data transfomation faster?

to Ed Prochak
>yuck, that sinks some of my logic below. How yould you know which of
>the two starts matches a single finish (kind=2)???

there couldn't be two not finished transactions for a single User. So if we know User_id and Finish_date in a record like:

record knownrec
(
 User_id number,
 Finish_date date
)

then SQL query to get start record for this end will be:

select * from stat
 where User_id=knownrec.User_id
   and Finish_date is null;

>I'm slightly more confused by your answer to sim. maybe a description
>of what this represents would help. Are these like session logs?
>library book checkouts? (in which case the iD would be the book/ and it
>can be check out a second time by the same user before he returns it?
>I'm breaking the laws of physics here so I must be way off in this
>example.)

These are credit logs. Users are just users (not books) who took credit from us. Start_Date is the date when they took a credit, Finish_date is the date of paing credit back. We not allow users take more than 1 credit at a time so there coudn't be two not finished transactions for a single user. We need a proof that user took the credit, so if he dosen't (IOW we didn't get proof for that), we need to cancel taking a credit from that user. Same to paing credit back. We should be able cancel paing. That's why there are kinds 3 and 4, for cancel previous changes.

>Look closer, I was making an OUTER JOIN
>or did I put the (+) on the wrong side??

select A.user_id,A.date_time from log A, log B
 where A.id=B.id(+)   --- yes an outer join
   and A.kind=1 and B.kind IS NULL

This will never return any row becouse of kind could not be null! Outer join just allows you to see rows with null values in join columns, but there aren't any null value.

>The query is finding rows of kind = 1 that DO NOT have a matching row
>of kind = 2
>IOW, making the SQL engine do some of the logic you coded as IF
>statements in PLSQL.
>(but since your log table allows duplicates, the second INSERT will
>fail)

Still this query won't work. And I have no idea what is right one.

>Well the fact that there can be duplicates makes it more difficult but
>I again suggest you view the log table as really 4 different tables and
>try to proceed from there.

I was tring do make any valuble result thinking that way, but coudn't find it (.

>Thanks for the feedback BTW. Too often we see posts with questions, and
>after posting possible solutions, we get no replies from the original
>poster. So again thanks.

lol :). You thanks me becouse of I interest in my question. That's nice. Well, you are welcome :)).
Actualy I realy appreciate your responces for my question. And thank you for your time, you spending here with me. Received on Fri Feb 03 2006 - 12:53:57 CST