Need to create <Query> statement programmatically

I am creating a web application that uses a using a web service to get data for my reports. Since the webservice only accepts 1 parameter called "sql" (the sql select statement), I am using the report's query string to get the data.

Here is the data source and dataset info I am using:

DataSource
Name: WebService
Type: XML
Connection string: http://localhost/myWeb/myWebService.asmx
Credentials: No credentials

DataSet
Query tab:
Name: WebService
Data source: WebService
Command type: Text
Query String: <Query><SoapAction>......</SoapAction></Query>

Here is a sample of the <Query> string that I use when I first build the report:

<Query>
<SoapAction>http://tempuri.org/GetDataset</SoapAction>
<Method Namespace="http://tempuri.org/" Name="GetDataset">
<Parameters>
<Parameter Name="sql" Type="String">
<DefaultValue>Select * From Customers</DefaultValue>
</Parameter>

</Parameters>
</Method>

<ElementPath IgnoreNamespaces="true">GetDatasetResponse{}/GetDatasetResult{}/diffgram{}/NewDataSet{}/Results</ElementPath>
</Query>

When the user selects a report in the web application, they are prompted for information about the sql statement, and then I can rebuild the <Query> xml fragment, substituting the new sql statemet for the default one. for example, the statement "Select * From Reports" would be replaced with "Select * From Customers where LN = 'Smith'".

Then I want to attach that new <Query> statement to the report and run it. How can I set this information in the report? I can't find anything that talks about it, but there must be some way!

Thanks in advance for your help!

Hi Videl,

There are a couple of ways you could accomplish this. But the best way is to take advantage of the QueryString being an expression. You could have the querystring reference a report parameter, and set this parameter to the new xml query with your web application when you run the report.

Example Dataset Query string: =Parameters!QueryParameter.Value

Or since you are just changing the sql query parameter, you could create a dataset parameter that references the report parameter. To do this you need to create a dataset parameter with the same name as the webmethod parameter. In this case, this would be sql. And the value for the dataset parameter would be the same expression above, only that in this case the value would just be the modified sql instead of the entire Xml query.

Another way is to directly modify the RDL and re-publish it, but this would not be very performant and would cause the report to be only useable by one user at a time.|||

Hi Ian,

OK, I set the dataset query string to Parameters!sql.Value, and created a report parameter called "sql". I set the default value of this parameter to :

="<Query><SoapAction>http://tempuri.org/GetDataset</SoapAction><Method Namespace='http://tempuri.org/' Name='GetDataset'><Parameters><Parameter Name='sql' Type='String'><DefaultValue>Select * From Reports</DefaultValue></Parameter></Parameters></Method><ElementPath IgnoreNamespaces='true'>GetDatasetResponse{}/GetDatasetResult{}/diffgram{}/NewDataSet{}/Results</ElementPath></Query>"

When I preview the report, I get the following error message:

An error occurred during local report processing. The definition of the report '/myReport/' is invalid. the Value expression for the textbox 'xyz' refers to the field 'xyz'. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope.

It looks like it is not finding the data before it tries to populate the fields on the report.

I also tried using a dataset paramter, and that looked maybe easier. I created a dataset parameter called sql, and set it's default value to the sql statement I want. Then I deleted the default value of the sql statement in the query statement, and ran it. I was prompted for the sql statement, and it ran fine. However, I am not sure how to set that value from code, since that is a dataset parameter and not a report parameter. That looks like an easier solution, but could you please tell me how to set that dataset parameter from my .NET code? Thanks!!!

Marlene

|||I think using a datset parameter is easier and less error prone, then dealing with the entire Xml query. However, you should be able to use the other approach. The reason you are getting the exception is that the fields are no longer defined in the dataset, since they were tied to the query. You need to manually add the fields to the dataset by right-clicking on the dataset in the Datasets panel in the Report Designer, and click add in the context menu.

Regarding your question, to set the dataset parameter programmatically, you need to use a report parameter as the value of the dataset parameter, like you did with the query expression, and set the report parameter in your call to create render the report.

Ian|||

Hi Ian,

I did as you suggested, manually adding the fields, and creating a reports parameter called "sql". Now the dataset parameter "sql" is set to Parameters!sql.value.

Now when I run the report, I get the right number of rows, but they are all blank. I have tried setting the field values to the dataset field value, and I have tried leaving them blank, and get the same results either way. How do I get them to mesh up?

|||Yes, they need to be mapped to the dataset field names. The issue you describe is usually caused by custom fields not being mapped to existing dataset fields. Can you make sure that the fields you are mapping to exist in the dataset returned by the Xml Query? Since this is a dataset being returned, can you try adding the following custom dataset field,

Name = RowID, DatabaseField = id

This should map RowID to the id attibute of the NewTable element. This also assumes that your ElementPath describes the path to the table level, without specifying the fields to retrieve. For example,

<ElementPath IgnoreNamespaces="true">GetDatasetResponse{}/GetDatasetResult{}/diffgram{}/NewDataSet{}/Results{}/NewTable</ElementPath>|||

Hi,

I didn't quite understand that.

I do have the <ElementPath> tag that you described in my <query> string, except it is slightly different:

<ElementPath IgnoreNamespaces="true">GetDatasetResponse{}/GetDatasetResult{}/diffgram{}/NewDataSet{}/Results</ElementPath

When I replaced /Results with /Results{}/NewTable, data was no longer returned to the dataset, so I changed it back.

I added a dataset field named "RowID", set it's type to "Database Field" and set the value to "=id" (I did not type in the quotes). However the data still did not display.

FYI, here is the entire query string I am using. I include it since it includes that elementPath part that stubbornly refuses to play nice with NewTable:
-

<Query>
<SoapAction>http://tempuri.org/GetDataset</SoapAction>
<Method Namespace="http://tempuri.org/" Name="GetDataset">
<Parameters>
<Parameter Name="sql" Type="String">
<DefaultValue></DefaultValue>
</Parameter>
</Parameters>
</Method>
<ElementPath IgnoreNamespaces="true">GetDatasetResponse{}/GetDatasetResult{}/diffgram{}/NewDataSet{}/Results</ElementPath>
</Query>
-
Here is a sample output from my webservice
-
<?xml version="1.0" encoding="utf-8" ?>

- <DataSet xmlns="http://tempuri.org/">

- <xs:schema id="NewDataSet" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">

- <xs:element name="NewDataSet" msdata:IsDataSet="true" msdata:UseCurrentLocale="true">

- <xs:complexType>

- <xs:choice minOccurs="0" maxOccurs="unbounded">

- <xs:element name="Results">

- <xs:complexType>

- <xs:sequence>

<xs:element name="ReportID" type="xs:int" minOccurs="0" />

<xs:element name="ReportName" type="xs:string" minOccurs="0" />

<xs:element name="ReportFriendlyName" type="xs:string" minOccurs="0" />

<xs:element name="ReportDescription" type="xs:string" minOccurs="0" />

<xs:element name="Display" type="xs:boolean" minOccurs="0" />

<xs:element name="DataViewName" type="xs:string" minOccurs="0" />

<xs:element name="Example1" type="xs:boolean" minOccurs="0" />

</xs:sequence>

</xs:complexType>

</xs:element>

</xs:choice>

</xs:complexType>

</xs:element>

</xs:schema>

- <diffgr:diffgram xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1">

- <NewDataSet xmlns="">

- <Results diffgr:id="Results1" msdata:rowOrder="0">

<ReportID>10</ReportID>

<ReportName>Reports_ByReportID1</ReportName>

<ReportFriendlyName>Reports by ID, w/ Dataset Parameter</ReportFriendlyName>

<ReportDescription>List of reports by report ID. This example uses a dataset parameter, which is referenced in the query string.</ReportDescription>

<Display>true</Display>

<DataViewName>Reports</DataViewName>

<Example1>false</Example1>

</Results>

</NewDataSet>

</diffgr:diffgram>

</DataSet>
--

And here are all my dataset fields:

FieldName Type Value
ReportID Database field =Fields!ReportID.Value
ReportName Database field =Fields!ReportName.Value
ReportFriendlyName Database field =Fields!ReportFriendlyName.Value

RowID Database field =id

|||Your element path is correct--you are describing the relationships to the table level. I mixed the name of your table, Results, with that of my dataset, also Results.

When creating custom Dataset fields, the value should be just the database field name. The value in the field is treated as a literal, so Reporting services is trying to match your custom dataset field, ReportID, to a field from the dataset returned from the query named "=Fields!ReportID.Value"

Try using these custom dataset field.

FieldNameType ValueReportIDDatabase field ReportIDReportNameDatabase field ReportNameReportFriendlyNameDatabase field ReportFriendlyName

More information on custom dataset fields:
http://msdn2.microsoft.com/en-us/library/ms189677.aspx|||You are the best!!!! |||When manually defining the fields in the dataset (e.g. programmatic select statement), how do you get them to 'stick'? When previewing the report, they get wiped out of the dataset once in a while.
sql

Need to create <Query> statement programmatically

I am creating a web application that uses a using a web service to get data for my reports. Since the webservice only accepts 1 parameter called "sql" (the sql select statement), I am using the report's query string to get the data.

Here is the data source and dataset info I am using:

DataSource
Name: WebService
Type: XML
Connection string: http://localhost/myWeb/myWebService.asmx
Credentials: No credentials

DataSet
Query tab:
Name: WebService
Data source: WebService
Command type: Text
Query String: <Query><SoapAction>......</SoapAction></Query>

Here is a sample of the <Query> string that I use when I first build the report:

<Query>
<SoapAction>http://tempuri.org/GetDataset</SoapAction>
<Method Namespace="http://tempuri.org/" Name="GetDataset">
<Parameters>
<Parameter Name="sql" Type="String">
<DefaultValue>Select * From Customers</DefaultValue>
</Parameter>

</Parameters>
</Method>

<ElementPath IgnoreNamespaces="true">GetDatasetResponse{}/GetDatasetResult{}/diffgram{}/NewDataSet{}/Results</ElementPath>
</Query>

