I'm having a problem with this SQL string, I want 1 record from each
joined salestrans table like for instance:

SALESNUMBE ITEMNUMBER
---------- --------------------
738744 6398000
738734 1201270
738737 2216000
738644 5251000
738694 5943000
737714 2401000
736774 5943000

But the following SQL string:

SELECT str.salesnumber, str.itemnumber FROM XAL_SUPERVISOR.SALESTRANS
STR, XAL_SUPERVISOR.SALESTABLE ST WHERE str.dataset = 'FIK' AND
st.dataset = 'FIK' AND st.salesnumber = str.salesnumber AND
st.DELIVERYDATE = TO_DATE('23-12-2004', 'DD/MM/YYYY') AND st.FIKRetbon
= 1 AND ROWNUM = 1

Returns only:

SALESNUMBE ITEMNUMBER
---------- --------------------
738744 6398000

Can someone please tell me how I solve this?

Thanks in advance,

Mike

Mike Dole wrote:
> I'm having a problem with this SQL string, I want 1 record from each
> joined salestrans table like for instance:
>
> SALESNUMBE ITEMNUMBER
> ---------- --------------------
> 738744 6398000
> 738734 1201270
> 738737 2216000
> 738644 5251000
> 738694 5943000
> 737714 2401000
> 736774 5943000
>
> But the following SQL string:
>
> SELECT str.salesnumber, str.itemnumber FROM XAL_SUPERVISOR.SALESTRANS
> STR, XAL_SUPERVISOR.SALESTABLE ST WHERE str.dataset = 'FIK' AND
> st.dataset = 'FIK' AND st.salesnumber = str.salesnumber AND
> st.DELIVERYDATE = TO_DATE('23-12-2004', 'DD/MM/YYYY') AND st.FIKRetbon
> = 1 AND ROWNUM = 1
>
> Returns only:
>
> SALESNUMBE ITEMNUMBER
> ---------- --------------------
> 738744 6398000
>
> Can someone please tell me how I solve this?
>
> Thanks in advance,
>
> Mike

AND ROWNUM = 1 guarantees the return of one and only one row no matter
the query.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace 'x' with 'u' to respond)


-----------== Posted via Newsfeed.Com - Uncensored Usenet News ==----------
http://www.newsfeed.com The #1 Newsgroup Service in the World!
-----= Over 100,000 Newsgroups - Unlimited Fast Downloads - 19 Servers =-----

have you tried ROW_NUMBER() OVER(...) ?

What is different about the rows? Using something like rownum=1 is a
kludge. It means you haven't identified all the conditions that are
unique to the solution set.

As a first pass, try removing the rownum condition, add a count(*) to
the select clause and the appropriate group by clause. If the counts
are all 1 then you never needed any other condition in the first place.
If they are not, you neee to think about what conditions you are
missing.

HTH
ed