get an error on a sample code

This is a sample code from an MSDN help site. I copied it and pasted into an open new query. I tried to execute it and got two errors:

USE AdventureWorks;
GO
DECLARE @.tablename sysname
SET @.tablename = N'Person.AddressType'
table_loop:
IF (@.@.FETCH_STATUS <> -2)
BEGIN
SELECT @.tablename = RTRIM(UPPER(@.tablename))
EXEC ('SELECT ''' + @.tablename + ''' = COUNT(*) FROM '
+ @.tablename )
PRINT ' '
END
FETCH NEXT FROM tnames_cursor INTO @.tablename
IF (@.@.FETCH_STATUS <> -1) GOTO table_loop
GO

The errors are:

Msg 208, Level 16, State 1, Line 1
Invalid object name 'PERSON.ADDRESSTYPE'.

Msg 16916, Level 16, State 1, Line 9
A cursor with the name 'tnames_cursor' does not exist

The database is connected. Table Person.AddressType is a part of it.

What is wrong?

Thanks.

Did you get this from the GOTO help topic? This note is in the topic right above the example:

Note:

The tnames_cursor cursor is not defined. This example is for illustration only.

so effectively this example is expected not to work. If you need some specific help on using the GOTO syntax, I would suggest asking in the T-SQL forum, that's where the syntax gurus hang out.

Mike

Get all records

Hi I have a table route with

routeid name

1 a

2 b

3 c

4 d

5 e

..

10 j

exExposure

caseid routeid

200 1

300 2

..

Now I have written a query which will join some other tables to get the results

select

er.exRouteID as RouteID,er.[Name] as route,

'<=5 Years'=SUM(CASE WHEN((pp.paAgeUnitId=1) THEN 1 ELSE 0 END ),

'6-12 Years'=SUM(CASE WHEN((pp.paAgeUnitId=2) THEN 1 ELSE 0 END ),

from cacase c

left join exExposure e on c.caCaseID=e.caCaseID

Left Join Route er on er.RouteID=e.RouteID

where --conditions

When i run this query I am getting only the routes which have the values exist in exExposure table.If for some routes like i, j I don't have corresponding casesids.But i need to get all the routes which exist in Route table even they don't have caseids.

For this i am trying like this:

select

er.exRouteID as RouteID,er.[Name] as route,

'<=5 Years'=SUM(CASE WHEN((pp.paAgeUnitId=1) THEN 1 ELSE 0 END ),

'6-12 Years'=SUM(CASE WHEN((pp.paAgeUnitId=2) THEN 1 ELSE 0 END ),

from cacase c

left join exExposure e on c.caCaseID=e.caCaseID

Left Join Route er on er.RouteID=e.RouteID

where --conditions

Union

Select er.RouteID,er.Name,0,0

From Route r

where r.routeID Not IN (Selelct RouteID From above Selelct query with the same where condtions and joins)

Any other better way for getting the unmatched routes other than this.

Thanks in advance.

I'm not sure what your question is, but I'll take a guess.

If you want the original query to contain all Routes (Route table), then name the Route table as the FIRST table in the series of joins, OR, in the current query, use a RIGHT JOIN with Routes. Either option 'should' give you what you seek.

|||

You can get your desired result using the following query..

select

er.exRouteID as RouteID,er.[Name] as route,

'<=5 Years'=SUM(CASE WHEN((pp.paAgeUnitId=1) THEN 1 ELSE 0 END ),

'6-12 Years'=SUM(CASE WHEN((pp.paAgeUnitId=2) THEN 1 ELSE 0 END ),

from cacase c

Left Join exExposure e on c.caCaseID=e.caCaseID And {all Your exExposure table based conditions}

Left Join Route er on er.RouteID=e.RouteID And {all your Route table based conditions}

where {other conditions}

|||

Hi,

I placed all the ExExposure table condion after the exExposure table join this is for Route table also. But now also it is giving Routes which have the CaseID s only. Any other way.

Thanks in advance.

|||

The following query will be help you...

Code Snippet

select

er.exRouteID as RouteID,er.[Name] as route,

'<=5 Years'=SUM(CASE WHEN((pp.paAgeUnitId=1) THEN 1 ELSE 0 END ),

'6-12 Years'=SUM(CASE WHEN((pp.paAgeUnitId=2) THEN 1 ELSE 0 END ),

from Route er

Left Join exExposure e on er.RouteID=e.RouteID

Left Join cacase c on c.caCaseID=e.caCaseID

where other condition

Get all active transactions

Hi

I have executed a data script using query analyser and closed the analyser without commiting the transaction.

I want to know are there any pending transactions still.

thanks

Quote:

Originally Posted by kvm

Hi

I have executed a data script using query analyser and closed the analyser without commiting the transaction.

I want to know are there any pending transactions still.

thanks

Did you use transactions explictly in your SQL or only wondering about Query analyser transaction?

Get active directory users from SQL Query Analyser

Get active directory users from SQL Query Analyser
--
I am trying to do a select in SQL Query Analyser to get the list of users in
active directory.
I use the following code:
sp_addlinkedserver 'ADSI', 'Active Directory Service Interfaces',
'ADSDSOObject', 'adsdatasource'
SELECT [Name],SN[Last Name]
FROM OPENQUERY( ADSI,
'SELECT Name,SN FROM ''LDAP://servername.domainname.com/CN=Users,
DC=domainname,DC=com''
WHERE objectCategory = ''Person'' AND objectClass = ''user'' order by
name')
But appears the following message error:
Server: Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing a query for execution against OLE DB
provider 'ADSDSOObject'.
OLE DB error trace [OLE/DB Provider 'ADSDSOObject' ICommandPrepare::Prepare
returned 0x80040e14].
What could be?
Tks.Try moving the ORDER BY clause outside of OPENQUERY. There is also something
a little strange in the outside SELECT; maybe a typo?
SELECT [Name],SN -- Why was this here? :[Last Name]
FROM OPENQUERY( ADSI,
'SELECT Name,SN FROM ''LDAP://servername.domainname.com/CN=Users,
DC=domainname,DC=com''
WHERE objectCategory = ''Person'' AND objectClass = ''user''')
ORDER BY Name
--
"Rui Oliveira" wrote:

