0

Please forgive my naivety, I’m an Oracle SQL newbie using Toad. I have a table with sales records, call it Sales. It has records of customers (by CustID) the date of a sale (SaleDate) and the item sold (by ItemID). It’s an Mview actually of other tables with final sales status in it.

I am trying to construct a query to return CustID, SaleDate, and ItemID if there is a sale on the same day for that customer for both ItemID=A and ItemID=B if between SaleDate 7/1/2013 and 7/31/2013. If this condition exists I want both records returned with the CustID, SaleDate and ItemID. I assume the two records would be on separate rows.

I’ve been researching IN, EXISTS, and sub queries but have yet to strike upon the right approach. The table has about 7 million records on it so I need something fairly efficient. Can someone point me in the right direction to achieve this? I’m learning, but I need to learn faster :)

GOT IT WOKING!

2/24/2014: Hey, I got it working and it returns the results on thesame row. One caveat to this. In my orginal example I was looking for dates when both 5P311 and 6R641 existed. In actuality I wanted all the days where 5P311 and any of the values from the RES group exists - of which 6R641 is a member. The code below achieves the results as I need them:

SELECT  ItemA.CLM_SSN, 
        ItemA.CLM_SERV_STRT Service_Date, 
        ItemA.CLM_COST_CTR_NBR,   
        ItemA.CLM_RECV_AMT,
        ItemB.CLM_COST_CTR_NBR RES_Cost_Center,
        ItemB.CLM_RECV_AMT,
        GroupCode,
        Service

FROM    DDIS.PTS_MV_CLM_STAT ItemA, 
        DDIS.PTS_MV_CLM_STAT ItemB,
        DDIS.CST_SERV

WHERE   TRUNC(ItemA.CLM_SERV_STRT) between to_date ('01-07-2013','dd-mm-yyyy') and to_date('31-    07-2013','dd-mm-yyyy')
    and TRUNC(ItemA.CLM_SERV_STRT) = TRUNC(ItemB.CLM_SERV_STRT)
    and TRIM(ItemA.CLM_COST_CTR_NBR) = '5P311' 
    and ITEMB.FK_SERV = CST_SERV.PKSERVICE
    and CST_SERV.GroupCode = 'RES'
    and Itema.CLM_SSN = ItemB.CLM_SSN
    and ItemA.CLM_RECV_AMT <> 0
    and ItemB.CLM_RECV_AMT <> 0

ORDER BY ItemA.CLM_SSN, ItemA.CLM_SERV_STRT
user3334579
  • 1
  • 1
  • @user2989408 See the code I posted up above. It returns all records from the time frame rather than those just of the two values in the WHERE statement. I'm still trying to figure this out. Any help would be appreciated. Thanks – user3334579 Feb 21 '14 at 20:52

1 Answers1

0

Try this, replace 'A' and 'B' values of course

SELECT CustID, SaleDate, ItemID 
FROM Mview AS mv
WHERE EXISTS(SELECT 1 FROM Mview AS itemA WHERE itemA.ItemID = 'A' 
             AND TRUNC(itemA.SaleDate) = TRUN(mv.SaleDate) )
AND EXISTS(SELECT 1 FROM Mview AS itemB WHERE itemB.ItemID = 'B' 
             AND TRUNC(itemB.SaleDate) = TRUNC(mv.SaleDate) )
AND mv.SaleDate BETWEEN  TO_DATE ('2003/01/07', 'yyyy/mm/dd')
AND TO_DATE ('2003/01/31', 'yyyy/mm/dd');

The exists combined ensures you that there is a sell that day that had those 2 items, the TRUNC in the date is to get rid of the hours and minutes of the date.

The between lets you seek the current range of dates, you have to convert it to date, since you are passing a string.

Edit: ItemA is a alias for the table Mview inside the exists oracle: can you assign an alias to the from clause? sql understand alias without the AS, but you can put it if it makes it easier for you to read.

In the full example you posted, you are not using any alias for DDIS.PTS_MV_CLM_STAT, so, the database motor doesnt distict wich table you are refering and that's why you dont get the values you want.

Community
  • 1
  • 1
Sr.PEDRO
  • 1,664
  • 19
  • 21
  • @ Sr.PEDRO I'm still learning this StackOverflow interface, sorry. See my answer below. I am getting results but not what I expect. It is returning all records from that time frame. – user3334579 Feb 21 '14 at 14:33
  • Hang on - I see my problem. Ignore what I wrote below. I'm on it - but have to go to a meeting first..... I'll fix this. [Slapping forehead....] – user3334579 Feb 21 '14 at 14:56
  • @ Sr.PEDRO Ok, I see where you're going with the Alias and 'AS'. Yes, for me (a newbie) it reads easier. In your example above I'd replace Mview with my table name, correct? I've tried it that way and am geting a "ORA-00933: SQL command not properly ended" error after: FROM DDIS.PTS_MV_CLM_STAT AS mv Any direction you can give? Thanks for all the help. I'm close, I know it.... – user3334579 Feb 21 '14 at 18:09
  • @ Sr.PEDRO As it turns out 'AS' is not liked in Toad. I was able to get the Alias set up right. See code above. But it is returning all 403K records forh te time frame instead of just the ones with the two values I put in. the WHERE statements. But, it's progress. – user3334579 Feb 21 '14 at 18:36
  • I didnt know that AS isnt accepted by Toad, glad to see that you resolved with alias without the AS, like in my first example. Cheers you pull it off – Sr.PEDRO Feb 25 '14 at 17:56