When the user selects a report in the web application, they are prompted for information about the sql statement, and then I can rebuild the <Query> xml fragment, substituting the new sql statemet for the default one. for example, the statement "Select * From Reports" would be replaced with "Select * From Customers where LN = 'Smith'".

Then I want to attach that new <Query> statement to the report and run it. How can I set this information in the report? I can't find anything that talks about it, but there must be some way!

Thanks in advance for your help!

Hi Videl,

There are a couple of ways you could accomplish this. But the best way is to take advantage of the QueryString being an expression. You could have the querystring reference a report parameter, and set this parameter to the new xml query with your web application when you run the report.

Example Dataset Query string: =Parameters!QueryParameter.Value

Or since you are just changing the sql query parameter, you could create a dataset parameter that references the report parameter. To do this you need to create a dataset parameter with the same name as the webmethod parameter. In this case, this would be sql. And the value for the dataset parameter would be the same expression above, only that in this case the value would just be the modified sql instead of the entire Xml query.

Another way is to directly modify the RDL and re-publish it, but this would not be very performant and would cause the report to be only useable by one user at a time.|||

Hi Ian,

OK, I set the dataset query string to Parameters!sql.Value, and created a report parameter called "sql". I set the default value of this parameter to :

="<Query><SoapAction>http://tempuri.org/GetDataset</SoapAction><Method Namespace='http://tempuri.org/' Name='GetDataset'><Parameters><Parameter Name='sql' Type='String'><DefaultValue>Select * From Reports</DefaultValue></Parameter></Parameters></Method><ElementPath IgnoreNamespaces='true'>GetDatasetResponse{}/GetDatasetResult{}/diffgram{}/NewDataSet{}/Results</ElementPath></Query>"

When I preview the report, I get the following error message:

An error occurred during local report processing. The definition of the report '/myReport/' is invalid. the Value expression for the textbox 'xyz' refers to the field 'xyz'. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope.

It looks like it is not finding the data before it tries to populate the fields on the report.

I also tried using a dataset paramter, and that looked maybe easier. I created a dataset parameter called sql, and set it's default value to the sql statement I want. Then I deleted the default value of the sql statement in the query statement, and ran it. I was prompted for the sql statement, and it ran fine. However, I am not sure how to set that value from code, since that is a dataset parameter and not a report parameter. That looks like an easier solution, but could you please tell me how to set that dataset parameter from my .NET code? Thanks!!!

Marlene

|||I think using a datset parameter is easier and less error prone, then dealing with the entire Xml query. However, you should be able to use the other approach. The reason you are getting the exception is that the fields are no longer defined in the dataset, since they were tied to the query. You need to manually add the fields to the dataset by right-clicking on the dataset in the Datasets panel in the Report Designer, and click add in the context menu.

Regarding your question, to set the dataset parameter programmatically, you need to use a report parameter as the value of the dataset parameter, like you did with the query expression, and set the report parameter in your call to create render the report.

Ian|||

Hi Ian,

I did as you suggested, manually adding the fields, and creating a reports parameter called "sql". Now the dataset parameter "sql" is set to Parameters!sql.value.

Now when I run the report, I get the right number of rows, but they are all blank. I have tried setting the field values to the dataset field value, and I have tried leaving them blank, and get the same results either way. How do I get them to mesh up?

|||Yes, they need to be mapped to the dataset field names. The issue you describe is usually caused by custom fields not being mapped to existing dataset fields. Can you make sure that the fields you are mapping to exist in the dataset returned by the Xml Query? Since this is a dataset being returned, can you try adding the following custom dataset field,

Name = RowID, DatabaseField = id

This should map RowID to the id attibute of the NewTable element. This also assumes that your ElementPath describes the path to the table level, without specifying the fields to retrieve. For example,

<ElementPath IgnoreNamespaces="true">GetDatasetResponse{}/GetDatasetResult{}/diffgram{}/NewDataSet{}/Results{}/NewTable</ElementPath>|||

Hi,

I didn't quite understand that.

I do have the <ElementPath> tag that you described in my <query> string, except it is slightly different:

<ElementPath IgnoreNamespaces="true">GetDatasetResponse{}/GetDatasetResult{}/diffgram{}/NewDataSet{}/Results</ElementPath

When I replaced /Results with /Results{}/NewTable, data was no longer returned to the dataset, so I changed it back.

I added a dataset field named "RowID", set it's type to "Database Field" and set the value to "=id" (I did not type in the quotes). However the data still did not display.

FYI, here is the entire query string I am using. I include it since it includes that elementPath part that stubbornly refuses to play nice with NewTable:
-

<Query>
<SoapAction>http://tempuri.org/GetDataset</SoapAction>
<Method Namespace="http://tempuri.org/" Name="GetDataset">
<Parameters>
<Parameter Name="sql" Type="String">
<DefaultValue></DefaultValue>
</Parameter>
</Parameters>
</Method>
<ElementPath IgnoreNamespaces="true">GetDatasetResponse{}/GetDatasetResult{}/diffgram{}/NewDataSet{}/Results</ElementPath>
</Query>
-
Here is a sample output from my webservice
-
<?xml version="1.0" encoding="utf-8" ?>

- <DataSet xmlns="http://tempuri.org/">

- <xs:schema id="NewDataSet" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">

- <xs:element name="NewDataSet" msdata:IsDataSet="true" msdata:UseCurrentLocale="true">

- <xs:complexType>

- <xs:choice minOccurs="0" maxOccurs="unbounded">

- <xs:element name="Results">

- <xs:complexType>

- <xs:sequence>

<xs:element name="ReportID" type="xs:int" minOccurs="0" />

<xs:element name="ReportName" type="xs:string" minOccurs="0" />

<xs:element name="ReportFriendlyName" type="xs:string" minOccurs="0" />

<xs:element name="ReportDescription" type="xs:string" minOccurs="0" />

<xs:element name="Display" type="xs:boolean" minOccurs="0" />

<xs:element name="DataViewName" type="xs:string" minOccurs="0" />

<xs:element name="Example1" type="xs:boolean" minOccurs="0" />

</xs:sequence>

</xs:complexType>

</xs:element>

</xs:choice>

</xs:complexType>

</xs:element>

</xs:schema>

- <diffgr:diffgram xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1">

- <NewDataSet xmlns="">

- <Results diffgr:id="Results1" msdata:rowOrder="0">

<ReportID>10</ReportID>

<ReportName>Reports_ByReportID1</ReportName>

<ReportFriendlyName>Reports by ID, w/ Dataset Parameter</ReportFriendlyName>

<ReportDescription>List of reports by report ID. This example uses a dataset parameter, which is referenced in the query string.</ReportDescription>

<Display>true</Display>

<DataViewName>Reports</DataViewName>

<Example1>false</Example1>

</Results>

</NewDataSet>

</diffgr:diffgram>

</DataSet>
--

And here are all my dataset fields:

FieldName Type Value
ReportID Database field =Fields!ReportID.Value
ReportName Database field =Fields!ReportName.Value
ReportFriendlyName Database field =Fields!ReportFriendlyName.Value

RowID Database field =id

|||Your element path is correct--you are describing the relationships to the table level. I mixed the name of your table, Results, with that of my dataset, also Results.

When creating custom Dataset fields, the value should be just the database field name. The value in the field is treated as a literal, so Reporting services is trying to match your custom dataset field, ReportID, to a field from the dataset returned from the query named "=Fields!ReportID.Value"

Try using these custom dataset field.

FieldNameType ValueReportIDDatabase field ReportIDReportNameDatabase field ReportNameReportFriendlyNameDatabase field ReportFriendlyName

More information on custom dataset fields:
http://msdn2.microsoft.com/en-us/library/ms189677.aspx|||You are the best!!!! |||When manually defining the fields in the dataset (e.g. programmatic select statement), how do you get them to 'stick'? When previewing the report, they get wiped out of the dataset once in a while.

Need to Calculate Grade Age

Need to calculate the Grade age based on the birthdate and Nov month and 30th Day of the current year.
I have a working datediff statement but I need to always but in the current year. I would like to have the statement get the current year. Then if the age is greater than xx and less than xx your age level is "xyz"

This works DateDiff("d" [Birthdate], 11/30/2004) /365.25 will return the age.

I want to replace the 2004 with a getdate yyyy so I do not need to maintain this statement.

Thanks in advance of a reply
GaryTry this:

Declare @.birthdate as smalldatetime,@.mydate as varchar(10)
SET @.birthdate = '07/11/1978'
SET @.mydate = '11/30/' + cast(year(getdate())as varchar)
DateDiff("d", @.birthdate, @.mydate) /365.25

|||Thanks that helped me with the project.sql

Need to add date during bulk import from csv