> Get active directory users from SQL Query Analyser
> --
> I am trying to do a select in SQL Query Analyser to get the list of users
in
> active directory.
> I use the following code:
> sp_addlinkedserver 'ADSI', 'Active Directory Service Interfaces',
> 'ADSDSOObject', 'adsdatasource'
> SELECT [Name],SN[Last Name]
> FROM OPENQUERY( ADSI,
> 'SELECT Name,SN FROM ''LDAP://servername.domainname.com/CN=Users,
> DC=domainname,DC=com''
> WHERE objectCategory = ''Person'' AND objectClass = ''user'' order by
> name')
> But appears the following message error:
> Server: Msg 7321, Level 16, State 2, Line 1
> An error occurred while preparing a query for execution against OLE DB
> provider 'ADSDSOObject'.
> OLE DB error trace [OLE/DB Provider 'ADSDSOObject' ICommandPrepare::Prepar
e
> returned 0x80040e14].
> What could be?
> Tks.
>

Get A return value with EXEC?

Hi, I have an sql query like this :

DECLARE
@.TableName varchar(200),
@.ColumnName varchar(200),
@.EmployeeID varchar(200),
@.Result varchar(200);

SET @.TableName = 'Customer';
SET @.ColumnName = 'First_Name';
SET @.CustomerID = 28;

-- This line return Error
SET @.Result = EXEC ('select' + @.ColumnName + ' from ' + @.TableName + ' where Recid = ' + @.CustomerID + '');

Print @.Result;

I am pretty sure the SELECT statement in EXEC will only return 0 or 1 record. But how to capture result from EXEC?

Thanks

I think you can do the following:
EXEC ('SET @.Result = select' + @.ColumnName + ' from ' + @.TableName + ' where Recid = ' + @.CustomerID + '');

Get a max value from one table base on date from another.

I have a problem where I need to join multiple tables. Part of the query needs me to make the following relationship in a join.

Say I have a table with items, prices, and dates.

PriceTable

ITEM PRICE DATE

A 1.00 5/5/2000

B 3.00 1/1/2000

A 2.50 6/5/2004

....

This table represents an items price from the date on. So Item 'A' costed 1.00 from 5/5/2000 through 6/5/2004, then it costed 2.50 from that day on.

Now say I have a table of transactions with items, amount bought, and dates.

TransactionTable

ITEM AMOUNT DATE

A 5 6/6/2003

A 1 8/5/2003

A 2 8/5/2004

The total for A should come out to be 11.00. There are multiple Items and multiple price changes.

If someone could point out how a inner join of this nature would work it will help me in my query. I guess the reason i say inner join is because I am joining multiple other tables to do my query.

My current Price Table has a start date and an end date and my query looks something like this. "Select SUM(PriceTable.Price * TransactionTable.Amount) From PriceTable Inner Join TransactionTable ON TransactionTable.Date Between PriceTable.StartDate and PriceTable.EndDate AND PriceTable.Item = TransactionTable.Item".

I want something like this because the tables need to be in the format above "Select SUM(PriceTable.Price * TransactionTable.Amount) From PriceTable INNER JOIN TransactionTable ON Max(PriceTable.Date) WHERE PriceTable.Date <= TransactionTable.Date AND PriceTable.Item = TransactionTable.Item".

Hope that made sense, thanks.

SELECT c.item, SUM(c.ItemAmount) AS "Total" FROM (SELECT a.item , a.amount*(SELECT TOP (1) b.price

FROM PriceTable AS b

WHERE (b.Date <= a.Date)

ORDER BY b.Date DESC) AS "ItemAmount"

FROM TransactionTable AS a) AS c

GROUP BY c.item

|||

I had to change it slightly:

SELECT c.item, SUM(c.ItemAmount) AS "Total" FROM (SELECT a.item , a.amount*(SELECT TOP (1) b.price

FROM PriceTable AS b

WHERE (b.Date <= a.Date) AND b.item = a.item

ORDER BY b.Date DESC) AS "ItemAmount"

FROM TransactionTable AS a) AS c

GROUP BY c.item

Thank you for your help. Now I just have to integrate this into my larger query.

sql

get a count between a time range

I am trying to figure out if I can do this in a SQL query. I need to
know how many users are active during a given time period. I want to
be able to return the results below. If you look at the row with
User3 I only want to count User2 once even though he was active twice
during the time frame for User3
Start Time End Time User Id Concurrent User Count
06/22/2006 6:38:21 AM 06/22/2006 6:38:25 AM User1 1
06/22/2006 6:38:56 AM 06/22/2006 6:39:05 AM User1 3
06/22/2006 6:39:03 AM 06/22/2006 6:39:07 AM User2 3
06/22/2006 6:39:04 AM 06/22/2006 6:39:08 AM User3 3
06/22/2006 6:39:07 AM 06/22/2006 6:39:11 AM User2 2
06/22/2006 6:39:22 AM 06/22/2006 6:39:24 AM User2 1
I am pretty much stumped as how to proceed.
Thanks TimAssuming you're trying to query users who were active for any part of
the duration @.StartTime...@.EndTime inclusive.
SELECT DISTINCT UserId
FROM Table1
WHERE ( (StartTime <= @.StartTime AND EndTime >= @.StartTime)
OR (StartTime BETWEEN @.StartTime AND @.EndTime) )|||If you look at User3 there were 3 users active during User3's the start
time and end time including User3. I need to figure that count.
Thanks Tim
Lubdha Khandelwal wrote:
> Assuming you're trying to query users who were active for any part of
> the duration @.StartTime...@.EndTime inclusive.
> SELECT DISTINCT UserId
> FROM Table1
> WHERE ( (StartTime <= @.StartTime AND EndTime >= @.StartTime)
> OR (StartTime BETWEEN @.StartTime AND @.EndTime) )|||I'm . Do you want to count all the unique users that were
active during each of the active duration for all the users?
If so, this could get you that count:
SELECT T1.UserId, COUNT(DISTINCT T2.UserId)
FROM UserTable T1 INNER JOIN UserTable T2
ON ( (T2.StartTime <= T1.StartTime AND T2.EndTime >= T1.StartTime)
OR (T2.StartTime BETWEEN T1.StartTime AND T1.EndTime) )
GROUP BY T1.UserId|||TDT (tim.trujillo@.gmd.com) writes:
> I am trying to figure out if I can do this in a SQL query. I need to
> know how many users are active during a given time period. I want to
> be able to return the results below. If you look at the row with
> User3 I only want to count User2 once even though he was active twice
> during the time frame for User3
> Start Time End Time User Id Concurrent User
> Count
> 06/22/2006 6:38:21 AM 06/22/2006 6:38:25 AM User1 1
> 06/22/2006 6:38:56 AM 06/22/2006 6:39:05 AM User1 3
> 06/22/2006 6:39:03 AM 06/22/2006 6:39:07 AM User2 3
> 06/22/2006 6:39:04 AM 06/22/2006 6:39:08 AM User3 3
> 06/22/2006 6:39:07 AM 06/22/2006 6:39:11 AM User2 2
> 06/22/2006 6:39:22 AM 06/22/2006 6:39:24 AM User2 1
> I am pretty much stumped as how to proceed.
I assume that the above is the desired output. But how does the input
look like? It would help to have the CREATE TABLE statement and
INSERT statements for the test data. Then it would be simple to copy-paste
into a query tool to develop a tested 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|||Lubdha that last query did not work. I was geting resource limit has
been reached and the query was cancelled. But here is script to create
the table and insert rows.
CREATE TABLE [dbo].[UserActivity]
(
[StartTime] [datetime] NULL,
[EndTime] [datetime] NULL,
[UserID] [varchar](50) NULL
)
INSERT INTO UserActivity (StartTime, EndTime, UserID) Values
('06/22/2006 6:38:21 AM', '06/22/2006 6:38:25 AM','User1')
INSERT INTO UserActivity (StartTime, EndTime, UserID) Values
('06/22/2006 6:38:56 AM','06/22/2006 6:39:05 AM','User1')
INSERT INTO UserActivity (StartTime, EndTime, UserID) Values
('06/22/2006 6:39:03 AM','06/22/2006 6:39:07 AM','User2')
INSERT INTO UserActivity (StartTime, EndTime, UserID) Values
('06/22/2006 6:39:04 AM','06/22/2006 6:39:08 AM','User3')
INSERT INTO UserActivity (StartTime, EndTime, UserID) Values
('06/22/2006 6:39:07 AM','06/22/2006 6:39:11 AM','User2')
INSERT INTO UserActivity (StartTime, EndTime, UserID) Values
('06/22/2006 6:39:22 AM','06/22/2006 6:39:24 AM','User2')
Erland Sommarskog wrote:
> TDT (tim.trujillo@.gmd.com) writes:
> I assume that the above is the desired output. But how does the input
> look like? It would help to have the CREATE TABLE statement and
> INSERT statements for the test data. Then it would be simple to copy-paste
> into a query tool to develop a tested 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|||Thanks for providing the table and data.
SELECT A.StartTime,
A.EndTime,
A.UserID,
count(distinct B.UserID) as ConcurrentUserCount
FROM UserActivity as A
JOIN UserActivity as B
ON A.StartTime between B.StartTime and B.EndTime
OR A.EndTime between B.StartTime and B.EndTime
or B.StartTime between A.StartTime and A.EndTime
GROUP BY A.StartTime,
A.EndTime,
A.UserID
ORDER BY 1, 2, 3
Roy Harvey
Beacon Falls, CT
On 23 Jun 2006 15:46:23 -0700, "TDT" <tim.trujillo@.gmd.com> wrote:
>Lubdha that last query did not work. I was geting resource limit has
>been reached and the query was cancelled. But here is script to create
>the table and insert rows.
>CREATE TABLE [dbo].[UserActivity]
>(
> [StartTime] [datetime] NULL,
> [EndTime] [datetime] NULL,
> [UserID] [varchar](50) NULL
> )
>
>INSERT INTO UserActivity (StartTime, EndTime, UserID) Values
>('06/22/2006 6:38:21 AM', '06/22/2006 6:38:25 AM','User1')
>INSERT INTO UserActivity (StartTime, EndTime, UserID) Values
>('06/22/2006 6:38:56 AM','06/22/2006 6:39:05 AM','User1')
>INSERT INTO UserActivity (StartTime, EndTime, UserID) Values
>('06/22/2006 6:39:03 AM','06/22/2006 6:39:07 AM','User2')
>INSERT INTO UserActivity (StartTime, EndTime, UserID) Values
>('06/22/2006 6:39:04 AM','06/22/2006 6:39:08 AM','User3')
>INSERT INTO UserActivity (StartTime, EndTime, UserID) Values
>('06/22/2006 6:39:07 AM','06/22/2006 6:39:11 AM','User2')
>INSERT INTO UserActivity (StartTime, EndTime, UserID) Values
>('06/22/2006 6:39:22 AM','06/22/2006 6:39:24 AM','User2')
>
>Erland Sommarskog wrote:|||This is not a table; it has no key and no possible way to get a key.
If you knew what a table was, would you have written this? If you knew
either ISO-8601 or SQL, would you have used the proper temporal
formats?
CREATE TABLE UserActivity
(user_id VARCHAR(50) NOT NULL, -- magic data type used by newbies!
start_time DATETIME NOT NULL,
end_time DATETIME, -- null = current
PRIMARY KEY (user_id, start_time))
Where is the DDL for your reporting periods? What is your
grandularity? There are some specs missing here. However, you can get
samples at points in time; Set up a VIEW or TABLE with times in it.
SELECT R.report_time, COUNT(user_id) AS active_user_cnt
FROM ReportPeriods AS R, UserActivity AS U
WHERE R.report_time BETWEEN U.start_time AND U.end_time
GROUP BY R.report_time;
This actually works pretty well with a fine grandularity for simple
time series analysis. But if you wanted to do (n)-minute durations,
then we need a very complex set of rules for logging in and out within
a single duration.

get 5 rows from 2 unrelated tables (was "Complicated sql query.... need help bad

I am trying to write a query that queries 2 tables and gets back 5 rows. These two tables arent really related and the data I get back is different for either table.
The 5 rows that I want to get back are for the LATEST 5 DATES. The date field for one table (F_INSPECTIONS) is END_DATE and the date field for the other table (F_OCCURRENCES) is OCCURRRENCE_DATE.

I am writing a program to do this so if its absolutely impossible to implement this with sql code then a suggestion of how I might be able to go about doing it antoher way would help.

Table descriptions:

/****** Object: Table [dbo].[F_INSPECTIONS] Script Date: 2/8/2005 10:59:41 AM ******/
CREATE TABLE [dbo].[F_INSPECTIONS] (
[INSPECTION_ID] [varchar] (15) COLLATE SQL_Latin1_General_CP850_CI_AI NOT NULL ,
[INSPECTION_NAME] [varchar] (150) COLLATE SQL_Latin1_General_CP850_CI_AI NULL ,
[CAP_FACILITY_ID] [int] NOT NULL ,
[REG_SURR_ID] [smallint] NOT NULL ,
[START_DATE] [datetime] NULL ,
[END_DATE] [datetime] NULL ,
[INSP_UPDATED_ON] [datetime] NULL ,
[INSP_ORIGIN_ID] [tinyint] NULL ,
[INSP_TYPE_ID] [tinyint] NULL ,
[DAYS_SINCE_LAST] [smallint] NULL ,
[VIOLATION_COUNT] [smallint] NULL ,
[NON_COMPLIANCE_IND] [tinyint] NULL ,
[INSPECTION_COUNT] [smallint] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[F_OCCURRENCES] (
[OCCURRENCE_ID] [varchar] (15) COLLATE SQL_Latin1_General_CP850_CI_AI NOT NULL ,
[CAP_FACILITY_ID] [int] NOT NULL ,
[OCCURRENCE_NM] [varchar] (150) COLLATE SQL_Latin1_General_CP850_CI_AI NULL ,
[OCCURRENCE_DATE] [datetime] NULL ,
[REG_SURR_ID] [smallint] NOT NULL ,
[REPORTED_DATE] [datetime] NULL ,
[ASSESSMENT_DATE] [datetime] NULL ,
[UPDATED_ON] [datetime] NULL ,
[ORIGIN_ID] [tinyint] NULL ,
[CATEGORY_ID] [tinyint] NULL ,
[OUTCOME_ID] [tinyint] NULL
) ON [PRIMARY]

I need to query from these 2 tables and get these columns back:Are you trying to get back only 5 rows(attempting to join 2 unrelated tables), or 5 rows from each table(candidate for a UNION)?|||If I understood well, you'll need something like this: first "join" similar results from both tables using the UNION, and then select first five of them.SELECT ID, dat
FROM (SELECT ins_id ID, end_date dat
FROM F_INS
UNION
SELECT occ_id ID, occ_date dat
FROM F_OCC
ORDER BY 2)
WHERE ROWNUM <= 5
ORDER BY 2;Selecting "top 5" rows can be done in different ways; this one (using the ROWNUM pseudocolumn) is used in Oracle. I *think* MySQL has "SELECT TOP 5 ..." statement; I don't know which database engine you use (is it Access?), but I hope my example will help you find the result.|||This is exacly the logic I was looking for (top 5 dates out of the entire set) Hopefully a union like this works in SQL SERVER.

Thanks so much.|||My translation of the Oracle syntax into SQL-92 (which should run on MS-SQL 2000) would be:SELECT TOP 5 ID, dat
FROM (
SELECT ins_id ID, end_date dat
FROM F_INS
UNION SELECT occ_id ID, occ_date dat
FROM F_OCC) AS A
ORDER BY 2-PatP|||Here is the statement I wrote using that same logic. It works great however. i cant get the top 5 to work.

(select
F_OCCURRENCES.CAP_FACILITY_ID,
F_OCCURRENCES.REG_SURR_ID as reg_surr_id,
'N/A' as INPECTOR,
'N/A' as COMPLIANCE_STATUS,
'OCCURRENCE' as ACTIVITY,
D_OCCURRENCE_OUTCOME.OUTCOME_ENG_DESC as OUTCOME,
D_REGULATION.REG_ENGLISH_DESC AS REGULATION,
F_OCCURRENCES.OCCURRENCE_DATE as theDATE
from F_OCCURRENCES INNER JOIN D_OCCURRENCE_OUTCOME ON F_OCCURRENCES.OUTCOME_ID = D_OCCURRENCE_OUTCOME.OUTCOME_ID INNER JOIN D_REGULATION ON D_REGULATION.REG_SURR_ID = F_OCCURRENCES.REG_SURR_ID where cap_facility_id = '11518' and F_OCCURRENCES.REG_SURR_ID = '101'
UNION
SELECT
F_INSPECTIONS.CAP_FACILITY_ID,
F_INSPECTIONS.REG_SURR_ID as reg_surr_id,
'NO DATA' as INSPECTOR,
CASE Non_COMPLIANCE_IND WHEN 1 THEN 'Non-Compliant' WHEN 0 THEN 'Compliant' END as COMPLIANCE_STATUS,
'INSPECTION' as ACTIVITY,
DISP_ENGLISH_DESC as OUTCOME,
D_REGULATION.REG_ENGLISH_DESC AS REGULATION,
F_INSPECTIONS.START_DATE as theDATE
FROM F_INSPECTIONS LEFT JOIN F_VIOLATIONS ON F_INSPECTIONS.INSPECTION_ID = F_VIOLATIONS.INSPECTION_ID Left Join D_DISPOSITION on D_DISPOSITION.VIOLATION_DISP_ID = F_VIOLATIONS.VIOLATION_DISP_ID LEFT JOIN D_REGULATION ON F_INSPECTIONS.REG_SURR_ID = D_REGULATION.REG_SURR_ID where F_INSPECTIONS.REG_SURR_ID = '101' and F_INSPECTIONS.CAP_FACILITY_ID = '11518'
)
order by theDate desc|||This is just a "shot in the dark" since I'm not willing to take the time to analyze this SQL at the moment, but if that SQL works then I'd suggest:SELECT TOP 5 *
FROM (
SELECT
F_OCCURRENCES.CAP_FACILITY_ID
, F_OCCURRENCES.REG_SURR_ID as reg_surr_id
, 'N/A' as INPECTOR
, 'N/A' as COMPLIANCE_STATUS
, 'OCCURRENCE' as ACTIVITY
, D_OCCURRENCE_OUTCOME.OUTCOME_ENG_DESC as OUTCOME
, D_REGULATION.REG_ENGLISH_DESC AS REGULATION
, F_OCCURRENCES.OCCURRENCE_DATE as theDATE
FROM F_OCCURRENCES
INNER JOIN D_OCCURRENCE_OUTCOME
ON F_OCCURRENCES.OUTCOME_ID = D_OCCURRENCE_OUTCOME.OUTCOME_ID
INNER JOIN D_REGULATION
ON D_REGULATION.REG_SURR_ID = F_OCCURRENCES.REG_SURR_ID
WHERE cap_facility_id = '11518'
and F_OCCURRENCES.REG_SURR_ID = '101'
UNION SELECT
F_INSPECTIONS.CAP_FACILITY_ID
, F_INSPECTIONS.REG_SURR_ID as reg_surr_id
, 'NO DATA' as INSPECTOR
, CASE Non_COMPLIANCE_IND
WHEN 1 THEN 'Non-Compliant'
WHEN 0 THEN 'Compliant'
END as COMPLIANCE_STATUS
, 'INSPECTION' as ACTIVITY
, DISP_ENGLISH_DESC as OUTCOME
, D_REGULATION.REG_ENGLISH_DESC AS REGULATION
, F_INSPECTIONS.START_DATE as theDATE
FROM F_INSPECTIONS
LEFT JOIN F_VIOLATIONS
ON F_INSPECTIONS.INSPECTION_ID = F_VIOLATIONS.INSPECTION_ID
LEFT JOIN D_DISPOSITION
on D_DISPOSITION.VIOLATION_DISP_ID = F_VIOLATIONS.VIOLATION_DISP_ID
LEFT JOIN D_REGULATION
ON F_INSPECTIONS.REG_SURR_ID = D_REGULATION.REG_SURR_ID
where F_INSPECTIONS.REG_SURR_ID = '101'
and F_INSPECTIONS.CAP_FACILITY_ID = '11518'
) AS z
ORDER BY theDate desc-PatP|||Brilliant!
the "as z" is what made this work.

thanks very much|||That need for an alias bytes a lot of people... Standard SQL-92 isn't always as forgiving as Oracle (or any other vendor) can be, but that's the price you pay for portability.

-PatPsql

Get 2 Decimal Places in Avg

I am running the query below but the results show only the integer portion.
I know the results should be values like "2.33" instead of "2". How do I get
the correct result format?
Wayne
========================================
SELECT judgeevalsbyunits.JudgeName, judgeevalsbyunits.JudgeCaption,
Count(judgeevalsbyunits.UnitID) As "Count of Evals",
Avg(judgeevalsbyunits.RegScoring) As "Reg Scoring Avg",
Avg(judgeevalsbyunits.WCScoring) As "WC Scoring Avg",
Avg(judgeevalsbyunits.TapeDialog) As "Tape Dialog Avg",
Avg(judgeevalsbyunits.Conduct) As "Conduct Avg"
FROM judgeevalsbyunits
INNER JOIN judges2eval ON judgeevalsbyunits.JudgeID=judges2eval.ID
Group By judgeevalsbyunits.JudgeName, judgeevalsbyunits.JudgeCaptionIf the source columns are only integer values, you only get those back, if
you want to get a decimal value back, you have to convert the source columns
to the appropiate format, eg. AVG(CONVERT(decimal(8,2),YourColumn))
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Wayne Wengert" <wayneSKIPSPAM@.wengert.org> schrieb im Newsbeitrag
news:%2354kWYrZFHA.3364@.TK2MSFTNGP09.phx.gbl...
>I am running the query below but the results show only the integer portion.
>I know the results should be values like "2.33" instead of "2". How do I
>get the correct result format?
> Wayne
> ========================================
> SELECT judgeevalsbyunits.JudgeName, judgeevalsbyunits.JudgeCaption,
> Count(judgeevalsbyunits.UnitID) As "Count of Evals",
> Avg(judgeevalsbyunits.RegScoring) As "Reg Scoring Avg",
> Avg(judgeevalsbyunits.WCScoring) As "WC Scoring Avg",
> Avg(judgeevalsbyunits.TapeDialog) As "Tape Dialog Avg",
> Avg(judgeevalsbyunits.Conduct) As "Conduct Avg"
> FROM judgeevalsbyunits
> INNER JOIN judges2eval ON judgeevalsbyunits.JudgeID=judges2eval.ID
> Group By judgeevalsbyunits.JudgeName, judgeevalsbyunits.JudgeCaption
>|||Try,
...
Avg(judgeevalsbyunits.RegScoring * 1.0) As "Reg Scoring Avg",
...
The average value of an integer column is an integer value. Cast the value
to decimal to get a decimal avg.
Example:
select
avg(c1 * 1.0)
from
(
select cast(1 as int)
union all
select cast(2 as int)
) t1(c1)
select
avg(c1)
from
(
select cast(1 as int)
union all
select cast(2 as int)
) t1(c1)
go
AMB
"Wayne Wengert" wrote:

> I am running the query below but the results show only the integer portion
.
> I know the results should be values like "2.33" instead of "2". How do I g
et
> the correct result format?
> Wayne
> ========================================
> SELECT judgeevalsbyunits.JudgeName, judgeevalsbyunits.JudgeCaption,
> Count(judgeevalsbyunits.UnitID) As "Count of Evals",
> Avg(judgeevalsbyunits.RegScoring) As "Reg Scoring Avg",
> Avg(judgeevalsbyunits.WCScoring) As "WC Scoring Avg",
> Avg(judgeevalsbyunits.TapeDialog) As "Tape Dialog Avg",
> Avg(judgeevalsbyunits.Conduct) As "Conduct Avg"
> FROM judgeevalsbyunits
> INNER JOIN judges2eval ON judgeevalsbyunits.JudgeID=judges2eval.ID
> Group By judgeevalsbyunits.JudgeName, judgeevalsbyunits.JudgeCaption
>
>|||convert the fields into the decimal format like
AVG(CAST(judgeevalsbyunits.RegScoring) AS FLOAT) or AS DECIMAL(x,x)
cheers
"Wayne Wengert" <wayneSKIPSPAM@.wengert.org> wrote in message
news:%2354kWYrZFHA.3364@.TK2MSFTNGP09.phx.gbl...
>I am running the query below but the results show only the integer portion.
>I know the results should be values like "2.33" instead of "2". How do I
>get the correct result format?
> Wayne
> ========================================
> SELECT judgeevalsbyunits.JudgeName, judgeevalsbyunits.JudgeCaption,
> Count(judgeevalsbyunits.UnitID) As "Count of Evals",
> Avg(judgeevalsbyunits.RegScoring) As "Reg Scoring Avg",
> Avg(judgeevalsbyunits.WCScoring) As "WC Scoring Avg",
> Avg(judgeevalsbyunits.TapeDialog) As "Tape Dialog Avg",
> Avg(judgeevalsbyunits.Conduct) As "Conduct Avg"
> FROM judgeevalsbyunits
> INNER JOIN judges2eval ON judgeevalsbyunits.JudgeID=judges2eval.ID
> Group By judgeevalsbyunits.JudgeName, judgeevalsbyunits.JudgeCaption
>|||Thanks guys. That is exactly what I needed.
Wayne
"keber" <k@.c.com> wrote in message
news:O6fKoirZFHA.3152@.TK2MSFTNGP14.phx.gbl...
> convert the fields into the decimal format like
> AVG(CAST(judgeevalsbyunits.RegScoring) AS FLOAT) or AS DECIMAL(x,x)
> cheers
> "Wayne Wengert" <wayneSKIPSPAM@.wengert.org> wrote in message
> news:%2354kWYrZFHA.3364@.TK2MSFTNGP09.phx.gbl...
>

get @@identity from several records

Hi,

I was wondering if you can help.

I am running an sql query that creates several new records based on a select query. In other words duplicating some existing records in a table.

What I need to do is after I have created these new records update a field in each of them.

Any ideas how I can retrieve all of their ids at insertion and then use them to update a field in each of them?

Ideally I would like to use @.@.identity and datareaders with vb.net.

Cheers

Mark :)

You need to put all the TSQL into a stored procedure. After each insert use SCOPE_IDENTITY() to get the id of the record just inserted.

|||Decorate your table with a new column, let's call it "batchid". It could be a uniqueidentifier.

DECLARE @.batchid uniqueidentifier

SELECT @.batchid=newid()

--The insert

INSERT INTO table (column1,column2 ..... , batchid)
SELECT column1,column2......, @.batchid FROM table WHERE...

--Retrieve the new id:s

SELECT id FROM table WHERE batchid=@.batchid

But it's quite likely that you can insert the correct data directly, instead of having to update it afterwards.

INSERT INTO table (column1,column2 ..... , column15)
SELECT column1,column2......, @.thenewvalueforcolumn15 FROM table WHERE...|||

Although I like the solution of gunteman, I'm just wondering why you can't update the field in the INSERT statement?

|||

Hi,

Thanks for your help. But i would really like to update the field in the insert statement.

Any suggestions?

Cheers

Mark :)

|||

I described it above. If you want to set the new value directly, just include it in your insert statement.

INSERT INTO table (column1,column2 ..... , column15)
SELECT column1,column2......, @.thenewvalueforcolumn15 FROM table WHERE...

If you show us your INSERT and UPDATE statement, we could help you combine them.

Geographical data in SQL Server

Hi,
Is there any way to use SQL server to store and query geographical data? For
example, be able to query all the records that fall withing certain
geographical poligon defined by lat/lon coordinates?
Thanks.
You're talking about GIS. You could store the data in SQL but you're
probably going to use MapInfo or ArcInfo to perform the geographical
queries..
Tim Heap
Software & Database Manager
POSTAR Ltd
www.postar.co.uk
tim@.postar.co.uk
*** Sent via Developersdex http://www.codecomments.com ***

Geographical data in SQL Server

Hi,
Is there any way to use SQL server to store and query geographical data? For
example, be able to query all the records that fall withing certain
geographical poligon defined by lat/lon coordinates?
Thanks.You're talking about GIS. You could store the data in SQL but you're
probably going to use MapInfo or ArcInfo to perform the geographical
queries..
Tim Heap
Software & Database Manager
POSTAR Ltd
www.postar.co.uk
tim@.postar.co.uk
*** Sent via Developersdex http://www.codecomments.com ***

Generic Query Designer quotations

I am trying to use the generic query designer so that I can add dynamic
parameters. My problem is that my query contains double quotes already, ie:
SELECT PRODUCTION_DATE, TESTDATE, "524CLPRXD1_CONS", "524ALPRXD1_BRITE",
"524ALPRXD1_PH", "524ALPRXD1_COND", "524TLPRXD1_TEMP",
"524CLPRXD2_CONS", "524ALPRXD2_BRITE",
"524ALPRXD2_PH", "524ALPRXD2_COND", "524TLPRXD2_TEMP", "524CLPRXD3_CONS",
"524ALPRXD3_BRITE", "524ALPRXD3_PH",
"524ALPRXD3_COND", "524TLPRXD3_TEMP", "615CLKRAFT_CONS", "615ALKRAFT_TEMP",
"615ALKRAFT_BRITE", "565ALPOTWR_CSF",
"565CLPOTWR_CONS", "565CLWW11_CONS", "524ALTMPSS_COND", "565ALPRTWR_BRITE",
"524ALTMP11_CSF", "524CLTMP11_CONS",
"524ALTMP11_BRITE", "524ALTMP11_PH", "524ALTMP11_COND", "524TLTMP11_TEMP",
"631ALTMP_CSF", "631CLTMP_CONS", "631ALTMP_BRITE"
FROM GP4_LAB
How would I put this into the generic query designer, because when I try to
do it, I get an error "End of statement expected".Try using single quotes unless double quotes are required by your data
provider.
--
Thanks.
Donovan R. Smith
Software Test Lead
This posting is provided "AS IS" with no warranties, and confers no rights.|||Double quotes are required. It is an Oracle database, I am using the OLE DB
provider.
"Donovan R. Smith [MS]" wrote:
> Try using single quotes unless double quotes are required by your data
> provider.
> --
> Thanks.
> Donovan R. Smith
> Software Test Lead
> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||It should work fine. Unless you are building an expression, quotes should
not matter. Have you tried it with the graphical one?
--
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"dachrist" <dachrist@.discussions.microsoft.com> wrote in message
news:F8F77C8B-FEC5-4D48-A272-2CC7AD4286B1@.microsoft.com...
> Double quotes are required. It is an Oracle database, I am using the OLE
> DB
> provider.
> "Donovan R. Smith [MS]" wrote:
>> Try using single quotes unless double quotes are required by your data
>> provider.
>> --
>> Thanks.
>> Donovan R. Smith
>> Software Test Lead
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.|||I need to build the query in the generic designer, because I need to create
dynamic parameters. I don't believe I can do that in the graphical designer?
The actual query works fine in the graphical designer.
"Brian Welcker [MSFT]" wrote:
> It should work fine. Unless you are building an expression, quotes should
> not matter. Have you tried it with the graphical one?
> --
> Brian Welcker
> Group Program Manager
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "dachrist" <dachrist@.discussions.microsoft.com> wrote in message
> news:F8F77C8B-FEC5-4D48-A272-2CC7AD4286B1@.microsoft.com...
> > Double quotes are required. It is an Oracle database, I am using the OLE
> > DB
> > provider.
> >
> > "Donovan R. Smith [MS]" wrote:
> >
> >> Try using single quotes unless double quotes are required by your data
> >> provider.
> >>
> >> --
> >> Thanks.
> >>
> >> Donovan R. Smith
> >> Software Test Lead
> >>
> >> This posting is provided "AS IS" with no warranties, and confers no
> >> rights.
> >>
>
>|||If you are building the SQL dynamically then I suggest you back up and make
sure you are creating the appropriate syntax. Set the source of a textbox to
the expression you are currently doing in the generic designer. Then you can
copy and paste that into whatever Oracle calls their tool to execute ad hoc
queries. Then you can quickly find out whether the syntax is correct.
Consider that the generic designer essentially allows passthrough queries.
You need the exact same syntax that you would have if you were going
directly against Oracle.
Bruce L-C
"dachrist" <dachrist@.discussions.microsoft.com> wrote in message
news:32DB3644-D789-45E9-A4F1-35173AA6A4D7@.microsoft.com...
>I need to build the query in the generic designer, because I need to create
> dynamic parameters. I don't believe I can do that in the graphical
> designer?
> The actual query works fine in the graphical designer.
> "Brian Welcker [MSFT]" wrote:
>> It should work fine. Unless you are building an expression, quotes should
>> not matter. Have you tried it with the graphical one?
>> --
>> Brian Welcker
>> Group Program Manager
>> SQL Server Reporting Services
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> "dachrist" <dachrist@.discussions.microsoft.com> wrote in message
>> news:F8F77C8B-FEC5-4D48-A272-2CC7AD4286B1@.microsoft.com...
>> > Double quotes are required. It is an Oracle database, I am using the
>> > OLE
>> > DB
>> > provider.
>> >
>> > "Donovan R. Smith [MS]" wrote:
>> >
>> >> Try using single quotes unless double quotes are required by your data
>> >> provider.
>> >>
>> >> --
>> >> Thanks.
>> >>
>> >> Donovan R. Smith
>> >> Software Test Lead
>> >>
>> >> This posting is provided "AS IS" with no warranties, and confers no
>> >> rights.
>> >>
>>|||Yes, I have done that and confirmed that I am using the correct syntax. I
think the problem is that Oracle requires any field that starts with a number
to have a quote around it to designate it as a string. These quotes are what
is causing me problems in the Generic query designer, because I want to add
dynamic parameters to the query.
"Bruce Loehle-Conger" wrote:
> If you are building the SQL dynamically then I suggest you back up and make
> sure you are creating the appropriate syntax. Set the source of a textbox to
> the expression you are currently doing in the generic designer. Then you can
> copy and paste that into whatever Oracle calls their tool to execute ad hoc
> queries. Then you can quickly find out whether the syntax is correct.
> Consider that the generic designer essentially allows passthrough queries.
> You need the exact same syntax that you would have if you were going
> directly against Oracle.
> Bruce L-C
> "dachrist" <dachrist@.discussions.microsoft.com> wrote in message
> news:32DB3644-D789-45E9-A4F1-35173AA6A4D7@.microsoft.com...
> >I need to build the query in the generic designer, because I need to create
> > dynamic parameters. I don't believe I can do that in the graphical
> > designer?
> > The actual query works fine in the graphical designer.
> >
> > "Brian Welcker [MSFT]" wrote:
> >
> >> It should work fine. Unless you are building an expression, quotes should
> >> not matter. Have you tried it with the graphical one?
> >>
> >> --
> >> Brian Welcker
> >> Group Program Manager
> >> SQL Server Reporting Services
> >>
> >> This posting is provided "AS IS" with no warranties, and confers no
> >> rights.
> >>
> >> "dachrist" <dachrist@.discussions.microsoft.com> wrote in message
> >> news:F8F77C8B-FEC5-4D48-A272-2CC7AD4286B1@.microsoft.com...
> >> > Double quotes are required. It is an Oracle database, I am using the
> >> > OLE
> >> > DB
> >> > provider.
> >> >
> >> > "Donovan R. Smith [MS]" wrote:
> >> >
> >> >> Try using single quotes unless double quotes are required by your data
> >> >> provider.
> >> >>
> >> >> --
> >> >> Thanks.
> >> >>
> >> >> Donovan R. Smith
> >> >> Software Test Lead
> >> >>
> >> >> This posting is provided "AS IS" with no warranties, and confers no
> >> >> rights.
> >> >>
> >>
> >>
> >>
>
>|||dachrist wrote:
> I think the problem is that Oracle requires any field that starts with a number
> to have a quote around it to designate it as a string.
I'm sure there's an Oracle equivalent of SQL Profiler. Would you run
that tool or NetMon and see whether the generic query designer is
passing the appropriate string to Oracle?
--
Thanks.
Donovan R. Smith
Software Test Lead
This posting is provided "AS IS" with no warranties, and confers no rights.|||Could you post the statement that you have in the Generic query designer?
That might help generate some ideas on what is going on.
Bruce L-C
"dachrist" <dachrist@.discussions.microsoft.com> wrote in message
news:0C34220B-C34E-4117-87A6-F9835BC80B98@.microsoft.com...
> Yes, I have done that and confirmed that I am using the correct syntax. I
> think the problem is that Oracle requires any field that starts with a
number
> to have a quote around it to designate it as a string. These quotes are
what
> is causing me problems in the Generic query designer, because I want to
add
> dynamic parameters to the query.
> "Bruce Loehle-Conger" wrote:
> > If you are building the SQL dynamically then I suggest you back up and
make
> > sure you are creating the appropriate syntax. Set the source of a
textbox to
> > the expression you are currently doing in the generic designer. Then you
can
> > copy and paste that into whatever Oracle calls their tool to execute ad
hoc
> > queries. Then you can quickly find out whether the syntax is correct.
> > Consider that the generic designer essentially allows passthrough
queries.
> > You need the exact same syntax that you would have if you were going
> > directly against Oracle.
> >
> > Bruce L-C
> >
> > "dachrist" <dachrist@.discussions.microsoft.com> wrote in message
> > news:32DB3644-D789-45E9-A4F1-35173AA6A4D7@.microsoft.com...
> > >I need to build the query in the generic designer, because I need to
create
> > > dynamic parameters. I don't believe I can do that in the graphical
> > > designer?
> > > The actual query works fine in the graphical designer.
> > >
> > > "Brian Welcker [MSFT]" wrote:
> > >
> > >> It should work fine. Unless you are building an expression, quotes
should
> > >> not matter. Have you tried it with the graphical one?
> > >>
> > >> --
> > >> Brian Welcker
> > >> Group Program Manager
> > >> SQL Server Reporting Services
> > >>
> > >> This posting is provided "AS IS" with no warranties, and confers no
> > >> rights.
> > >>
> > >> "dachrist" <dachrist@.discussions.microsoft.com> wrote in message
> > >> news:F8F77C8B-FEC5-4D48-A272-2CC7AD4286B1@.microsoft.com...
> > >> > Double quotes are required. It is an Oracle database, I am using
the
> > >> > OLE
> > >> > DB
> > >> > provider.
> > >> >
> > >> > "Donovan R. Smith [MS]" wrote:
> > >> >
> > >> >> Try using single quotes unless double quotes are required by your
data
> > >> >> provider.
> > >> >>
> > >> >> --
> > >> >> Thanks.
> > >> >>
> > >> >> Donovan R. Smith
> > >> >> Software Test Lead
> > >> >>
> > >> >> This posting is provided "AS IS" with no warranties, and confers
no
> > >> >> rights.
> > >> >>
> > >>
> > >>
> > >>
> >
> >
> >|||This is the statement that works:
SELECT PRODUCTION_DATE, TESTDATE, "524CLPRXD1_CONS",
"524ALPRXD1_BRITE","524ALPRXD1_PH", "524ALPRXD1_COND",
"524TLPRXD1_TEMP","524CLPRXD2_CONS", "524ALPRXD2_BRITE","524ALPRXD2_PH",
"524ALPRXD2_COND", "524TLPRXD2_TEMP",
"524CLPRXD3_CONS","524ALPRXD3_BRITE","524ALPRXD3_PH","524ALPRXD3_COND",
"524TLPRXD3_TEMP", "615CLKRAFT_CONS", "615ALKRAFT_TEMP","615ALKRAFT_BRITE",
"565ALPOTWR_CSF","565CLPOTWR_CONS", "565CLWW11_CONS", "524ALTMPSS_COND",
"565ALPRTWR_BRITE","524ALTMP11_CSF", "524CLTMP11_CONS","524ALTMP11_BRITE",
"524ALTMP11_PH", "524ALTMP11_COND", "524TLTMP11_TEMP","631ALTMP_CSF",
"631CLTMP_CONS", "631ALTMP_BRITE" FROM GP4_LAB WHERE (PRODUCTION_DATE = ?)
What I want to do is put in a couple of dynamic parameters. But I add the
=" at the beginning of the statement, and end the statement with ", the query
will not run, with the error "The expression for the query â'PRPQISâ' contains
an error: [BC30205] End of statement expected." If I change the double
quotes within the statement to single quotes, I get an Oracle error,
ORA-00936: Missing Expression. If I remove the quotes all together, I don't
get anything returned from my query.
"Bruce Loehle-Conger" wrote:
> Could you post the statement that you have in the Generic query designer?
> That might help generate some ideas on what is going on.
> Bruce L-C
> "dachrist" <dachrist@.discussions.microsoft.com> wrote in message
> news:0C34220B-C34E-4117-87A6-F9835BC80B98@.microsoft.com...
> > Yes, I have done that and confirmed that I am using the correct syntax. I
> > think the problem is that Oracle requires any field that starts with a
> number
> > to have a quote around it to designate it as a string. These quotes are
> what
> > is causing me problems in the Generic query designer, because I want to
> add
> > dynamic parameters to the query.
> >
> > "Bruce Loehle-Conger" wrote:
> >
> > > If you are building the SQL dynamically then I suggest you back up and
> make
> > > sure you are creating the appropriate syntax. Set the source of a
> textbox to
> > > the expression you are currently doing in the generic designer. Then you
> can
> > > copy and paste that into whatever Oracle calls their tool to execute ad
> hoc
> > > queries. Then you can quickly find out whether the syntax is correct.
> > > Consider that the generic designer essentially allows passthrough
> queries.
> > > You need the exact same syntax that you would have if you were going
> > > directly against Oracle.
> > >
> > > Bruce L-C
> > >
> > > "dachrist" <dachrist@.discussions.microsoft.com> wrote in message
> > > news:32DB3644-D789-45E9-A4F1-35173AA6A4D7@.microsoft.com...
> > > >I need to build the query in the generic designer, because I need to
> create
> > > > dynamic parameters. I don't believe I can do that in the graphical
> > > > designer?
> > > > The actual query works fine in the graphical designer.
> > > >
> > > > "Brian Welcker [MSFT]" wrote:
> > > >
> > > >> It should work fine. Unless you are building an expression, quotes
> should
> > > >> not matter. Have you tried it with the graphical one?
> > > >>
> > > >> --
> > > >> Brian Welcker
> > > >> Group Program Manager
> > > >> SQL Server Reporting Services
> > > >>
> > > >> This posting is provided "AS IS" with no warranties, and confers no
> > > >> rights.
> > > >>
> > > >> "dachrist" <dachrist@.discussions.microsoft.com> wrote in message
> > > >> news:F8F77C8B-FEC5-4D48-A272-2CC7AD4286B1@.microsoft.com...
> > > >> > Double quotes are required. It is an Oracle database, I am using
> the
> > > >> > OLE
> > > >> > DB
> > > >> > provider.
> > > >> >
> > > >> > "Donovan R. Smith [MS]" wrote:
> > > >> >
> > > >> >> Try using single quotes unless double quotes are required by your
> data
> > > >> >> provider.
> > > >> >>
> > > >> >> --
> > > >> >> Thanks.
> > > >> >>
> > > >> >> Donovan R. Smith
> > > >> >> Software Test Lead
> > > >> >>
> > > >> >> This posting is provided "AS IS" with no warranties, and confers
> no
> > > >> >> rights.
> > > >> >>
> > > >>
> > > >>
> > > >>
> > >
> > >
> > >
>
>|||It looks to me that you are coming up against the typical problem of
embedding double quotes in a string. As a coincidence today I have just been
dealing with creating a dynamic sql statement in a stored procedure which
had the same issue but with single quotes. As I mentioned before, the best
way to deal with making sure that your expression is correct is to back off
from trying to create the dataset and first make sure the correct string is
being created. You should just assign the expression to a text box (I create
a report with nothing on it but a textbox).
For instance in your case I started off with this expression for a textbox.
="SELECT PRODUCTION_DATE, TESTDATE, "524CLPRXD1_CONS","
i.e I did what you said you did, added an =" and finished off with a ".
Trying to go into preview it fails with an error. Which is what I expected
would happen. You can not put in double quotes in a string without following
the special string rule, do it twice.
I assigned this string to the textbox:
="SELECT PRODUCTION_DATE, TESTDATE, ""524CLPRXD1_CONS"", "
Notice that everywhere you want your resulting string to have double quotes
I put double quotes twice. In preview the textbox has this:
SELECT PRODUCTION_DATE, TESTDATE, "524CLPRXD1_CONS",
Just what you want. When testing this your textbox needs to end up with
exactly the string expected by Oracle. Once you have this then you can
assign the expression to the dataset and away you go.
Bruce L-C
"dachrist" <dachrist@.discussions.microsoft.com> wrote in message
news:9069F07D-C554-4172-BD89-E4760FF11316@.microsoft.com...
> This is the statement that works:
> SELECT PRODUCTION_DATE, TESTDATE, "524CLPRXD1_CONS",
> "524ALPRXD1_BRITE","524ALPRXD1_PH", "524ALPRXD1_COND",
> "524TLPRXD1_TEMP","524CLPRXD2_CONS", "524ALPRXD2_BRITE","524ALPRXD2_PH",
> "524ALPRXD2_COND", "524TLPRXD2_TEMP",
> "524CLPRXD3_CONS","524ALPRXD3_BRITE","524ALPRXD3_PH","524ALPRXD3_COND",
> "524TLPRXD3_TEMP", "615CLKRAFT_CONS",
"615ALKRAFT_TEMP","615ALKRAFT_BRITE",
> "565ALPOTWR_CSF","565CLPOTWR_CONS", "565CLWW11_CONS", "524ALTMPSS_COND",
> "565ALPRTWR_BRITE","524ALTMP11_CSF", "524CLTMP11_CONS","524ALTMP11_BRITE",
> "524ALTMP11_PH", "524ALTMP11_COND", "524TLTMP11_TEMP","631ALTMP_CSF",
> "631CLTMP_CONS", "631ALTMP_BRITE" FROM GP4_LAB WHERE (PRODUCTION_DATE = ?)
>
> What I want to do is put in a couple of dynamic parameters. But I add the
> =" at the beginning of the statement, and end the statement with ", the
query
> will not run, with the error "The expression for the query 'PRPQIS'
contains
> an error: [BC30205] End of statement expected." If I change the double
> quotes within the statement to single quotes, I get an Oracle error,
> ORA-00936: Missing Expression. If I remove the quotes all together, I
don't
> get anything returned from my query.
>
> "Bruce Loehle-Conger" wrote:
> > Could you post the statement that you have in the Generic query
designer?
> > That might help generate some ideas on what is going on.
> >
> > Bruce L-C
> >
> > "dachrist" <dachrist@.discussions.microsoft.com> wrote in message
> > news:0C34220B-C34E-4117-87A6-F9835BC80B98@.microsoft.com...
> > > Yes, I have done that and confirmed that I am using the correct
syntax. I
> > > think the problem is that Oracle requires any field that starts with a
> > number
> > > to have a quote around it to designate it as a string. These quotes
are
> > what
> > > is causing me problems in the Generic query designer, because I want
to
> > add
> > > dynamic parameters to the query.
> > >
> > > "Bruce Loehle-Conger" wrote:
> > >
> > > > If you are building the SQL dynamically then I suggest you back up
and
> > make
> > > > sure you are creating the appropriate syntax. Set the source of a
> > textbox to
> > > > the expression you are currently doing in the generic designer. Then
you
> > can
> > > > copy and paste that into whatever Oracle calls their tool to execute
ad
> > hoc
> > > > queries. Then you can quickly find out whether the syntax is
correct.
> > > > Consider that the generic designer essentially allows passthrough
> > queries.
> > > > You need the exact same syntax that you would have if you were going
> > > > directly against Oracle.
> > > >
> > > > Bruce L-C
> > > >
> > > > "dachrist" <dachrist@.discussions.microsoft.com> wrote in message
> > > > news:32DB3644-D789-45E9-A4F1-35173AA6A4D7@.microsoft.com...
> > > > >I need to build the query in the generic designer, because I need
to
> > create
> > > > > dynamic parameters. I don't believe I can do that in the
graphical
> > > > > designer?
> > > > > The actual query works fine in the graphical designer.
> > > > >
> > > > > "Brian Welcker [MSFT]" wrote:
> > > > >
> > > > >> It should work fine. Unless you are building an expression,
quotes
> > should
> > > > >> not matter. Have you tried it with the graphical one?
> > > > >>
> > > > >> --
> > > > >> Brian Welcker
> > > > >> Group Program Manager
> > > > >> SQL Server Reporting Services
> > > > >>
> > > > >> This posting is provided "AS IS" with no warranties, and confers
no
> > > > >> rights.
> > > > >>
> > > > >> "dachrist" <dachrist@.discussions.microsoft.com> wrote in message
> > > > >> news:F8F77C8B-FEC5-4D48-A272-2CC7AD4286B1@.microsoft.com...
> > > > >> > Double quotes are required. It is an Oracle database, I am
using
> > the
> > > > >> > OLE
> > > > >> > DB
> > > > >> > provider.
> > > > >> >
> > > > >> > "Donovan R. Smith [MS]" wrote:
> > > > >> >
> > > > >> >> Try using single quotes unless double quotes are required by
your
> > data
> > > > >> >> provider.
> > > > >> >>
> > > > >> >> --
> > > > >> >> Thanks.
> > > > >> >>
> > > > >> >> Donovan R. Smith
> > > > >> >> Software Test Lead
> > > > >> >>
> > > > >> >> This posting is provided "AS IS" with no warranties, and
confers
> > no
> > > > >> >> rights.
> > > > >> >>
> > > > >>
> > > > >>
> > > > >>
> > > >
> > > >
> > > >
> >
> >
> >|||Thanks Bruce! That did it.
"Bruce Loehle-Conger" wrote:
> It looks to me that you are coming up against the typical problem of
> embedding double quotes in a string. As a coincidence today I have just been
> dealing with creating a dynamic sql statement in a stored procedure which
> had the same issue but with single quotes. As I mentioned before, the best
> way to deal with making sure that your expression is correct is to back off
> from trying to create the dataset and first make sure the correct string is
> being created. You should just assign the expression to a text box (I create
> a report with nothing on it but a textbox).
> For instance in your case I started off with this expression for a textbox.
> ="SELECT PRODUCTION_DATE, TESTDATE, "524CLPRXD1_CONS","
> i.e I did what you said you did, added an =" and finished off with a ".
> Trying to go into preview it fails with an error. Which is what I expected
> would happen. You can not put in double quotes in a string without following
> the special string rule, do it twice.
> I assigned this string to the textbox:
> ="SELECT PRODUCTION_DATE, TESTDATE, ""524CLPRXD1_CONS"", "
> Notice that everywhere you want your resulting string to have double quotes
> I put double quotes twice. In preview the textbox has this:
> SELECT PRODUCTION_DATE, TESTDATE, "524CLPRXD1_CONS",
> Just what you want. When testing this your textbox needs to end up with
> exactly the string expected by Oracle. Once you have this then you can
> assign the expression to the dataset and away you go.
> Bruce L-C
>
> "dachrist" <dachrist@.discussions.microsoft.com> wrote in message
> news:9069F07D-C554-4172-BD89-E4760FF11316@.microsoft.com...
> > This is the statement that works:
> >
> > SELECT PRODUCTION_DATE, TESTDATE, "524CLPRXD1_CONS",
> > "524ALPRXD1_BRITE","524ALPRXD1_PH", "524ALPRXD1_COND",
> > "524TLPRXD1_TEMP","524CLPRXD2_CONS", "524ALPRXD2_BRITE","524ALPRXD2_PH",
> > "524ALPRXD2_COND", "524TLPRXD2_TEMP",
> > "524CLPRXD3_CONS","524ALPRXD3_BRITE","524ALPRXD3_PH","524ALPRXD3_COND",
> > "524TLPRXD3_TEMP", "615CLKRAFT_CONS",
> "615ALKRAFT_TEMP","615ALKRAFT_BRITE",
> > "565ALPOTWR_CSF","565CLPOTWR_CONS", "565CLWW11_CONS", "524ALTMPSS_COND",
> > "565ALPRTWR_BRITE","524ALTMP11_CSF", "524CLTMP11_CONS","524ALTMP11_BRITE",
> > "524ALTMP11_PH", "524ALTMP11_COND", "524TLTMP11_TEMP","631ALTMP_CSF",
> > "631CLTMP_CONS", "631ALTMP_BRITE" FROM GP4_LAB WHERE (PRODUCTION_DATE = ?)
> >
> >
> > What I want to do is put in a couple of dynamic parameters. But I add the
> > =" at the beginning of the statement, and end the statement with ", the
> query
> > will not run, with the error "The expression for the query 'PRPQIS'
> contains
> > an error: [BC30205] End of statement expected." If I change the double
> > quotes within the statement to single quotes, I get an Oracle error,
> > ORA-00936: Missing Expression. If I remove the quotes all together, I
> don't
> > get anything returned from my query.
> >
> >
> > "Bruce Loehle-Conger" wrote:
> >
> > > Could you post the statement that you have in the Generic query
> designer?
> > > That might help generate some ideas on what is going on.
> > >
> > > Bruce L-C
> > >
> > > "dachrist" <dachrist@.discussions.microsoft.com> wrote in message
> > > news:0C34220B-C34E-4117-87A6-F9835BC80B98@.microsoft.com...
> > > > Yes, I have done that and confirmed that I am using the correct
> syntax. I
> > > > think the problem is that Oracle requires any field that starts with a
> > > number
> > > > to have a quote around it to designate it as a string. These quotes
> are
> > > what
> > > > is causing me problems in the Generic query designer, because I want
> to
> > > add
> > > > dynamic parameters to the query.
> > > >
> > > > "Bruce Loehle-Conger" wrote:
> > > >
> > > > > If you are building the SQL dynamically then I suggest you back up
> and
> > > make
> > > > > sure you are creating the appropriate syntax. Set the source of a
> > > textbox to
> > > > > the expression you are currently doing in the generic designer. Then
> you
> > > can
> > > > > copy and paste that into whatever Oracle calls their tool to execute
> ad
> > > hoc
> > > > > queries. Then you can quickly find out whether the syntax is
> correct.
> > > > > Consider that the generic designer essentially allows passthrough
> > > queries.
> > > > > You need the exact same syntax that you would have if you were going
> > > > > directly against Oracle.
> > > > >
> > > > > Bruce L-C
> > > > >
> > > > > "dachrist" <dachrist@.discussions.microsoft.com> wrote in message
> > > > > news:32DB3644-D789-45E9-A4F1-35173AA6A4D7@.microsoft.com...
> > > > > >I need to build the query in the generic designer, because I need
> to
> > > create
> > > > > > dynamic parameters. I don't believe I can do that in the
> graphical
> > > > > > designer?
> > > > > > The actual query works fine in the graphical designer.
> > > > > >
> > > > > > "Brian Welcker [MSFT]" wrote:
> > > > > >
> > > > > >> It should work fine. Unless you are building an expression,
> quotes
> > > should
> > > > > >> not matter. Have you tried it with the graphical one?
> > > > > >>
> > > > > >> --
> > > > > >> Brian Welcker
> > > > > >> Group Program Manager
> > > > > >> SQL Server Reporting Services
> > > > > >>
> > > > > >> This posting is provided "AS IS" with no warranties, and confers
> no
> > > > > >> rights.
> > > > > >>
> > > > > >> "dachrist" <dachrist@.discussions.microsoft.com> wrote in message
> > > > > >> news:F8F77C8B-FEC5-4D48-A272-2CC7AD4286B1@.microsoft.com...
> > > > > >> > Double quotes are required. It is an Oracle database, I am
> using
> > > the
> > > > > >> > OLE
> > > > > >> > DB
> > > > > >> > provider.
> > > > > >> >
> > > > > >> > "Donovan R. Smith [MS]" wrote:
> > > > > >> >
> > > > > >> >> Try using single quotes unless double quotes are required by
> your
> > > data
> > > > > >> >> provider.
> > > > > >> >>
> > > > > >> >> --
> > > > > >> >> Thanks.
> > > > > >> >>
> > > > > >> >> Donovan R. Smith
> > > > > >> >> Software Test Lead
> > > > > >> >>
> > > > > >> >> This posting is provided "AS IS" with no warranties, and
> confers
> > > no
> > > > > >> >> rights.
> > > > > >> >>
> > > > > >>
> > > > > >>
> > > > > >>
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>

Generic Query Designer Disabled

Hi,

This is really a Visual Studio 2005 Report Designer problem but I didn't see a good place to post this question in the Visual Studion forum.

On some reports when looking at the Data tab the Generic Query Designer is disabled. Anybody know why? The report runs. Other reports have it enabled.

I am using Visual Studio 2005 sp1.

Thanks,

Darren

Just answered my own question.

It seems the Generic Query Designer is disabled when you are using an expression for a connection string.

Generation of estimated execution plan takes a long time

Hello,
I am puzzled by following problem. One of our tables has more than 12
billion rows. Usually, query plan and query itself is executed almost
immediately but occasionally, it take about 2 minutes to generate estimated
query execution plan for following query:
SELECT MAX(qwEventIx) FROM FTE_STREAM_EVENT (NOLOCK)
There is a PK clustered index defined on qwEventIx field. I do not observer
any blocking on this object when it takes a long time to generate estimated
query plan. There is a plan cached in syscacheobjects for this query.
I was wondering if anyone can explain why it takes such a long time to
generate estimated query plan on some occasions.
Thanks,
IgorIgor,
Are the statistics current and up-to-date?
HTH
Jerry
"imarchenko" <igormarchenko@.hotmail.com> wrote in message
news:uIwoFzZ2FHA.4076@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I am puzzled by following problem. One of our tables has more than 12
> billion rows. Usually, query plan and query itself is executed almost
> immediately but occasionally, it take about 2 minutes to generate
> estimated query execution plan for following query:
> SELECT MAX(qwEventIx) FROM FTE_STREAM_EVENT (NOLOCK)
> There is a PK clustered index defined on qwEventIx field. I do not
> observer any blocking on this object when it takes a long time to
> generate estimated query plan. There is a plan cached in syscacheobjects
> for this query.
> I was wondering if anyone can explain why it takes such a long time to
> generate estimated query plan on some occasions.
> Thanks,
> Igor
>|||It could be that this is when auto-update of statistics occurs. You can catc
h that in Profiler using
Object, Auto Stats.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"imarchenko" <igormarchenko@.hotmail.com> wrote in message
news:uIwoFzZ2FHA.4076@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I am puzzled by following problem. One of our tables has more than 12 bill
ion rows. Usually, query
> plan and query itself is executed almost immediately but occasionally, it
take about 2 minutes to
> generate estimated query execution plan for following query:
> SELECT MAX(qwEventIx) FROM FTE_STREAM_EVENT (NOLOCK)
> There is a PK clustered index defined on qwEventIx field. I do not observe
r any blocking on this
> object when it takes a long time to generate estimated query plan. There
is a plan cached in
> syscacheobjects for this query.
> I was wondering if anyone can explain why it takes such a long time to gen
erate estimated query
> plan on some occasions.
> Thanks,
> Igor
>|||Tibor,
You were absolutely correct! SQL Server was updating statistics on 12
billion rows table. It was taking over 2 minutes. I suppose it might make
sense to disable AUTOSTATS on this table with sp_autostats and update
statistics manually during off-peak hours. Would you recommend this
approach?
Thanks,
Igor
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%2311fWFg2FHA.956@.TK2MSFTNGP10.phx.gbl...
> It could be that this is when auto-update of statistics occurs. You can
> catch that in Profiler using Object, Auto Stats.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "imarchenko" <igormarchenko@.hotmail.com> wrote in message
> news:uIwoFzZ2FHA.4076@.TK2MSFTNGP09.phx.gbl...
>|||Jerry,
It is indeed statistics related.
Thanks,
Igor
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:uQmyqVa2FHA.2364@.TK2MSFTNGP12.phx.gbl...
> Igor,
> Are the statistics current and up-to-date?
> HTH
> Jerry
> "imarchenko" <igormarchenko@.hotmail.com> wrote in message
> news:uIwoFzZ2FHA.4076@.TK2MSFTNGP09.phx.gbl...
>|||Yes, with that long auto.stats time, disabling autostats for that table is r
easonable. Make sure you
schedule manual update statistics with a reasonable sample and at a good tim
e (like not before some
big batch, but after :-) ).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"imarchenko" <igormarchenko@.hotmail.com> wrote in message
news:e1KGDu%232FHA.3420@.TK2MSFTNGP15.phx.gbl...
> Tibor,
> You were absolutely correct! SQL Server was updating statistics on 12 b
illion rows table. It
> was taking over 2 minutes. I suppose it might make sense to disable AUTOST
ATS on this table with
> sp_autostats and update statistics manually during off-peak hours. Would y
ou recommend this
> approach?
> Thanks,
> Igor
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:%2311fWFg2FHA.956@.TK2MSFTNGP10.phx.gbl...
>

