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!
>
>

annoying SQL Server error message

Hi,
We have developped many applications, for a major customer, connected
to the same SQL Server database. Those apps have been programmed in VB6
and VB.NET. The SQL Server is located on a dual-processor server,
running Windows 2000 Server, with 3 gigs RAM. The apps are installed on
Windows 2000 and Windows XP/SP2 client.
And the Windows 2000 client PC's, there is no problem at all. But, on
the XPs, we have, this error message popup: "Unable to load SQL Server
OLEDB provider ressource DLL. The application cannot continue". Look
like it appears randomly.
The same application, when installed on a Win2000, don't have any
problem at all. Just the damn XP machines. We cannot change the OS of
these PC's (enterprise rules).
This error is a major problem because it stops the application until
you acknowledge the error. The app is used for realtime control of a
factory, so, you see the problem...
XP workstation uses MDAC 2.8 SP1 for Windows XP SP2
2000 uses MDAC 2.6 RTM
Apps has been compiled on a MDAC 2.8 SP1 Win XP SP2 computer
How can I fix this?
thanks a lot for your help, it's really appreciated.
1)Have you got the latest MDAC installation ?
2)Track down all instances of sqloledb.rll and sqloledb.dll . Make sure
they are the latest versions and match up
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
"Dominic30" <gagned@.videotron.ca> wrote in message
news:1142610474.169168.15240@.i40g2000cwc.googlegro ups.com...
> Hi,
> We have developped many applications, for a major customer, connected
> to the same SQL Server database. Those apps have been programmed in VB6
> and VB.NET. The SQL Server is located on a dual-processor server,
> running Windows 2000 Server, with 3 gigs RAM. The apps are installed on
> Windows 2000 and Windows XP/SP2 client.
> And the Windows 2000 client PC's, there is no problem at all. But, on
> the XPs, we have, this error message popup: "Unable to load SQL Server
> OLEDB provider ressource DLL. The application cannot continue". Look
> like it appears randomly.
> The same application, when installed on a Win2000, don't have any
> problem at all. Just the damn XP machines. We cannot change the OS of
> these PC's (enterprise rules).
> This error is a major problem because it stops the application until
> you acknowledge the error. The app is used for realtime control of a
> factory, so, you see the problem...
> XP workstation uses MDAC 2.8 SP1 for Windows XP SP2
> 2000 uses MDAC 2.6 RTM
> Apps has been compiled on a MDAC 2.8 SP1 Win XP SP2 computer
> How can I fix this?
> thanks a lot for your help, it's really appreciated.
>
|||1) Yeah, I've got the latest MDAC installation. Anyway, I can't
installed another MDAC version on the XP machines, the install failed.
2) These files match up yes. I've checked all the station and it's
looks fine.
any more idea?
by the way, thanks for the reply
|||In what path is the SQLOLEDB.RLL file ?
You mention that all the apps are using the same sql server. Is this though
Linked Servers?
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
"Dominic30" <gagned@.videotron.ca> wrote in message
news:1142613417.547606.39710@.z34g2000cwc.googlegro ups.com...
> 1) Yeah, I've got the latest MDAC installation. Anyway, I can't
> installed another MDAC version on the XP machines, the install failed.
> 2) These files match up yes. I've checked all the station and it's
> looks fine.
> any more idea?
> by the way, thanks for the reply
>

annoying SQL Server error message

