Search results for query: *

Thanks again, I shall investigate. Any pointers?

Yeah I had the same thoughts, but the error message specifically pointed to the fact that the Installation Type did not appear in "either an aggregate function or the GROUP BY clause". I would argue that it DOES appear in an aggregate, but since this would be futile I resorted to hard coding it...

No I'm on SQL Server. I'm better with Oracle and never had any problems with Decodes on there, but this is all new to me!

I did this but it wouldn't work until I amended as below... SELECT CUSTOMER, MAX(CASE WHEN Installation Type = New THEN Installation Date END) AS 'New', MAX(CASE WHEN Installation Type = Existing THEN Installation Date END) AS 'Existing' from table group by CUSTOMER, (((Installation Type)))...

I did hardcode that but it looks like I'm back to square one. Customer Number Installation Date 1 Installation Date 2 Installation Date 3 100008001 29/01/2010 100008001 12/02/2010 12/02/2010 100008001 It still returns a null row and one of the dates stays on it's own row even though...

Sorry, I forgot to mention that I have added a MAX to the dates returned. I still have a problem though! Because there is now a MAX in the SELECT statement, a GROUP BY clause is built. But the INSTALLATION TYPE from the CASE WHEN statement does not appear in this GROUP BY. I could hard code...

Thanks again, I'll have a look at this now I've got a bit more time, but since it was new to me I went with an old technique. Long story short, I previously had this... NAME INSTALLATION TYPE INSTALLATION DATE Mr Jones New 03/01/2010 Mr Jones Existing 05/02/2010 Mr Jones NA NA But I've...

This worked! Thanks for all your input.

I had a potentially new solution but I got a bit stuck. My idea was to have a new object for each date, so that they all appear on one row, as below... NAME NEW INSTALLATION DATE OTHER INSTALLATION DATE Mr Jones 03/01/2010 05/02/2010 I used to do this in Oracle using the Max Decode...

Thanks very much for the reply... I'll start looking at this asap. Cheers

Here's what the query returns if I don't use a MAX on anything (or if I just use it against one of the objects)... Customer Install Date Item No. Install Type 100008001 11/01/2010 1108216 NA 100008001 29/01/2010 1173516 HouseMove 100008001 12/02/2010 1187266 Existing Customer 100008001 NA...

Thanks for this Steve. I've tried that for the Install date, bringing back only one line as hoped, but I'll need to do the same for all of the other objects which do the same thing I suppose. The danger is that the max returns the alpha-numerically highest value rather than the one I need...

Allow me to explain. The customer below has both an Install Type of "Existing..." and an Install Type of "NA". Almost all customers in the dataset have this NA. If I filter down to only show customers with "Existing" I'll never see the genuine "NA" cases and therefore I won't get the split...

Hello there and thanks for the reply... "Is Not Null" won't work for me I'm afraid. I'm trying to create a summary crosstab to show the split of customers installed and not installed. My variable therefore says "= If <Install Type - Full Desc>="New Customer Addition" Then "Installed" Else...

Hello I'm new to the board but not new to Business Objects. I can't, however, seem to fix this seemingly simple problem. A date field within my universe must have multiple rows. One for each value and one to return a null value. Therefore, when I attempt to build a variable to ask if a...