I'm downloading an wly retailer statement from our state's website in csv
format so I can import it into a SQL database and then use the data for some
custom reports. I've been able to successfully write my first bcp command to
import the file into an existing SQL database. However, I just realized that
the csv file I'm importing doesn't have any date information so I can select
database records based on a date. I've tried adding a Timestamp column at
the end of my database thinking it wouldn't interfere with the bulk import
but after I did such the import no longer worked. Does anyone have any idea
of how I can add a date field to each record during the import. Would BULK
INSERT allow me anymore flexibility? I could setup a user form where the
desired date is entered and then included with each record. Any suggestions
would be greatly appreciated.
Here's my bcp command:
bcp mydatabase.dbo.mytable in
c:\RetailerStatement.csv -c -t, -r\n -F3 -L9 -Smyservername -U -P
Thanks,
Barryyou want to create a datetime column with the default defined as getdate().
e.g.
alter table <tb> add crdate default getdate()
-oj
"BCS" <bswedeen@.tayloroil.com> wrote in message
news:kxikg.29114$JW5.5867@.southeast.rr.com...
> I'm downloading an wly retailer statement from our state's website in
> csv
> format so I can import it into a SQL database and then use the data for
> some
> custom reports. I've been able to successfully write my first bcp command
> to
> import the file into an existing SQL database. However, I just realized
> that
> the csv file I'm importing doesn't have any date information so I can
> select
> database records based on a date. I've tried adding a Timestamp column at
> the end of my database thinking it wouldn't interfere with the bulk import
> but after I did such the import no longer worked. Does anyone have any
> idea
> of how I can add a date field to each record during the import. Would BULK
> INSERT allow me anymore flexibility? I could setup a user form where the
> desired date is entered and then included with each record. Any
> suggestions
> would be greatly appreciated.
> Here's my bcp command:
> bcp mydatabase.dbo.mytable in
> c:\RetailerStatement.csv -c -t, -r\n -F3 -L9 -Smyservername -U -P
> Thanks,
> Barry
>|||No go. Here's the error I get:
#@. Row 1, Column 22: Invalid character value for cast specification @.#
The error repeats for all rows.
Barry
"oj" <nospam_ojngo@.home.com> wrote in message
news:O%23MGDlRkGHA.3588@.TK2MSFTNGP02.phx.gbl...
> you want to create a datetime column with the default defined as
getdate().
> e.g.
> alter table <tb> add crdate default getdate()
>
> --
> -oj
>
> "BCS" <bswedeen@.tayloroil.com> wrote in message
> news:kxikg.29114$JW5.5867@.southeast.rr.com...
command
at
import
BULK
>|||Try creating a view that selects all columns except
the (new) datetime column with the default date.
create view myview
as
select col1,col2,...
from mytable
Change your BCP command to import into this view instead.
bcp mydatabase.dbo.myview in c:\RetailerStatement.csv -c -t, -r\n -F3
-L9 -Smyservername -U -P|||1. my sample alter tb is missing 'datetime' datatype for the added column.
hopefully, you've caught that.
2. if your source cvs does not contain data for the datetime column, you
will need to exclude it by using a format file. you can, of course, use a
view as suggested by markc600.
-oj
"BCS" <bswedeen@.tayloroil.com> wrote in message
news:Hgxkg.29152$JW5.10677@.southeast.rr.com...
> No go. Here's the error I get:
> #@. Row 1, Column 22: Invalid character value for cast specification @.#
> The error repeats for all rows.
> Barry
> "oj" <nospam_ojngo@.home.com> wrote in message
> news:O%23MGDlRkGHA.3588@.TK2MSFTNGP02.phx.gbl...
> getdate().
> command
> at
> import
> BULK
>|||My cvs file does not contain the datetime data and apparently the bcp
command won't work if I try to plug a default value in a datetime column in
my table or a timestamp column.
I've decided to import the csv file into an Excel workbook using a macro,
adding the date value, and then doing an INSERT into my database. I'm sure
using a VIEW as Mark suggested would have accomplished the same thing. The
Excel route gave me an opportunity to allow the user to verify the data
before making the final INSERT.
Thanks everyone!
Barry
"oj" <nospam_ojngo@.home.com> wrote in message
news:eiCqTuWkGHA.2200@.TK2MSFTNGP05.phx.gbl...
> 1. my sample alter tb is missing 'datetime' datatype for the added column.
> hopefully, you've caught that.
> 2. if your source cvs does not contain data for the datetime column, you
> will need to exclude it by using a format file. you can, of course, use a
> view as suggested by markc600.
> --
> -oj
>
> "BCS" <bswedeen@.tayloroil.com> wrote in message
> news:Hgxkg.29152$JW5.10677@.southeast.rr.com...
for
realized
column
any
>

need stored proc to append table 1 to table 2

HI just wondering if there is a simple transact statement to use to copy the
contents of table one and append it to a second table. Each table has the
same columns and datatypes, thanks.
Paul G
Software engineer.
Here's the books online example from the INSERT...SELECT topic.
USE pubs
INSERT INTO mybooks
SELECT title_id
, title
, TYPE
FROM titles
WHERE TYPE = 'mod_cook'
|||Paul wrote:
> HI just wondering if there is a simple transact statement to use to
> copy the contents of table one and append it to a second table. Each
> table has the same columns and datatypes, thanks.
In order to have SQL Server create the destination table, use
SELECT...INTO
Select
id,
type,
name
Into
dbo.MyObjects
From
dbo.sysobjects
Where
id < 100
Select * from dbo.MyObjects
Drop Table dbo.MyObjects
David Gugick - SQL Server MVP
Quest Software
|||ok thanks.
Paul G
Software engineer.
"David Gugick" wrote:

> Paul wrote:
> In order to have SQL Server create the destination table, use
> SELECT...INTO
> Select
> id,
> type,
> name
> Into
> dbo.MyObjects
> From
> dbo.sysobjects
> Where
> id < 100
> Select * from dbo.MyObjects
> Drop Table dbo.MyObjects
>
> --
> David Gugick - SQL Server MVP
> Quest Software
>

need stored proc to append table 1 to table 2

HI just wondering if there is a simple transact statement to use to copy the
contents of table one and append it to a second table. Each table has the
same columns and datatypes, thanks.
--
Paul G
Software engineer.Here's the books online example from the INSERT...SELECT topic.
USE pubs
INSERT INTO mybooks
SELECT title_id
, title
, TYPE
FROM titles
WHERE TYPE = 'mod_cook'|||Paul wrote:
> HI just wondering if there is a simple transact statement to use to
> copy the contents of table one and append it to a second table. Each
> table has the same columns and datatypes, thanks.
In order to have SQL Server create the destination table, use
SELECT...INTO
Select
id,
type,
name
Into
dbo.MyObjects
From
dbo.sysobjects
Where
id < 100
Select * from dbo.MyObjects
Drop Table dbo.MyObjects
David Gugick - SQL Server MVP
Quest Software|||ok thanks.
--
Paul G
Software engineer.
"David Gugick" wrote:

> Paul wrote:
> In order to have SQL Server create the destination table, use
> SELECT...INTO
> Select
> id,
> type,
> name
> Into
> dbo.MyObjects
> From
> dbo.sysobjects
> Where
> id < 100
> Select * from dbo.MyObjects
> Drop Table dbo.MyObjects
>
> --
> David Gugick - SQL Server MVP
> Quest Software
>

need stored proc to append table 1 to table 2

HI just wondering if there is a simple transact statement to use to copy the
contents of table one and append it to a second table. Each table has the
same columns and datatypes, thanks.
--
Paul G
Software engineer.Here's the books online example from the INSERT...SELECT topic.
USE pubs
INSERT INTO mybooks
SELECT title_id
, title
, TYPE
FROM titles
WHERE TYPE = 'mod_cook'|||Paul wrote:
> HI just wondering if there is a simple transact statement to use to
> copy the contents of table one and append it to a second table. Each
> table has the same columns and datatypes, thanks.
In order to have SQL Server create the destination table, use
SELECT...INTO
Select
id,
type,
name
Into
dbo.MyObjects
From
dbo.sysobjects
Where
id < 100
Select * from dbo.MyObjects
Drop Table dbo.MyObjects
David Gugick - SQL Server MVP
Quest Software|||ok thanks.
--
Paul G
Software engineer.
"David Gugick" wrote:
> Paul wrote:
> > HI just wondering if there is a simple transact statement to use to
> > copy the contents of table one and append it to a second table. Each
> > table has the same columns and datatypes, thanks.
> In order to have SQL Server create the destination table, use
> SELECT...INTO
> Select
> id,
> type,
> name
> Into
> dbo.MyObjects
> From
> dbo.sysobjects
> Where
> id < 100
> Select * from dbo.MyObjects
> Drop Table dbo.MyObjects
>
> --
> David Gugick - SQL Server MVP
> Quest Software
>

Need SQL statement help!

Hello everyone,
I need help with two SQL statements
The first is:
I have a table called tbSitters which contains two columns named
bsLastName and bsAboutMe. I want to be able to find records where the
value stored in bsLastName can be found in bsAboutMe for a single row
(not all records) bsAboutMe is a text field which may contain up to 50
words or more.
The second is:
I have a table called tbPcodes which has two columns named bsID and
pcMyZips. I want to be able to bring up any duplicates in pcMyZips,
which are associated to bsID. For example:
bsID pcMyZips
001 100
001 100
001 101
001 102
002 201
002 202
002 203
002 204
It would return bsID 001 because there is a duplicate entry(100) in
pcMyZips.
Any help would be greatly appreciated!
Thanks Robert> The first is:
> I have a table called tbSitters which contains two columns named
> bsLastName and bsAboutMe. I want to be able to find records where the
> value stored in bsLastName can be found in bsAboutMe for a single row
> (not all records) bsAboutMe is a text field which may contain up to 50
> words or more.