Generation of estimated execution plan takes a long time

Hello,
I am puzzled by following problem. One of our tables has more than 12
billion rows. Usually, query plan and query itself is executed almost
immediately but occasionally, it take about 2 minutes to generate estimated
query execution plan for following query:
SELECT MAX(qwEventIx) FROM FTE_STREAM_EVENT (NOLOCK)
There is a PK clustered index defined on qwEventIx field. I do not observer
any blocking on this object when it takes a long time to generate estimated
query plan. There is a plan cached in syscacheobjects for this query.
I was wondering if anyone can explain why it takes such a long time to
generate estimated query plan on some occasions.
Thanks,
Igor
Igor,
Are the statistics current and up-to-date?
HTH
Jerry
"imarchenko" <igormarchenko@.hotmail.com> wrote in message
news:uIwoFzZ2FHA.4076@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I am puzzled by following problem. One of our tables has more than 12
> billion rows. Usually, query plan and query itself is executed almost
> immediately but occasionally, it take about 2 minutes to generate
> estimated query execution plan for following query:
> SELECT MAX(qwEventIx) FROM FTE_STREAM_EVENT (NOLOCK)
> There is a PK clustered index defined on qwEventIx field. I do not
> observer any blocking on this object when it takes a long time to
> generate estimated query plan. There is a plan cached in syscacheobjects
> for this query.
> I was wondering if anyone can explain why it takes such a long time to
> generate estimated query plan on some occasions.
> Thanks,
> Igor
>
|||It could be that this is when auto-update of statistics occurs. You can catch that in Profiler using
Object, Auto Stats.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"imarchenko" <igormarchenko@.hotmail.com> wrote in message
news:uIwoFzZ2FHA.4076@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I am puzzled by following problem. One of our tables has more than 12 billion rows. Usually, query
> plan and query itself is executed almost immediately but occasionally, it take about 2 minutes to
> generate estimated query execution plan for following query:
> SELECT MAX(qwEventIx) FROM FTE_STREAM_EVENT (NOLOCK)
> There is a PK clustered index defined on qwEventIx field. I do not observer any blocking on this
> object when it takes a long time to generate estimated query plan. There is a plan cached in
> syscacheobjects for this query.
> I was wondering if anyone can explain why it takes such a long time to generate estimated query
> plan on some occasions.
> Thanks,
> Igor
>
|||Tibor,
You were absolutely correct! SQL Server was updating statistics on 12
billion rows table. It was taking over 2 minutes. I suppose it might make
sense to disable AUTOSTATS on this table with sp_autostats and update
statistics manually during off-peak hours. Would you recommend this
approach?
Thanks,
Igor
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%2311fWFg2FHA.956@.TK2MSFTNGP10.phx.gbl...
> It could be that this is when auto-update of statistics occurs. You can
> catch that in Profiler using Object, Auto Stats.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "imarchenko" <igormarchenko@.hotmail.com> wrote in message
> news:uIwoFzZ2FHA.4076@.TK2MSFTNGP09.phx.gbl...
>
|||Jerry,
It is indeed statistics related.
Thanks,
Igor
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:uQmyqVa2FHA.2364@.TK2MSFTNGP12.phx.gbl...
> Igor,
> Are the statistics current and up-to-date?
> HTH
> Jerry
> "imarchenko" <igormarchenko@.hotmail.com> wrote in message
> news:uIwoFzZ2FHA.4076@.TK2MSFTNGP09.phx.gbl...
>
|||Yes, with that long auto.stats time, disabling autostats for that table is reasonable. Make sure you
schedule manual update statistics with a reasonable sample and at a good time (like not before some
big batch, but after :-) ).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"imarchenko" <igormarchenko@.hotmail.com> wrote in message
news:e1KGDu%232FHA.3420@.TK2MSFTNGP15.phx.gbl...
> Tibor,
> You were absolutely correct! SQL Server was updating statistics on 12 billion rows table. It
> was taking over 2 minutes. I suppose it might make sense to disable AUTOSTATS on this table with
> sp_autostats and update statistics manually during off-peak hours. Would you recommend this
> approach?
> Thanks,
> Igor
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:%2311fWFg2FHA.956@.TK2MSFTNGP10.phx.gbl...
>

