Need help with why min function not working

[satinsilhouette]

Hi I have this code, and generally the min function works, but it is not picking up the minimum date as requested. Can someone help. The from clause and where clause do not matter. And there is a group clause, which should not matter right now.

This is the piece that is not working. I have tried it with To_Date to force it to a date and without, still will not pick up minimum due date.


Activity.COMPANY, MHSTHDR.CUSTOMER, CUSTDESC.R_NAME, Activity.ACCT_UNIT,
PRODDTL.CONTRACT, ACCNTRACT.DESCRIPTION AS "Activity Desc", PRODHDR.CONT_DATE,
MHSTHDR.INVOICE_DATE AS TRANS_DATE, MHSTHDR.INVOICE AS "Prod Invoice", PRODDTL.PRODUCT,
PRODDTL.USERS, MHSTHDR.CURRENCY_CODE, PRODDTL.LIST_PRICE, PRODDTL.DISCOUNT_AMT,
PRODDTL.NET_PRICE, PRODDTL.PREV_EXP_AMT, PRODDTL.TOTAL_COST, PRODDTL.CGSX_ACCOUNT,
PRODDTL.CGSX_SUB_ACCT, CUSTDESC.ADDR1, CUSTDESC.ADDR2, CUSTDESC.ADDR3, CUSTDESC.ADDR4,
CUSTDESC.CITY, CUSTDESC.STATE, CUSTDESC.ZIP, CUSTDESC.COUNTRY, PRODDTL.SITE_LOCATION,
Activity.POSTING_FLAG, MIN(To_DATE(MHSTHDR.DUE_DATE)) AS DUEDATE

Thanks!

Thanks so much!
satinsilhouette

 

[snitin78]

Please disregard my last question. There won't by any error if the due_date is not in the group by clause

 

[gmmastros]

Does this solve your problem now?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[satinsilhouette]

I am learning here - I need all those fields, but I need only the first due date for an invoice.

So am I on the same page as your question markos?

Thanks so much!
satinsilhouette

 

[markros]

Yes. The blog I mentioned solves this issue (for SQL Server) in 6 different ways. Except for ROW_NUMBER() solution I don't know the analogue in Oracle, the rest of the solutions should work. Try to apply them yourself first and post back if you would need more help.

 

[Emtucifor]

Oracle has a rowid but it's not quite the same meaning as in SQL Server.

 

[satinsilhouette]

So I put a inner query in somewhere that will go get the due date first and then it throws it to the outer query, correct?

Thanks so much!
satinsilhouette

 

[satinsilhouette]

Okay I tried following the less than dot blog example and I am getting back too many values when I try to run the query ora ERROR 00913. Does anyone see something wrong that I am doing?


SELECT ACTIVITY.COMPANY, ACPM.CUSTOMER, CUSTDESC.R_NAME, ACTIVITY.ACCT_UNIT, PRODDTL.CONTRACT,
CNTRACT.DESCRIPTION AS "Activity Desc", ACPRODHDR.CONT_DATE, ACPM.INVOICE_DATE AS TRANS_DATE,
ACPM.INVOICE AS "Prod Invoice", PRODDTL.PRODUCT, PRODDTL.USERS, ACPM.CURRENCY_CODE,
PRODDTL.LIST_PRICE, PRODDTL.DISCOUNT_AMT, PRODDTL.NET_PRICE, PRODDTL.PREV_EXP_AMT,
PRODDTL.TOTAL_COST, PRODDTL.CGSX_ACCOUNT, PRODDTL.CGSX_SUB_ACCT, CUSTDESC.ADDR1,
CUSTDESC.ADDR2, CUSTDESC.ADDR3, CUSTDESC.ADDR4, CUSTDESC.CITY, CUSTDESC.STATE,
CUSTDESC.ZIP, CUSTDESC.COUNTRY, PRODDTL.SITE_LOCATION, ACTIVITY.POSTING_FLAG,
ACPM.DUE_DATE AS DUEDATE
FROM PRODDTL RIGHT OUTER JOIN
ACTIVITY RIGHT OUTER JOIN
ACPRODHDR INNER JOIN
ACPMHSTHDR ACPM ON ACPRODHDR.CONTRACT = ACPM.CONTRACT ON ACTIVITY.ACTIVITY = ACPRODHDR.ACTIVITY ON
PRODDTL.CONTRACT = ACPRODHDR.CONTRACT LEFT OUTER JOIN
PRODLSTX ON PRODDTL.PRODUCT = PRODLSTX.PRODUCT LEFT OUTER JOIN
CNTRACT INNER JOIN
CUSTDESC ON CNTRACT.CUSTOMER = CUSTDESC.CUSTOMER ON
ACPRODHDR.CONTRACT = CNTRACT.CONTRACT
WHERE (ACTIVITY.COMPANY IN COMPANY)) AND (PRODLSTX.R_STATUS = 'A') AND (TRIM(PRODDTL.PRODUCT) IN PRODUCT))
AND (ACPM.INVOICE_DATE BETWEEN :StartDate AND :EndDate) AND (ACPM.INVOICE_TYPE <> 'T') AND (ACPM.DUE_DATE =
(SELECT CONTRACT, MIN(DUE_DATE) AS DUEDATE
FROM MHSTHDR
GROUP BY CONTRACT))