> The second is:
> I have a table called tbPcodes which has two columns named bsID and
> pcMyZips. I want to be able to bring up any duplicates in pcMyZips,
> which are associated to bsID. For example:
> bsID pcMyZips
> 001 100
> 001 100
> 001 101
> 001 102
> 002 201
> 002 202
> 002 203
> 002 204
>
For the first one try:
SELECT * FROM tbSitters
WHERE bsAboutMe LIKE '%' + bsLastName + '%'
For the second one try:
SELECT bsID, pcMyZips, COUNT(pcMyZips)
FROM pcMyZips
GROUP BY bsID, pcMyZips
HAVING COUNT(pcMyZips) > 1
Rick Sawtell|||Hi Rick,
Thans for the reply!
The second statement works perfect.
The problems I'm having with the results of the first statement, is
some members only enter in their intial in the bsLastName field, so a
lot of results are being returned. Would it be possible to specify that
there must be a blank space before and after when matching the text
values? This would eliminate a lot of the false returns.
Or, If that's not possible, could the statement be altered to include
bsLastName('s) which are a minimum of 3 characters long only?
Much appreciated Rick,
Thanks, Rob|||ROBinBRAMPTON wrote:

> Would it be possible to specify
> that there must be a blank space before and after when matching the
> text values? This would eliminate a lot of the false returns.
Try SELECT * FROM tbSitters
WHERE bsAboutMe LIKE '% ' + bsLastName + ' %'
HTH,
Stijn Verrept.

Need Sql statement

--Table
Create table tableName (Field1 int, Field2 int)

Table Data
Field1 Field2
1 10
2 10
3 10
1 20
2 20
3 30

I need Like a Sql statement like

1 10 (any separator) 20
2 10 20
3 10 30

thx a lot waiting for result

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!radha (pottua_radha@.yahoo.co.in) writes:
> --Table
> Create table tableName (Field1 int, Field2 int)
> Table Data
> Field1 Field2
> 1 10
> 2 10
> 3 10
> 1 20
> 2 20
> 3 30
> I need Like a Sql statement like
> 1 10 (any separator) 20
> 2 10 20
> 3 10 30
>
> thx a lot waiting for result

It is not clear what result you are looking for. Do you want max or
min values? Or if there are 19 different values for Field1, do you want
all 19 values of Field2 in one row?

In the first case it's simple:

SELECT Field1, MIN(Field2), MAX(Field2)
FROM tableName
GROUP BY Field1

The recommendation for the second case is usually that you should to it
client-side, as SQL does not lend itself for this sort of thing. An
iterative solution is somewhat tedious to write, but it may be the best
way. I seem to recall that someone - I believe it was Anith Sen or
David Portas - a while back posted a set-based solution, which used an
auxilliary table with numbers from 1 to whatever number that is needed.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Need sql script or statement to extract all relations from one tree

Suppose I have a table with relations (REL). In this table there are relations between parent en child, so the columns are: Rel_Id, parent_Id, child_Id. Example:

1, A, B
2, A, C
3, A, D
4, B, E
5, C, F
6, G, H
7, G, I
8, H, J

I need a query which returns all relations from REL that are in the same tree as the input unit.
In the example, giving D as input unit, it should return relations 1, 2, 3, 4 and 5, because A, B, C, E and F are (in)directly related to D and belong therefore to the same tree.
Giving H as input, it should return relations 6, 7 and 8.
(with a tree, I mean that a parent can have 0, 1 or more children and a child belongs at most one parent.)

Thanks for your help in advance!

Here you go...

CREATE TABLE REL(Num int, ColumnA varchar(5), ColumnB varchar(5))
CREATE TABLE Direct(Num int, ColumnA varchar(5), ColumnB varchar(5))
CREATE TABLE Indirect(Num int, ColumnA varchar(5), ColumnB varchar(5))

INSERT INTO REL(Num, ColumnA, ColumnB)VALUES(1, 'A', 'B')
INSERT INTO REL(Num, ColumnA, ColumnB)VALUES(2, 'A', 'C')
INSERT INTO REL(Num, ColumnA, ColumnB)VALUES(3, 'A', 'D')
INSERT INTO REL(Num, ColumnA, ColumnB)VALUES(4, 'B', 'E')
INSERT INTO REL(Num, ColumnA, ColumnB)VALUES(5, 'C', 'F')
INSERT INTO REL(Num, ColumnA, ColumnB)VALUES(6, 'G', 'H')
INSERT INTO REL(Num, ColumnA, ColumnB)VALUES(7, 'G', 'I')
INSERT INTO REL(Num, ColumnA, ColumnB)VALUES(8, 'H', 'J')

--Delete above after first execution

DECLARE @.Input varchar(5), @.Counter int

SET @.Input = 'D'

SET @.Counter = 0

INSERT INTO Direct
SELECT * FROM REL WHERE ColumnA = @.Input OR ColumnB = @.Input

WHILE @.Counter <> (SELECT COUNT(*) FROM REL)
BEGIN

INSERT INTO Indirect
SELECT * FROM REL
WHERE ColumnA IN (SELECT ColumnA FROM Direct)
OR ColumnA IN (SELECT ColumnB FROM Direct)
OR ColumnB IN (SELECT ColumnB FROM Direct)
OR ColumnB IN (SELECT ColumnA FROM Direct)

INSERT INTO Direct
SELECT DISTINCT * FROM Indirect

DELETE FROM Indirect

SET @.Counter = @.Counter + 1

END

SELECT * FROM Direct
UNION
SELECT * FROM Indirect

DELETE FROM Direct
DELETE FROM Indirect

Adamus

|||

Thanks Adamus,

That helps a lot. I have not run the script yet, but when looking at it, I have two questions.

1) You write

INSERT INTO Direct
SELECT DISTINCT * FROM Indirect

At the first cycle, the relation A-D is in table Direct, but this one is also added in the above insert into statement, when I am right. So, after the insert, I have two relations with A-D in Direct. Is that correct? If yes, I assume that I can use the distinct afterwards.

2) You write

WHILE @.Counter <> (SELECT COUNT(*) FROM REL)

The example I gave, had only 8 relations, but my actual table has thousands of relations. The while statement will then be executed a lot of times unnecessary (also in the order of thousands, because most trees are quite simple).

Is there a way to compare tables? If yes how can I compare if Direct is exactly the same as Indirect? Because, if that is the case, no more relations are found and I can end the loop.

|||

The loop is a necessary evil because the logical number of possibilities are NumOfRecords * NumOfFieldsCompared. You must iterate fully without exiting the loop in order to get accurate information. As a result, the Indirect table will contain many duplicate rows that are necessary in your case. This is why the DISTINCT Keyword is used.

Run the query without the loop. Look at the resultset. Run it again with the loop and compare. It may make more sense.

Also, the UNION makes a difference if you remove it and replace it.

And yes, I tested the query with all 8 relations. Please run the script before replying.

Thanks,

Adamus

|||

ratslav wrote:

The example I gave, had only 8 relations, but my actual table has thousands of relations. The while statement will then be executed a lot of times unnecessary (also in the order of thousands, because most trees are quite simple).

Is there a way to compare tables? If yes how can I compare if Direct is exactly the same as Indirect? Because, if that is the case, no more relations are found and I can end the loop.

Because of the temporary relationships, comparing the tables won't work. For each iteration, the relationships will change. For iteration 1, The child becomes the parent for the next iteration. During distinct iterations, each element will at some point be the parent with a direct relation to the proceeding child. Technically, there is never an indirect relationship until the query completes or before the query is executed.

There is a reason I named the tables Direct and Indirect. Indirect relationships become Direct until all relationships are found.

Do you understand that the query and the logic force all elements to be parents in order to find the children?

Also, there is no such thing as unnecessary iterations. You don't know how many records to compare until you compare all of them.

Thanks,

Adamus

|||Here is an example of how to solve this using SQL Server 2005

recursive queries - perhaps not as efficiently as is possible,

but it should work. This uses as sample data Employee,Manager

pairs from the sample database AdventureWorks, excluding some

top managers' rows so there are several "trees."

Your scenario is made a bit messy by the fact that some of

your items are only in ColumnA and some only in ColumnB. If

you had a single table containing one of every item, it would

be a little easier.

DECLARE @.Input varchar(5)

SET @.Input = 'D';

with Manages(EmpID,MgrID,Distance) as (

select

ColumnB,

ColumnB,

0

from REL

union

select

M1.ColumnA,

M1.ColumnA,

0

from REL as M1

where not exists (

select * from REL as M2

where M1.ColumnA = M2.ColumnB

)

union all

select

M.EmpID,

E.ColumnA,

Distance+1

from Manages as M

join REL as E

on E.ColumnB = M.MgrID

where E.ColumnA is not null

)

select

EmpID

from Manages

where MgrID = (

select top 1 MgrID

from Manages

where EmpID = @.Input

order by Distance desc

)

order by EmpID

go

Here is another solution that is a bit more direct and that

may be faster.

create function TopManager(

@.emp char

) returns char as begin

declare @.mgr char

set @.mgr = @.emp

set @.emp = case when @.mgr = 'A' then '$' else 'A' end

-- anything but @.mgr

while @.emp <> @.mgr begin

select

@.emp = @.mgr,

@.mgr = coalesce(ColumnA,@.mgr)

from REL

where ColumnB = @.mgr

if @.@.rowcount = 0 return @.mgr

end

return @.emp

end

go

declare @.emp char;

set @.emp = 'D';

with Descendants(EmpID) as (

select ColumnB

from REL

where ColumnA = dbo.TopManager(@.emp)

union all

select M.ColumnB

from REL as M

join Descendants as D

on D.EmpID = M.ColumnA

)

select EmpID

from Descendants

union

select dbo.TopManager(@.emp)

order by EmpID

go

-- Steve Kass

-- Drew University

-- http://www.stevekass.com

-- CBCEF696-CEA4-4B36-8099-0CBAC7C86AB5

ratslav@.discussions.microsoft.com wrote:

> Thanks Adamus,

>

> That helps a lot. I have not run the script yet, but when looking at it,

> I have two questions.

>

> 1) You write

>

> INSERT INTO Direct

> SELECT DISTINCT * FROM Indirect

>

> At the first cycle, the relation A-D is in table Direct, but this one is

> also added in the above insert into statement, when I am right. So,

> after the insert, I have two relations with A-D in Direct. Is that

> correct? If yes, I assume that I can use the distinct afterwards.

>

> 2) You write

>

> WHILE @.Counter <> (SELECT COUNT(*) FROM REL)

>

> The example I gave, had only 8 relations, but my actual table has

> thousands of relations. The while statement will then be executed a lot

> of times unnecessary (also in the order of thousands, because most trees

> are quite simple).

>

> Is there a way to compare tables? If yes how can I compare if Direct is

> exactly the same as Indirect? Because, if that is the case, no more

> relations are found and I can end the loop.

>

>

>

>|||

Adamus,

I would like to reply to your two answers:
First, I believe in your solution. Today I tried it at work, and it gave the correct relations.
I know a loop is necessary, but going through it that many times, seems unlogical to me.
I did run the script on the thousands of relations that were in my actual #REL.
After a minute I still didn′t have any results. Because others employees use the same database,
and their performance must not be affected by my query, this is no solution. I need a more efficient one.

Please review the script below. I am unable to run this at home. Tomorrow at the office I will try it myself.
I hope it works.

NNTP User,

Thanks for your contribution, but I don′t understand the text. Probably because I am quite new to SQL.
If the script below fails, I will look at your answer again (I first have to learn something about "with" etc).