Generation of estimated execution plan takes a long time

Hello,
I am puzzled by following problem. One of our tables has more than 12
billion rows. Usually, query plan and query itself is executed almost
immediately but occasionally, it take about 2 minutes to generate estimated
query execution plan for following query:
SELECT MAX(qwEventIx) FROM FTE_STREAM_EVENT (NOLOCK)
There is a PK clustered index defined on qwEventIx field. I do not observer
any blocking on this object when it takes a long time to generate estimated
query plan. There is a plan cached in syscacheobjects for this query.
I was wondering if anyone can explain why it takes such a long time to
generate estimated query plan on some occasions.
Thanks,
IgorIgor,
Are the statistics current and up-to-date?
HTH
Jerry
"imarchenko" <igormarchenko@.hotmail.com> wrote in message
news:uIwoFzZ2FHA.4076@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I am puzzled by following problem. One of our tables has more than 12
> billion rows. Usually, query plan and query itself is executed almost
> immediately but occasionally, it take about 2 minutes to generate
> estimated query execution plan for following query:
> SELECT MAX(qwEventIx) FROM FTE_STREAM_EVENT (NOLOCK)
> There is a PK clustered index defined on qwEventIx field. I do not
> observer any blocking on this object when it takes a long time to
> generate estimated query plan. There is a plan cached in syscacheobjects
> for this query.
> I was wondering if anyone can explain why it takes such a long time to
> generate estimated query plan on some occasions.
> Thanks,
> Igor
>|||It could be that this is when auto-update of statistics occurs. You can catch that in Profiler using
Object, Auto Stats.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"imarchenko" <igormarchenko@.hotmail.com> wrote in message
news:uIwoFzZ2FHA.4076@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I am puzzled by following problem. One of our tables has more than 12 billion rows. Usually, query
> plan and query itself is executed almost immediately but occasionally, it take about 2 minutes to
> generate estimated query execution plan for following query:
> SELECT MAX(qwEventIx) FROM FTE_STREAM_EVENT (NOLOCK)
> There is a PK clustered index defined on qwEventIx field. I do not observer any blocking on this
> object when it takes a long time to generate estimated query plan. There is a plan cached in
> syscacheobjects for this query.
> I was wondering if anyone can explain why it takes such a long time to generate estimated query
> plan on some occasions.
> Thanks,
> Igor
>|||Tibor,
You were absolutely correct! SQL Server was updating statistics on 12
billion rows table. It was taking over 2 minutes. I suppose it might make
sense to disable AUTOSTATS on this table with sp_autostats and update
statistics manually during off-peak hours. Would you recommend this
approach?
Thanks,
Igor
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%2311fWFg2FHA.956@.TK2MSFTNGP10.phx.gbl...
> It could be that this is when auto-update of statistics occurs. You can
> catch that in Profiler using Object, Auto Stats.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "imarchenko" <igormarchenko@.hotmail.com> wrote in message
> news:uIwoFzZ2FHA.4076@.TK2MSFTNGP09.phx.gbl...
>> Hello,
>> I am puzzled by following problem. One of our tables has more than 12
>> billion rows. Usually, query plan and query itself is executed almost
>> immediately but occasionally, it take about 2 minutes to generate
>> estimated query execution plan for following query:
>> SELECT MAX(qwEventIx) FROM FTE_STREAM_EVENT (NOLOCK)
>> There is a PK clustered index defined on qwEventIx field. I do not
>> observer any blocking on this object when it takes a long time to
>> generate estimated query plan. There is a plan cached in syscacheobjects
>> for this query.
>> I was wondering if anyone can explain why it takes such a long time to
>> generate estimated query plan on some occasions.
>> Thanks,
>> Igor
>|||Jerry,
It is indeed statistics related.
Thanks,
Igor
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:uQmyqVa2FHA.2364@.TK2MSFTNGP12.phx.gbl...
> Igor,
> Are the statistics current and up-to-date?
> HTH
> Jerry
> "imarchenko" <igormarchenko@.hotmail.com> wrote in message
> news:uIwoFzZ2FHA.4076@.TK2MSFTNGP09.phx.gbl...
>> Hello,
>> I am puzzled by following problem. One of our tables has more than 12
>> billion rows. Usually, query plan and query itself is executed almost
>> immediately but occasionally, it take about 2 minutes to generate
>> estimated query execution plan for following query:
>> SELECT MAX(qwEventIx) FROM FTE_STREAM_EVENT (NOLOCK)
>> There is a PK clustered index defined on qwEventIx field. I do not
>> observer any blocking on this object when it takes a long time to
>> generate estimated query plan. There is a plan cached in syscacheobjects
>> for this query.
>> I was wondering if anyone can explain why it takes such a long time to
>> generate estimated query plan on some occasions.
>> Thanks,
>> Igor
>|||Yes, with that long auto.stats time, disabling autostats for that table is reasonable. Make sure you
schedule manual update statistics with a reasonable sample and at a good time (like not before some
big batch, but after :-) ).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"imarchenko" <igormarchenko@.hotmail.com> wrote in message
news:e1KGDu%232FHA.3420@.TK2MSFTNGP15.phx.gbl...
> Tibor,
> You were absolutely correct! SQL Server was updating statistics on 12 billion rows table. It
> was taking over 2 minutes. I suppose it might make sense to disable AUTOSTATS on this table with
> sp_autostats and update statistics manually during off-peak hours. Would you recommend this
> approach?
> Thanks,
> Igor
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:%2311fWFg2FHA.956@.TK2MSFTNGP10.phx.gbl...
>> It could be that this is when auto-update of statistics occurs. You can catch that in Profiler
>> using Object, Auto Stats.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "imarchenko" <igormarchenko@.hotmail.com> wrote in message
>> news:uIwoFzZ2FHA.4076@.TK2MSFTNGP09.phx.gbl...
>> Hello,
>> I am puzzled by following problem. One of our tables has more than 12 billion rows. Usually,
>> query plan and query itself is executed almost immediately but occasionally, it take about 2
>> minutes to generate estimated query execution plan for following query:
>> SELECT MAX(qwEventIx) FROM FTE_STREAM_EVENT (NOLOCK)
>> There is a PK clustered index defined on qwEventIx field. I do not observer any blocking on this
>> object when it takes a long time to generate estimated query plan. There is a plan cached in
>> syscacheobjects for this query.
>> I was wondering if anyone can explain why it takes such a long time to generate estimated query
>> plan on some occasions.
>> Thanks,
>> Igor
>>
>sql

