Another IIF statement question

I have a matrix table set up that returns some invoice amounts for each
customer, by month. The months are going across the top; the customers going
down the side; amounts as the 'data' portion.
Anyway, in my actual database table, some customers didn't have invoice
amounts for certain months (ie. they didn't buy products for that
month).When I run a query by month, only the months that they have invoices
for will show up.
Ex.
Cust Mnt InvAmt
1 1 $5
1 2 $5
1 9 $5
2 12 $0
Now, when I run my matrix table, all works great except that there are nulls
in the spots under the months no invoices exist (for instance, months 1 -11
for cust#2 in my example would show nulls, but month 12 would show $0).
I set up an IIF statement in my stored procedure (which is the dataset for
my matrix table) that will replace nulls with 0s, but since there are no
rows for the months with no invoices, the nulls don't exist and, therefore,
0s aren't inserted.
I've also tried making an IIF statement right inside the Expression part of
the matrix cell but I keep getting compilation errors.
Any ideas? TIA!I usually resolve this sort of problem in one of two ways, either use an
OUTER JOIN or UNION in the underlying query to make sure you get at least
one row for every month. Combined with the ISNULL function to make sure
there are zero values in the appropriate columns. This gives the matrix what
it needs to produce the sort of result I think you are looking for.
Sometimes it means you have to have a contrived table to enable this, I
usually have something like a Period table which acts as a time dimension for
just this sort of thing.
Hope this helps
Mike
"Kelly" wrote:
> I have a matrix table set up that returns some invoice amounts for each
> customer, by month. The months are going across the top; the customers going
> down the side; amounts as the 'data' portion.
> Anyway, in my actual database table, some customers didn't have invoice
> amounts for certain months (ie. they didn't buy products for that
> month).When I run a query by month, only the months that they have invoices
> for will show up.
> Ex.
> Cust Mnt InvAmt
> 1 1 $5
> 1 2 $5
> 1 9 $5
> 2 12 $0
> Now, when I run my matrix table, all works great except that there are nulls
> in the spots under the months no invoices exist (for instance, months 1 -11
> for cust#2 in my example would show nulls, but month 12 would show $0).
> I set up an IIF statement in my stored procedure (which is the dataset for
> my matrix table) that will replace nulls with 0s, but since there are no
> rows for the months with no invoices, the nulls don't exist and, therefore,
> 0s aren't inserted.
> I've also tried making an IIF statement right inside the Expression part of
> the matrix cell but I keep getting compilation errors.
> Any ideas? TIA!
>
>

and / or

Hi,
Can you see if this query is correct?
I would like to make sure the select query returns fieldID only if any of the fields inside the bracket (field4, 5, 6, 7) has changed.

--has anything for this record changed?...
--if so, add it into the Differences table...
select
fieldID
from
tblTable
where
fieldID = @.fieldID and
fieldID2 = @.NB
and
( field3 != @.field3 or field4 != @.field4
or field5 != @.field5
or field6 != @.field6
or field7 != @.field7
)This will work if all of your fields do not allow null; however, you will need to test for nulls if your columns allow for nulls.|||

Some of the fields do allow for nulls.

You mean I should include nullif ?

|||

You will need to change

Code Snippet

field3 != @.field3

into

Code Snippet

field3 != @.field3 or field3 is null and @.field3 is not null or field3 is not null and @.field3 is null

You will need to do this or something similar using ISNULL or COALESCE for all of your fields that you are testing for not equal.

|||

You could use the CHECKSUM() function to compare the left and right sides of the WHERE clause.

As this example demonstrates, data conversions are not required and null values will also work.

Code Snippet

DECLARE
@.Param1 varchar(20),
@.Param2 varchar(20),
@.Param3 int,
@.Param4 int,
@.NullValue varchar(10)

SELECT
@.Param1 = 'Test',
@.Param2 = 'Test2',
@.Param3 = 25

IF checksum( @.Param1, @.Param2, @.Param3, @.Param4 ) = checksum( 'Test', 'Test2', 25, @.NullValue )
PRINT 'Match'
ELSE
PRINT 'NoMatch'

So for your needs, you would use:

AND checksum( Field3, Field4, Field5) <> checksum( @.Field3, @.Field4, @.Field5)

(List shortened for display purposes...)

|||

Arnie Rowland wrote:

You could use the CHECKSUM() function to compare the left and right sides of the WHERE clause.

Please don't use CHECKSUM or BINARY_CHECKSUM functions. They are not guaranteed to produce unique values for input. They are simple hash functions used to divide set of values into different ranges (for example to create compact indexes or partition the data). In fact, with the current implementation you can get duplicate checksum values quite easily and there are certain types of input values that will simply produce unexpected results (repeated values, NULLs etc). You could use hashbytes in SQL Server 2005 which can generate MD5 or MD4 hash for example which can avoid collisions but still no guarantee to produce unique value for each input.

|||

You can change your WHERE clause to below since indexes if any on the columns (those with the OR checks) will not really help.

and
( coalesce(field3, '') <> coalesce(@.field3, '') -- use appriate data type value '' or 0 and so on.

-- repeat for others

or field4 != @.field4
or field5 != @.field5
or field6 != @.field6
or field7 != @.field7
)