CREATE TABLE #REL(Num int, ColumnA varchar(5), ColumnB varchar(5))
CREATE TABLE #Direct(Num int, ColumnA varchar(5), ColumnB varchar(5))
CREATE TABLE #Indirect(Num int, ColumnA varchar(5), ColumnB varchar(5))
CREATE TABLE #UniqueIndirect(Num int, ColumnA varchar(5), ColumnB varchar(5))

INSERT INTO #REL(Num, ColumnA, ColumnB) VALUES(1, 'A', 'B')
INSERT INTO #REL(Num, ColumnA, ColumnB) VALUES(2, 'A', 'C')
INSERT INTO #REL(Num, ColumnA, ColumnB) VALUES(3, 'A', 'D')
INSERT INTO #REL(Num, ColumnA, ColumnB) VALUES(4, 'B', 'E')
INSERT INTO #REL(Num, ColumnA, ColumnB) VALUES(5, 'C', 'F')
INSERT INTO #REL(Num, ColumnA, ColumnB) VALUES(6, 'G', 'H')
INSERT INTO #REL(Num, ColumnA, ColumnB) VALUES(7, 'G', 'I')
INSERT INTO #REL(Num, ColumnA, ColumnB) VALUES(8, 'H', 'J')

-- Delete above after first execution

DECLARE @.Input varchar(5)
SET @.Input = 471524

INSERT INTO #Direct
SELECT * FROM #REL WHERE ColumnA = @.Input OR ColumnB = @.Input