Hi,
We have developped many applications, for a major customer, connected
to the same SQL Server database. Those apps have been programmed in VB6
and VB.NET. The SQL Server is located on a dual-processor server,
running Windows 2000 Server, with 3 gigs RAM. The apps are installed on
Windows 2000 and Windows XP/SP2 client.
And the Windows 2000 client PC's, there is no problem at all. But, on
the XPs, we have, this error message popup: "Unable to load SQL Server
OLEDB provider ressource DLL. The application cannot continue". Look
like it appears randomly.
The same application, when installed on a Win2000, don't have any
problem at all. Just the damn XP machines. We cannot change the OS of
these PC's (enterprise rules).
This error is a major problem because it stops the application until
you acknowledge the error. The app is used for realtime control of a
factory, so, you see the problem...
XP workstation uses MDAC 2.8 SP1 for Windows XP SP2
2000 uses MDAC 2.6 RTM
Apps has been compiled on a MDAC 2.8 SP1 Win XP SP2 computer
How can I fix this'
thanks a lot for your help, it's really appreciated.1)Have you got the latest MDAC installation ?
2)Track down all instances of sqloledb.rll and sqloledb.dll . Make sure
they are the latest versions and match up
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
"Dominic30" <gagned@.videotron.ca> wrote in message
news:1142610474.169168.15240@.i40g2000cwc.googlegroups.com...
> Hi,
> We have developped many applications, for a major customer, connected
> to the same SQL Server database. Those apps have been programmed in VB6
> and VB.NET. The SQL Server is located on a dual-processor server,
> running Windows 2000 Server, with 3 gigs RAM. The apps are installed on
> Windows 2000 and Windows XP/SP2 client.
> And the Windows 2000 client PC's, there is no problem at all. But, on
> the XPs, we have, this error message popup: "Unable to load SQL Server
> OLEDB provider ressource DLL. The application cannot continue". Look
> like it appears randomly.
> The same application, when installed on a Win2000, don't have any
> problem at all. Just the damn XP machines. We cannot change the OS of
> these PC's (enterprise rules).
> This error is a major problem because it stops the application until
> you acknowledge the error. The app is used for realtime control of a
> factory, so, you see the problem...
> XP workstation uses MDAC 2.8 SP1 for Windows XP SP2
> 2000 uses MDAC 2.6 RTM
> Apps has been compiled on a MDAC 2.8 SP1 Win XP SP2 computer
> How can I fix this'
> thanks a lot for your help, it's really appreciated.
>|||1) Yeah, I've got the latest MDAC installation. Anyway, I can't
installed another MDAC version on the XP machines, the install failed.
2) These files match up yes. I've checked all the station and it's
looks fine.
any more idea?
by the way, thanks for the reply|||In what path is the SQLOLEDB.RLL file ?
You mention that all the apps are using the same sql server. Is this though
Linked Servers?
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
"Dominic30" <gagned@.videotron.ca> wrote in message
news:1142613417.547606.39710@.z34g2000cwc.googlegroups.com...
> 1) Yeah, I've got the latest MDAC installation. Anyway, I can't
> installed another MDAC version on the XP machines, the install failed.
> 2) These files match up yes. I've checked all the station and it's
> looks fine.
> any more idea?
> by the way, thanks for the reply
>

annoying SQL Server error message

Hi,
We have developped many applications, for a major customer, connected
to the same SQL Server database. Those apps have been programmed in VB6
and VB.NET. The SQL Server is located on a dual-processor server,
running Windows 2000 Server, with 3 gigs RAM. The apps are installed on
Windows 2000 and Windows XP/SP2 client.
And the Windows 2000 client PC's, there is no problem at all. But, on
the XPs, we have, this error message popup: "Unable to load SQL Server
OLEDB provider ressource DLL. The application cannot continue". Look
like it appears randomly.
The same application, when installed on a Win2000, don't have any
problem at all. Just the damn XP machines. We cannot change the OS of
these PC's (enterprise rules).
This error is a major problem because it stops the application until
you acknowledge the error. The app is used for realtime control of a
factory, so, you see the problem...
XP workstation uses MDAC 2.8 SP1 for Windows XP SP2
2000 uses MDAC 2.6 RTM
Apps has been compiled on a MDAC 2.8 SP1 Win XP SP2 computer
How can I fix this'
thanks a lot for your help, it's really appreciated.1)Have you got the latest MDAC installation ?
2)Track down all instances of sqloledb.rll and sqloledb.dll . Make sure
they are the latest versions and match up
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
"Dominic30" <gagned@.videotron.ca> wrote in message
news:1142610474.169168.15240@.i40g2000cwc.googlegroups.com...
> Hi,
> We have developped many applications, for a major customer, connected
> to the same SQL Server database. Those apps have been programmed in VB6
> and VB.NET. The SQL Server is located on a dual-processor server,
> running Windows 2000 Server, with 3 gigs RAM. The apps are installed on
> Windows 2000 and Windows XP/SP2 client.
> And the Windows 2000 client PC's, there is no problem at all. But, on
> the XPs, we have, this error message popup: "Unable to load SQL Server
> OLEDB provider ressource DLL. The application cannot continue". Look
> like it appears randomly.
> The same application, when installed on a Win2000, don't have any
> problem at all. Just the damn XP machines. We cannot change the OS of
> these PC's (enterprise rules).
> This error is a major problem because it stops the application until
> you acknowledge the error. The app is used for realtime control of a
> factory, so, you see the problem...
> XP workstation uses MDAC 2.8 SP1 for Windows XP SP2
> 2000 uses MDAC 2.6 RTM
> Apps has been compiled on a MDAC 2.8 SP1 Win XP SP2 computer
> How can I fix this'
> thanks a lot for your help, it's really appreciated.
>|||1) Yeah, I've got the latest MDAC installation. Anyway, I can't
installed another MDAC version on the XP machines, the install failed.
2) These files match up yes. I've checked all the station and it's
looks fine.
any more idea?
by the way, thanks for the reply|||In what path is the SQLOLEDB.RLL file ?
You mention that all the apps are using the same sql server. Is this though
Linked Servers?
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
"Dominic30" <gagned@.videotron.ca> wrote in message
news:1142613417.547606.39710@.z34g2000cwc.googlegroups.com...
> 1) Yeah, I've got the latest MDAC installation. Anyway, I can't
> installed another MDAC version on the XP machines, the install failed.
> 2) These files match up yes. I've checked all the station and it's
> looks fine.
> any more idea?
> by the way, thanks for the reply
>