generating auto sequential numbers

I am using sybase aSE12.5. I have a table with only one column with
datatype char(2). when i query this table to select all the records, i
should get these records in the ascending order and they should be numbered
, i.e, the o/p should look something like this
column_name
-- ---
1 AB
2 AC
3 AD
and so on.

I cannot add an extra column and i need this to be done in a single query.

--
Message posted via http://www.sqlmonster.comPraveen D'Souza via SQLMonster.com (forum@.nospam.SQLMonster.com) writes:
> I am using sybase aSE12.5. I have a table with only one column with
> datatype char(2). when i query this table to select all the records, i
> should get these records in the ascending order and they should be
> numbered , i.e, the o/p should look something like this
> column_name
> -- ---
> 1 AB
> 2 AC
> 3 AD
> and so on.
> I cannot add an extra column and i need this to be done in a single query.

If you are using Sybase, you should be posting to
comp.databases.sybase, or whatever that groups goes by at SQLMonster.

However, this solution should work on Sybase as well:

SELECT col, (SELECT COUNT(*) FROM tbl b WHERE b.col >= a.col)
FROM tbl a
ORDER BY col

For large amount of data this may not be very performant, though.
SQL 2005, currently in beta, offers a ROW_NUMBER() function to
handle this a little more effeciently. I don't know if Sybase offers
something similar.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||In a tiered architecture display is done in the front end and not the
database.