INSERT INTO #Indirect
SELECT * FROM #REL WHERE
ColumnA IN (SELECT ColumnA FROM #Direct) OR
ColumnA IN (SELECT ColumnB FROM #Direct) OR
ColumnB IN (SELECT ColumnB FROM #Direct) OR
ColumnB IN (SELECT ColumnA FROM #Direct)

INSERT INTO #UniqueIndirect
SELECT DISTINCT * FROM #Indirect

DELETE FROM #Indirect

WHILE (SELECT COUNT(*) FROM #UniqueIndirect) <> (SELECT COUNT(*) FROM #Direct)
BEGIN

DELETE FROM #Direct

INSERT INTO #Direct
SELECT * FROM #UniqueIndirect

DELETE FROM #UniqueIndirect

INSERT INTO #Indirect
SELECT * FROM #REL WHERE
ColumnA IN (SELECT ColumnA FROM #Direct) OR
ColumnA IN (SELECT ColumnB FROM #Direct) OR
ColumnB IN (SELECT ColumnB FROM #Direct) OR
ColumnB IN (SELECT ColumnA FROM #Direct)

INSERT INTO #UniqueIndirect
SELECT DISTINCT * FROM #Indirect

DELETE FROM #Indirect

END

SELECT * FROM #UniqueIndirect

DELETE FROM #Direct
DELETE FROM #Indirect

|||

Friend,

A query that runs for 1+ minutes is not that surprising. I have queries that take 15 minutes to run and these are standard queries. This is not unusual. Unless you have a terabyte of processing and maxed out RAM on the server, it's inevitable that some queries will take longer than others.

My only other suggestion, if possible, is to use concatenated keys on the fields so that indexes are created. This should speed up the query time. As far as minimizing the loops, I have no answer. As I have said before, you don't know how many records/fields to compare until you've compared them all at least once.

Best of luck,

Adamus

|||

ratslav wrote:

CREATE TABLE #REL(Num int, ColumnA varchar(5), ColumnB varchar(5))
CREATE TABLE #Direct(Num int, ColumnA varchar(5), ColumnB varchar(5))
CREATE TABLE #Indirect(Num int, ColumnA varchar(5), ColumnB varchar(5))
CREATE TABLE #UniqueIndirect(Num int, ColumnA varchar(5), ColumnB varchar(5))

INSERT INTO #REL(Num, ColumnA, ColumnB) VALUES(1, 'A', 'B')
INSERT INTO #REL(Num, ColumnA, ColumnB) VALUES(2, 'A', 'C')
INSERT INTO #REL(Num, ColumnA, ColumnB) VALUES(3, 'A', 'D')
INSERT INTO #REL(Num, ColumnA, ColumnB) VALUES(4, 'B', 'E')
INSERT INTO #REL(Num, ColumnA, ColumnB) VALUES(5, 'C', 'F')
INSERT INTO #REL(Num, ColumnA, ColumnB) VALUES(6, 'G', 'H')
INSERT INTO #REL(Num, ColumnA, ColumnB) VALUES(7, 'G', 'I')
INSERT INTO #REL(Num, ColumnA, ColumnB) VALUES(8, 'H', 'J')

-- Delete above after first execution

DECLARE @.Input varchar(5)
SET @.Input = 471524

INSERT INTO #Direct
SELECT * FROM #REL WHERE ColumnA = @.Input OR ColumnB = @.Input

INSERT INTO #Indirect
SELECT * FROM #REL WHERE
ColumnA IN (SELECT ColumnA FROM #Direct) OR
ColumnA IN (SELECT ColumnB FROM #Direct) OR
ColumnB IN (SELECT ColumnB FROM #Direct) OR
ColumnB IN (SELECT ColumnA FROM #Direct)

INSERT INTO #UniqueIndirect
SELECT DISTINCT * FROM #Indirect

DELETE FROM #Indirect

WHILE (SELECT COUNT(*) FROM #UniqueIndirect) <> (SELECT COUNT(*) FROM #Direct)
BEGIN

DELETE FROM #Direct

INSERT INTO #Direct
SELECT * FROM #UniqueIndirect

DELETE FROM #UniqueIndirect

INSERT INTO #Indirect
SELECT * FROM #REL WHERE
ColumnA IN (SELECT ColumnA FROM #Direct) OR
ColumnA IN (SELECT ColumnB FROM #Direct) OR
ColumnB IN (SELECT ColumnB FROM #Direct) OR
ColumnB IN (SELECT ColumnA FROM #Direct)

INSERT INTO #UniqueIndirect
SELECT DISTINCT * FROM #Indirect

DELETE FROM #Indirect

END

SELECT * FROM #UniqueIndirect

DELETE FROM #Direct
DELETE FROM #Indirect

This work fine for me?

You just have to drop the tables

Drop Table #Rel
Drop Table #Direct
Drop Table #Indirect
Drop Table #UniqueIndirect

Where are you having difficulty?

Adamus

|||

Also,

You may want to consult a MSVP on the performance difference in derived tables vs. real tables. I don't know enough about the memory allocations to make an experienced judgement call.

As a hunch, I would think temp tables would take longer simply due to allocations but I could be wrong.

Adamus

|||

Thanks Adamus,

I will try the script tomorrow and let you know if it works fine for me as well.

The reason why I used temp tables is because of permissions. I am not allowed to create dbo or user tables, but I have permission to create temp tables.

|||

The script worked for me for a small tree (the one from the example). I wasn′t satisfied with the result of a larger tree, but this may have been caused by the statements after the things were executed which were described above. Anyway, it looks very promising.

I have just one question left. I need to save the output of the following three lines into a textfile. In Oracle I could use something like spool. In SQL Server this won′t work. Has anyone suggestions?

Thanks!

SET NOCOUNT ON

print 'blablabefore' + CHAR(13)
select * from #endresult
print 'blablaafter'

Need SQL Help - Trim parentheses in column of data

Hi All, I am sure someone has done this before. Trying to write a SQL statement to UPDATE a column named Prod_Model. The table name is tbl_MASTER. Wanting to trim the parentheses out of the data and update back to the table. Any help on the SQL statement?

Trim or remove? Trim usually means only leading and/or trailing, and remove means to get rid of all.

Here is how to remove:

UPDATE Table

SET field=REPLACE(REPLACE(field,')',''),'(','')

WHERE field LIKE '%)%' OR field LIKE '%(%'

Here is how to trim:

UPDATE Table

SET field=REVERSE(SUBSTRING(REVERSE(SUBSTRING(field,PATINDEX('%[^()]%',field),LEN(field)-PATINDEX('%[^()]%',field)+1)),PATINDEX('%[^()]%',REVERSE(SUBSTRING(field,PATINDEX('%[^()]%',field),LEN(field)-PATINDEX('%[^()]%',field)+1))),LEN(REVERSE(SUBSTRING(field,PATINDEX('%[^()]%',field),LEN(field)-PATINDEX('%[^()]%',field)+1)))-PATINDEX('%[^()]%',REVERSE(SUBSTRING(field,PATINDEX('%[^()]%',field),LEN(field)-PATINDEX('%[^()]%',field)+1)))+1))

WHERE field LIKE '(%' or field LIKE ')%' or field LIKE '%)' or field LIKE '%('

There is a much shorter way using STUFF, REVERSE, and PATINDEX, but I thought of this way first.

|||

The remove method did the ticket. Thank you so much.

sql

Need Report select statement help CR9

i need to report for a center (a location #) within a date range where item #1 and item #2's qty's are not the same. The center and date range is fine

"{DS_DATA.DBCTR} = 50 and
{DS_DATA.DBDATE} in DateTime (2006, 02, 20, 0, 0, 0) to DateTime (2006, 03, 19, 0, 0, 0)"

it is the the item's that i am stuck on. How can I select where the center = 50 the date is between x and y, and the qty's for items 1 and 2 are NOT the same on the same day for the same location. I keep envisioning in sql string terms like a select statement within a select statement but you cant do that in crystal. at least I dont think. HELP!It might help if we knew your table / data structure.

I assume that there are multiple rows per DBCTR, DBDATE pair i.e. that there's an item column and a quantity column.
I assume that the DS_DATA table's key is DBCTR, DBDATE, ITEM_NO.
I assume that the DBDATE column is a date (rather than a datetime with a non-zero time).

So, the SQL is something like

Select t1.dbctr, t1.dbdate, t1.qty, t2.qty
from ds_data t1
inner join ds_data t2 on t2.DBCTR = t1.DBCTR and t2.DBDATE = t1.DBDATE
and t1.item_no = 1 and t2.item_no = 2
where t1.qty <> t2.qty
and ...

(Yes, I did mean to put the item_no restrictions on the join - I've no idea how big your table is or how many items there could be and it limits the cartesian product before where clause filtering.)

So you could either put the SQL straight into the 'Add command' or do it via the graphical interface: add the table in the database expert twice (the 2nd time it'll be given a different name), add the inner join (just the column joins here) as links and then add the item_no and qty restrictions to your record selection formula, using the correctly aliased table.|||It didn't even dawn on me to put the table in twice and do the join. It was stairing me right in the face. I actually tried using the repository and used this sql statement:
select d.dscenter, d.DSDATE, d.dsvoid, s.storevoid from
(select DBCTR DSCENTER, DBDATE DSDATE, dbqty DSVOID from ds_user.ds_data where dbitem = 11200 )D ,
(select DBCTR STORECTR, DBDATE STOREDATE, dbqty STOREVOID from ds_user.ds_data where dbitem = 21200 )S
where
d.dsdate = s.storedate
and
d.dscenter = s.storectr
and
d.dsvoid <> s.storevoid

it worked too and the speed was no different. Thanks for the help

Need permission to use bulk load statement in SQL 2005?

Hello,
I've developed a tool in Visual Basic.NET and have a local instance of SQL
Server 2005 running. This tool loads a lot of data into my database. It runs
locally just fine, but when I set it to load the data into the remote
(webhosting) database, I get an error: "You do not have permission to use th
e
bulk load statement."
Can anyone tell me what kind of permission that I need to use this
statement? Using a simple Insert state would take weeks and weeks to perform
the operation. The remote database at the hosting company has a bunch of SQL
Server 2005 databases on it. I'm sure that they won't want to give me admin
rights. Is there anyway that they can give us specifically rights to use a
bulk load statement without giving us access to all the other databases on
the server?
ThanksAdd the login to the bulkadmin fixed server role.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
"John Riddle" <JohnRiddle@.discussions.microsoft.com> wrote in message
news:256E7437-06C4-4392-A5DA-C9DF50F70A58@.microsoft.com...
Hello,
I've developed a tool in Visual Basic.NET and have a local instance of SQL
Server 2005 running. This tool loads a lot of data into my database. It runs
locally just fine, but when I set it to load the data into the remote
(webhosting) database, I get an error: "You do not have permission to use
the
bulk load statement."
Can anyone tell me what kind of permission that I need to use this
statement? Using a simple Insert state would take weeks and weeks to perform
the operation. The remote database at the hosting company has a bunch of SQL
Server 2005 databases on it. I'm sure that they won't want to give me admin
rights. Is there anyway that they can give us specifically rights to use a
bulk load statement without giving us access to all the other databases on
the server?
Thanks

need help. need to check a value inside a sql statement

What i need to do is check a field inside a sql statement to see if it is null and if it is then change the value to a 0.

for example

select Field1,field2,field3(if null then =0)
from table1

does this make sense. I think in oracle PL/SQL is uses decode(field3, null... or something like that

any help would be appreciated

thanksYou can use either the coalesce function which takes the first non-null value in a list, or the isnull function. I understand coalesce is more ansi-sql friendly, and I believe is implemented in Oracle as well.|||Example:

select Field1, field2, isnull(field3, 0) from table1

or

select Field1, field2, coalesce(field3, 0) from table1

blindman|||I am trying to put this in a MS access query

My statement look like this

Select Table_Name.Field1, Table_name.Field2, ISNULL(Table_Name.Field3,0),Table_Name.field4
from Table, Table
where....

I get an error message saying

" wrong number of arguments used with function in query expression
ISNULL(LDSSHLRN_LDMCH1.QMCHRS,0"

thanks|||I am trying to put this in a MS access query

My statement look like this

Select Table_Name.Field1, Table_name.Field2, ISNULL(Table_Name.Field3,0),Table_Name.field4
from Table, Table
where....

I get an error message saying

" wrong number of arguments used with function in query expression
ISNULL(LDSSHLRN_LDMCH1.QMCHRS,0"

thanks|||MS Access does not use the same exact same function list as SQL Server. The ISNULL function in MS Access takes a single parameter and returns a boolean value indicating whether the parameter was null.

The MSAccess function you are looking for is "NZ", short for Null to Zero.

Select Table_Name.Field1, Table_name.Field2, NZ(Table_Name.Field3,0),Table_Name.field4
from Table, Table

MS Access SQL is hybridized with VB.

blindman

NEED HELP!!! Unknown Error in SQL 2005 express toolkit

I am encountering this error when tried to execute a simple select statement against the Enterprise Edition SQL 2000 and SQl2005

An error occurred while executing batch. Error message is: Unknown error "-1".

Please advise what is the cause of this.

Thanks.

I also encountering it even I upgraded my Management studio to

Microsoft SQL Server Management Studio 9.00.1399.00

Please advise/assist in this error. Thanks.

|||

which simple select statement?

are you using Microsoft SQL Server Management Studio Express? with SQL Server 2005 Express Edition or SQL Server 2005 Express Edition with Advanced Services or SQL Server 2005 database engine?

tosc

|||

I installed SQL 2005 express toolkit in my PC and tried to connect to a Enterprise Edition SQL 2000 engine and SQL 2005 engine server. I tried for select rows in a table, example,

select * from<tablename>

It returns that error. i uninstall the Express edition toolkit and installed a enterprise version of the management studio. (version on my last post), still i got the same error when I executed that SELECT statement.

|||

is this SQL Server 2005 Express or EE ... u have mentioned EE ... in any case it seems that u have not applied SP1 and SP2 .... BTW can u post the script which u ran ...

Madhu

|||

I installed SQL 2005 Express Toolkit for the client tool,(Management Studio). my plan is to use this to manage our Enterprise Edition SQL servers(2000 and 2005).

I connect my Management Studio(SQL 2005 Express) to a SQL 2000 server enterprise edition.

I encountered this error:

An error occurred while executing batch. Error message is: Unknown error "-1".

even my script is a simple SELECT statement like this one:

SELECT * FROM <dbo.TableName>

I test the same script to a SQL 2005 Ent. Edition server. same error encountered. I tried a simple UPDATE statement, no error occured. it updates row in the SQL2000 server.

Then I uninstall the Express edition Management Studio(SQL 2005 Express toolkit). I install a Enterprise Edition management Studio(See my previous reply for the version) and still I encounter the same error when selecting data from any table of an Enterprise edition server.

Hope my issue is clear. do I have to install something? please advise.

|||Do I need to install SP1 even if I don't have SQL engine in my PC? i only have client tools in my PC. Please advise.|||

Hi,

which framework and OS are you using? are you sure, that you have all privelegs - to your databases?

|||

I have .NET 1.1 and 2.0 in my PC. I am using Win XP SP2. I already applied SP1 for SQL 2005 in my PC. still same error is encountered.

I have SQL 2000 and SQL 2005 Client utilities in my PC.

I didn't encounter it in Query Analyzer. Only when I use Management Studio.

I am using SA account.

|||

I have the same issue and have yet to find the resolution. One thing I have noticed is that the issue does not occur if you set your results to Text Mode. The issue appears to be related to mscorlib.dll.

|||

Could it be resolved by replacing the same dll with one from a working PC? or do i have to consider something like file version etc.?

|||

Hi all,

Any comment/suggestions to resolve this issue?

|||

I was able to resolve this issue on my system. It turns out that the client firewall tool used at my company "BlackIce" was detecting a buffer overflow exploit when making the calls to mscorlib.cll. I was able to list "C:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\SqlWb.exe" as an exception to rule and all is fine.

I hope these findings help you as well.

NEED HELP!!! Unknown Error in SQL 2005 express toolkit

I am encountering this error when tried to execute a simple select statement against the Enterprise Edition SQL 2000 and SQl2005

An error occurred while executing batch. Error message is: Unknown error "-1".

Please advise what is the cause of this.

Thanks.

I also encountering it even I upgraded my Management studio to

Microsoft SQL Server Management Studio 9.00.1399.00

Please advise/assist in this error. Thanks.

|||

which simple select statement?

are you using Microsoft SQL Server Management Studio Express? with SQL Server 2005 Express Edition or SQL Server 2005 Express Edition with Advanced Services or SQL Server 2005 database engine?

tosc

|||

I installed SQL 2005 express toolkit in my PC and tried to connect to a Enterprise Edition SQL 2000 engine and SQL 2005 engine server. I tried for select rows in a table, example,

select * from<tablename>

It returns that error. i uninstall the Express edition toolkit and installed a enterprise version of the management studio. (version on my last post), still i got the same error when I executed that SELECT statement.

|||

is this SQL Server 2005 Express or EE ... u have mentioned EE ... in any case it seems that u have not applied SP1 and SP2 .... BTW can u post the script which u ran ...

Madhu

|||

I installed SQL 2005 Express Toolkit for the client tool,(Management Studio). my plan is to use this to manage our Enterprise Edition SQL servers(2000 and 2005).

I connect my Management Studio(SQL 2005 Express) to a SQL 2000 server enterprise edition.

I encountered this error:

An error occurred while executing batch. Error message is: Unknown error "-1".

even my script is a simple SELECT statement like this one:

SELECT * FROM <dbo.TableName>

I test the same script to a SQL 2005 Ent. Edition server. same error encountered. I tried a simple UPDATE statement, no error occured. it updates row in the SQL2000 server.

Then I uninstall the Express edition Management Studio(SQL 2005 Express toolkit). I install a Enterprise Edition management Studio(See my previous reply for the version) and still I encounter the same error when selecting data from any table of an Enterprise edition server.

Hope my issue is clear. do I have to install something? please advise.

|||Do I need to install SP1 even if I don't have SQL engine in my PC? i only have client tools in my PC. Please advise.|||

Hi,

which framework and OS are you using? are you sure, that you have all privelegs - to your databases?

|||

I have .NET 1.1 and 2.0 in my PC. I am using Win XP SP2. I already applied SP1 for SQL 2005 in my PC. still same error is encountered.

I have SQL 2000 and SQL 2005 Client utilities in my PC.

I didn't encounter it in Query Analyzer. Only when I use Management Studio.

I am using SA account.

|||

I have the same issue and have yet to find the resolution. One thing I have noticed is that the issue does not occur if you set your results to Text Mode. The issue appears to be related to mscorlib.dll.

|||

Could it be resolved by replacing the same dll with one from a working PC? or do i have to consider something like file version etc.?

|||

Hi all,

Any comment/suggestions to resolve this issue?

|||

I was able to resolve this issue on my system. It turns out that the client firewall tool used at my company "BlackIce" was detecting a buffer overflow exploit when making the calls to mscorlib.cll. I was able to list "C:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\SqlWb.exe" as an exception to rule and all is fine.

I hope these findings help you as well.

NEED HELP!!! Unknown Error in SQL 2005 express toolkit

I am encountering this error when tried to execute a simple select statement against the Enterprise Edition SQL 2000 and SQl2005

An error occurred while executing batch. Error message is: Unknown error "-1".

Please advise what is the cause of this.

Thanks.

I also encountering it even I upgraded my Management studio to

Microsoft SQL Server Management Studio 9.00.1399.00

Please advise/assist in this error. Thanks.

|||

which simple select statement?

are you using Microsoft SQL Server Management Studio Express? with SQL Server 2005 Express Edition or SQL Server 2005 Express Edition with Advanced Services or SQL Server 2005 database engine?

tosc

|||

I installed SQL 2005 express toolkit in my PC and tried to connect to a Enterprise Edition SQL 2000 engine and SQL 2005 engine server. I tried for select rows in a table, example,

select * from<tablename>

It returns that error. i uninstall the Express edition toolkit and installed a enterprise version of the management studio. (version on my last post), still i got the same error when I executed that SELECT statement.

|||

is this SQL Server 2005 Express or EE ... u have mentioned EE ... in any case it seems that u have not applied SP1 and SP2 .... BTW can u post the script which u ran ...

Madhu

|||

I installed SQL 2005 Express Toolkit for the client tool,(Management Studio). my plan is to use this to manage our Enterprise Edition SQL servers(2000 and 2005).

I connect my Management Studio(SQL 2005 Express) to a SQL 2000 server enterprise edition.

I encountered this error:

An error occurred while executing batch. Error message is: Unknown error "-1".

even my script is a simple SELECT statement like this one:

SELECT * FROM <dbo.TableName>

I test the same script to a SQL 2005 Ent. Edition server. same error encountered. I tried a simple UPDATE statement, no error occured. it updates row in the SQL2000 server.

Then I uninstall the Express edition Management Studio(SQL 2005 Express toolkit). I install a Enterprise Edition management Studio(See my previous reply for the version) and still I encounter the same error when selecting data from any table of an Enterprise edition server.

Hope my issue is clear. do I have to install something? please advise.

|||Do I need to install SP1 even if I don't have SQL engine in my PC? i only have client tools in my PC. Please advise.|||

Hi,

which framework and OS are you using? are you sure, that you have all privelegs - to your databases?

|||

I have .NET 1.1 and 2.0 in my PC. I am using Win XP SP2. I already applied SP1 for SQL 2005 in my PC. still same error is encountered.

I have SQL 2000 and SQL 2005 Client utilities in my PC.

I didn't encounter it in Query Analyzer. Only when I use Management Studio.

I am using SA account.

|||

I have the same issue and have yet to find the resolution. One thing I have noticed is that the issue does not occur if you set your results to Text Mode. The issue appears to be related to mscorlib.dll.

|||

Could it be resolved by replacing the same dll with one from a working PC? or do i have to consider something like file version etc.?

|||

Hi all,

Any comment/suggestions to resolve this issue?

|||

I was able to resolve this issue on my system. It turns out that the client firewall tool used at my company "BlackIce" was detecting a buffer overflow exploit when making the calls to mscorlib.cll. I was able to list "C:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\SqlWb.exe" as an exception to rule and all is fine.

I hope these findings help you as well.

Need help with Update statement; DDL included

Four table: Securities, Positions, ExchangeList and ExchangeListMember
Securities has an SecurityID field and the exchange it is traded on.
Positions has a SecurityID and an Account.
ExchangeList is used to define the exchanges that an account is permitted to
own securities on. The ExchangeList.ExchangeListCode would contain an
account code.
ExchangeListMember contains the specific exchanges permitted for an account.
For example Account A can own securities on the NYS and BUE exchanges.
I have created a temp table called #TempPositions which contains all
positions. I want to update #TempPositions by placing an asterisk before the
SecurityID in records where the security is NOT permitted because the
securitys' exchange is NOT permissible.
UPDATE #TempPositions SET #TempPositions.SecurityID = '*' + #TempPositions
.SecurityID
WHERE .....
Here are my expected results for SELECT * FROM #TempPositions after the
update.
1,A
*2,A
3,A
3,B
4,B
*5,B
6,C
7,C
*1,D
*2,D
More narrative:
Account A is permitted to own securities on the NYS and BUE exchanges. It
has a position with SecurityID =2 which is traded on CBT. This position is
restricted.
Account B is permitted to own securities on the BUE and CPH exchanges. It
has a position with SecurityID =5 which is traded on NAS. This position is
restricted.
Account C does not have an ExchangeList. No restrictions.
Account D is permitted to own securities on the NAS exchange only. It has a
position with SecurityID =1 which is traded on NYS. It has a position with
SecurityID =2 which is traded on CBT .These positions is restricted.
This would be easier for me if the ExchangeList table defined restricted
exchanges rather than permitted exchanges. Any help would be appreciated.
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Securities]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Securities]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Positions]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Positions]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[ExchangeList]') and OBJECTPROPERTY(id, N'IsUserTable') =
1)
drop table [dbo].[ExchangeList]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[ExchangeListMember]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[ExchangeListMember]
GO
CREATE TABLE [dbo].[Securities] (
[SecurityID] [int] NOT NULL ,
[Exchange] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Positions] (
[SecurityID] [int] NOT NULL ,
[Account] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[ExchangeList] (
[ExchangeListCode] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[ExchangeListName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[ExchangeListMember] (
[ExchangeListCode] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[Exchange] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[#TempPositions] (
[SecurityID] [int] NOT NULL ,
[Account] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Securities] ADD
CONSTRAINT [PK_Securities] PRIMARY KEY CLUSTERED
(
[SecurityID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Positions] ADD
CONSTRAINT [PK_Positions] PRIMARY KEY CLUSTERED
(
[SecurityID],
[Account]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ExchangeList] ADD
CONSTRAINT [PK_ExchangeList] PRIMARY KEY CLUSTERED
(
[ExchangeListCode]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ExchangeListMember] ADD
CONSTRAINT [PK_ExchangeListMember] PRIMARY KEY CLUSTERED
(
[ExchangeListCode],
[Exchange]
) ON [PRIMARY]
GO
INSERT Securities (SecurityID,Exchange) VALUES (1,'NYS')
INSERT Securities (SecurityID,Exchange) VALUES (2,'CBT')
INSERT Securities (SecurityID,Exchange) VALUES (3,'BUE')
INSERT Securities (SecurityID,Exchange) VALUES (4,'CPH')
INSERT Securities (SecurityID,Exchange) VALUES (5,'NAS')
INSERT Securities (SecurityID,Exchange) VALUES (6,'IRL')
INSERT Securities (SecurityID,Exchange) VALUES (7,'JPN')
INSERT Securities (SecurityID,Exchange) VALUES (8,'KOR')
GO
INSERT Positions (SecurityID,Account)VALUES (1,'A')
INSERT Positions (SecurityID,Account)VALUES (2,'A')
INSERT Positions (SecurityID,Account)VALUES (3,'A')
INSERT Positions (SecurityID,Account)VALUES (3,'B')
INSERT Positions (SecurityID,Account)VALUES (4,'B')
INSERT Positions (SecurityID,Account)VALUES (5,'B')
INSERT Positions (SecurityID,Account)VALUES (6,'C')
INSERT Positions (SecurityID,Account)VALUES (7,'C')
INSERT Positions (SecurityID,Account)VALUES (1,'D')
INSERT Positions (SecurityID,Account)VALUES (2,'D')
GO
INSERT ExchangeList (ExchangeListCode,ExchangeListName) VALUES ('A', 'A
Permissible Exchanges')
INSERT ExchangeList (ExchangeListCode,ExchangeListName) VALUES ('B', 'B
Permissible Exchanges')
INSERT ExchangeList (ExchangeListCode,ExchangeListName) VALUES ('D', 'D
Permissible Exchanges')
INSERT ExchangeListMember (ExchangeListCode,Exchange) VALUES ('A','NYS')
INSERT ExchangeListMember (ExchangeListCode,Exchange) VALUES ('A','BUE')
INSERT ExchangeListMember (ExchangeListCode,Exchange) VALUES ('B','BUE')
INSERT ExchangeListMember (ExchangeListCode,Exchange) VALUES ('B','CPH')
INSERT ExchangeListMember (ExchangeListCode,Exchange) VALUES ('B','NAS')
INSERT ExchangeListMember (ExchangeListCode,Exchange) VALUES ('D','NAS')
INSERT #TempPositions (SecurityID,Account)(SELECT * FROM Positions)Terri wrote:
Thanks for the DDL.
I tried it out but I'm .
Your narrative explanation doesn't seem to completely fit the data you
supplied or I'm looking at it the wrong way.
However I think this is what you're looking for:
alter table #TempPositions add Restricted bit default 0 NOT NULL
UPDATE #TempPositions SET #TempPositions.Restricted = 1
WHERE securityID not in (select securities.SecurityID from
ExchangeListMember inner join securities on securities.SecurityID =
#TempPositions.SecurityID and ExchangeListCode = #TempPositions.Account
and securities.Exchange = ExchangeListMember.Exchange)
You cannot add a asterix to a integer column so I added a column named
Restricted and flag it 1 if it is.
HTH,
Stijn Verrept.|||Thanks Stijn, Sorry about me trying to add an asterisk to an integer field.
Altering the table is fine.
I had tried something like your solution but the problem is with Account C.
Account C doesn't have an ExchangeList so securities should never be
restricted. The subselect somehow needs to take into account "accounts" with
no ExchangeList and make sure the securities of those accounts are not
flagged as restricted.
"Stijn Verrept" <stjin@.entrysoft.com> wrote in message
news:1sqdnTV81ZHwoDreRVny0A@.scarlet.biz...
> Terri wrote:
> Thanks for the DDL.
> I tried it out but I'm .
> Your narrative explanation doesn't seem to completely fit the data you
> supplied or I'm looking at it the wrong way.
> However I think this is what you're looking for:
> alter table #TempPositions add Restricted bit default 0 NOT NULL
> UPDATE #TempPositions SET #TempPositions.Restricted = 1
> WHERE securityID not in (select securities.SecurityID from
> ExchangeListMember inner join securities on securities.SecurityID =
> #TempPositions.SecurityID and ExchangeListCode = #TempPositions.Account
> and securities.Exchange = ExchangeListMember.Exchange)
>
> You cannot add a asterix to a integer column so I added a column named
> Restricted and flag it 1 if it is.
> --
> HTH,
> Stijn Verrept.|||Terri wrote:

> Thanks Stijn, Sorry about me trying to add an asterisk to an integer
> field. Altering the table is fine.
> I had tried something like your solution but the problem is with
> Account C. Account C doesn't have an ExchangeList so securities
> should never be restricted. The subselect somehow needs to take into
> account "accounts" with no ExchangeList and make sure the securities
> of those accounts are not flagged as restricted.
Aha! That's probably what I didn't understand, then this will be
better :)
UPDATE #TempPositions SET #TempPositions.Restricted = 1
WHERE securityID not in (select securities.SecurityID from
ExchangeListMember inner join securities on securities.SecurityID =
#TempPositions.SecurityID and ExchangeListCode = #TempPositions.Account
and securities.Exchange = ExchangeListMember.Exchange)
and (select count(*) from ExchangeListMember where ExchangeListCode =
#TempPositions.Account) > 0
HTH,
Stijn Verrept.|||"Stijn Verrept" <stjin@.entrysoft.com> wrote in message
news:T9qdnWst9ZU81TrenZ2dnUVZ8qidnZ2d@.sc
arlet.biz...
> Aha! That's probably what I didn't understand, then this will be
> better :)
Perfect, thanks so much.|||>> I have created a temp table called #TempPositions which contains all posi
tions. I want to update #TempPositions by placing an asterisk [in violation
of the rule about not formatting display data in the database!!] before the
SecurityID in records [sic
] where the security is NOT permitted because the securitys' exchange is NOT
permissible. <<
Have you thought about using a VIEW that would always be up to date,
instead of constantly updating a temp table in proprietary syntax?
But the real point is that this is a constraint and needs to be done
with REFERENCES clauses and a proper design -- NOT a temp table at all!
Also, ask yourself why you have two tables with the SAME structure in
violation of the rules of any data modeling?|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1135050199.293240.265890@.g14g2000cwa.googlegroups.com...
positions. I want to update #TempPositions by placing an asterisk [in
violation of the rule about not formatting display data in the database!!]
before the SecurityID in records [sic] where the security is NOT permitted
because the securitys' exchange is NOT permissible. <<
> Have you thought about using a VIEW that would always be up to date,
> instead of constantly updating a temp table in proprietary syntax?
> But the real point is that this is a constraint and needs to be done
> with REFERENCES clauses and a proper design -- NOT a temp table at all!
> Also, ask yourself why you have two tables with the SAME structure in
> violation of the rules of any data modeling?
>
My employer strongly discourages me from presenting real data or table
structure so I may post a problem that helps me solve my issue but doesn't
reveal any information about my data, data structure, or systems. I suspect
others do this is well so I don't think it's safe to assume that posted data
structure or methods are used in production. They could merely serve as a
demonstration of a related problem.
In this case I am actually using temp tables in a stored procedure. I update
my temp table 6 times in the procedure. Perhaps someone could write a single
select statement which would return the results I need, but not me at this
point. I consider the logic of the procedure to be highly complex. I find it
easier to achieve my desired result with a series of updates against temp
tables although I realize this is not ideal from an academic point of view.
The procedure takes <4 seconds to run and is only run in production several
times a w so I consider the performance acceptable. Others who have
viewed this procedure find it easier to comprehend. I find it easier to
maintain and modify. I understand your point and will consider it in the
future.

Need help with UNION statement

Hello,
I'm trying to join results from two tables and still don't understand why
the following statement doesn't work:
SELECT rating
FROM (SELECT DISTINCT TOP 10 bp.rating, bp2.Cnt
FROM businesspartners bp INNER JOIN
(SELECT rating,
COUNT(*) AS Cnt
FROM
businesspartners
GROUP BY rating) bp2 ON
bp.rating = bp2.rating
WHERE (bp.rating <> '') AND (bp.rating IS NOT
NULL)
ORDER BY bp2.Cnt DESC)
UNION
SELECT rating
FROM (SELECT DISTINCT TOP 10 bp3.category AS rating, bp4.Cnt
FROM businesspartners bp3 INNER JOIN
(SELECT rating,
COUNT(*) AS Cnt
FROM
businesspartners
GROUP BY rating) bp4 ON
bp3.rating = bp4.rating
WHERE (bp3.rating <> '') AND (bp3.rating IS NOT
NULL)
ORDER BY bp4.Cnt DESC)
I tried each table and I get correct results, only when I tried to "union"
them it's giving me "Incorrect syntax near the keyword 'UNION'"
Give an alias to each derived table.
SELECT rating
FROM
(
SELECT DISTINCT TOP 10 bp.rating, bp2.Cnt
FROM businesspartners bp INNER JOIN
(
SELECT rating, COUNT(*) AS Cnt
FROM businesspartners
GROUP BY rating
) bp2
ON bp.rating = bp2.rating
WHERE (bp.rating <> '') AND (bp.rating IS NOT NULL)
ORDER BY bp2.Cnt DESC
) as t1 <--
UNION
SELECT rating
FROM (SELECT DISTINCT TOP 10 bp3.category AS rating, bp4.Cnt
FROM businesspartners bp3 INNER JOIN
(SELECT rating,
COUNT(*) AS Cnt
FROM
businesspartners
GROUP BY rating) bp4 ON
bp3.rating = bp4.rating
WHERE (bp3.rating <> '') AND (bp3.rating IS NOT
NULL)
ORDER BY bp4.Cnt DESC) as t2 <--
AMB
"Vlado Jasovic (excelleinc.com)" wrote:

> Hello,
> I'm trying to join results from two tables and still don't understand why
> the following statement doesn't work:
> SELECT rating
> FROM (SELECT DISTINCT TOP 10 bp.rating, bp2.Cnt
> FROM businesspartners bp INNER JOIN
> (SELECT rating,
> COUNT(*) AS Cnt
> FROM
> businesspartners
> GROUP BY rating) bp2 ON
> bp.rating = bp2.rating
> WHERE (bp.rating <> '') AND (bp.rating IS NOT
> NULL)
> ORDER BY bp2.Cnt DESC)
> UNION
> SELECT rating
> FROM (SELECT DISTINCT TOP 10 bp3.category AS rating, bp4.Cnt
> FROM businesspartners bp3 INNER JOIN
> (SELECT rating,
> COUNT(*) AS Cnt
> FROM
> businesspartners
> GROUP BY rating) bp4 ON
> bp3.rating = bp4.rating
> WHERE (bp3.rating <> '') AND (bp3.rating IS NOT
> NULL)
> ORDER BY bp4.Cnt DESC)
> I tried each table and I get correct results, only when I tried to "union"
> them it's giving me "Incorrect syntax near the keyword 'UNION'"
>
>
|||Also the whole query can only have one ORDER BY clause ... at the end of all
UNIONed sleect statements.
Also one note, you may or may not know the difference between "UNION" and
"UNION ALL" ... often people want "UNION ALL" and don't know it.
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> Give an alias to each derived table.
> SELECT rating
> FROM
> (
> SELECT DISTINCT TOP 10 bp.rating, bp2.Cnt
> FROM businesspartners bp INNER JOIN
> (
> SELECT rating, COUNT(*) AS Cnt
> FROM businesspartners
> GROUP BY rating
> ) bp2
> ON bp.rating = bp2.rating
> WHERE (bp.rating <> '') AND (bp.rating IS NOT NULL)
> ORDER BY bp2.Cnt DESC
> ) as t1 <--
> UNION
> SELECT rating
> FROM (SELECT DISTINCT TOP 10 bp3.category AS rating, bp4.Cnt
> FROM businesspartners bp3 INNER JOIN
> (SELECT rating,
> COUNT(*) AS Cnt
> FROM
> businesspartners
> GROUP BY rating) bp4 ON
> bp3.rating = bp4.rating
> WHERE (bp3.rating <> '') AND (bp3.rating IS NOT
> NULL)
> ORDER BY bp4.Cnt DESC) as t2 <--
>
> AMB
>
> "Vlado Jasovic (excelleinc.com)" wrote:
|||KH,
I think he is using the "order by" to asure that each "select top ..." will
be consistent.
AMB
"KH" wrote:
[vbcol=seagreen]
> Also the whole query can only have one ORDER BY clause ... at the end of all
> UNIONed sleect statements.
> Also one note, you may or may not know the difference between "UNION" and
> "UNION ALL" ... often people want "UNION ALL" and don't know it.
>
> "Alejandro Mesa" wrote:
|||Thank you very much for quick and great answer.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:D75847A7-61FC-4A41-BD91-8F88C6FFC1B6@.microsoft.com...[vbcol=seagreen]
> KH,
> I think he is using the "order by" to asure that each "select top ..."
> will
> be consistent.
>
> AMB
> "KH" wrote:
|||Ach, indeed... missed that!
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> KH,
> I think he is using the "order by" to asure that each "select top ..." will
> be consistent.
>
> AMB
> "KH" wrote: