Hi Sven. I experienced similar and more basic problems with joins what make them unuseful. My main trouble occurs when I join data from a view with GROUP BY statement with data from basic tables. Then sapdb goes completely crazy and even does not pay any attention to my final where clause. To give an example: the view definition is: SELECT NUM(ID) AS ID,COUNT(ID) AS L_ZAL FROM TBL_REPOSITORY_DESC WHERE TYPE='OFFERS' GROUP BY ID
I create another view like: SELECT O.REC_ID,O.REC_DELETED,O.OWNER_ID, O.TEOID_PREPARATION, O.TEOID_REALIZATION, O.EST_CLOSURE_DATE, O.OFFER_STATUS,O.OFFER_TYPE,O.CUSTOMER_NAME,O.CUSTOMER_CITY,O.VALID_DATE,O.S ENT_DATE,O.SUBJECT, O.OFFER_VALUE, CHAR(DATE(O.CREATE_DT)) || ' ' || CHAR(TIME(O.CREATE_DT)) AS CREATE_DT, SUBSTR(CHAR(O.LACTION_DT),1,16) AS LACTION_DT, D.ELEMENT_ID AS OFFER_STATUS_NAME, U.FIRST_NAME || ' ' || U.LAST_NAME AS OWNER_NAME, DECODE(V.L_ZAL,NULL,0,V.L_ZAL) AS L_ZAL FROM TBL_OFFERS O, TBL_DICTIONARY D, TBL_USERS U,VIEW_ATT_FOR_OFFERS V WHERE (D.DICT_ID = 'OFFER_STATUS') AND (O.OFFER_STATUS = D.FLX_NUM1) AND (O.OWNER_ID= U.USER_ID(+) ) AND (O.REC_ID=V.ID(+)) and them execute a query with simple where clause: SELECT * FROM DBV_OFFERS WHERE OWNER_NAME ='MyName' I get all the records from DBV_OFFERS, just like if Where clause was not specified, but, what's more, all the rows that should not be in the result (having OWNER_NAME<>'MyName') have Null in the field OWNER_NAME. Without where clause the result is OK. I have found a workaround - and the query SELECT * FROM DBV_OFFERS WHERE OWNER_ID ='MyID' works well, but.... Do I make something incorrect and can't see my mistake? I would be very grateful if someone could put some light on this issue. And BTW, could anyone from SAP give us the date ofnew official builds? (I remember the time, where there was a build every six weeks, but this nice 'tradition' has gone away ... as the marriage with MySql started?) Kind regards, Marcin > Hi, > > i know that there were bug-reports in the past about joins. Some i > reported myself, and some reported my others. > I don't know if this one has been fixed yet, and i'm still waiting for a > new version that fixes the known issues. > > here's an example where SAPDB doesn't to the right thing: > > 1) SELECT * FROM a > LEFT JOIN b ON (b.id=a.id) > WHERE b.test=true > > Well, as you can see, the left join is not what i want here. If SAPDB is > clever, it could optimize that to > > 2) SELECT * FROM a > INNER JOIN b ON (b.id=a.id) > WHERE b.test=true > > or even > > 3) SELECT * FROM a > INNER JOIN b ON (b.id=a.id AND b.test) > > The result of the statements 2) and 3) are as expected. The result of > the statement 1) looks very much the result of > > 4) SELECT * FROM a > LEFT JOIN b ON (b.id=a.id AND b.test) > > which is something completety different than statement 1). > Statements 1)-3) should be equivalent, but 1) and 4) are not. > > Of course i'm using the latest version of SAPDB. > > Thx > Sven > > > > -- > MaxDB Discussion Mailing List > For list archives: http://lists.mysql.com/maxdb > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > -- MaxDB Discussion Mailing List For list archives: http://lists.mysql.com/maxdb To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