Generating an Index

Hi, I need some assistance with a query. To be honest, I'm not even
sure it can be done.
I'll try to keep the information limited to only what's relevant to
what I have and what I am trying to achieve with this query.
I have a table that contains around 100,000 records. For the sake of
this discussion, assume just two columns:
ID Data
1 000000000000
2 010000000000
3 011111111111
4 101100011101
5 110100011011
6 111100000000
7 111100000111
8 111100010111
9 111100011011
10 111111111111
The Data column contains only 1's and 0's.
The Data column is a text column, not numeric.
The Data column is actually 255 chars long. (I limited it above to 12
for this example only)
Duplicates on the Data column are allowed and do exist.
With 100,000 records, you would note that in the above example record
10 would actually be record 100,000.
My aim is to somehow sort the data (by creating a third column) so that
the records are in order of "string distance" or similar. In other
words, so that similar strings are located next to (or as close as
possible to) each other.
For example, taking the data above:
The "distance" between record 8 and record 9 is 2 (ie Only two
positions in the Data are not the same)
The "distance" between record 3 and record 4 is 6
However, the "distance" between record 3 and record 10 is only 1,
but sorted in the normal fashion, there would be some 99,990 records
between them.
I was hoping that as I add records to this table I could calculate a
number or a code or something to create a third column that could be
indexed. Accessing records using this index would give me the records
in order of "String Distance" or similar.
I have looked up functions such as "Levenshtein - Edit String
Distance", which is fine when I have to strings to compare. I can't
see it helping in generating an index though.
I hope I have been clear in my explanation.
I would really appreciate any comments or discussion that could help me
achieve this.
Thanks for your time,
MJPlease post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.
Based on your narrative, did you mean to post something like this?
CREATE TABLE Foobar
(foo_id INTEGER NOT NULL PRIMARY KEY,
vector CHAR(255) NOT NULL);
INSERT INTO Foobar VALUES(1, '000000000000');
INSERT INTO Foobar VALUES(2, '010000000000')
INSERT INTO Foobar VALUES(3, '011111111111');
INSERT INTO Foobar VALUES(4, '101100011101');
INSERT INTO Foobar VALUES(5, '110100011011');
INSERT INTO Foobar VALUES(6, '111100000000');
INSERT INTO Foobar VALUES(7, '111100000111');
INSERT INTO Foobar VALUES(8, '111100010111');
INSERT INTO Foobar VALUES(9, '111100011011');
INSERT INTO Foobar VALUES(10, '111111111111');
Look up the difference in rows and records, and what a scalar value is.
These strings look like (uuggh!) binary vectors, not scalar values as
required by First Normal Form.
There is no way to use string distance as a sorting order because it is
a **binary** relationship.
Look up the use of the Sequence Auxiliary table.
CREATE PROCEDURE StringDistance (@.foo_1 INTEGER, @.foo_2 INTEGER)
AS
SELECT SUM (CASE
WHEN SUBSTRING (F1.vector, S.seq, 1)
<> SUBSTRING (F2.vector, S.seq, 1)
THEN 1 ELSE 0 END) AS distance
FROM Foobar AS F1, Foobar AS F2, Sequence AS S
WHERE F1.foo_id = @.foo_1
AND F2.foo_id = @.foo_2
AND S.seq <= 255 -- or whatever
GROUP BY F1.foo_id, F2.foo_id;|||Hi Mary, I created an example that may help you to solve the problem.
Instead of calculating the difference between two binary
representations, i used products table in Northwind database to gererate a
new computed column that represents the price difference between two
consecutives products. In your case, you can replace this basic function to
any that you wish. Here's the script:
USE Northwind
GO
-- Creates a new table just like products
SELECT *
INTO ProductsComputed
FROM Products
GO
-- Creates a function that calculates the difference between the current
record and the one just before (it selects all the products lower than the
actual key and pick -- the first one, in the inverse order).
-- ISNULL is needed due to productId 1
CREATE FUNCTION fnMoneyDifference (@.ProductId INT)
RETURNS MONEY
AS
BEGIN
DECLARE @.Value Money
SELECT @.Value = P.UnitPrice -
ISNULL ((SELECT TOP 1 P2.UnitPRice
FROM ProductsComputed AS P2
WHERE P2.ProductID < P.ProductID
ORDER BY ProductID DESC), 0)
FROM ProductsComputed AS P
WHERE P.ProductID = @.ProductID
RETURN @.Value
END
GO
-- Add a computed columns to the table, using the function and passing the
product key
ALTER TABLE ProductsComputed
ADD ComputedColumn AS dbo.fnMoneyDifference(ProductID)
GO
-- Verify the result
SELECT * FROM ProductsComputed
GO
Using computed column you guarantee that the difference will always be
updated, but be careful, this can consume many resources when a query is
issued against a big table. If the data almost doesn't change, it's better
to create a real column and populate the values instead of using a computed
column.
I hope it helps.
[]s
Luciano Caixeta Moreira
Brazil
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1126146910.541347.236630@.g14g2000cwa.googlegroups.com...
> Please post DDL, so that people do not have to guess what the keys,
> constraints, Declarative Referential Integrity, data types, etc. in
> your schema are. Sample data is also a good idea, along with clear
> specifications. It is very hard to debug code when you do not let us
> see it.
> Based on your narrative, did you mean to post something like this?
> CREATE TABLE Foobar
> (foo_id INTEGER NOT NULL PRIMARY KEY,
> vector CHAR(255) NOT NULL);
> INSERT INTO Foobar VALUES(1, '000000000000');
> INSERT INTO Foobar VALUES(2, '010000000000')
> INSERT INTO Foobar VALUES(3, '011111111111');
> INSERT INTO Foobar VALUES(4, '101100011101');
> INSERT INTO Foobar VALUES(5, '110100011011');
> INSERT INTO Foobar VALUES(6, '111100000000');
> INSERT INTO Foobar VALUES(7, '111100000111');
> INSERT INTO Foobar VALUES(8, '111100010111');
> INSERT INTO Foobar VALUES(9, '111100011011');
> INSERT INTO Foobar VALUES(10, '111111111111');
>
> Look up the difference in rows and records, and what a scalar value is.
> These strings look like (uuggh!) binary vectors, not scalar values as
> required by First Normal Form.
> There is no way to use string distance as a sorting order because it is
> a **binary** relationship.
> Look up the use of the Sequence Auxiliary table.
> CREATE PROCEDURE StringDistance (@.foo_1 INTEGER, @.foo_2 INTEGER)
> AS
> SELECT SUM (CASE
> WHEN SUBSTRING (F1.vector, S.seq, 1)
> <> SUBSTRING (F2.vector, S.seq, 1)
> THEN 1 ELSE 0 END) AS distance
> FROM Foobar AS F1, Foobar AS F2, Sequence AS S
> WHERE F1.foo_id = @.foo_1
> AND F2.foo_id = @.foo_2
> AND S.seq <= 255 -- or whatever
> GROUP BY F1.foo_id, F2.foo_id;
>|||Hi Luciano,
Thank you very much for your response. I really appreciate you helping
me out with my problem.
I'll see what I can do to convert your code to my needs.
Thanks again,
MJ|||let's say you ahve data like this
ID Data
1 000000000000
2 100000000000
3 010000000000
4 001000000000
5 000100000000
rows 2-5 are all only 1 digit different foprm row 1. Hiow would you
sort them?|||Ok Mary. If you need any further assistance, just send a new message.
Good luck.
Luciano
"Mary" <maryjones11289@.hotmail.com> wrote in message
news:1126209734.166547.191330@.z14g2000cwz.googlegroups.com...
> Hi Luciano,
> Thank you very much for your response. I really appreciate you helping
> me out with my problem.
> I'll see what I can do to convert your code to my needs.
> Thanks again,
> MJ
>|||That's exactly my question.
I'm not looking to sort on the "data" column...I was hoping to add a
third column which contains some sort of "weighting factor" or
something similar, so that when I sort the data on this column I end up
with records with the smallest "distance" between each other "close" to
each other.
You are correct with your sample data that all of them have a distance
of 1 from each other and I would expect that when sorted by a weight
factor they will reside "close" to each other, but consider:
ID Data
1 00000000000
.
.
.
55 011111111111
.
.
.
100,000 111111111111
Record 55 and record 100000 have a distance of "1", yet are over 90,000
records apart when sorted on the "data" column.
If I could weight this column and sort by that weight, I am hoping to
get record 55 "as close as possible" to record 100,000.
Even if I have to read 50-100 records to get it would be much better
than having to read over 90,000
MJ|||On 7 Sep 2005 18:55:45 -0700, Mary wrote:

>Hi, I need some assistance with a query. To be honest, I'm not even
>sure it can be done.
Hi Mary,
I'm pretty sure it can't be done. Any ordering must be defined on the
contents of the row, not on the difference between a row and the
"previous" or "next" one, as you can only determine the "previous" and
"next" after applying the ordering.
Simplifying your example even further, to length 2:
ID Data
1 00
2 01
3 10
4 11
Now, which row should be first? There is no answer - because for each
row, there are two rows with a difference in one position, and one row
with a difference in two positions.
An ordering that WOULD bne possible is an ordering on the number of
position changed from a chosen "first" value. Let's say we arbitrarily
choose "01" to be the first value in the sample above. That would make
row #2 the first (no difference). The second and third rows would be #1
and #4 (or #4 and #1 - that's arbitrary again), that both differ in one
position from "01", and row 3 ("10" - both position changed) goes last.
But that is probably not the ordering you want, because the second and
third row now differ in two positions, and the third anf fourth only on
one!
I hope this example has managed to cinvince you that the ordering you
require is indeed impossible - not as a result of a limitation in SQL
Server, but because the requirement itself is insuffucient to define
"the" correct order.
The next step, of course, is to find out why you (think that you) need
the data to be ordered in this way, and to consider alternate ways of
achieveing the same goal. In short: what is the actual business
requirement that you're trying to solve?
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)