Thanks so much!
satinsilhouette

 

[Emtucifor]

I didn't know you could do this in Oracle:

ACTIVITY.COMPANY IN COMPANY)

Which reminds me. Did you know there is an Oracle forum here and that you're actually in the wrong forum? Everyone trying to help you so far knows ANSI SQL but we can only go so far... I would never have suggested this because you can't do that in SQL Server.

Okay. For starters I have a bunch of formatting critique. I realize you may be using a visual tool to create your query, but after you compare your query with my query below maybe this will convince you why writing them manually is so much better.
1. It would be best to rearrange your joins so they are LEFT JOINs. This helps you or others later when revisiting your code to understand it.
2. It's useful to put in parentheses when JOIN order needs enforcement, even though parentheses aren't required, because this helps make the query more clear.
3. Using aliases and good formatting always makes the query easier to understand (at least for me--and try to use no more than two characters per alias).
4. Even though I know Oracle likes upper case for column names, using all upper case in your queries makes it so much harder to see what's happening because the text turns into a huge blob of goo--using Mixed Case helps with this.
5. All the unneeded extra parentheses in your WHERE condition also just make it harder to scan and understand.

As for the actual problem, your final subquery is returning two columns and many rows, and you're trying to compare it to a single scalar value:

DUE_DATE = (SELECT 2 columns with many rows)

This is never going to work. You would either need to remove the first column and then correlate the subquery (meaning refer inside of it to a table in the JOIN clause) or make it a derived table and join to that (like I did for you below). Also, because CNTRACT is LEFT JOINed to the main query, you need to put this condition in a derived table inside the parenthetically JOINed tables otherwise you'll improperly convert the LEFT JOIN to an INNER JOIN. I think Oracle (or was it MySql?) can do something with comparing 2 values to 2 columns inside of an IN subquery, but that's not what's happening here.

