I have wrote a query that returns product details for all orders that are at stage 6. Is there a way to number the product lines in each order no?!
Eg.
Order 1
Product 1 - 1
Product 2 - 2
Product 3 - 3
--counter restarts
Order 2
Product 1 - 1
Product 2 -2
thanks?If you are using SQL 2005, you could look into using the ROW_NUMBER function with the PARTITION BY option.|||Or you can just let the front end do it..or insert the rows into a temp table with an identity column then select from that
Showing posts with label returns. Show all posts
Showing posts with label returns. Show all posts
Monday, March 26, 2012
Monday, March 19, 2012
Number formatting has me stumped
Hi, I dont seem to be able to output a number that has 3 decimal places.
The data source returns eg. 12.564 % and the output format on the report
is ##.###.
The output value though shows 12.56%, I want to show the figure above.
What am I missing or doing wrong? Is there a rounding switch or option I am
missing ?
Thanks in advance .Try this:
#,##0.000
returns:
50.123
0.000
15,050.123
15.050,123 (for example in german systems)
"PaulQld" <PaulQld@.discussions.microsoft.com> schrieb im Newsbeitrag
news:7C955125-6CD3-49DF-9028-D0AD04D2457E@.microsoft.com...
> Hi, I dont seem to be able to output a number that has 3 decimal places.
> The data source returns eg. 12.564 % and the output format on the report
> is ##.###.
> The output value though shows 12.56%, I want to show the figure above.
> What am I missing or doing wrong? Is there a rounding switch or option I
> am
> missing ?
> Thanks in advance .|||BTW, more information on number format strings can be found on MSDN:
*
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconstandardnumericformatstrings.asp
*
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconcustomnumericformatstrings.asp
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jens Konerow" <keineangabe@.web.de> wrote in message
news:Ow%23cGYqwFHA.2132@.TK2MSFTNGP15.phx.gbl...
> Try this:
> #,##0.000
> returns:
> 50.123
> 0.000
> 15,050.123
> 15.050,123 (for example in german systems)
> "PaulQld" <PaulQld@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:7C955125-6CD3-49DF-9028-D0AD04D2457E@.microsoft.com...
>> Hi, I dont seem to be able to output a number that has 3 decimal places.
>> The data source returns eg. 12.564 % and the output format on the report
>> is ##.###.
>> The output value though shows 12.56%, I want to show the figure above.
>> What am I missing or doing wrong? Is there a rounding switch or option I
>> am
>> missing ?
>> Thanks in advance .
>|||In the Textbox Properties under Format, select Custom.
Use "N3" for numbers, or "P3" for percentages. You will see the example
change to the format you are trying to get.
"PaulQld" wrote:
> Hi, I dont seem to be able to output a number that has 3 decimal places.
> The data source returns eg. 12.564 % and the output format on the report
> is ##.###.
> The output value though shows 12.56%, I want to show the figure above.
> What am I missing or doing wrong? Is there a rounding switch or option I am
> missing ?
> Thanks in advance .
The data source returns eg. 12.564 % and the output format on the report
is ##.###.
The output value though shows 12.56%, I want to show the figure above.
What am I missing or doing wrong? Is there a rounding switch or option I am
missing ?
Thanks in advance .Try this:
#,##0.000
returns:
50.123
0.000
15,050.123
15.050,123 (for example in german systems)
"PaulQld" <PaulQld@.discussions.microsoft.com> schrieb im Newsbeitrag
news:7C955125-6CD3-49DF-9028-D0AD04D2457E@.microsoft.com...
> Hi, I dont seem to be able to output a number that has 3 decimal places.
> The data source returns eg. 12.564 % and the output format on the report
> is ##.###.
> The output value though shows 12.56%, I want to show the figure above.
> What am I missing or doing wrong? Is there a rounding switch or option I
> am
> missing ?
> Thanks in advance .|||BTW, more information on number format strings can be found on MSDN:
*
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconstandardnumericformatstrings.asp
*
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconcustomnumericformatstrings.asp
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jens Konerow" <keineangabe@.web.de> wrote in message
news:Ow%23cGYqwFHA.2132@.TK2MSFTNGP15.phx.gbl...
> Try this:
> #,##0.000
> returns:
> 50.123
> 0.000
> 15,050.123
> 15.050,123 (for example in german systems)
> "PaulQld" <PaulQld@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:7C955125-6CD3-49DF-9028-D0AD04D2457E@.microsoft.com...
>> Hi, I dont seem to be able to output a number that has 3 decimal places.
>> The data source returns eg. 12.564 % and the output format on the report
>> is ##.###.
>> The output value though shows 12.56%, I want to show the figure above.
>> What am I missing or doing wrong? Is there a rounding switch or option I
>> am
>> missing ?
>> Thanks in advance .
>|||In the Textbox Properties under Format, select Custom.
Use "N3" for numbers, or "P3" for percentages. You will see the example
change to the format you are trying to get.
"PaulQld" wrote:
> Hi, I dont seem to be able to output a number that has 3 decimal places.
> The data source returns eg. 12.564 % and the output format on the report
> is ##.###.
> The output value though shows 12.56%, I want to show the figure above.
> What am I missing or doing wrong? Is there a rounding switch or option I am
> missing ?
> Thanks in advance .
Number formatting
Here's a quickie - I have a stored procedure which returns a list of numbers.
I want the output formatted with commas so 95,000 instead of 95000 - how
would I do that?"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:F11D15E1-2FFB-4ADE-85C3-F5C97E278214@.microsoft.com...
> Here's a quickie - I have a stored procedure which returns a list of
numbers.
> I want the output formatted with commas so 95,000 instead of 95000 - how
> would I do that?
Do it at the client, not at the server.|||Joe,
here is an attempt at doing it, this is definitely an overkill and can
reduce the performance in case you have a lot of data coming back,
Select
LEFT(Convert(varchar(12),Convert(money,95000),1),LEN(Convert(varchar(12),Convert(money,95000),1)) - 3)
I would agree with Greg it will be simpler & more efficient on the
front-end. But I just had to try and do it in the backend :)
Enjoy,
Rakesh Ajwani
MCSD, MCSD.NET
"Greg D. Moore (Strider)" wrote:
> "Joe" <Joe@.discussions.microsoft.com> wrote in message
> news:F11D15E1-2FFB-4ADE-85C3-F5C97E278214@.microsoft.com...
> > Here's a quickie - I have a stored procedure which returns a list of
> numbers.
> > I want the output formatted with commas so 95,000 instead of 95000 - how
> > would I do that?
> Do it at the client, not at the server.
>
>
I want the output formatted with commas so 95,000 instead of 95000 - how
would I do that?"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:F11D15E1-2FFB-4ADE-85C3-F5C97E278214@.microsoft.com...
> Here's a quickie - I have a stored procedure which returns a list of
numbers.
> I want the output formatted with commas so 95,000 instead of 95000 - how
> would I do that?
Do it at the client, not at the server.|||Joe,
here is an attempt at doing it, this is definitely an overkill and can
reduce the performance in case you have a lot of data coming back,
Select
LEFT(Convert(varchar(12),Convert(money,95000),1),LEN(Convert(varchar(12),Convert(money,95000),1)) - 3)
I would agree with Greg it will be simpler & more efficient on the
front-end. But I just had to try and do it in the backend :)
Enjoy,
Rakesh Ajwani
MCSD, MCSD.NET
"Greg D. Moore (Strider)" wrote:
> "Joe" <Joe@.discussions.microsoft.com> wrote in message
> news:F11D15E1-2FFB-4ADE-85C3-F5C97E278214@.microsoft.com...
> > Here's a quickie - I have a stored procedure which returns a list of
> numbers.
> > I want the output formatted with commas so 95,000 instead of 95000 - how
> > would I do that?
> Do it at the client, not at the server.
>
>
Number formatting
Here's a quickie - I have a stored procedure which returns a list of numbers
.
I want the output formatted with commas so 95,000 instead of 95000 - how
would I do that?"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:F11D15E1-2FFB-4ADE-85C3-F5C97E278214@.microsoft.com...
> Here's a quickie - I have a stored procedure which returns a list of
numbers.
> I want the output formatted with commas so 95,000 instead of 95000 - how
> would I do that?
Do it at the client, not at the server.|||Joe,
here is an attempt at doing it, this is definitely an overkill and can
reduce the performance in case you have a lot of data coming back,
Select
LEFT(Convert(varchar(12),Convert(money,9
5000),1),LEN(Convert(varchar(12),Con
vert(money,95000),1)) - 3)
I would agree with Greg it will be simpler & more efficient on the
front-end. But I just had to try and do it in the backend
Enjoy,
Rakesh Ajwani
MCSD, MCSD.NET
"Greg D. Moore (Strider)" wrote:
> "Joe" <Joe@.discussions.microsoft.com> wrote in message
> news:F11D15E1-2FFB-4ADE-85C3-F5C97E278214@.microsoft.com...
> numbers.
> Do it at the client, not at the server.
>
>
.
I want the output formatted with commas so 95,000 instead of 95000 - how
would I do that?"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:F11D15E1-2FFB-4ADE-85C3-F5C97E278214@.microsoft.com...
> Here's a quickie - I have a stored procedure which returns a list of
numbers.
> I want the output formatted with commas so 95,000 instead of 95000 - how
> would I do that?
Do it at the client, not at the server.|||Joe,
here is an attempt at doing it, this is definitely an overkill and can
reduce the performance in case you have a lot of data coming back,
Select
LEFT(Convert(varchar(12),Convert(money,9
5000),1),LEN(Convert(varchar(12),Con
vert(money,95000),1)) - 3)
I would agree with Greg it will be simpler & more efficient on the
front-end. But I just had to try and do it in the backend
Enjoy,
Rakesh Ajwani
MCSD, MCSD.NET
"Greg D. Moore (Strider)" wrote:
> "Joe" <Joe@.discussions.microsoft.com> wrote in message
> news:F11D15E1-2FFB-4ADE-85C3-F5C97E278214@.microsoft.com...
> numbers.
> Do it at the client, not at the server.
>
>
Friday, March 9, 2012
NullReferenceException when using data-driven subscription with custom data extension
Hello,
I am trying to set up a data-driven subscription, where the command
that returns the list of recipients is run against a custom data
provider extension. However, when I validate the query (on step 3 of
the wizard), I always get the error rsCannotPrepareQuery, with the
message "Object reference not set to an instance of an object." When I
step into the code, the exception is being thrown inside the following
stack trace:
Microsoft.ReportingServices.Library.SubscriptionManager.PrepareQuery()
Microsoft.ReportingServices.Library.RSService.PrepareQuery()
Microsoft.ReportingServices.WebServer.ReportingService.PrepareQuery()
System.Web.Services.Protocols.LogicalMethodInfo.Invoke()
...
The exception is thrown right after IDbConnection.Open() returns, which
is immediately before IDbConnection.CreateCommand() is called.
Everywhere else, my custom data provider extension works great. And it
doesn't matter what query or credentials I type it - the problem always
exists.
I am running MS RS 2000 SP1.
Thanks in advance.Which interfaces do you implement: RS.IDbConnection, etc or
System.Data.IDbConnection?
--
Alex Mineev
Software Design Engineer. Report expressions; Code Access Security; Xml;
SQE.
This posting is provided "AS IS" with no warranties, and confers no rights
<bigredgum1@.excite.com> wrote in message
news:1107886911.665093.180630@.c13g2000cwb.googlegroups.com...
> Hello,
> I am trying to set up a data-driven subscription, where the command
> that returns the list of recipients is run against a custom data
> provider extension. However, when I validate the query (on step 3 of
> the wizard), I always get the error rsCannotPrepareQuery, with the
> message "Object reference not set to an instance of an object." When I
> step into the code, the exception is being thrown inside the following
> stack trace:
> Microsoft.ReportingServices.Library.SubscriptionManager.PrepareQuery()
> Microsoft.ReportingServices.Library.RSService.PrepareQuery()
> Microsoft.ReportingServices.WebServer.ReportingService.PrepareQuery()
> System.Web.Services.Protocols.LogicalMethodInfo.Invoke()
> ...
> The exception is thrown right after IDbConnection.Open() returns, which
> is immediately before IDbConnection.CreateCommand() is called.
> Everywhere else, my custom data provider extension works great. And it
> doesn't matter what query or credentials I type it - the problem always
> exists.
> I am running MS RS 2000 SP1.
> Thanks in advance.
>|||My connection class implements
Microsoft.ReportingServices.DataProcessing.IDbConnection.
I am basically just using the code provided in the Microsoft article
"Using an ADO.NET DataSet as a Reporting Services Data Source."
Thanks for your help.|||I need more info. Could you please repro it once again and send me recent rs
logs?
thanks
--
Alex Mineev
Software Design Engineer. Report expressions; Code Access Security; Xml;
SQE.
This posting is provided "AS IS" with no warranties, and confers no rights
<bigredgum1@.excite.com> wrote in message
news:1108048910.737444.302770@.f14g2000cwb.googlegroups.com...
> My connection class implements
> Microsoft.ReportingServices.DataProcessing.IDbConnection.
> I am basically just using the code provided in the Microsoft article
> "Using an ADO.NET DataSet as a Reporting Services Data Source."
> Thanks for your help.
>|||Sure. To reproduce, follow these steps:
- Set up the custom data extension as described in the article "Using
an ADO.NET DataSet as a Reporting Services Data Source".
- Go to the Report Manager web site and view any report.
- Click on the Subscriptions tab.
- Click the New Data-driven Subscription button.
- On page 1, enter any description and delivery provider. Select
"Specify for this subscription only". Click Next.
- On page 2, select your custom data extension. Enter any connection
string. Select "Credentials stored securely in the report server" and
enter any user name and password. Click Next.
- On page 3, enter any text and click Validate. The error
rsCannotPrepareQuery and text "Object reference not set to an instance
of an object" is displayed.
(Incidentally, if you choose "Credentials are not required" on page 2,
then on page 3 when you attempt to validate your query, you will get
error rsInvalidDataSourceCredentialSetting. This appears to be another
bug or design mistake. If my custom data extension does not require
credentials (e.g. it obtains them from a configuration file), then
there is no reason I should have to enter them in the UI. This just
ends up being confusing for end users to enter meaningless
credentials.)
When I click the Validate button on step 3 of the data-driven
subscription wizard, the following is appended to the ReportServer log
(I cranked up logging to Verbose):
aspnet_wp!runningrequests!15c4!02/10/2005-14:42:53:: v VERBOSE: User
map'<Users><User><Name>Person
1</Name><Paths><Path>http://mymachine/ReportServer/reportservice.asmx</Path><NrReq>1</NrReq></Paths></User></Users>'
aspnet_wp!runningrequests!15c4!02/10/2005-14:42:53:: v VERBOSE:
SoapAction:
"http://schemas.microsoft.com/sqlserver/2003/12/reporting/reportingservices/GetSystemProperties"
aspnet_wp!library!15c4!02/10/2005-14:42:53:: v VERBOSE: Call to
GetSystemProperties()
aspnet_wp!library!15c4!02/10/2005-14:42:53:: v VERBOSE: Call to
GetSystemProperties completed.
aspnet_wp!runningrequests!17fc!02/10/2005-14:45:07:: v VERBOSE: User
map'<Users><User><Name>Person
1</Name><Paths><Path>http://mymachine/ReportServer/reportservice.asmx</Path><NrReq>1</NrReq></Paths></User></Users>'
aspnet_wp!runningrequests!17fc!02/10/2005-14:45:07:: v VERBOSE:
SoapAction:
"http://schemas.microsoft.com/sqlserver/2003/12/reporting/reportingservices/ListSecureMethods"
aspnet_wp!runningrequests!17fc!02/10/2005-14:45:07:: v VERBOSE: User
map'<Users><User><Name>Person
1</Name><Paths><Path>http://mymachine/ReportServer/reportservice.asmx</Path><NrReq>1</NrReq></Paths></User></Users>'
aspnet_wp!runningrequests!17fc!02/10/2005-14:45:07:: v VERBOSE:
SoapAction:
"http://schemas.microsoft.com/sqlserver/2003/12/reporting/reportingservices/GetSystemPermissions"
aspnet_wp!library!17fc!02/10/2005-14:45:07:: i INFO: Call to
GetSystemPermissions
aspnet_wp!runningrequests!17fc!02/10/2005-14:45:07:: v VERBOSE: User
map'<Users><User><Name>Person
1</Name><Paths><Path>http://mymachine/ReportServer/reportservice.asmx</Path><NrReq>1</NrReq></Paths></User></Users>'
aspnet_wp!runningrequests!17fc!02/10/2005-14:45:07:: v VERBOSE:
SoapAction:
"http://schemas.microsoft.com/sqlserver/2003/12/reporting/reportingservices/ListChildren"
aspnet_wp!library!17fc!02/10/2005-14:45:07:: v VERBOSE: Call to
ListChildren( '/', False )
aspnet_wp!library!17fc!02/10/2005-14:45:07:: v VERBOSE: Call to
ListChildren completed
aspnet_wp!runningrequests!17fc!02/10/2005-14:45:07:: v VERBOSE: User
map'<Users><User><Name>Person
1</Name><Paths><Path>http://mymachine/ReportServer/reportservice.asmx</Path><NrReq>1</NrReq></Paths></User></Users>'
aspnet_wp!runningrequests!17fc!02/10/2005-14:45:07:: v VERBOSE:
SoapAction:
"http://schemas.microsoft.com/sqlserver/2003/12/reporting/reportingservices/ListExtensions"
aspnet_wp!library!17fc!02/10/2005-14:45:07:: v VERBOSE: Call to
ListProviders: type (Data).
aspnet_wp!library!17fc!02/10/2005-14:45:07:: v VERBOSE: Call to
ListProviders completed.
aspnet_wp!runningrequests!17fc!02/10/2005-14:45:07:: v VERBOSE: User
map'<Users><User><Name>Person
1</Name><Paths><Path>http://mymachine/ReportServer/reportservice.asmx</Path><NrReq>1</NrReq></Paths></User></Users>'
aspnet_wp!runningrequests!17fc!02/10/2005-14:45:07:: v VERBOSE:
SoapAction:
"http://schemas.microsoft.com/sqlserver/2003/12/reporting/reportingservices/PrepareQuery"
aspnet_wp!library!17fc!02/10/2005-14:45:07:: v VERBOSE: Call to
PrepareQuery: dataSource
(<DataSource><DataSourceDefinition><Extension>My
DataSet</Extension><ConnectString>asdf</ConnectString><CredentialRetrieval>Store</CredentialRetrieval><WindowsCredentials>False</WindowsCredentials><ImpersonateUser>False</ImpersonateUser><UserName>user</UserName><Password>test</Password></DataSourceDefinition></DataSource>),
dataSet(<DataSet><Query><CommandType>Text</CommandType><CommandText>asdfasdf</CommandText></Query></DataSet>).
aspnet_wp!crypto!17fc!02/10/2005-14:45:07:: v VERBOSE: Starting crypto
operation DBProtectString
aspnet_wp!crypto!17fc!02/10/2005-14:45:07:: v VERBOSE: Completed crypto
operation DBProtectString
aspnet_wp!crypto!17fc!02/10/2005-14:45:07:: v VERBOSE: Starting crypto
operation DBProtectString
aspnet_wp!crypto!17fc!02/10/2005-14:45:07:: v VERBOSE: Completed crypto
operation DBProtectString
aspnet_wp!crypto!17fc!02/10/2005-14:45:07:: v VERBOSE: Starting crypto
operation DBProtectString
aspnet_wp!crypto!17fc!02/10/2005-14:45:07:: v VERBOSE: Completed crypto
operation DBProtectString
aspnet_wp!crypto!17fc!02/10/2005-14:45:07:: v VERBOSE: Starting crypto
operation DBUnProtectString
aspnet_wp!crypto!17fc!02/10/2005-14:45:07:: v VERBOSE: Completed crypto
operation DBUnProtectString
aspnet_wp!crypto!17fc!02/10/2005-14:45:07:: v VERBOSE: Starting crypto
operation DBUnProtectString
aspnet_wp!crypto!17fc!02/10/2005-14:45:07:: v VERBOSE: Completed crypto
operation DBUnProtectString
aspnet_wp!crypto!17fc!02/10/2005-14:45:07:: v VERBOSE: Starting crypto
operation DBUnProtectString
aspnet_wp!crypto!17fc!02/10/2005-14:45:07:: v VERBOSE: Completed crypto
operation DBUnProtectString
aspnet_wp!processing!17fc!02/10/2005-14:45:07:: v VERBOSE: A connection
object for the My DataSet data source has been created.
aspnet_wp!crypto!17fc!02/10/2005-14:45:07:: v VERBOSE: Starting crypto
operation DBUnProtectString
aspnet_wp!crypto!17fc!02/10/2005-14:45:07:: v VERBOSE: Completed crypto
operation DBUnProtectString
aspnet_wp!library!17fc!02/10/2005-14:45:07:: e ERROR: Throwing
Microsoft.ReportingServices.Diagnostics.Utilities.CannotPrepareQueryException:
The dataset cannot be generated. An error occurred while connecting to
a data source, or the query is not valid for the data source., ;
Info:
Microsoft.ReportingServices.Diagnostics.Utilities.CannotPrepareQueryException:
The dataset cannot be generated. An error occurred while connecting to
a data source, or the query is not valid for the data source. -->
System.NullReferenceException: Object reference not set to an instance
of an object.
at
Microsoft.ReportingServices.Library.SubscriptionManager.PrepareQuery(DataSource
dataSource, DataSetDefinition dataSet, Boolean& changed)
-- End of inner exception stack trace --
aspnet_wp!runningrequests!17fc!02/10/2005-14:45:07:: v VERBOSE: User
map'<Users><User><Name>Person
1</Name><Paths><Path>http://mymachine/ReportServer/reportservice.asmx</Path><NrReq>1</NrReq></Paths></User></Users>'
aspnet_wp!runningrequests!17fc!02/10/2005-14:45:07:: v VERBOSE:
SoapAction:
"http://schemas.microsoft.com/sqlserver/2003/12/reporting/reportingservices/GetSystemProperties"
aspnet_wp!library!17fc!02/10/2005-14:45:07:: v VERBOSE: Call to
GetSystemProperties()
aspnet_wp!library!17fc!02/10/2005-14:45:07:: v VERBOSE: Call to
GetSystemProperties completed.
No changes are made to the ReportServerService or ReportServerWebApp
logs. No other errors are listed in any of these logs.
For reference, here is my connection class:
using System;
using System.Data;
using System.Configuration;
using System.Xml;
using Microsoft.ReportingServices.DataProcessing;
namespace My.Samples.DataSetProcessingExtension
{
public class DSXConnection
: Microsoft.ReportingServices.DataProcessing.IDbConnection
{
private string _connString;
private int _connTimeout = 15;
private ConnectionState _state = ConnectionState.Closed;
public DSXConnection() {}
public DSXConnection(string connString) { _connString = connString; }
public string ConnectionString { get { return _connString; } set {
_connString = value; } }
public int ConnectionTimeout { get { return _connTimeout; } }
public ConnectionState State { get { return _state; } }
public Microsoft.ReportingServices.DataProcessing.IDbTransaction
BeginTransaction()
{
return null;
}
public void Open()
{
_state = ConnectionState.Open;
}
public void Close()
{
this.Dispose();
_state = ConnectionState.Closed;
}
// Implemented.
public Microsoft.ReportingServices.DataProcessing.IDbCommand
CreateCommand()
{
return new DSXCommand(this);
}
public string LocalizedName
{
get { return "DataSet Data Processing Extension"; }
}
public void SetConfiguration(string configuration) {}
public void Dispose() {}
}
}
I also have forms authentication and a custom security extension set
up, which works fine as far as I can tell. I don't know if that would
have any bearing on this issue, but I thought I'd mention it anyways.
Hope that helps. If you need more information feel free to post here or
e-mail me.
I am trying to set up a data-driven subscription, where the command
that returns the list of recipients is run against a custom data
provider extension. However, when I validate the query (on step 3 of
the wizard), I always get the error rsCannotPrepareQuery, with the
message "Object reference not set to an instance of an object." When I
step into the code, the exception is being thrown inside the following
stack trace:
Microsoft.ReportingServices.Library.SubscriptionManager.PrepareQuery()
Microsoft.ReportingServices.Library.RSService.PrepareQuery()
Microsoft.ReportingServices.WebServer.ReportingService.PrepareQuery()
System.Web.Services.Protocols.LogicalMethodInfo.Invoke()
...
The exception is thrown right after IDbConnection.Open() returns, which
is immediately before IDbConnection.CreateCommand() is called.
Everywhere else, my custom data provider extension works great. And it
doesn't matter what query or credentials I type it - the problem always
exists.
I am running MS RS 2000 SP1.
Thanks in advance.Which interfaces do you implement: RS.IDbConnection, etc or
System.Data.IDbConnection?
--
Alex Mineev
Software Design Engineer. Report expressions; Code Access Security; Xml;
SQE.
This posting is provided "AS IS" with no warranties, and confers no rights
<bigredgum1@.excite.com> wrote in message
news:1107886911.665093.180630@.c13g2000cwb.googlegroups.com...
> Hello,
> I am trying to set up a data-driven subscription, where the command
> that returns the list of recipients is run against a custom data
> provider extension. However, when I validate the query (on step 3 of
> the wizard), I always get the error rsCannotPrepareQuery, with the
> message "Object reference not set to an instance of an object." When I
> step into the code, the exception is being thrown inside the following
> stack trace:
> Microsoft.ReportingServices.Library.SubscriptionManager.PrepareQuery()
> Microsoft.ReportingServices.Library.RSService.PrepareQuery()
> Microsoft.ReportingServices.WebServer.ReportingService.PrepareQuery()
> System.Web.Services.Protocols.LogicalMethodInfo.Invoke()
> ...
> The exception is thrown right after IDbConnection.Open() returns, which
> is immediately before IDbConnection.CreateCommand() is called.
> Everywhere else, my custom data provider extension works great. And it
> doesn't matter what query or credentials I type it - the problem always
> exists.
> I am running MS RS 2000 SP1.
> Thanks in advance.
>|||My connection class implements
Microsoft.ReportingServices.DataProcessing.IDbConnection.
I am basically just using the code provided in the Microsoft article
"Using an ADO.NET DataSet as a Reporting Services Data Source."
Thanks for your help.|||I need more info. Could you please repro it once again and send me recent rs
logs?
thanks
--
Alex Mineev
Software Design Engineer. Report expressions; Code Access Security; Xml;
SQE.
This posting is provided "AS IS" with no warranties, and confers no rights
<bigredgum1@.excite.com> wrote in message
news:1108048910.737444.302770@.f14g2000cwb.googlegroups.com...
> My connection class implements
> Microsoft.ReportingServices.DataProcessing.IDbConnection.
> I am basically just using the code provided in the Microsoft article
> "Using an ADO.NET DataSet as a Reporting Services Data Source."
> Thanks for your help.
>|||Sure. To reproduce, follow these steps:
- Set up the custom data extension as described in the article "Using
an ADO.NET DataSet as a Reporting Services Data Source".
- Go to the Report Manager web site and view any report.
- Click on the Subscriptions tab.
- Click the New Data-driven Subscription button.
- On page 1, enter any description and delivery provider. Select
"Specify for this subscription only". Click Next.
- On page 2, select your custom data extension. Enter any connection
string. Select "Credentials stored securely in the report server" and
enter any user name and password. Click Next.
- On page 3, enter any text and click Validate. The error
rsCannotPrepareQuery and text "Object reference not set to an instance
of an object" is displayed.
(Incidentally, if you choose "Credentials are not required" on page 2,
then on page 3 when you attempt to validate your query, you will get
error rsInvalidDataSourceCredentialSetting. This appears to be another
bug or design mistake. If my custom data extension does not require
credentials (e.g. it obtains them from a configuration file), then
there is no reason I should have to enter them in the UI. This just
ends up being confusing for end users to enter meaningless
credentials.)
When I click the Validate button on step 3 of the data-driven
subscription wizard, the following is appended to the ReportServer log
(I cranked up logging to Verbose):
aspnet_wp!runningrequests!15c4!02/10/2005-14:42:53:: v VERBOSE: User
map'<Users><User><Name>Person
1</Name><Paths><Path>http://mymachine/ReportServer/reportservice.asmx</Path><NrReq>1</NrReq></Paths></User></Users>'
aspnet_wp!runningrequests!15c4!02/10/2005-14:42:53:: v VERBOSE:
SoapAction:
"http://schemas.microsoft.com/sqlserver/2003/12/reporting/reportingservices/GetSystemProperties"
aspnet_wp!library!15c4!02/10/2005-14:42:53:: v VERBOSE: Call to
GetSystemProperties()
aspnet_wp!library!15c4!02/10/2005-14:42:53:: v VERBOSE: Call to
GetSystemProperties completed.
aspnet_wp!runningrequests!17fc!02/10/2005-14:45:07:: v VERBOSE: User
map'<Users><User><Name>Person
1</Name><Paths><Path>http://mymachine/ReportServer/reportservice.asmx</Path><NrReq>1</NrReq></Paths></User></Users>'
aspnet_wp!runningrequests!17fc!02/10/2005-14:45:07:: v VERBOSE:
SoapAction:
"http://schemas.microsoft.com/sqlserver/2003/12/reporting/reportingservices/ListSecureMethods"
aspnet_wp!runningrequests!17fc!02/10/2005-14:45:07:: v VERBOSE: User
map'<Users><User><Name>Person
1</Name><Paths><Path>http://mymachine/ReportServer/reportservice.asmx</Path><NrReq>1</NrReq></Paths></User></Users>'
aspnet_wp!runningrequests!17fc!02/10/2005-14:45:07:: v VERBOSE:
SoapAction:
"http://schemas.microsoft.com/sqlserver/2003/12/reporting/reportingservices/GetSystemPermissions"
aspnet_wp!library!17fc!02/10/2005-14:45:07:: i INFO: Call to
GetSystemPermissions
aspnet_wp!runningrequests!17fc!02/10/2005-14:45:07:: v VERBOSE: User
map'<Users><User><Name>Person
1</Name><Paths><Path>http://mymachine/ReportServer/reportservice.asmx</Path><NrReq>1</NrReq></Paths></User></Users>'
aspnet_wp!runningrequests!17fc!02/10/2005-14:45:07:: v VERBOSE:
SoapAction:
"http://schemas.microsoft.com/sqlserver/2003/12/reporting/reportingservices/ListChildren"
aspnet_wp!library!17fc!02/10/2005-14:45:07:: v VERBOSE: Call to
ListChildren( '/', False )
aspnet_wp!library!17fc!02/10/2005-14:45:07:: v VERBOSE: Call to
ListChildren completed
aspnet_wp!runningrequests!17fc!02/10/2005-14:45:07:: v VERBOSE: User
map'<Users><User><Name>Person
1</Name><Paths><Path>http://mymachine/ReportServer/reportservice.asmx</Path><NrReq>1</NrReq></Paths></User></Users>'
aspnet_wp!runningrequests!17fc!02/10/2005-14:45:07:: v VERBOSE:
SoapAction:
"http://schemas.microsoft.com/sqlserver/2003/12/reporting/reportingservices/ListExtensions"
aspnet_wp!library!17fc!02/10/2005-14:45:07:: v VERBOSE: Call to
ListProviders: type (Data).
aspnet_wp!library!17fc!02/10/2005-14:45:07:: v VERBOSE: Call to
ListProviders completed.
aspnet_wp!runningrequests!17fc!02/10/2005-14:45:07:: v VERBOSE: User
map'<Users><User><Name>Person
1</Name><Paths><Path>http://mymachine/ReportServer/reportservice.asmx</Path><NrReq>1</NrReq></Paths></User></Users>'
aspnet_wp!runningrequests!17fc!02/10/2005-14:45:07:: v VERBOSE:
SoapAction:
"http://schemas.microsoft.com/sqlserver/2003/12/reporting/reportingservices/PrepareQuery"
aspnet_wp!library!17fc!02/10/2005-14:45:07:: v VERBOSE: Call to
PrepareQuery: dataSource
(<DataSource><DataSourceDefinition><Extension>My
DataSet</Extension><ConnectString>asdf</ConnectString><CredentialRetrieval>Store</CredentialRetrieval><WindowsCredentials>False</WindowsCredentials><ImpersonateUser>False</ImpersonateUser><UserName>user</UserName><Password>test</Password></DataSourceDefinition></DataSource>),
dataSet(<DataSet><Query><CommandType>Text</CommandType><CommandText>asdfasdf</CommandText></Query></DataSet>).
aspnet_wp!crypto!17fc!02/10/2005-14:45:07:: v VERBOSE: Starting crypto
operation DBProtectString
aspnet_wp!crypto!17fc!02/10/2005-14:45:07:: v VERBOSE: Completed crypto
operation DBProtectString
aspnet_wp!crypto!17fc!02/10/2005-14:45:07:: v VERBOSE: Starting crypto
operation DBProtectString
aspnet_wp!crypto!17fc!02/10/2005-14:45:07:: v VERBOSE: Completed crypto
operation DBProtectString
aspnet_wp!crypto!17fc!02/10/2005-14:45:07:: v VERBOSE: Starting crypto
operation DBProtectString
aspnet_wp!crypto!17fc!02/10/2005-14:45:07:: v VERBOSE: Completed crypto
operation DBProtectString
aspnet_wp!crypto!17fc!02/10/2005-14:45:07:: v VERBOSE: Starting crypto
operation DBUnProtectString
aspnet_wp!crypto!17fc!02/10/2005-14:45:07:: v VERBOSE: Completed crypto
operation DBUnProtectString
aspnet_wp!crypto!17fc!02/10/2005-14:45:07:: v VERBOSE: Starting crypto
operation DBUnProtectString
aspnet_wp!crypto!17fc!02/10/2005-14:45:07:: v VERBOSE: Completed crypto
operation DBUnProtectString
aspnet_wp!crypto!17fc!02/10/2005-14:45:07:: v VERBOSE: Starting crypto
operation DBUnProtectString
aspnet_wp!crypto!17fc!02/10/2005-14:45:07:: v VERBOSE: Completed crypto
operation DBUnProtectString
aspnet_wp!processing!17fc!02/10/2005-14:45:07:: v VERBOSE: A connection
object for the My DataSet data source has been created.
aspnet_wp!crypto!17fc!02/10/2005-14:45:07:: v VERBOSE: Starting crypto
operation DBUnProtectString
aspnet_wp!crypto!17fc!02/10/2005-14:45:07:: v VERBOSE: Completed crypto
operation DBUnProtectString
aspnet_wp!library!17fc!02/10/2005-14:45:07:: e ERROR: Throwing
Microsoft.ReportingServices.Diagnostics.Utilities.CannotPrepareQueryException:
The dataset cannot be generated. An error occurred while connecting to
a data source, or the query is not valid for the data source., ;
Info:
Microsoft.ReportingServices.Diagnostics.Utilities.CannotPrepareQueryException:
The dataset cannot be generated. An error occurred while connecting to
a data source, or the query is not valid for the data source. -->
System.NullReferenceException: Object reference not set to an instance
of an object.
at
Microsoft.ReportingServices.Library.SubscriptionManager.PrepareQuery(DataSource
dataSource, DataSetDefinition dataSet, Boolean& changed)
-- End of inner exception stack trace --
aspnet_wp!runningrequests!17fc!02/10/2005-14:45:07:: v VERBOSE: User
map'<Users><User><Name>Person
1</Name><Paths><Path>http://mymachine/ReportServer/reportservice.asmx</Path><NrReq>1</NrReq></Paths></User></Users>'
aspnet_wp!runningrequests!17fc!02/10/2005-14:45:07:: v VERBOSE:
SoapAction:
"http://schemas.microsoft.com/sqlserver/2003/12/reporting/reportingservices/GetSystemProperties"
aspnet_wp!library!17fc!02/10/2005-14:45:07:: v VERBOSE: Call to
GetSystemProperties()
aspnet_wp!library!17fc!02/10/2005-14:45:07:: v VERBOSE: Call to
GetSystemProperties completed.
No changes are made to the ReportServerService or ReportServerWebApp
logs. No other errors are listed in any of these logs.
For reference, here is my connection class:
using System;
using System.Data;
using System.Configuration;
using System.Xml;
using Microsoft.ReportingServices.DataProcessing;
namespace My.Samples.DataSetProcessingExtension
{
public class DSXConnection
: Microsoft.ReportingServices.DataProcessing.IDbConnection
{
private string _connString;
private int _connTimeout = 15;
private ConnectionState _state = ConnectionState.Closed;
public DSXConnection() {}
public DSXConnection(string connString) { _connString = connString; }
public string ConnectionString { get { return _connString; } set {
_connString = value; } }
public int ConnectionTimeout { get { return _connTimeout; } }
public ConnectionState State { get { return _state; } }
public Microsoft.ReportingServices.DataProcessing.IDbTransaction
BeginTransaction()
{
return null;
}
public void Open()
{
_state = ConnectionState.Open;
}
public void Close()
{
this.Dispose();
_state = ConnectionState.Closed;
}
// Implemented.
public Microsoft.ReportingServices.DataProcessing.IDbCommand
CreateCommand()
{
return new DSXCommand(this);
}
public string LocalizedName
{
get { return "DataSet Data Processing Extension"; }
}
public void SetConfiguration(string configuration) {}
public void Dispose() {}
}
}
I also have forms authentication and a custom security extension set
up, which works fine as far as I can tell. I don't know if that would
have any bearing on this issue, but I thought I'd mention it anyways.
Hope that helps. If you need more information feel free to post here or
e-mail me.
Wednesday, March 7, 2012
Null values not returns when "Not in List" used on column
Greetings,
I am running MS SQL 2000 here. We've just found that queries with "not
in list" where clauses do not return the rows that have NULL values in
the column that the "not in list" clause pertains to.
For instance, let's say the column is Auto.makers. The column has the
values of Ford, Porche, VW, and a few NULL. If you run a:
Select *
where Auto.makers NOT IN LIST VW
It would return all rows with Ford and Porche, but it will not return
NULLs.
At first I thought it was the reporting software we are using, but then
I ran the query with query analyzer itself and got the same behavior.
Is this normal SQL behavior. Besides adding another where clause to
include NULLs, is there a way on the database side to force NULLs to be
returned in such circumstances?
Thanks.
Paul
Yes, that is normal behavior. When you do a comparison, it is compared
against known values.
NULL represents UNKNOWN values, and therefore cannot be compared.
You should add:
OR Auto.Makers IS NULL
to your WHERE clause.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Paul" <pauldi@.iona.com> wrote in message
news:1159477112.303885.194920@.i3g2000cwc.googlegro ups.com...
> Greetings,
> I am running MS SQL 2000 here. We've just found that queries with "not
> in list" where clauses do not return the rows that have NULL values in
> the column that the "not in list" clause pertains to.
> For instance, let's say the column is Auto.makers. The column has the
> values of Ford, Porche, VW, and a few NULL. If you run a:
> Select *
> where Auto.makers NOT IN LIST VW
> It would return all rows with Ford and Porche, but it will not return
> NULLs.
> At first I thought it was the reporting software we are using, but then
> I ran the query with query analyzer itself and got the same behavior.
> Is this normal SQL behavior. Besides adding another where clause to
> include NULLs, is there a way on the database side to force NULLs to be
> returned in such circumstances?
> Thanks.
> Paul
>
|||"Paul" <pauldi@.iona.com> wrote in message
news:1159477112.303885.194920@.i3g2000cwc.googlegro ups.com...
> Greetings,
> I am running MS SQL 2000 here. We've just found that queries with "not
> in list" where clauses do not return the rows that have NULL values in
> the column that the "not in list" clause pertains to.
> For instance, let's say the column is Auto.makers. The column has the
> values of Ford, Porche, VW, and a few NULL. If you run a:
> Select *
> where Auto.makers NOT IN LIST VW
> It would return all rows with Ford and Porche, but it will not return
> NULLs.
> At first I thought it was the reporting software we are using, but then
> I ran the query with query analyzer itself and got the same behavior.
> Is this normal SQL behavior. Besides adding another where clause to
> include NULLs, is there a way on the database side to force NULLs to be
> returned in such circumstances?
> Thanks.
> Paul
>
Firstly, it isn't entirely clear just what query you ran or where your nulls
are because the pseudo-code you posted is not a valid SQL statement. It
always helps to post real code.
Secondly, null never equals null. That's just the way SQL works. If you are
sensible about design then you should avoid using nulls in any way that
forces you to write needlessly complex queries to work around them. Since
you apparently need the null values to be equal to each other one might
conclude that the person who designed your table didn't think properly about
your reporting needs.
Lookup "three-value logic" in Books Online to read about how nulls work.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
|||On 28 Sep 2006 13:58:32 -0700, Paul wrote:
(snip)
>For instance, let's say the column is Auto.makers. The column has the
>values of Ford, Porche, VW, and a few NULL. If you run a:
>Select *
>where Auto.makers NOT IN LIST VW
>It would return all rows with Ford and Porche, but it will not return
>NULLs.
(snip)
Hi Paul,
In addition to the replies by Arnie and David -- would you be willing to
wager a bet that the maker of my car is NOT IN ('Ford','Porsche','VW')?
That is what you seem to expect of your DB. NULL means "no value here".
Nothing more, nothing less. It doesn't mean "has no car", or "self-made
car", or anything else - just "no value here". The DB, like you, has no
idea if I have any car and if so, what make it is - and yet, you expect
the DB to say that the maker of my car is not Ford, Porshe, or VW.
Hugo Kornelis, SQL Server MVP
|||Arnie,
Thanks. That is what I have been telling them to do. I just recently
picked up any responsability for this database which is an extract from
the database our SAP system runs on top of. Unfortunately for me, SAP
allows them to use NULL values in fields they are attempting to report
from.
Hugo and Arnie,
That clears it up a bit, and this is how I initially explained it to my
users. As usual, they weren't satisfied with the answer since it "just
should work" they way they want it to.
Thanks all.
Paul
I am running MS SQL 2000 here. We've just found that queries with "not
in list" where clauses do not return the rows that have NULL values in
the column that the "not in list" clause pertains to.
For instance, let's say the column is Auto.makers. The column has the
values of Ford, Porche, VW, and a few NULL. If you run a:
Select *
where Auto.makers NOT IN LIST VW
It would return all rows with Ford and Porche, but it will not return
NULLs.
At first I thought it was the reporting software we are using, but then
I ran the query with query analyzer itself and got the same behavior.
Is this normal SQL behavior. Besides adding another where clause to
include NULLs, is there a way on the database side to force NULLs to be
returned in such circumstances?
Thanks.
Paul
Yes, that is normal behavior. When you do a comparison, it is compared
against known values.
NULL represents UNKNOWN values, and therefore cannot be compared.
You should add:
OR Auto.Makers IS NULL
to your WHERE clause.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Paul" <pauldi@.iona.com> wrote in message
news:1159477112.303885.194920@.i3g2000cwc.googlegro ups.com...
> Greetings,
> I am running MS SQL 2000 here. We've just found that queries with "not
> in list" where clauses do not return the rows that have NULL values in
> the column that the "not in list" clause pertains to.
> For instance, let's say the column is Auto.makers. The column has the
> values of Ford, Porche, VW, and a few NULL. If you run a:
> Select *
> where Auto.makers NOT IN LIST VW
> It would return all rows with Ford and Porche, but it will not return
> NULLs.
> At first I thought it was the reporting software we are using, but then
> I ran the query with query analyzer itself and got the same behavior.
> Is this normal SQL behavior. Besides adding another where clause to
> include NULLs, is there a way on the database side to force NULLs to be
> returned in such circumstances?
> Thanks.
> Paul
>
|||"Paul" <pauldi@.iona.com> wrote in message
news:1159477112.303885.194920@.i3g2000cwc.googlegro ups.com...
> Greetings,
> I am running MS SQL 2000 here. We've just found that queries with "not
> in list" where clauses do not return the rows that have NULL values in
> the column that the "not in list" clause pertains to.
> For instance, let's say the column is Auto.makers. The column has the
> values of Ford, Porche, VW, and a few NULL. If you run a:
> Select *
> where Auto.makers NOT IN LIST VW
> It would return all rows with Ford and Porche, but it will not return
> NULLs.
> At first I thought it was the reporting software we are using, but then
> I ran the query with query analyzer itself and got the same behavior.
> Is this normal SQL behavior. Besides adding another where clause to
> include NULLs, is there a way on the database side to force NULLs to be
> returned in such circumstances?
> Thanks.
> Paul
>
Firstly, it isn't entirely clear just what query you ran or where your nulls
are because the pseudo-code you posted is not a valid SQL statement. It
always helps to post real code.
Secondly, null never equals null. That's just the way SQL works. If you are
sensible about design then you should avoid using nulls in any way that
forces you to write needlessly complex queries to work around them. Since
you apparently need the null values to be equal to each other one might
conclude that the person who designed your table didn't think properly about
your reporting needs.
Lookup "three-value logic" in Books Online to read about how nulls work.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
|||On 28 Sep 2006 13:58:32 -0700, Paul wrote:
(snip)
>For instance, let's say the column is Auto.makers. The column has the
>values of Ford, Porche, VW, and a few NULL. If you run a:
>Select *
>where Auto.makers NOT IN LIST VW
>It would return all rows with Ford and Porche, but it will not return
>NULLs.
(snip)
Hi Paul,
In addition to the replies by Arnie and David -- would you be willing to
wager a bet that the maker of my car is NOT IN ('Ford','Porsche','VW')?
That is what you seem to expect of your DB. NULL means "no value here".
Nothing more, nothing less. It doesn't mean "has no car", or "self-made
car", or anything else - just "no value here". The DB, like you, has no
idea if I have any car and if so, what make it is - and yet, you expect
the DB to say that the maker of my car is not Ford, Porshe, or VW.
Hugo Kornelis, SQL Server MVP
|||Arnie,
Thanks. That is what I have been telling them to do. I just recently
picked up any responsability for this database which is an extract from
the database our SAP system runs on top of. Unfortunately for me, SAP
allows them to use NULL values in fields they are attempting to report
from.
Hugo and Arnie,
That clears it up a bit, and this is how I initially explained it to my
users. As usual, they weren't satisfied with the answer since it "just
should work" they way they want it to.
Thanks all.
Paul
Null values not returns when "Not in List" used on column
Greetings,
I am running MS SQL 2000 here. We've just found that queries with "not
in list" where clauses do not return the rows that have NULL values in
the column that the "not in list" clause pertains to.
For instance, let's say the column is Auto.makers. The column has the
values of Ford, Porche, VW, and a few NULL. If you run a:
Select *
where Auto.makers NOT IN LIST VW
It would return all rows with Ford and Porche, but it will not return
NULLs.
At first I thought it was the reporting software we are using, but then
I ran the query with query analyzer itself and got the same behavior.
Is this normal SQL behavior. Besides adding another where clause to
include NULLs, is there a way on the database side to force NULLs to be
returned in such circumstances?
Thanks.
PaulYes, that is normal behavior. When you do a comparison, it is compared
against known values.
NULL represents UNKNOWN values, and therefore cannot be compared.
You should add:
OR Auto.Makers IS NULL
to your WHERE clause.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Paul" <pauldi@.iona.com> wrote in message
news:1159477112.303885.194920@.i3g2000cwc.googlegroups.com...
> Greetings,
> I am running MS SQL 2000 here. We've just found that queries with "not
> in list" where clauses do not return the rows that have NULL values in
> the column that the "not in list" clause pertains to.
> For instance, let's say the column is Auto.makers. The column has the
> values of Ford, Porche, VW, and a few NULL. If you run a:
> Select *
> where Auto.makers NOT IN LIST VW
> It would return all rows with Ford and Porche, but it will not return
> NULLs.
> At first I thought it was the reporting software we are using, but then
> I ran the query with query analyzer itself and got the same behavior.
> Is this normal SQL behavior. Besides adding another where clause to
> include NULLs, is there a way on the database side to force NULLs to be
> returned in such circumstances?
> Thanks.
> Paul
>|||"Paul" <pauldi@.iona.com> wrote in message
news:1159477112.303885.194920@.i3g2000cwc.googlegroups.com...
> Greetings,
> I am running MS SQL 2000 here. We've just found that queries with "not
> in list" where clauses do not return the rows that have NULL values in
> the column that the "not in list" clause pertains to.
> For instance, let's say the column is Auto.makers. The column has the
> values of Ford, Porche, VW, and a few NULL. If you run a:
> Select *
> where Auto.makers NOT IN LIST VW
> It would return all rows with Ford and Porche, but it will not return
> NULLs.
> At first I thought it was the reporting software we are using, but then
> I ran the query with query analyzer itself and got the same behavior.
> Is this normal SQL behavior. Besides adding another where clause to
> include NULLs, is there a way on the database side to force NULLs to be
> returned in such circumstances?
> Thanks.
> Paul
>
Firstly, it isn't entirely clear just what query you ran or where your nulls
are because the pseudo-code you posted is not a valid SQL statement. It
always helps to post real code.
Secondly, null never equals null. That's just the way SQL works. If you are
sensible about design then you should avoid using nulls in any way that
forces you to write needlessly complex queries to work around them. Since
you apparently need the null values to be equal to each other one might
conclude that the person who designed your table didn't think properly about
your reporting needs.
Lookup "three-value logic" in Books Online to read about how nulls work.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||On 28 Sep 2006 13:58:32 -0700, Paul wrote:
(snip)
>For instance, let's say the column is Auto.makers. The column has the
>values of Ford, Porche, VW, and a few NULL. If you run a:
>Select *
>where Auto.makers NOT IN LIST VW
>It would return all rows with Ford and Porche, but it will not return
>NULLs.
(snip)
Hi Paul,
In addition to the replies by Arnie and David -- would you be willing to
wager a bet that the maker of my car is NOT IN ('Ford','Porsche','VW')?
That is what you seem to expect of your DB. NULL means "no value here".
Nothing more, nothing less. It doesn't mean "has no car", or "self-made
car", or anything else - just "no value here". The DB, like you, has no
idea if I have any car and if so, what make it is - and yet, you expect
the DB to say that the maker of my car is not Ford, Porshe, or VW.
--
Hugo Kornelis, SQL Server MVP|||Arnie,
Thanks. That is what I have been telling them to do. I just recently
picked up any responsability for this database which is an extract from
the database our SAP system runs on top of. Unfortunately for me, SAP
allows them to use NULL values in fields they are attempting to report
from.
Hugo and Arnie,
That clears it up a bit, and this is how I initially explained it to my
users. As usual, they weren't satisfied with the answer since it "just
should work" they way they want it to. :)
Thanks all.
Paul
I am running MS SQL 2000 here. We've just found that queries with "not
in list" where clauses do not return the rows that have NULL values in
the column that the "not in list" clause pertains to.
For instance, let's say the column is Auto.makers. The column has the
values of Ford, Porche, VW, and a few NULL. If you run a:
Select *
where Auto.makers NOT IN LIST VW
It would return all rows with Ford and Porche, but it will not return
NULLs.
At first I thought it was the reporting software we are using, but then
I ran the query with query analyzer itself and got the same behavior.
Is this normal SQL behavior. Besides adding another where clause to
include NULLs, is there a way on the database side to force NULLs to be
returned in such circumstances?
Thanks.
PaulYes, that is normal behavior. When you do a comparison, it is compared
against known values.
NULL represents UNKNOWN values, and therefore cannot be compared.
You should add:
OR Auto.Makers IS NULL
to your WHERE clause.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Paul" <pauldi@.iona.com> wrote in message
news:1159477112.303885.194920@.i3g2000cwc.googlegroups.com...
> Greetings,
> I am running MS SQL 2000 here. We've just found that queries with "not
> in list" where clauses do not return the rows that have NULL values in
> the column that the "not in list" clause pertains to.
> For instance, let's say the column is Auto.makers. The column has the
> values of Ford, Porche, VW, and a few NULL. If you run a:
> Select *
> where Auto.makers NOT IN LIST VW
> It would return all rows with Ford and Porche, but it will not return
> NULLs.
> At first I thought it was the reporting software we are using, but then
> I ran the query with query analyzer itself and got the same behavior.
> Is this normal SQL behavior. Besides adding another where clause to
> include NULLs, is there a way on the database side to force NULLs to be
> returned in such circumstances?
> Thanks.
> Paul
>|||"Paul" <pauldi@.iona.com> wrote in message
news:1159477112.303885.194920@.i3g2000cwc.googlegroups.com...
> Greetings,
> I am running MS SQL 2000 here. We've just found that queries with "not
> in list" where clauses do not return the rows that have NULL values in
> the column that the "not in list" clause pertains to.
> For instance, let's say the column is Auto.makers. The column has the
> values of Ford, Porche, VW, and a few NULL. If you run a:
> Select *
> where Auto.makers NOT IN LIST VW
> It would return all rows with Ford and Porche, but it will not return
> NULLs.
> At first I thought it was the reporting software we are using, but then
> I ran the query with query analyzer itself and got the same behavior.
> Is this normal SQL behavior. Besides adding another where clause to
> include NULLs, is there a way on the database side to force NULLs to be
> returned in such circumstances?
> Thanks.
> Paul
>
Firstly, it isn't entirely clear just what query you ran or where your nulls
are because the pseudo-code you posted is not a valid SQL statement. It
always helps to post real code.
Secondly, null never equals null. That's just the way SQL works. If you are
sensible about design then you should avoid using nulls in any way that
forces you to write needlessly complex queries to work around them. Since
you apparently need the null values to be equal to each other one might
conclude that the person who designed your table didn't think properly about
your reporting needs.
Lookup "three-value logic" in Books Online to read about how nulls work.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||On 28 Sep 2006 13:58:32 -0700, Paul wrote:
(snip)
>For instance, let's say the column is Auto.makers. The column has the
>values of Ford, Porche, VW, and a few NULL. If you run a:
>Select *
>where Auto.makers NOT IN LIST VW
>It would return all rows with Ford and Porche, but it will not return
>NULLs.
(snip)
Hi Paul,
In addition to the replies by Arnie and David -- would you be willing to
wager a bet that the maker of my car is NOT IN ('Ford','Porsche','VW')?
That is what you seem to expect of your DB. NULL means "no value here".
Nothing more, nothing less. It doesn't mean "has no car", or "self-made
car", or anything else - just "no value here". The DB, like you, has no
idea if I have any car and if so, what make it is - and yet, you expect
the DB to say that the maker of my car is not Ford, Porshe, or VW.
--
Hugo Kornelis, SQL Server MVP|||Arnie,
Thanks. That is what I have been telling them to do. I just recently
picked up any responsability for this database which is an extract from
the database our SAP system runs on top of. Unfortunately for me, SAP
allows them to use NULL values in fields they are attempting to report
from.
Hugo and Arnie,
That clears it up a bit, and this is how I initially explained it to my
users. As usual, they weren't satisfied with the answer since it "just
should work" they way they want it to. :)
Thanks all.
Paul
Null values not returns when "Not in List" used on column
Greetings,
I am running MS SQL 2000 here. We've just found that queries with "not
in list" where clauses do not return the rows that have NULL values in
the column that the "not in list" clause pertains to.
For instance, let's say the column is Auto.makers. The column has the
values of Ford, Porche, VW, and a few NULL. If you run a:
Select *
where Auto.makers NOT IN LIST VW
It would return all rows with Ford and Porche, but it will not return
NULLs.
At first I thought it was the reporting software we are using, but then
I ran the query with query analyzer itself and got the same behavior.
Is this normal SQL behavior. Besides adding another where clause to
include NULLs, is there a way on the database side to force NULLs to be
returned in such circumstances?
Thanks.
PaulYes, that is normal behavior. When you do a comparison, it is compared
against known values.
NULL represents UNKNOWN values, and therefore cannot be compared.
You should add:
OR Auto.Makers IS NULL
to your WHERE clause.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Paul" <pauldi@.iona.com> wrote in message
news:1159477112.303885.194920@.i3g2000cwc.googlegroups.com...
> Greetings,
> I am running MS SQL 2000 here. We've just found that queries with "not
> in list" where clauses do not return the rows that have NULL values in
> the column that the "not in list" clause pertains to.
> For instance, let's say the column is Auto.makers. The column has the
> values of Ford, Porche, VW, and a few NULL. If you run a:
> Select *
> where Auto.makers NOT IN LIST VW
> It would return all rows with Ford and Porche, but it will not return
> NULLs.
> At first I thought it was the reporting software we are using, but then
> I ran the query with query analyzer itself and got the same behavior.
> Is this normal SQL behavior. Besides adding another where clause to
> include NULLs, is there a way on the database side to force NULLs to be
> returned in such circumstances?
> Thanks.
> Paul
>|||"Paul" <pauldi@.iona.com> wrote in message
news:1159477112.303885.194920@.i3g2000cwc.googlegroups.com...
> Greetings,
> I am running MS SQL 2000 here. We've just found that queries with "not
> in list" where clauses do not return the rows that have NULL values in
> the column that the "not in list" clause pertains to.
> For instance, let's say the column is Auto.makers. The column has the
> values of Ford, Porche, VW, and a few NULL. If you run a:
> Select *
> where Auto.makers NOT IN LIST VW
> It would return all rows with Ford and Porche, but it will not return
> NULLs.
> At first I thought it was the reporting software we are using, but then
> I ran the query with query analyzer itself and got the same behavior.
> Is this normal SQL behavior. Besides adding another where clause to
> include NULLs, is there a way on the database side to force NULLs to be
> returned in such circumstances?
> Thanks.
> Paul
>
Firstly, it isn't entirely clear just what query you ran or where your nulls
are because the pseudo-code you posted is not a valid SQL statement. It
always helps to post real code.
Secondly, null never equals null. That's just the way SQL works. If you are
sensible about design then you should avoid using nulls in any way that
forces you to write needlessly complex queries to work around them. Since
you apparently need the null values to be equal to each other one might
conclude that the person who designed your table didn't think properly about
your reporting needs.
Lookup "three-value logic" in Books Online to read about how nulls work.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||On 28 Sep 2006 13:58:32 -0700, Paul wrote:
(snip)
>For instance, let's say the column is Auto.makers. The column has the
>values of Ford, Porche, VW, and a few NULL. If you run a:
>Select *
>where Auto.makers NOT IN LIST VW
>It would return all rows with Ford and Porche, but it will not return
>NULLs.
(snip)
Hi Paul,
In addition to the replies by Arnie and David -- would you be willing to
wager a bet that the maker of my car is NOT IN ('Ford','Porsche','VW')?
That is what you seem to expect of your DB. NULL means "no value here".
Nothing more, nothing less. It doesn't mean "has no car", or "self-made
car", or anything else - just "no value here". The DB, like you, has no
idea if I have any car and if so, what make it is - and yet, you expect
the DB to say that the maker of my car is not Ford, Porshe, or VW.
Hugo Kornelis, SQL Server MVP|||Arnie,
Thanks. That is what I have been telling them to do. I just recently
picked up any responsability for this database which is an extract from
the database our SAP system runs on top of. Unfortunately for me, SAP
allows them to use NULL values in fields they are attempting to report
from.
Hugo and Arnie,
That clears it up a bit, and this is how I initially explained it to my
users. As usual, they weren't satisfied with the answer since it "just
should work" they way they want it to.
Thanks all.
Paul
I am running MS SQL 2000 here. We've just found that queries with "not
in list" where clauses do not return the rows that have NULL values in
the column that the "not in list" clause pertains to.
For instance, let's say the column is Auto.makers. The column has the
values of Ford, Porche, VW, and a few NULL. If you run a:
Select *
where Auto.makers NOT IN LIST VW
It would return all rows with Ford and Porche, but it will not return
NULLs.
At first I thought it was the reporting software we are using, but then
I ran the query with query analyzer itself and got the same behavior.
Is this normal SQL behavior. Besides adding another where clause to
include NULLs, is there a way on the database side to force NULLs to be
returned in such circumstances?
Thanks.
PaulYes, that is normal behavior. When you do a comparison, it is compared
against known values.
NULL represents UNKNOWN values, and therefore cannot be compared.
You should add:
OR Auto.Makers IS NULL
to your WHERE clause.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Paul" <pauldi@.iona.com> wrote in message
news:1159477112.303885.194920@.i3g2000cwc.googlegroups.com...
> Greetings,
> I am running MS SQL 2000 here. We've just found that queries with "not
> in list" where clauses do not return the rows that have NULL values in
> the column that the "not in list" clause pertains to.
> For instance, let's say the column is Auto.makers. The column has the
> values of Ford, Porche, VW, and a few NULL. If you run a:
> Select *
> where Auto.makers NOT IN LIST VW
> It would return all rows with Ford and Porche, but it will not return
> NULLs.
> At first I thought it was the reporting software we are using, but then
> I ran the query with query analyzer itself and got the same behavior.
> Is this normal SQL behavior. Besides adding another where clause to
> include NULLs, is there a way on the database side to force NULLs to be
> returned in such circumstances?
> Thanks.
> Paul
>|||"Paul" <pauldi@.iona.com> wrote in message
news:1159477112.303885.194920@.i3g2000cwc.googlegroups.com...
> Greetings,
> I am running MS SQL 2000 here. We've just found that queries with "not
> in list" where clauses do not return the rows that have NULL values in
> the column that the "not in list" clause pertains to.
> For instance, let's say the column is Auto.makers. The column has the
> values of Ford, Porche, VW, and a few NULL. If you run a:
> Select *
> where Auto.makers NOT IN LIST VW
> It would return all rows with Ford and Porche, but it will not return
> NULLs.
> At first I thought it was the reporting software we are using, but then
> I ran the query with query analyzer itself and got the same behavior.
> Is this normal SQL behavior. Besides adding another where clause to
> include NULLs, is there a way on the database side to force NULLs to be
> returned in such circumstances?
> Thanks.
> Paul
>
Firstly, it isn't entirely clear just what query you ran or where your nulls
are because the pseudo-code you posted is not a valid SQL statement. It
always helps to post real code.
Secondly, null never equals null. That's just the way SQL works. If you are
sensible about design then you should avoid using nulls in any way that
forces you to write needlessly complex queries to work around them. Since
you apparently need the null values to be equal to each other one might
conclude that the person who designed your table didn't think properly about
your reporting needs.
Lookup "three-value logic" in Books Online to read about how nulls work.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||On 28 Sep 2006 13:58:32 -0700, Paul wrote:
(snip)
>For instance, let's say the column is Auto.makers. The column has the
>values of Ford, Porche, VW, and a few NULL. If you run a:
>Select *
>where Auto.makers NOT IN LIST VW
>It would return all rows with Ford and Porche, but it will not return
>NULLs.
(snip)
Hi Paul,
In addition to the replies by Arnie and David -- would you be willing to
wager a bet that the maker of my car is NOT IN ('Ford','Porsche','VW')?
That is what you seem to expect of your DB. NULL means "no value here".
Nothing more, nothing less. It doesn't mean "has no car", or "self-made
car", or anything else - just "no value here". The DB, like you, has no
idea if I have any car and if so, what make it is - and yet, you expect
the DB to say that the maker of my car is not Ford, Porshe, or VW.
Hugo Kornelis, SQL Server MVP|||Arnie,
Thanks. That is what I have been telling them to do. I just recently
picked up any responsability for this database which is an extract from
the database our SAP system runs on top of. Unfortunately for me, SAP
allows them to use NULL values in fields they are attempting to report
from.
Hugo and Arnie,
That clears it up a bit, and this is how I initially explained it to my
users. As usual, they weren't satisfied with the answer since it "just
should work" they way they want it to.
Thanks all.
Paul
Null Values in SQL query
I have datatable field with values "ABC", " ", and NULL
When I say "select * from tbl_abc where myfield <>"ABC", the query returns
the value " ". However, the field with value = NULL is ignored.
Is there any way, we get the null value also in the query results?
Thanks.select *
from tbl_abc
where myfield <> 'ABC'
OR myfield IS NULL
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"rkbnair" <rkbnair@.discussions.microsoft.com> wrote in message
news:53082B03-92E6-42AF-8241-6B4B55DE613E@.microsoft.com...
> I have datatable field with values "ABC", " ", and NULL
> When I say "select * from tbl_abc where myfield <>"ABC", the query returns
> the value " ". However, the field with value = NULL is ignored.
> Is there any way, we get the null value also in the query results?
> Thanks.|||myfield <> 'ABC' means myfield is not null. So, why should
myfield IS NULL to be specified explicitely?
"Adam Machanic" wrote:
> select *
> from tbl_abc
> where myfield <> 'ABC'
> OR myfield IS NULL
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
> "rkbnair" <rkbnair@.discussions.microsoft.com> wrote in message
> news:53082B03-92E6-42AF-8241-6B4B55DE613E@.microsoft.com...
> > I have datatable field with values "ABC", " ", and NULL
> >
> > When I say "select * from tbl_abc where myfield <>"ABC", the query returns
> > the value " ". However, the field with value = NULL is ignored.
> >
> > Is there any way, we get the null value also in the query results?
> >
> > Thanks.
>
>|||"rkbnair" <rkbnair@.discussions.microsoft.com> wrote in message
news:0B7BB9AC-B436-4DB6-AE20-4D05A4204ABB@.microsoft.com...
> myfield <> 'ABC' means myfield is not null.
No, it doesn't.
NULL is not equal to anything. Including NULL. In addition, NULL is not
not-equal to anything. Including NULL. The result of any comparison
involving NULL is unknown.
(NULL = <anything>) is unknown.
And (NULL <> <anything>) is also unknown.
And (NULL = NULL) is unknown too!
A predicate is only valid if it resolves to a true condition. (myfield <>
'ABC'), to resolve to true, must be true. If myfield is NULL, it resolves
to unknown, not true -- and therefore the row is not returned.
This is called "three-valued logic" and is quite confusing for a lot of
people just starting with SQL. It's one reason that I recommend that NULLs
should be used as little as possible. Instead of using NULLs, I advocate
the use of "missing value tokens" -- well defined tokens that you can put in
for missing values when you'd otherwise use a NULL. For instance, an empty
string, or the string 'Value Undefined'. These can be domain-specific; so
if you're dealing with postal codes, your unknown value token might be
'00000'; if you were dealing with an author biography, your token might be,
'Biography not on record'.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
When I say "select * from tbl_abc where myfield <>"ABC", the query returns
the value " ". However, the field with value = NULL is ignored.
Is there any way, we get the null value also in the query results?
Thanks.select *
from tbl_abc
where myfield <> 'ABC'
OR myfield IS NULL
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"rkbnair" <rkbnair@.discussions.microsoft.com> wrote in message
news:53082B03-92E6-42AF-8241-6B4B55DE613E@.microsoft.com...
> I have datatable field with values "ABC", " ", and NULL
> When I say "select * from tbl_abc where myfield <>"ABC", the query returns
> the value " ". However, the field with value = NULL is ignored.
> Is there any way, we get the null value also in the query results?
> Thanks.|||myfield <> 'ABC' means myfield is not null. So, why should
myfield IS NULL to be specified explicitely?
"Adam Machanic" wrote:
> select *
> from tbl_abc
> where myfield <> 'ABC'
> OR myfield IS NULL
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
> "rkbnair" <rkbnair@.discussions.microsoft.com> wrote in message
> news:53082B03-92E6-42AF-8241-6B4B55DE613E@.microsoft.com...
> > I have datatable field with values "ABC", " ", and NULL
> >
> > When I say "select * from tbl_abc where myfield <>"ABC", the query returns
> > the value " ". However, the field with value = NULL is ignored.
> >
> > Is there any way, we get the null value also in the query results?
> >
> > Thanks.
>
>|||"rkbnair" <rkbnair@.discussions.microsoft.com> wrote in message
news:0B7BB9AC-B436-4DB6-AE20-4D05A4204ABB@.microsoft.com...
> myfield <> 'ABC' means myfield is not null.
No, it doesn't.
NULL is not equal to anything. Including NULL. In addition, NULL is not
not-equal to anything. Including NULL. The result of any comparison
involving NULL is unknown.
(NULL = <anything>) is unknown.
And (NULL <> <anything>) is also unknown.
And (NULL = NULL) is unknown too!
A predicate is only valid if it resolves to a true condition. (myfield <>
'ABC'), to resolve to true, must be true. If myfield is NULL, it resolves
to unknown, not true -- and therefore the row is not returned.
This is called "three-valued logic" and is quite confusing for a lot of
people just starting with SQL. It's one reason that I recommend that NULLs
should be used as little as possible. Instead of using NULLs, I advocate
the use of "missing value tokens" -- well defined tokens that you can put in
for missing values when you'd otherwise use a NULL. For instance, an empty
string, or the string 'Value Undefined'. These can be domain-specific; so
if you're dealing with postal codes, your unknown value token might be
'00000'; if you were dealing with an author biography, your token might be,
'Biography not on record'.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
NULL values in CLR TableResult UDF
Hi everyone,
I need my UDF (which returns a table) to be able to return NULL values.
My function looks like this:
<SqlFunction(FillRowMethodName:="Process_TrainingInfo", TableDefinition:=" CourseName nvarchar(80), CreditDate DateTime, " + _
"CreditResult nvarchar(1), ExpiryDate DateTime ")> _
Public Shared Function funct_GetCreditsFromTIMS(ByVal EmployeeID As String) As IEnumerable
Dim dr() as dataRow
…. This queries an oracle database which returns a small number of rows…. This all works well….
Return dr
End Function
This is the Fill Row Method:
Public Shared Sub Process_ TrainingInfo(ByVal row As Object, <Runtime.InteropServices.Out()>ByRef CourseName As String, _
<Runtime.InteropServices.Out()> ByRef CreditDate As Date, _
<Runtime.InteropServices.Out()> ByRef CreditResult As String, _
<Runtime.InteropServices.Out()> ByRef ExpiryDate As Date)
Dim dr As DataRow = CType(row, DataRow)
CourseName = dr.Item(1).ToString
CreditDate = CType(dr.Item(2), Date)
CreditResult = dr.Item(3).ToString
'dr.Item(5) Might be NULL!!!!
If Not IsDBNull(dr.Item(5)) Then
ExpiryDate = CType(dr.Item(5), Date)
End If
End Sub
The problem is that the EXPIRYDATE field may be NULL. If I leave the field empty, or I try ExpiryDate = Nothing I get this error:
An error occurred while getting new row from user defined Table Valued Function :
System.Data.SqlTypes.SqlTypeException: SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.
I have also tried:
Dim nullDate as Date
ExpiryDate = nullDate
…but I get the same error.
I can’t do ExpiryDate = Dbnull.value, as I get this error “System.dbnull can not be converted to date”
Is there anyway that I can do this?
Thanks,
Forch
Niels
Saturday, February 25, 2012
Null Processing and RowCounts
Problem: Although my query against three relational fact tables correctly returns the basic truth that "RowCount All_Transaction table" = "RowCount Loan table" + "RowCount Sale table", my cube-browser query rowcounts in these tables' resulting cube shows (incorrectly) that "RowCount All_Transaction table" = "RowCount Loan Table" ...and I'm scratchin' my head about why! Note: I'm on SSAS2005 SP2, Dev Edition
Background:
DSV -- Fact Tables:
(1.) All_Transaction: Contains generic facts (dates, asset info, etc.) on two types of asset transactions (sales and loans). In DSV, it is is directly related to each of the next two fact tables.
(2) Loan: Contains loan-specific facts (loan amt, etc.) for loan transactions. Does not directly connect to Sale fact table.
(3) Sale: Contains sale-specific facts (sale price, etc.) for sale transactions. Does not directly connect to Loan fact table.
Additionally, DSV table relationships from all_transaction to other two has all_transaction as "Destination / PK" and other table as "Source / FK" table, which is in keeping with relational rule that all FK's must relate to one PK. Lastly, with regard to relational and DSV fact tables, cardinality is actually one-to-one, since each row in LOAN (or SALE) table relates to exactly one row in All_Transaction.
Cube Measure Groups:
Of course, all three fact tables are measure groups. ALL_Transaction and LOAN fact tables are also reference dimensions, with dimension cardinality to each other's measure group set as "One". This was done to allow slice-n-dice by dimensions, all of which are connected to just those two measure groups.
Anybody have ideas on why this is occurring, and what to do about it?
Note: My relational SQL-Dev coworker suggests it sounds like a "left-join vs. inner join vs. right join" issue and/or an issue of which measure group is the "main" measure group", but I don't know that these ideas apply to SSAS.
Your thoughts?
I'm going to close the loop by answering my own question...Reason for Problem:
The reason is that, (1) I was using the "ALL_Transaction" fact table also as an intermediate dimension for "ALL_Transaction" - related dimensions indirectly referencing "LOAN" fact table, and (2) I had selected "unknown member" for null properties on the dimension key for "ALL_Transaction" intermediate dimension. As a result, the COUNT aggregation, by design, ignored all of the so-called "unknown member" ALL_Transaction PK's that don't have a corresponding LOAN table FK (by design, many don't).
Solution: On "ALL_Transaction" dimension key field, change "KeyColumn / Null Processing" property from "Unknown Member" to "Preserve". Simple (now that I found the solution).
Monday, February 20, 2012
NULL output when trying to script objects with SQL-DMO
The following statement SHOULD generate a script of the specified stored
procedure. Instead, it returns NULL. Please tell me what I'm doign wrong:
DECLARE @.oServer int
DECLARE @.method varchar(300)
DECLARE @.TSQL varchar(4000)
DECLARE @.ScriptType int
EXEC sp_OACreate 'SQLDMO.SQLServer', @.oServer OUT
EXEC sp_OASetProperty @.oServer, 'loginsecure', 'true'
EXEC sp_OAMethod @.oServer, 'Connect', NULL, 'US1SQLDEV'
SET @.ScriptType = 1|4|32|64|256|262144
SET @.method = 'Databases("Compass").' +
'StoredProcedures("p_rpt_Distance").Script' +
'(' + CAST (@.ScriptType AS VARCHAR) + ')'
EXEC sp_OAMethod @.oServer, @.method ,
@.TSQL OUTPUT
SELECT @.TSQL
EXEC sp_OADestroy @.oServercongratulations, you've found the slowest possible way to do this ;)
if your sql is going to be <=4000 chars, you should just go against
syscomments. otherwise, your variable won't be big enough, anyway.
if you're dead-set on using the object junk, check the script type.
you've got the script type of 64 (To File only) set, but no file name to
output to.
CadeBryant wrote:
> The following statement SHOULD generate a script of the specified stored
> procedure. Instead, it returns NULL. Please tell me what I'm doign wrong
:
> DECLARE @.oServer int
> DECLARE @.method varchar(300)
> DECLARE @.TSQL varchar(4000)
> DECLARE @.ScriptType int
> EXEC sp_OACreate 'SQLDMO.SQLServer', @.oServer OUT
> EXEC sp_OASetProperty @.oServer, 'loginsecure', 'true'
> EXEC sp_OAMethod @.oServer, 'Connect', NULL, 'US1SQLDEV'
> SET @.ScriptType = 1|4|32|64|256|262144
> SET @.method = 'Databases("Compass").' +
> 'StoredProcedures("p_rpt_Distance").Script' +
> '(' + CAST (@.ScriptType AS VARCHAR) + ')'
> EXEC sp_OAMethod @.oServer, @.method ,
> @.TSQL OUTPUT
> SELECT @.TSQL
> EXEC sp_OADestroy @.oServer
procedure. Instead, it returns NULL. Please tell me what I'm doign wrong:
DECLARE @.oServer int
DECLARE @.method varchar(300)
DECLARE @.TSQL varchar(4000)
DECLARE @.ScriptType int
EXEC sp_OACreate 'SQLDMO.SQLServer', @.oServer OUT
EXEC sp_OASetProperty @.oServer, 'loginsecure', 'true'
EXEC sp_OAMethod @.oServer, 'Connect', NULL, 'US1SQLDEV'
SET @.ScriptType = 1|4|32|64|256|262144
SET @.method = 'Databases("Compass").' +
'StoredProcedures("p_rpt_Distance").Script' +
'(' + CAST (@.ScriptType AS VARCHAR) + ')'
EXEC sp_OAMethod @.oServer, @.method ,
@.TSQL OUTPUT
SELECT @.TSQL
EXEC sp_OADestroy @.oServercongratulations, you've found the slowest possible way to do this ;)
if your sql is going to be <=4000 chars, you should just go against
syscomments. otherwise, your variable won't be big enough, anyway.
if you're dead-set on using the object junk, check the script type.
you've got the script type of 64 (To File only) set, but no file name to
output to.
CadeBryant wrote:
> The following statement SHOULD generate a script of the specified stored
> procedure. Instead, it returns NULL. Please tell me what I'm doign wrong
:
> DECLARE @.oServer int
> DECLARE @.method varchar(300)
> DECLARE @.TSQL varchar(4000)
> DECLARE @.ScriptType int
> EXEC sp_OACreate 'SQLDMO.SQLServer', @.oServer OUT
> EXEC sp_OASetProperty @.oServer, 'loginsecure', 'true'
> EXEC sp_OAMethod @.oServer, 'Connect', NULL, 'US1SQLDEV'
> SET @.ScriptType = 1|4|32|64|256|262144
> SET @.method = 'Databases("Compass").' +
> 'StoredProcedures("p_rpt_Distance").Script' +
> '(' + CAST (@.ScriptType AS VARCHAR) + ')'
> EXEC sp_OAMethod @.oServer, @.method ,
> @.TSQL OUTPUT
> SELECT @.TSQL
> EXEC sp_OADestroy @.oServer
NULL in Joins
I have converted some legacy SQL to use the newer JOIN syntax, however the
original SQL returns the required NULL values for the RolesToLinksXRefID
column, but the newer SQL doesn't.
--OLD SQL
Select T.TempID, FT.Message, L.LinkID, L.LinkCaption, RLX.RolesToLinksXRefID
From Templates T, ForeignTextKey FTK, ForeignText FT, Links L,
RolesToLinksXRef RLX
Where T.Title = FTK.TextCode
And FTK.TextID = FT.TextID
And RLX.LinkID =* L.LinkID
And RLX.TempID =* T.TempID
And RLX.RoleID = 1
And FT.CultureID = 1
--NEW SQL
SELECT T.TempID, FT.Message, L.LinkID, L.LinkCaption, RLX.RolesToLinksXRefID
FROM RolesToLinksXRef RLX
RIGHT OUTER JOIN Links L ON RLX.LinkID = L.LinkID
RIGHT OUTER JOIN Templates T
INNER JOIN ForeignTextKey FTK ON T.Title = FTK.TextCode
INNER JOIN ForeignText FT ON FTK.TextID = FT.TextID ON RLX.TempID = T.TempID
WHERE (RLX.RoleID = 1) AND (FT.CultureID = 1)
Could anyone help me to return a resultset using the new SQL that is
identical to the one produced by the old SQL?
Thanks,
Vince.The conditions in the WHERE clause eliminate all unmatched rows in the join,
and in effect cause the joins to behave as inner joins.
You need to "allow" the unmatched values from the outer table in the where
clause. Try this (untested since you haven't posted DDL and sample data):
WHERE (RLX.RoleID = 1 or RLX.RoleID is null) AND (FT.CultureID = 1)
or
WHERE (RLX.RoleID = 1 or RLX.RoleID is null) AND (FT.CultureID = 1 or
FT.CultureID is null)
ML
http://milambda.blogspot.com/|||Hi Vince
I don't know how the old =, =*, *= syntax is interpreted, as I've never
used it; so I can't guess how it processes the joins. The key thing is
the order in which the joins are processed - which is not necessarily
the order they appear in the SQL statement.
If you have more than one OUTER join, the resultset can vary depending
on which join is performed first. I don't know how the order of
processing is determined - in fact I'm not even sure if it isn't
arbitrary. (Anyone out there happen to know in which order SQL
processes multiple outer joins?)
I avoid this problem by never using more than one OUTER join at any
level of a SELECT statement. This is possible by using subqueries:
instead of
[SQL statement 1]
Table A
INNER JOIN
Table B
ON A.[col]=B.[col]
LEFT JOIN
Table C
ON B.[col]=C.[col]
RIGHT JOIN
Table D
ON C.[col]=D.[col]
which is highly ambiguous, I'd use e.g. (ON statements omitted for
clarity):
[SQL statement 2]
SELECT [columns]
(SELECT [columns] FROM
(SELECT [columns] FROM
Table A
INNER JOIN
Table B) setA
LEFT JOIN
Table C) setB
RIGHT JOIN
Table D
which is one of two different SQL statements that the first statement
_could_ mean.
(The other one is
(Table A INNER JOIN Table B)
LEFT JOIN
(TableC RIGHT JOIN Table D)
)
The problem is that you've got two RIGHT JOINS: one to Links and one to
(Templates INNER JOIN FTK INNER JOIN FT). In a RIGHT join the set of
rows to be returned, in the first instance, is all rows from the 2nd
table - there may or may not be matching rows in the 1st table. With 2
RIGHT JOINS, it's not clear which table should be used to determine the
set of rows - should it be Links or (Templates INNER JOIN FTK INNER
JOIN FT)?
Another way of putting the question is: using the old statement, if you
have 30 rows in Links and 40 rows in (Templates INNER JOIN etc...), do
you end up with 30 rows or 40 rows in the result-set?
For the former (same number of rows as in Links), you'd have to rewrite
the statement as
SELECT [columns] FROM
(SELECT [columns] FROM
RLX
RIGHT JOIN
(Templates INNER JOIN... etc)
) set1
RIGHT JOIN
Links
For the latter, you'd swap the positions of Links and (Templates INNER
JOIN ...):
SELECT [columns] FROM
(SELECT [columns] FROM
RLX
RIGHT JOIN
Links
) set1
RIGHT JOIN
(Templates INNER JOIN... etc) set2
Hope this helps.
cheers
Seb|||VinceKav wrote on Fri, 6 Jan 2006 04:05:01 -0800:
> I have converted some legacy SQL to use the newer JOIN syntax, however the
> original SQL returns the required NULL values for the RolesToLinksXRefID
> column, but the newer SQL doesn't.
> --OLD SQL
> Select T.TempID, FT.Message, L.LinkID, L.LinkCaption,
> RLX.RolesToLinksXRefID From Templates T, ForeignTextKey FTK,
> ForeignText FT, Links L, RolesToLinksXRef RLX
> Where T.Title = FTK.TextCode
> And FTK.TextID = FT.TextID
> And RLX.LinkID =* L.LinkID
> And RLX.TempID =* T.TempID
> And RLX.RoleID = 1
> And FT.CultureID = 1
> --NEW SQL
> SELECT T.TempID, FT.Message, L.LinkID, L.LinkCaption,
> RLX.RolesToLinksXRefID FROM RolesToLinksXRef RLX
> RIGHT OUTER JOIN Links L ON RLX.LinkID = L.LinkID
> RIGHT OUTER JOIN Templates T
> INNER JOIN ForeignTextKey FTK ON T.Title = FTK.TextCode
> INNER JOIN ForeignText FT ON FTK.TextID = FT.TextID ON RLX.TempID =
> T.TempID WHERE (RLX.RoleID = 1) AND (FT.CultureID = 1)
> Could anyone help me to return a resultset using the new SQL that is
> identical to the one produced by the old SQL?
You need to move your WHERE criteria into the joins, or as ML did check for
nulls in the WHERE. Try this:
SELECT T.TempID, FT.Message, L.LinkID, L.LinkCaption,
RLX.RolesToLinksXRefID FROM RolesToLinksXRef RLX
RIGHT OUTER JOIN Links L ON RLX.LinkID = L.LinkID AND RLX.RoleID = 1
RIGHT OUTER JOIN Templates T
INNER JOIN ForeignTextKey FTK ON T.Title = FTK.TextCode
INNER JOIN ForeignText FT ON FTK.TextID = FT.TextID ON RLX.TempID =
T.TempID AND FT.CultureID = 1
This moves your WHERE criteria to filter the rows before the joins are
evaluated.
Dan|||VinceKav (VinceKav@.discussions.microsoft.com) writes:
> I have converted some legacy SQL to use the newer JOIN syntax, however the
> original SQL returns the required NULL values for the RolesToLinksXRefID
> column, but the newer SQL doesn't.
> --OLD SQL
> Select T.TempID, FT.Message, L.LinkID, L.LinkCaption,
> RLX.RolesToLinksXRefID
> From Templates T, ForeignTextKey FTK, ForeignText FT, Links L,
> RolesToLinksXRef RLX
> Where T.Title = FTK.TextCode
> And FTK.TextID = FT.TextID
> And RLX.LinkID =* L.LinkID
> And RLX.TempID =* T.TempID
> And RLX.RoleID = 1
> And FT.CultureID = 1
> --NEW SQL
> SELECT T.TempID, FT.Message, L.LinkID, L.LinkCaption,
> RLX.RolesToLinksXRefID
> FROM RolesToLinksXRef RLX
> RIGHT OUTER JOIN Links L ON RLX.LinkID = L.LinkID
> RIGHT OUTER JOIN Templates T
> INNER JOIN ForeignTextKey FTK ON T.Title = FTK.TextCode
> INNER JOIN ForeignText FT ON FTK.TextID = FT.TextID ON RLX.TempID =
> T.TempID
> WHERE (RLX.RoleID = 1) AND (FT.CultureID = 1)
> Could anyone help me to return a resultset using the new SQL that is
> identical to the one produced by the old SQL?
It only goes to show that the old syntax was confusing. :-)
A tip for the new syntax, is to stick with LEFT JOIN. At least I find
that easier to read. Then you can read the query as you start with
the table in the FROM clause, and then you add the other tables.
Here is my suggestion for a rewrite:
SELECT T.TempID, FT.Message, L.LinkID, L.LinkCaption,
RLX.RolesToLinksXRefID
FROM Templates T
JOIN ForeignTextKey FTK ON T.Title = FTK.TextCode
JOIN ForeignText FT ON FTK.TextID = FT.TextID
LEFT JOIN (RolesToLinksXRef RLX
JOIN Links L RLX.LinkID = L.LinkID)
ON RLX.TempID = T.TempID
AND RLX.RoleID = 1
WHERE FT.CultureID = 1
Note that for the join from RolesToLinksXRef to Links there are
two possibilities. I've assumed that you want an inner join between
these two tables, something that was not possible to express with
the old syntax. The newer syntax permits this, as you can use
parenthesis to control computation order. (The logical order that is.
The optimizer may recast as long as the result is not affected.)
Of course, since I don't have the tables or test data, this may not be
correct.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thank you all for your help, but I'm still not getting the required results.
I would very much like to understand the relationship between the old and ne
w
SQL syntax so that I can apply it to other legacy code. I have supplied mor
e
information relating to the table relationships and the output.
The tables used in the query are related as follows:
The RolesToLinksXRef table holds a foreign key to the following tables:
1. Links on RolesToLinksXRef.LinkID = Links.LinkID
2. Templates on RolesToLinksXRef.TempID = Templates.TempID
The Templates table holds a foreign key to the following tables:
1. ForeignTextKey on Templates.Title = ForeignTextKey.TextCode
The ForeignTextKey table holds a foreign key to the following tables:
1. ForeignText on ForeignTextKey.TextID = ForeignText.TextID
All the answers I have received produce much the same results, however none
produce the same results as the old SQL.
The old SQL produces the following correct result set:
TempID Message LinkID LinkCaption RolesToLinksXRefID
60 Sign In 1 ADD NULL
60 Sign Out 2 EXIT NULL
60 Edit User 3 EDIT NULL
and the new SQL produces this result set:
TempID Message LinkID LinkCaption RolesToLinksXRefID
60 Sign In NULL NULL NULL
The purpose of the query is to return all the Templates.TempID, the
ForeignText.Message, the Links.LinkID, the Links.Caption and the
RolesToLinksXRef.RolesToLinksXRefID for a specific RolesToLinksXRef.RoleID,
as well as all the links on templates that the role has not been allocated t
o.
A template may have 6 links, however the role may only have been allocated
3, I need to return all rows so I can see which links have or have not been
allocated.
Many thanks in advance,
Vince Kavanagh.
"Erland Sommarskog" wrote:
> VinceKav (VinceKav@.discussions.microsoft.com) writes:
> It only goes to show that the old syntax was confusing. :-)
> A tip for the new syntax, is to stick with LEFT JOIN. At least I find
> that easier to read. Then you can read the query as you start with
> the table in the FROM clause, and then you add the other tables.
> Here is my suggestion for a rewrite:
> SELECT T.TempID, FT.Message, L.LinkID, L.LinkCaption,
> RLX.RolesToLinksXRefID
> FROM Templates T
> JOIN ForeignTextKey FTK ON T.Title = FTK.TextCode
> JOIN ForeignText FT ON FTK.TextID = FT.TextID
> LEFT JOIN (RolesToLinksXRef RLX
> JOIN Links L RLX.LinkID = L.LinkID)
> ON RLX.TempID = T.TempID
> AND RLX.RoleID = 1
> WHERE FT.CultureID = 1
>
> Note that for the join from RolesToLinksXRef to Links there are
> two possibilities. I've assumed that you want an inner join between
> these two tables, something that was not possible to express with
> the old syntax. The newer syntax permits this, as you can use
> parenthesis to control computation order. (The logical order that is.
> The optimizer may recast as long as the result is not affected.)
> Of course, since I don't have the tables or test data, this may not be
> correct.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>|||VinceKav (VinceKav@.discussions.microsoft.com) writes:
> Thank you all for your help, but I'm still not getting the required
> results.
The standard recommendation is that you should include:
o CREATE TABLE statments for your tables.
o INSERT statements with sample data.
o The desired result without the sample.
Without that, you will be more or less good guesses.
> I would very much like to understand the relationship between the old
> and new SQL syntax so that I can apply it to other legacy code.
I'm afraid that very few can help you with that. It's not that we don't
understand the new syntax. But we have forgotten how the old syntax
worked - if we ever understood it.
> The purpose of the query is to return all the Templates.TempID, the
> ForeignText.Message, the Links.LinkID, the Links.Caption and the
> RolesToLinksXRef.RolesToLinksXRefID for a specific
> RolesToLinksXRef.RoleID, as well as all the links on templates that the
> role has not been allocated to.
So a wild guess based from the sample output, is that this query might
work:
SELECT T.TempID, FT.Message, L.LinkID, L.LinkCaption,
RLX.RolesToLinksXRefID
FROM Templates T
JOIN ForeignTextKey FTK ON T.Title = FTK.TextCode
JOIN ForeignText FT ON FTK.TextID = FT.TextID
CROSS JOIN Links L
LEFT JOIN RolesToLinksXRef RLX ON RLX.LinkID = L.LinkID)
AND RLX.TempID = T.TempID
AND RLX.RoleID = 1
WHERE FT.CultureID = 1
Thanks to the use of CROSS JOIN, this has to be stamped as an
"unusual query".
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||
Hi Erland,
Thanks very much for your help, the last query you sent me worked, I had
also tried the CROSS join approach but that resulted in too many records.
However as you advised I have posted the table creation and data insert
scripts.
I have been working with SQL for many years, but this is the first time I
have been so exasperated. Have you written any articles on the methodology
to use when designing queries that use multiple joins?
--Table creation scripts
--**********************
CREATE TABLE dbo.Templates
(
TempID int NULL,
Title nvarchar(50) NULL
)
GO
CREATE TABLE dbo.ForeignTextKey
(
TextID int NULL,
TextCode nvarchar(50) NULL
)
GO
CREATE TABLE dbo.ForeignText
(
TextID int NULL,
CultureID int NULL,
Message nvarchar(100) NULL
)
GO
CREATE TABLE dbo.Links
(
LinkID int NULL,
LinkCaption nvarchar(50) NULL
)
GO
CREATE TABLE dbo.RolesToLinksXRef
(
RolesToLinksXRefID int NULL,
LinkID int NULL,
TempID int NULL,
RoleID int NULL
)
GO
--Data inserts
--************
--Links data
INSERT INTO [Links]([LinkID], [LinkCaption])VALUES(1, 'ADD')
INSERT INTO [Links]([LinkID], [LinkCaption])VALUES(2, 'EDIT')
INSERT INTO [Links]([LinkID], [LinkCaption])VALUES(3, 'VIEW')
INSERT INTO [Links]([LinkID], [LinkCaption])VALUES(4, 'COPY')
INSERT INTO [Links]([LinkID], [LinkCaption])VALUES(5, 'DELETE')
--ForeignTextKey data
INSERT INTO [ForeignTextKey]([TextID], [TextCode])VALUES(1, 'FTK_001')
INSERT INTO [ForeignTextKey]([TextID], [TextCode])VALUES(2, 'FTK_002')
INSERT INTO [ForeignTextKey]([TextID], [TextCode])VALUES(3, 'FTK_003')
INSERT INTO [ForeignTextKey]([TextID], [TextCode])VALUES(4, 'FTK_004')
INSERT INTO [ForeignTextKey]([TextID], [TextCode])VALUES(5, 'FTK_005')
--ForeignText data
INSERT INTO [ForeignText]([TextID], [CultureID], [Message])VALUES(1, 1,
'Template1')
INSERT INTO [ForeignText]([TextID], [CultureID], [Message])VALUES(2, 1,
'Template2')
INSERT INTO [ForeignText]([TextID], [CultureID], [Message])VALUES(3, 1,
'Template3')
INSERT INTO [ForeignText]([TextID], [CultureID], [Message])VALUES(4, 1,
'Template4')
INSERT INTO [ForeignText]([TextID], [CultureID], [Message])VALUES(5, 1,
'Template5')
INSERT INTO [ForeignText]([TextID], [CultureID], [Message])VALUES(1, 2,
'Calibre1')
INSERT INTO [ForeignText]([TextID], [CultureID], [Message])VALUES(2, 2,
'Calibre2')
INSERT INTO [ForeignText]([TextID], [CultureID], [Message])VALUES(3, 2,
'Calibre3')
INSERT INTO [ForeignText]([TextID], [CultureID], [Message])VALUES(4, 2,
'Calibre4')
INSERT INTO [ForeignText]([TextID], [CultureID], [Message])VALUES(5, 2,
'Calibre5')
--Template data
INSERT INTO [Templates]([TempID], [Title])VALUES(1,'FTK_001')
INSERT INTO [Templates]([TempID], [Title])VALUES(2,'FTK_002')
INSERT INTO [Templates]([TempID], [Title])VALUES(3,'FTK_003')
INSERT INTO [Templates]([TempID], [Title])VALUES(4,'FTK_004')
INSERT INTO [Templates]([TempID], [Title])VALUES(5,'FTK_005')
--RolesToLinksXRef data
INSERT INTO [RolesToLinksXRef]([RolesToLinksXRefID],
[LinkID], [TempID],
[RoleID])VALUES(1, 1, 1, 1)
INSERT INTO [RolesToLinksXRef]([RolesToLinksXRefID],
[LinkID], [TempID],
[RoleID])VALUES(2, 2, 1, 1)
INSERT INTO [RolesToLinksXRef]([RolesToLinksXRefID],
[LinkID], [TempID],
[RoleID])VALUES(3, 3, 1, 1)
INSERT INTO [RolesToLinksXRef]([RolesToLinksXRefID],
[LinkID], [TempID],
[RoleID])VALUES(4, 4, 1, 1)
INSERT INTO [RolesToLinksXRef]([RolesToLinksXRefID],
[LinkID], [TempID],
[RoleID])VALUES(5, 1, 2, 1)
INSERT INTO [RolesToLinksXRef]([RolesToLinksXRefID],
[LinkID], [TempID],
[RoleID])VALUES(6, 2, 2, 1)
INSERT INTO [RolesToLinksXRef]([RolesToLinksXRefID],
[LinkID], [TempID],
[RoleID])VALUES(7, 1, 3, 1)
INSERT INTO [RolesToLinksXRef]([RolesToLinksXRefID],
[LinkID], [TempID],
[RoleID])VALUES(8, 2, 3, 1)
INSERT INTO [RolesToLinksXRef]([RolesToLinksXRefID],
[LinkID], [TempID],
[RoleID])VALUES(9, 3, 3, 1)
INSERT INTO [RolesToLinksXRef]([RolesToLinksXRefID],
[LinkID], [TempID],
[RoleID])VALUES(10, 1, 4, 1)
INSERT INTO [RolesToLinksXRef]([RolesToLinksXRefID],
[LinkID], [TempID],
[RoleID])VALUES(11, 2, 4, 1)
INSERT INTO [RolesToLinksXRef]([RolesToLinksXRefID],
[LinkID], [TempID],
[RoleID])VALUES(12, 1, 1, 2)
INSERT INTO [RolesToLinksXRef]([RolesToLinksXRefID],
[LinkID], [TempID],
[RoleID])VALUES(13, 2, 1, 2)
INSERT INTO [RolesToLinksXRef]([RolesToLinksXRefID],
[LinkID], [TempID],
[RoleID])VALUES(14, 1, 2, 2)
INSERT INTO [RolesToLinksXRef]([RolesToLinksXRefID],
[LinkID], [TempID],
[RoleID])VALUES(15, 2, 2, 2)
INSERT INTO [RolesToLinksXRef]([RolesToLinksXRefID],
[LinkID], [TempID],
[RoleID])VALUES(16, 3, 2, 2)
INSERT INTO [RolesToLinksXRef]([RolesToLinksXRefID],
[LinkID], [TempID],
[RoleID])VALUES(17, 1, 3, 2)
INSERT INTO [RolesToLinksXRef]([RolesToLinksXRefID],
[LinkID], [TempID],
[RoleID])VALUES(18, 2, 3, 2)
INSERT INTO [RolesToLinksXRef]([RolesToLinksXRefID],
[LinkID], [TempID],
[RoleID])VALUES(19, 3, 3, 2)
INSERT INTO [RolesToLinksXRef]([RolesToLinksXRefID],
[LinkID], [TempID],
[RoleID])VALUES(20, 4, 3, 2)
/*
This is the desired result in that it displays which links have been applied
to templates for a specific role.
Desired Result
**************
TempID Message LinkID LinkCaption Roles
ToLinkXRefID
-- -- -- -- --
1 Template1 1 ADD 1
1 Template1 2 EDIT 2
1 Template1 3 VIEW 3
1 Template1 4 COPY 4
1 Template1 5 DELETE NULL
2 Template2 1 ADD 5
2 Template2 2 EDIT 6
2 Template2 3 VIEW NULL
2 Template2 4 COPY NULL
2 Template2 5 DELETE NULL
3 Template3 1 ADD 7
3 Template3 2 EDIT 8
3 Template3 3 VIEW 9
3 Template3 4 COPY NULL
3 Template3 5 DELETE NULL
4 Template4 1 ADD 10
4 Template4 2 EDIT 11
4 Template4 3 VIEW NULL
4 Template4 4 COPY NULL
4 Template4 5 DELETE NULL
5 Template5 1 ADD NULL
5 Template5 2 EDIT NULL
5 Template5 3 VIEW NULL
5 Template5 4 COPY NULL
5 Template5 5 DELETE NULL
*/
Once again many thanks,
Vince.
"Erland Sommarskog" wrote:
> VinceKav (VinceKav@.discussions.microsoft.com) writes:
> The standard recommendation is that you should include:
> o CREATE TABLE statments for your tables.
> o INSERT statements with sample data.
> o The desired result without the sample.
> Without that, you will be more or less good guesses.
>
> I'm afraid that very few can help you with that. It's not that we don't
> understand the new syntax. But we have forgotten how the old syntax
> worked - if we ever understood it.
>
> So a wild guess based from the sample output, is that this query might
> work:
> SELECT T.TempID, FT.Message, L.LinkID, L.LinkCaption,
> RLX.RolesToLinksXRefID
> FROM Templates T
> JOIN ForeignTextKey FTK ON T.Title = FTK.TextCode
> JOIN ForeignText FT ON FTK.TextID = FT.TextID
> CROSS JOIN Links L
> LEFT JOIN RolesToLinksXRef RLX ON RLX.LinkID = L.LinkID)
> AND RLX.TempID = T.TempID
> AND RLX.RoleID = 1
> WHERE FT.CultureID = 1
> Thanks to the use of CROSS JOIN, this has to be stamped as an
> "unusual query".
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>|||On Wed, 11 Jan 2006 04:47:04 -0800, VinceKav wrote:
>
>Hi Erland,
>Thanks very much for your help, the last query you sent me worked, I had
>also tried the CROSS join approach but that resulted in too many records.
>However as you advised I have posted the table creation and data insert
>scripts.
Hi Vince,
Thanks for posting the repro code. The query below returns the results
you need:
SELECT t.TempID, ft.Message,
l.LinkID, l.LinkCaption,
x.RolesToLinksXRefID
FROM ForeignText AS ft
INNER JOIN ForeignTextKey AS ftk
ON ftk.TextID = ft.TextID
INNER JOIN Templates AS t
ON t.Title = ftk.TextCode
CROSS JOIN Links AS l
LEFT JOIN RolesToLinksXRef AS x
ON x.TempID = t.TempID
AND x.LinkID = l.LinkID
AND x.RoleID = 1
WHERE ft.CultureID = 1
ORDER BY t.TempID, l.LinkID
(BTW, I just went back to Erlands previous message, and after removing
the extraneous closing parenthesis, his query gives the exact same
results).
Hugo Kornelis, SQL Server MVP|||VinceKav (VinceKav@.discussions.microsoft.com) writes:
> Thanks very much for your help, the last query you sent me worked, I had
> also tried the CROSS join approach but that resulted in too many records.
> However as you advised I have posted the table creation and data insert
> scripts.
> I have been working with SQL for many years, but this is the first time
> I have been so exasperated. Have you written any articles on the
> methodology to use when designing queries that use multiple joins?
Egads, no! That is not an article I would want to write about. The
foremost important when designing queries is to know your data model.
As I mentioned, you query was a bit odd, but chance had it that I had
a similar case at work recently. One of junior developers was tasked
to go through all stored procedures with the old-style join as a preparation
for SQL 2005. When she ran into difficulties, she sent me the query
and asked for help. One of the queries was translated to something
similar to yours. What I recall was that the procedure included some
commented piece of code with ANSI join, and apparently the original
author had given up with the ANSI join and used the legacy syntax. And
it took me some time to get the query right as well, and then I was
fortunate to know the tables.
It is the CROSS JOIN that makes it looks so unusual. The old syntax
is much better of hiding cross joins so that you don't see them.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
original SQL returns the required NULL values for the RolesToLinksXRefID
column, but the newer SQL doesn't.
--OLD SQL
Select T.TempID, FT.Message, L.LinkID, L.LinkCaption, RLX.RolesToLinksXRefID
From Templates T, ForeignTextKey FTK, ForeignText FT, Links L,
RolesToLinksXRef RLX
Where T.Title = FTK.TextCode
And FTK.TextID = FT.TextID
And RLX.LinkID =* L.LinkID
And RLX.TempID =* T.TempID
And RLX.RoleID = 1
And FT.CultureID = 1
--NEW SQL
SELECT T.TempID, FT.Message, L.LinkID, L.LinkCaption, RLX.RolesToLinksXRefID
FROM RolesToLinksXRef RLX
RIGHT OUTER JOIN Links L ON RLX.LinkID = L.LinkID
RIGHT OUTER JOIN Templates T
INNER JOIN ForeignTextKey FTK ON T.Title = FTK.TextCode
INNER JOIN ForeignText FT ON FTK.TextID = FT.TextID ON RLX.TempID = T.TempID
WHERE (RLX.RoleID = 1) AND (FT.CultureID = 1)
Could anyone help me to return a resultset using the new SQL that is
identical to the one produced by the old SQL?
Thanks,
Vince.The conditions in the WHERE clause eliminate all unmatched rows in the join,
and in effect cause the joins to behave as inner joins.
You need to "allow" the unmatched values from the outer table in the where
clause. Try this (untested since you haven't posted DDL and sample data):
WHERE (RLX.RoleID = 1 or RLX.RoleID is null) AND (FT.CultureID = 1)
or
WHERE (RLX.RoleID = 1 or RLX.RoleID is null) AND (FT.CultureID = 1 or
FT.CultureID is null)
ML
http://milambda.blogspot.com/|||Hi Vince
I don't know how the old =, =*, *= syntax is interpreted, as I've never
used it; so I can't guess how it processes the joins. The key thing is
the order in which the joins are processed - which is not necessarily
the order they appear in the SQL statement.
If you have more than one OUTER join, the resultset can vary depending
on which join is performed first. I don't know how the order of
processing is determined - in fact I'm not even sure if it isn't
arbitrary. (Anyone out there happen to know in which order SQL
processes multiple outer joins?)
I avoid this problem by never using more than one OUTER join at any
level of a SELECT statement. This is possible by using subqueries:
instead of
[SQL statement 1]
Table A
INNER JOIN
Table B
ON A.[col]=B.[col]
LEFT JOIN
Table C
ON B.[col]=C.[col]
RIGHT JOIN
Table D
ON C.[col]=D.[col]
which is highly ambiguous, I'd use e.g. (ON statements omitted for
clarity):
[SQL statement 2]
SELECT [columns]
(SELECT [columns] FROM
(SELECT [columns] FROM
Table A
INNER JOIN
Table B) setA
LEFT JOIN
Table C) setB
RIGHT JOIN
Table D
which is one of two different SQL statements that the first statement
_could_ mean.
(The other one is
(Table A INNER JOIN Table B)
LEFT JOIN
(TableC RIGHT JOIN Table D)
)
The problem is that you've got two RIGHT JOINS: one to Links and one to
(Templates INNER JOIN FTK INNER JOIN FT). In a RIGHT join the set of
rows to be returned, in the first instance, is all rows from the 2nd
table - there may or may not be matching rows in the 1st table. With 2
RIGHT JOINS, it's not clear which table should be used to determine the
set of rows - should it be Links or (Templates INNER JOIN FTK INNER
JOIN FT)?
Another way of putting the question is: using the old statement, if you
have 30 rows in Links and 40 rows in (Templates INNER JOIN etc...), do
you end up with 30 rows or 40 rows in the result-set?
For the former (same number of rows as in Links), you'd have to rewrite
the statement as
SELECT [columns] FROM
(SELECT [columns] FROM
RLX
RIGHT JOIN
(Templates INNER JOIN... etc)
) set1
RIGHT JOIN
Links
For the latter, you'd swap the positions of Links and (Templates INNER
JOIN ...):
SELECT [columns] FROM
(SELECT [columns] FROM
RLX
RIGHT JOIN
Links
) set1
RIGHT JOIN
(Templates INNER JOIN... etc) set2
Hope this helps.
cheers
Seb|||VinceKav wrote on Fri, 6 Jan 2006 04:05:01 -0800:
> I have converted some legacy SQL to use the newer JOIN syntax, however the
> original SQL returns the required NULL values for the RolesToLinksXRefID
> column, but the newer SQL doesn't.
> --OLD SQL
> Select T.TempID, FT.Message, L.LinkID, L.LinkCaption,
> RLX.RolesToLinksXRefID From Templates T, ForeignTextKey FTK,
> ForeignText FT, Links L, RolesToLinksXRef RLX
> Where T.Title = FTK.TextCode
> And FTK.TextID = FT.TextID
> And RLX.LinkID =* L.LinkID
> And RLX.TempID =* T.TempID
> And RLX.RoleID = 1
> And FT.CultureID = 1
> --NEW SQL
> SELECT T.TempID, FT.Message, L.LinkID, L.LinkCaption,
> RLX.RolesToLinksXRefID FROM RolesToLinksXRef RLX
> RIGHT OUTER JOIN Links L ON RLX.LinkID = L.LinkID
> RIGHT OUTER JOIN Templates T
> INNER JOIN ForeignTextKey FTK ON T.Title = FTK.TextCode
> INNER JOIN ForeignText FT ON FTK.TextID = FT.TextID ON RLX.TempID =
> T.TempID WHERE (RLX.RoleID = 1) AND (FT.CultureID = 1)
> Could anyone help me to return a resultset using the new SQL that is
> identical to the one produced by the old SQL?
You need to move your WHERE criteria into the joins, or as ML did check for
nulls in the WHERE. Try this:
SELECT T.TempID, FT.Message, L.LinkID, L.LinkCaption,
RLX.RolesToLinksXRefID FROM RolesToLinksXRef RLX
RIGHT OUTER JOIN Links L ON RLX.LinkID = L.LinkID AND RLX.RoleID = 1
RIGHT OUTER JOIN Templates T
INNER JOIN ForeignTextKey FTK ON T.Title = FTK.TextCode
INNER JOIN ForeignText FT ON FTK.TextID = FT.TextID ON RLX.TempID =
T.TempID AND FT.CultureID = 1
This moves your WHERE criteria to filter the rows before the joins are
evaluated.
Dan|||VinceKav (VinceKav@.discussions.microsoft.com) writes:
> I have converted some legacy SQL to use the newer JOIN syntax, however the
> original SQL returns the required NULL values for the RolesToLinksXRefID
> column, but the newer SQL doesn't.
> --OLD SQL
> Select T.TempID, FT.Message, L.LinkID, L.LinkCaption,
> RLX.RolesToLinksXRefID
> From Templates T, ForeignTextKey FTK, ForeignText FT, Links L,
> RolesToLinksXRef RLX
> Where T.Title = FTK.TextCode
> And FTK.TextID = FT.TextID
> And RLX.LinkID =* L.LinkID
> And RLX.TempID =* T.TempID
> And RLX.RoleID = 1
> And FT.CultureID = 1
> --NEW SQL
> SELECT T.TempID, FT.Message, L.LinkID, L.LinkCaption,
> RLX.RolesToLinksXRefID
> FROM RolesToLinksXRef RLX
> RIGHT OUTER JOIN Links L ON RLX.LinkID = L.LinkID
> RIGHT OUTER JOIN Templates T
> INNER JOIN ForeignTextKey FTK ON T.Title = FTK.TextCode
> INNER JOIN ForeignText FT ON FTK.TextID = FT.TextID ON RLX.TempID =
> T.TempID
> WHERE (RLX.RoleID = 1) AND (FT.CultureID = 1)
> Could anyone help me to return a resultset using the new SQL that is
> identical to the one produced by the old SQL?
It only goes to show that the old syntax was confusing. :-)
A tip for the new syntax, is to stick with LEFT JOIN. At least I find
that easier to read. Then you can read the query as you start with
the table in the FROM clause, and then you add the other tables.
Here is my suggestion for a rewrite:
SELECT T.TempID, FT.Message, L.LinkID, L.LinkCaption,
RLX.RolesToLinksXRefID
FROM Templates T
JOIN ForeignTextKey FTK ON T.Title = FTK.TextCode
JOIN ForeignText FT ON FTK.TextID = FT.TextID
LEFT JOIN (RolesToLinksXRef RLX
JOIN Links L RLX.LinkID = L.LinkID)
ON RLX.TempID = T.TempID
AND RLX.RoleID = 1
WHERE FT.CultureID = 1
Note that for the join from RolesToLinksXRef to Links there are
two possibilities. I've assumed that you want an inner join between
these two tables, something that was not possible to express with
the old syntax. The newer syntax permits this, as you can use
parenthesis to control computation order. (The logical order that is.
The optimizer may recast as long as the result is not affected.)
Of course, since I don't have the tables or test data, this may not be
correct.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thank you all for your help, but I'm still not getting the required results.
I would very much like to understand the relationship between the old and ne
w
SQL syntax so that I can apply it to other legacy code. I have supplied mor
e
information relating to the table relationships and the output.
The tables used in the query are related as follows:
The RolesToLinksXRef table holds a foreign key to the following tables:
1. Links on RolesToLinksXRef.LinkID = Links.LinkID
2. Templates on RolesToLinksXRef.TempID = Templates.TempID
The Templates table holds a foreign key to the following tables:
1. ForeignTextKey on Templates.Title = ForeignTextKey.TextCode
The ForeignTextKey table holds a foreign key to the following tables:
1. ForeignText on ForeignTextKey.TextID = ForeignText.TextID
All the answers I have received produce much the same results, however none
produce the same results as the old SQL.
The old SQL produces the following correct result set:
TempID Message LinkID LinkCaption RolesToLinksXRefID
60 Sign In 1 ADD NULL
60 Sign Out 2 EXIT NULL
60 Edit User 3 EDIT NULL
and the new SQL produces this result set:
TempID Message LinkID LinkCaption RolesToLinksXRefID
60 Sign In NULL NULL NULL
The purpose of the query is to return all the Templates.TempID, the
ForeignText.Message, the Links.LinkID, the Links.Caption and the
RolesToLinksXRef.RolesToLinksXRefID for a specific RolesToLinksXRef.RoleID,
as well as all the links on templates that the role has not been allocated t
o.
A template may have 6 links, however the role may only have been allocated
3, I need to return all rows so I can see which links have or have not been
allocated.
Many thanks in advance,
Vince Kavanagh.
"Erland Sommarskog" wrote:
> VinceKav (VinceKav@.discussions.microsoft.com) writes:
> It only goes to show that the old syntax was confusing. :-)
> A tip for the new syntax, is to stick with LEFT JOIN. At least I find
> that easier to read. Then you can read the query as you start with
> the table in the FROM clause, and then you add the other tables.
> Here is my suggestion for a rewrite:
> SELECT T.TempID, FT.Message, L.LinkID, L.LinkCaption,
> RLX.RolesToLinksXRefID
> FROM Templates T
> JOIN ForeignTextKey FTK ON T.Title = FTK.TextCode
> JOIN ForeignText FT ON FTK.TextID = FT.TextID
> LEFT JOIN (RolesToLinksXRef RLX
> JOIN Links L RLX.LinkID = L.LinkID)
> ON RLX.TempID = T.TempID
> AND RLX.RoleID = 1
> WHERE FT.CultureID = 1
>
> Note that for the join from RolesToLinksXRef to Links there are
> two possibilities. I've assumed that you want an inner join between
> these two tables, something that was not possible to express with
> the old syntax. The newer syntax permits this, as you can use
> parenthesis to control computation order. (The logical order that is.
> The optimizer may recast as long as the result is not affected.)
> Of course, since I don't have the tables or test data, this may not be
> correct.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>|||VinceKav (VinceKav@.discussions.microsoft.com) writes:
> Thank you all for your help, but I'm still not getting the required
> results.
The standard recommendation is that you should include:
o CREATE TABLE statments for your tables.
o INSERT statements with sample data.
o The desired result without the sample.
Without that, you will be more or less good guesses.
> I would very much like to understand the relationship between the old
> and new SQL syntax so that I can apply it to other legacy code.
I'm afraid that very few can help you with that. It's not that we don't
understand the new syntax. But we have forgotten how the old syntax
worked - if we ever understood it.
> The purpose of the query is to return all the Templates.TempID, the
> ForeignText.Message, the Links.LinkID, the Links.Caption and the
> RolesToLinksXRef.RolesToLinksXRefID for a specific
> RolesToLinksXRef.RoleID, as well as all the links on templates that the
> role has not been allocated to.
So a wild guess based from the sample output, is that this query might
work:
SELECT T.TempID, FT.Message, L.LinkID, L.LinkCaption,
RLX.RolesToLinksXRefID
FROM Templates T
JOIN ForeignTextKey FTK ON T.Title = FTK.TextCode
JOIN ForeignText FT ON FTK.TextID = FT.TextID
CROSS JOIN Links L
LEFT JOIN RolesToLinksXRef RLX ON RLX.LinkID = L.LinkID)
AND RLX.TempID = T.TempID
AND RLX.RoleID = 1
WHERE FT.CultureID = 1
Thanks to the use of CROSS JOIN, this has to be stamped as an
"unusual query".
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||
Hi Erland,
Thanks very much for your help, the last query you sent me worked, I had
also tried the CROSS join approach but that resulted in too many records.
However as you advised I have posted the table creation and data insert
scripts.
I have been working with SQL for many years, but this is the first time I
have been so exasperated. Have you written any articles on the methodology
to use when designing queries that use multiple joins?
--Table creation scripts
--**********************
CREATE TABLE dbo.Templates
(
TempID int NULL,
Title nvarchar(50) NULL
)
GO
CREATE TABLE dbo.ForeignTextKey
(
TextID int NULL,
TextCode nvarchar(50) NULL
)
GO
CREATE TABLE dbo.ForeignText
(
TextID int NULL,
CultureID int NULL,
Message nvarchar(100) NULL
)
GO
CREATE TABLE dbo.Links
(
LinkID int NULL,
LinkCaption nvarchar(50) NULL
)
GO
CREATE TABLE dbo.RolesToLinksXRef
(
RolesToLinksXRefID int NULL,
LinkID int NULL,
TempID int NULL,
RoleID int NULL
)
GO
--Data inserts
--************
--Links data
INSERT INTO [Links]([LinkID], [LinkCaption])VALUES(1, 'ADD')
INSERT INTO [Links]([LinkID], [LinkCaption])VALUES(2, 'EDIT')
INSERT INTO [Links]([LinkID], [LinkCaption])VALUES(3, 'VIEW')
INSERT INTO [Links]([LinkID], [LinkCaption])VALUES(4, 'COPY')
INSERT INTO [Links]([LinkID], [LinkCaption])VALUES(5, 'DELETE')
--ForeignTextKey data
INSERT INTO [ForeignTextKey]([TextID], [TextCode])VALUES(1, 'FTK_001')
INSERT INTO [ForeignTextKey]([TextID], [TextCode])VALUES(2, 'FTK_002')
INSERT INTO [ForeignTextKey]([TextID], [TextCode])VALUES(3, 'FTK_003')
INSERT INTO [ForeignTextKey]([TextID], [TextCode])VALUES(4, 'FTK_004')
INSERT INTO [ForeignTextKey]([TextID], [TextCode])VALUES(5, 'FTK_005')
--ForeignText data
INSERT INTO [ForeignText]([TextID], [CultureID], [Message])VALUES(1, 1,
'Template1')
INSERT INTO [ForeignText]([TextID], [CultureID], [Message])VALUES(2, 1,
'Template2')
INSERT INTO [ForeignText]([TextID], [CultureID], [Message])VALUES(3, 1,
'Template3')
INSERT INTO [ForeignText]([TextID], [CultureID], [Message])VALUES(4, 1,
'Template4')
INSERT INTO [ForeignText]([TextID], [CultureID], [Message])VALUES(5, 1,
'Template5')
INSERT INTO [ForeignText]([TextID], [CultureID], [Message])VALUES(1, 2,
'Calibre1')
INSERT INTO [ForeignText]([TextID], [CultureID], [Message])VALUES(2, 2,
'Calibre2')
INSERT INTO [ForeignText]([TextID], [CultureID], [Message])VALUES(3, 2,
'Calibre3')
INSERT INTO [ForeignText]([TextID], [CultureID], [Message])VALUES(4, 2,
'Calibre4')
INSERT INTO [ForeignText]([TextID], [CultureID], [Message])VALUES(5, 2,
'Calibre5')
--Template data
INSERT INTO [Templates]([TempID], [Title])VALUES(1,'FTK_001')
INSERT INTO [Templates]([TempID], [Title])VALUES(2,'FTK_002')
INSERT INTO [Templates]([TempID], [Title])VALUES(3,'FTK_003')
INSERT INTO [Templates]([TempID], [Title])VALUES(4,'FTK_004')
INSERT INTO [Templates]([TempID], [Title])VALUES(5,'FTK_005')
--RolesToLinksXRef data
INSERT INTO [RolesToLinksXRef]([RolesToLinksXRefID],
[LinkID], [TempID],
[RoleID])VALUES(1, 1, 1, 1)
INSERT INTO [RolesToLinksXRef]([RolesToLinksXRefID],
[LinkID], [TempID],
[RoleID])VALUES(2, 2, 1, 1)
INSERT INTO [RolesToLinksXRef]([RolesToLinksXRefID],
[LinkID], [TempID],
[RoleID])VALUES(3, 3, 1, 1)
INSERT INTO [RolesToLinksXRef]([RolesToLinksXRefID],
[LinkID], [TempID],
[RoleID])VALUES(4, 4, 1, 1)
INSERT INTO [RolesToLinksXRef]([RolesToLinksXRefID],
[LinkID], [TempID],
[RoleID])VALUES(5, 1, 2, 1)
INSERT INTO [RolesToLinksXRef]([RolesToLinksXRefID],
[LinkID], [TempID],
[RoleID])VALUES(6, 2, 2, 1)
INSERT INTO [RolesToLinksXRef]([RolesToLinksXRefID],
[LinkID], [TempID],
[RoleID])VALUES(7, 1, 3, 1)
INSERT INTO [RolesToLinksXRef]([RolesToLinksXRefID],
[LinkID], [TempID],
[RoleID])VALUES(8, 2, 3, 1)
INSERT INTO [RolesToLinksXRef]([RolesToLinksXRefID],
[LinkID], [TempID],
[RoleID])VALUES(9, 3, 3, 1)
INSERT INTO [RolesToLinksXRef]([RolesToLinksXRefID],
[LinkID], [TempID],
[RoleID])VALUES(10, 1, 4, 1)
INSERT INTO [RolesToLinksXRef]([RolesToLinksXRefID],
[LinkID], [TempID],
[RoleID])VALUES(11, 2, 4, 1)
INSERT INTO [RolesToLinksXRef]([RolesToLinksXRefID],
[LinkID], [TempID],
[RoleID])VALUES(12, 1, 1, 2)
INSERT INTO [RolesToLinksXRef]([RolesToLinksXRefID],
[LinkID], [TempID],
[RoleID])VALUES(13, 2, 1, 2)
INSERT INTO [RolesToLinksXRef]([RolesToLinksXRefID],
[LinkID], [TempID],
[RoleID])VALUES(14, 1, 2, 2)
INSERT INTO [RolesToLinksXRef]([RolesToLinksXRefID],
[LinkID], [TempID],
[RoleID])VALUES(15, 2, 2, 2)
INSERT INTO [RolesToLinksXRef]([RolesToLinksXRefID],
[LinkID], [TempID],
[RoleID])VALUES(16, 3, 2, 2)
INSERT INTO [RolesToLinksXRef]([RolesToLinksXRefID],
[LinkID], [TempID],
[RoleID])VALUES(17, 1, 3, 2)
INSERT INTO [RolesToLinksXRef]([RolesToLinksXRefID],
[LinkID], [TempID],
[RoleID])VALUES(18, 2, 3, 2)
INSERT INTO [RolesToLinksXRef]([RolesToLinksXRefID],
[LinkID], [TempID],
[RoleID])VALUES(19, 3, 3, 2)
INSERT INTO [RolesToLinksXRef]([RolesToLinksXRefID],
[LinkID], [TempID],
[RoleID])VALUES(20, 4, 3, 2)
/*
This is the desired result in that it displays which links have been applied
to templates for a specific role.
Desired Result
**************
TempID Message LinkID LinkCaption Roles
ToLinkXRefID
-- -- -- -- --
1 Template1 1 ADD 1
1 Template1 2 EDIT 2
1 Template1 3 VIEW 3
1 Template1 4 COPY 4
1 Template1 5 DELETE NULL
2 Template2 1 ADD 5
2 Template2 2 EDIT 6
2 Template2 3 VIEW NULL
2 Template2 4 COPY NULL
2 Template2 5 DELETE NULL
3 Template3 1 ADD 7
3 Template3 2 EDIT 8
3 Template3 3 VIEW 9
3 Template3 4 COPY NULL
3 Template3 5 DELETE NULL
4 Template4 1 ADD 10
4 Template4 2 EDIT 11
4 Template4 3 VIEW NULL
4 Template4 4 COPY NULL
4 Template4 5 DELETE NULL
5 Template5 1 ADD NULL
5 Template5 2 EDIT NULL
5 Template5 3 VIEW NULL
5 Template5 4 COPY NULL
5 Template5 5 DELETE NULL
*/
Once again many thanks,
Vince.
"Erland Sommarskog" wrote:
> VinceKav (VinceKav@.discussions.microsoft.com) writes:
> The standard recommendation is that you should include:
> o CREATE TABLE statments for your tables.
> o INSERT statements with sample data.
> o The desired result without the sample.
> Without that, you will be more or less good guesses.
>
> I'm afraid that very few can help you with that. It's not that we don't
> understand the new syntax. But we have forgotten how the old syntax
> worked - if we ever understood it.
>
> So a wild guess based from the sample output, is that this query might
> work:
> SELECT T.TempID, FT.Message, L.LinkID, L.LinkCaption,
> RLX.RolesToLinksXRefID
> FROM Templates T
> JOIN ForeignTextKey FTK ON T.Title = FTK.TextCode
> JOIN ForeignText FT ON FTK.TextID = FT.TextID
> CROSS JOIN Links L
> LEFT JOIN RolesToLinksXRef RLX ON RLX.LinkID = L.LinkID)
> AND RLX.TempID = T.TempID
> AND RLX.RoleID = 1
> WHERE FT.CultureID = 1
> Thanks to the use of CROSS JOIN, this has to be stamped as an
> "unusual query".
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>|||On Wed, 11 Jan 2006 04:47:04 -0800, VinceKav wrote:
>
>Hi Erland,
>Thanks very much for your help, the last query you sent me worked, I had
>also tried the CROSS join approach but that resulted in too many records.
>However as you advised I have posted the table creation and data insert
>scripts.
Hi Vince,
Thanks for posting the repro code. The query below returns the results
you need:
SELECT t.TempID, ft.Message,
l.LinkID, l.LinkCaption,
x.RolesToLinksXRefID
FROM ForeignText AS ft
INNER JOIN ForeignTextKey AS ftk
ON ftk.TextID = ft.TextID
INNER JOIN Templates AS t
ON t.Title = ftk.TextCode
CROSS JOIN Links AS l
LEFT JOIN RolesToLinksXRef AS x
ON x.TempID = t.TempID
AND x.LinkID = l.LinkID
AND x.RoleID = 1
WHERE ft.CultureID = 1
ORDER BY t.TempID, l.LinkID
(BTW, I just went back to Erlands previous message, and after removing
the extraneous closing parenthesis, his query gives the exact same
results).
Hugo Kornelis, SQL Server MVP|||VinceKav (VinceKav@.discussions.microsoft.com) writes:
> Thanks very much for your help, the last query you sent me worked, I had
> also tried the CROSS join approach but that resulted in too many records.
> However as you advised I have posted the table creation and data insert
> scripts.
> I have been working with SQL for many years, but this is the first time
> I have been so exasperated. Have you written any articles on the
> methodology to use when designing queries that use multiple joins?
Egads, no! That is not an article I would want to write about. The
foremost important when designing queries is to know your data model.
As I mentioned, you query was a bit odd, but chance had it that I had
a similar case at work recently. One of junior developers was tasked
to go through all stored procedures with the old-style join as a preparation
for SQL 2005. When she ran into difficulties, she sent me the query
and asked for help. One of the queries was translated to something
similar to yours. What I recall was that the procedure included some
commented piece of code with ANSI join, and apparently the original
author had given up with the ANSI join and used the legacy syntax. And
it took me some time to get the query right as well, and then I was
fortunate to know the tables.
It is the CROSS JOIN that makes it looks so unusual. The old syntax
is much better of hiding cross joins so that you don't see them.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
Subscribe to:
Posts (Atom)