Finally, you have a condition on PL which is LEFT JOINed, converting it to an INNER JOIN. So the condition has to move into the join (unless you didn't really want a LEFT JOIN in the first place, which just about proves the query was too confusing with the mix of LEFT and RIGHT joins anyway).

SELECT
   A.Company,
   PM.Customer,
   CD.R_Name,
   A.Acct_Unit,
   PD.Contract,
   CNTRACT.Description AS "Activity Desc",
   PH.CONT_Date,
   PM.Invoice_Date AS Trans_Date,
   PM.Invoice AS "Prod Invoice",
   PD.Product,
   PD.Users,
   PM.Currency_Code,
   PD.List_Price,
   PD.Discount_Amt,
   PD.Net_Price,
   PD.Prev_Exp_Amt,
   PD.Total_Cost,
   PD.CGSX_Account,
   PD.CGSX_Sub_Acct,
   CD.Addr1,
   CD.Addr2,
   CD.Addr3,
   CD.Addr4,
   CD.City,
   CD.State,
   CD.Zip,
   CD.Country,
   PD.Site_Location,
   A.Posting_Flag,
   PM.Due_Date AS DueDate
FROM
   ACPRODHDR PH
   INNER JOIN ACPMHSTHDR PM ON PH.Contract = PM.Contract
   LEFT JOIN ACTIVITY A ON PH.Activity = PH.Activity 
   LEFT JOIN PRODDTL PD ON PH.Contract = PD.Contract
   LEFT JOIN PRODLSTX PL ON PD.Product = PL.Product AND PL.R_Status = 'A'
   LEFT JOIN (
      CNTRACT C
      INNER JOIN CUSTDESC CD ON C.Customer = CD.Customer
      INNER JOIN (
         SELECT Contract, MIN(Due_Date) AS DueDate
         FROM MHSTHDR
         GROUP BY Contract
      ) MD ON C.Contract = MD.Contract   
   ) ON PH.Contract = C.Contract
WHERE
   A.Company IN (:COMPANY)
   AND TRIM(PD.Product) IN (:Product)
   AND PM.Invoice_Date BETWEEN :StartDate AND :EndDate
   AND PM.Invoice_Type <> 'T'

Would anyone else be willing to support at least some of the formatting recommendations I've made? I think this final query is 10 times easier to understand than the given one.

 

[gmmastros]

Would anyone else be willing to support at least [!]some[/!] of the formatting recommendations I've made?

If you are willing to stress the "some" part of that statement, then yeah. Hell yeah. I'd even be willing to switch "some" with "most" . Humans and computers BOTH need to understand the code. SQL Server does give a hoot about formatting, but humans do. Make it easy to read... easy to understand.... and you'll have code that is 1000 times more maintainable.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[satinsilhouette]

OH my I didn't realize I had that many errors. I am learning.

Unfortunatley, after making all the adjustments you recommended - the initial need of getting the minimum date, still hasn't been figured out. I am getting the same 5 records and not the one with the minimum due date.

And I do apologize - I may have gotten in the wrong forum - BUT you guys have been a big help - I have learned an enormous amount already.

SO Why aren't I getting the minimum due date record?

5 records are returned:

9/30/2009
10/31/2009
11/20/2009
12/31/2009
1/31/2010

I need 9/30/2009 and only that record.

Thanks so much!
satinsilhouette

 

[Emtucifor]

Are your dates stored as a date data type or as character data? If character data, you'll (shudder) have to put a to_date inside the min() expression.

Wait.... my mistake. I left out something. I don't really have all the info I need to get the query right.

In this derived table, you need to add a join condition on Due_Date. Here's my GUESS at it:

      INNER JOIN (
         SELECT Contract, MIN(Due_Date) AS DueDate
         FROM MHSTHDR
         GROUP BY Contract
      ) MD ON C.Contract = MD.Contract [b][red]AND C.Due_Date = MD.DueDate[/red][/b]

 

[satinsilhouette]

I thought that too, but it is a datetime field.

I added the join suggested and I am still getting the same amount of records back.

Man I don't get it. If you do a subquery like that and get the minimum of the duedate, and you prove that piece out--adding it into the main query in the where clause ought to net you the same results?

Logically this makes sense to me, but its not acting that way.

Thanks so much!
satinsilhouette

 

[Emtucifor]

Again, I still wasn't thinking clearly.

You don't want the Min(Due_Date) per contract... you want it per customer (or something). So you need to change the query to group by Customer or whatever you want, plus the query is going to have to be rearranged a bit in order to get the tables you need in the right places with the right joins.

If you can't figure out how to do it, please reply with a single example of one customer with multiple contracts and the key fields for the minimum query that would show we were getting the right rows. I am out of time today for helping so either someone else can take up the slack or perhaps I'll be able to help another day.

 

[markros]

May be you can start a new thread instead - this one is quite long already.

I also noticed what Erik missed, but since it doesn't return the desired result, perhaps you need a different level of grouping - not by contact ID, but by something else.