I want to write a dynamic Query to select a Column called sFirstName from
different table based on Name column which exists in all the table
The table name which I get from My process is with spaces So I need to
replace " "(Space) with ("_") underscore. I know this can be done using
execute sp_executesql @.sql... but Please advice if this can be done with a
Sql Statement
Declare @.sTemp_Name varchar(100)
Set @.sTemp_Name = replace('Complaint table' ,' ','_')
Select sFirstName from dbo. + @.sTemp_Name + where Name = 'Samay'
The above Code gives me an Error as " Incorrect syntax near '+'. "
Please advice
Thanks
kritiHi,
The only solution for this is using Dynamic SQL using either sp_executesql
or EXEC
Thanks
Hari
SQL Server MVP
"KritiVerma@.hotmail.com" <KritiVermahotmailcom@.discussions.microsoft.com>
wrote in message news:41FF6DE6-DE4F-4C32-B959-3D268626E46F@.microsoft.com...
>I want to write a dynamic Query to select a Column called sFirstName from
> different table based on Name column which exists in all the table
> The table name which I get from My process is with spaces So I need to
> replace " "(Space) with ("_") underscore. I know this can be done using
> execute sp_executesql @.sql... but Please advice if this can be done with a
> Sql Statement
> Declare @.sTemp_Name varchar(100)
> Set @.sTemp_Name = replace('Complaint table' ,' ','_')
> Select sFirstName from dbo. + @.sTemp_Name + where Name = 'Samay'
> The above Code gives me an Error as " Incorrect syntax near '+'. "
> Please advice
> Thanks
> kritisql
Showing posts with label write. Show all posts
Showing posts with label write. Show all posts
Thursday, March 29, 2012
Dynamic selecting a table
I want to write a dynamic Query to select a Column called sFirstName from
different table based on Name column which exists in all the table
The table name which I get from My process is with spaces So I need to
replace " "(Space) with ("_") underscore. I know this can be done using
execute sp_executesql @.sql... but Please advice if this can be done with a
Sql Statement
Declare @.sTemp_Name varchar(100)
Set @.sTemp_Name = replace('Complaint table' ,' ','_')
Select sFirstName from dbo. + @.sTemp_Name + where Name = 'Samay'
The above Code gives me an Error as " Incorrect syntax near '+'. "
Please advice
Thanks
kriti
Hi,
The only solution for this is using Dynamic SQL using either sp_executesql
or EXEC
Thanks
Hari
SQL Server MVP
"KritiVerma@.hotmail.com" <KritiVermahotmailcom@.discussions.microsoft.com>
wrote in message news:41FF6DE6-DE4F-4C32-B959-3D268626E46F@.microsoft.com...
>I want to write a dynamic Query to select a Column called sFirstName from
> different table based on Name column which exists in all the table
> The table name which I get from My process is with spaces So I need to
> replace " "(Space) with ("_") underscore. I know this can be done using
> execute sp_executesql @.sql... but Please advice if this can be done with a
> Sql Statement
> Declare @.sTemp_Name varchar(100)
> Set @.sTemp_Name = replace('Complaint table' ,' ','_')
> Select sFirstName from dbo. + @.sTemp_Name + where Name = 'Samay'
> The above Code gives me an Error as " Incorrect syntax near '+'. "
> Please advice
> Thanks
> kriti
different table based on Name column which exists in all the table
The table name which I get from My process is with spaces So I need to
replace " "(Space) with ("_") underscore. I know this can be done using
execute sp_executesql @.sql... but Please advice if this can be done with a
Sql Statement
Declare @.sTemp_Name varchar(100)
Set @.sTemp_Name = replace('Complaint table' ,' ','_')
Select sFirstName from dbo. + @.sTemp_Name + where Name = 'Samay'
The above Code gives me an Error as " Incorrect syntax near '+'. "
Please advice
Thanks
kriti
Hi,
The only solution for this is using Dynamic SQL using either sp_executesql
or EXEC
Thanks
Hari
SQL Server MVP
"KritiVerma@.hotmail.com" <KritiVermahotmailcom@.discussions.microsoft.com>
wrote in message news:41FF6DE6-DE4F-4C32-B959-3D268626E46F@.microsoft.com...
>I want to write a dynamic Query to select a Column called sFirstName from
> different table based on Name column which exists in all the table
> The table name which I get from My process is with spaces So I need to
> replace " "(Space) with ("_") underscore. I know this can be done using
> execute sp_executesql @.sql... but Please advice if this can be done with a
> Sql Statement
> Declare @.sTemp_Name varchar(100)
> Set @.sTemp_Name = replace('Complaint table' ,' ','_')
> Select sFirstName from dbo. + @.sTemp_Name + where Name = 'Samay'
> The above Code gives me an Error as " Incorrect syntax near '+'. "
> Please advice
> Thanks
> kriti
Dynamic selecting a table
I want to write a dynamic Query to select a Column called sFirstName from
different table based on Name column which exists in all the table
The table name which I get from My process is with spaces So I need to
replace " "(Space) with ("_") underscore. I know this can be done using
execute sp_executesql @.sql... but Please advice if this can be done with a
Sql Statement
Declare @.sTemp_Name varchar(100)
Set @.sTemp_Name = replace('Complaint table' ,' ','_')
Select sFirstName from dbo. + @.sTemp_Name + where Name = 'Samay'
The above Code gives me an Error as " Incorrect syntax near '+'. "
Please advice
Thanks
kritiHi,
The only solution for this is using Dynamic SQL using either sp_executesql
or EXEC
Thanks
Hari
SQL Server MVP
"KritiVerma@.hotmail.com" <KritiVermahotmailcom@.discussions.microsoft.com>
wrote in message news:41FF6DE6-DE4F-4C32-B959-3D268626E46F@.microsoft.com...
>I want to write a dynamic Query to select a Column called sFirstName from
> different table based on Name column which exists in all the table
> The table name which I get from My process is with spaces So I need to
> replace " "(Space) with ("_") underscore. I know this can be done using
> execute sp_executesql @.sql... but Please advice if this can be done with a
> Sql Statement
> Declare @.sTemp_Name varchar(100)
> Set @.sTemp_Name = replace('Complaint table' ,' ','_')
> Select sFirstName from dbo. + @.sTemp_Name + where Name = 'Samay'
> The above Code gives me an Error as " Incorrect syntax near '+'. "
> Please advice
> Thanks
> kriti
different table based on Name column which exists in all the table
The table name which I get from My process is with spaces So I need to
replace " "(Space) with ("_") underscore. I know this can be done using
execute sp_executesql @.sql... but Please advice if this can be done with a
Sql Statement
Declare @.sTemp_Name varchar(100)
Set @.sTemp_Name = replace('Complaint table' ,' ','_')
Select sFirstName from dbo. + @.sTemp_Name + where Name = 'Samay'
The above Code gives me an Error as " Incorrect syntax near '+'. "
Please advice
Thanks
kritiHi,
The only solution for this is using Dynamic SQL using either sp_executesql
or EXEC
Thanks
Hari
SQL Server MVP
"KritiVerma@.hotmail.com" <KritiVermahotmailcom@.discussions.microsoft.com>
wrote in message news:41FF6DE6-DE4F-4C32-B959-3D268626E46F@.microsoft.com...
>I want to write a dynamic Query to select a Column called sFirstName from
> different table based on Name column which exists in all the table
> The table name which I get from My process is with spaces So I need to
> replace " "(Space) with ("_") underscore. I know this can be done using
> execute sp_executesql @.sql... but Please advice if this can be done with a
> Sql Statement
> Declare @.sTemp_Name varchar(100)
> Set @.sTemp_Name = replace('Complaint table' ,' ','_')
> Select sFirstName from dbo. + @.sTemp_Name + where Name = 'Samay'
> The above Code gives me an Error as " Incorrect syntax near '+'. "
> Please advice
> Thanks
> kriti
Dynamic script
Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.programming:546732
I want to write a script for a table 'A'. I
1) want to drop all the foreign key constraints which are created in another
table(s) and in which table 'A' is being used. I dont know those foregin key
constraints while executing the script.
2) want to drop all foreign key constraints which are created in table 'A'.
I dont know those foregin key constraints while executing the script.
Is it possible?
ThanksThere is no builtin tool/script. However, you could use this as a starting
point to construct your 'drop constraint' statements.
if object_id('usp_findreferences','p') is not null
drop proc usp_findreferences
go
create procedure usp_findreferences
/ ****************************************
***********************************
*/
/* Purpose: A quick & dirty way to find ref. objects for a[ll] table[s] */
/* Author: OJ Ngo */
/* Date: 02/28/2002 */
/ ****************************************
***********************************
*/
@.tbname sysname=null
as
set nocount on
Print 'Referenced:'
select c1.table_name,
c1.column_name,
fkey=r.constraint_name,
referenced_parent_table=c2.table_name,
c2.column_name
from information_schema.constraint_column_usage c1 join
information_schema.referential_constraints r on
c1.constraint_name=r.constraint_name
join information_schema.constraint_column_usage c2 on
r.unique_constraint_name=c2.constraint_name
where c1.table_name=coalesce(@.tbname,c1.table_name)
order by case when @.tbname is null then c1.table_name else c2.table_name end
print ''
print 'Referencing:'
select c1.table_name,
c1.column_name,
fkey=r.constraint_name,
referencing_child_table=c2.table_name,
c2.column_name
from information_schema.constraint_column_usage c1 join
information_schema.referential_constraints r on
c1.constraint_name=r.unique_constraint_name
join information_schema.constraint_column_usage c2 on
r.constraint_name=c2.constraint_name
where c1.table_name=coalesce(@.tbname,c1.table_name)
order by case when @.tbname is null then c1.table_name else c2.table_name end
go
exec usp_findreferences
-oj
"Rizwan" <hussains@.pendylum.com> wrote in message
news:LYuMe.7394$7R.474488@.news20.bellglobal.com...
>I want to write a script for a table 'A'. I
> 1) want to drop all the foreign key constraints which are created in
> another table(s) and in which table 'A' is being used. I dont know those
> foregin key constraints while executing the script.
> 2) want to drop all foreign key constraints which are created in table
> 'A'. I dont know those foregin key constraints while executing the script.
> Is it possible?
> Thanks
>
I want to write a script for a table 'A'. I
1) want to drop all the foreign key constraints which are created in another
table(s) and in which table 'A' is being used. I dont know those foregin key
constraints while executing the script.
2) want to drop all foreign key constraints which are created in table 'A'.
I dont know those foregin key constraints while executing the script.
Is it possible?
ThanksThere is no builtin tool/script. However, you could use this as a starting
point to construct your 'drop constraint' statements.
if object_id('usp_findreferences','p') is not null
drop proc usp_findreferences
go
create procedure usp_findreferences
/ ****************************************
***********************************
*/
/* Purpose: A quick & dirty way to find ref. objects for a[ll] table[s] */
/* Author: OJ Ngo */
/* Date: 02/28/2002 */
/ ****************************************
***********************************
*/
@.tbname sysname=null
as
set nocount on
Print 'Referenced:'
select c1.table_name,
c1.column_name,
fkey=r.constraint_name,
referenced_parent_table=c2.table_name,
c2.column_name
from information_schema.constraint_column_usage c1 join
information_schema.referential_constraints r on
c1.constraint_name=r.constraint_name
join information_schema.constraint_column_usage c2 on
r.unique_constraint_name=c2.constraint_name
where c1.table_name=coalesce(@.tbname,c1.table_name)
order by case when @.tbname is null then c1.table_name else c2.table_name end
print ''
print 'Referencing:'
select c1.table_name,
c1.column_name,
fkey=r.constraint_name,
referencing_child_table=c2.table_name,
c2.column_name
from information_schema.constraint_column_usage c1 join
information_schema.referential_constraints r on
c1.constraint_name=r.unique_constraint_name
join information_schema.constraint_column_usage c2 on
r.constraint_name=c2.constraint_name
where c1.table_name=coalesce(@.tbname,c1.table_name)
order by case when @.tbname is null then c1.table_name else c2.table_name end
go
exec usp_findreferences
-oj
"Rizwan" <hussains@.pendylum.com> wrote in message
news:LYuMe.7394$7R.474488@.news20.bellglobal.com...
>I want to write a script for a table 'A'. I
> 1) want to drop all the foreign key constraints which are created in
> another table(s) and in which table 'A' is being used. I dont know those
> foregin key constraints while executing the script.
> 2) want to drop all foreign key constraints which are created in table
> 'A'. I dont know those foregin key constraints while executing the script.
> Is it possible?
> Thanks
>
Tuesday, March 27, 2012
Dynamic query to store record count in variable??
Hello everyone,
I am attempting to write a stored procedure to retrieve the record count but
have failed so far. Here's what I have tried...
SET @.value = 'SELECT COUNT(*) FROM ' + @.tablea
EXECUTE sp_executesql @.sql
...but this just ends up displaying the count to the screen, so I did...
SET @.value = 'DECLARE @.tablea_count NVARCHAR(128) SELECT
@.tablea_count=COUNT(*) FROM ' + @.tablea
EXECUTE sp_executesql @.sql, @.tablea_count OUTPUT
but this doesnt seem to work either.
Help?
--
ChrisHave a look here:
http://www.sommarskog.se/dynamic_sql.html
http://www.support.microsoft.com/?id=262499
Andrew J. Kelly SQL MVP
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:292BA190-F8FD-4696-A6EA-8EE930095F5A@.microsoft.com...
> Hello everyone,
> I am attempting to write a stored procedure to retrieve the record count
> but
> have failed so far. Here's what I have tried...
> SET @.value = 'SELECT COUNT(*) FROM ' + @.tablea
> EXECUTE sp_executesql @.sql
> ...but this just ends up displaying the count to the screen, so I did...
> SET @.value = 'DECLARE @.tablea_count NVARCHAR(128) SELECT
> @.tablea_count=COUNT(*) FROM ' + @.tablea
> EXECUTE sp_executesql @.sql, @.tablea_count OUTPUT
> but this doesnt seem to work either.
> Help?
> --
> Chris
I am attempting to write a stored procedure to retrieve the record count but
have failed so far. Here's what I have tried...
SET @.value = 'SELECT COUNT(*) FROM ' + @.tablea
EXECUTE sp_executesql @.sql
...but this just ends up displaying the count to the screen, so I did...
SET @.value = 'DECLARE @.tablea_count NVARCHAR(128) SELECT
@.tablea_count=COUNT(*) FROM ' + @.tablea
EXECUTE sp_executesql @.sql, @.tablea_count OUTPUT
but this doesnt seem to work either.
Help?
--
ChrisHave a look here:
http://www.sommarskog.se/dynamic_sql.html
http://www.support.microsoft.com/?id=262499
Andrew J. Kelly SQL MVP
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:292BA190-F8FD-4696-A6EA-8EE930095F5A@.microsoft.com...
> Hello everyone,
> I am attempting to write a stored procedure to retrieve the record count
> but
> have failed so far. Here's what I have tried...
> SET @.value = 'SELECT COUNT(*) FROM ' + @.tablea
> EXECUTE sp_executesql @.sql
> ...but this just ends up displaying the count to the screen, so I did...
> SET @.value = 'DECLARE @.tablea_count NVARCHAR(128) SELECT
> @.tablea_count=COUNT(*) FROM ' + @.tablea
> EXECUTE sp_executesql @.sql, @.tablea_count OUTPUT
> but this doesnt seem to work either.
> Help?
> --
> Chris
Dynamic query to store record count in variable??
Hello everyone,
I am attempting to write a stored procedure to retrieve the record count but
have failed so far. Here's what I have tried...
SET @.value = 'SELECT COUNT(*) FROM ' + @.tablea
EXECUTE sp_executesql @.sql
...but this just ends up displaying the count to the screen, so I did...
SET @.value = 'DECLARE @.tablea_count NVARCHAR(128) SELECT
@.tablea_count=COUNT(*) FROM ' + @.tablea
EXECUTE sp_executesql @.sql, @.tablea_count OUTPUT
but this doesnt seem to work either.
Help?
Chris
Have a look here:
http://www.sommarskog.se/dynamic_sql.html
http://www.support.microsoft.com/?id=262499
Andrew J. Kelly SQL MVP
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:292BA190-F8FD-4696-A6EA-8EE930095F5A@.microsoft.com...
> Hello everyone,
> I am attempting to write a stored procedure to retrieve the record count
> but
> have failed so far. Here's what I have tried...
> SET @.value = 'SELECT COUNT(*) FROM ' + @.tablea
> EXECUTE sp_executesql @.sql
> ...but this just ends up displaying the count to the screen, so I did...
> SET @.value = 'DECLARE @.tablea_count NVARCHAR(128) SELECT
> @.tablea_count=COUNT(*) FROM ' + @.tablea
> EXECUTE sp_executesql @.sql, @.tablea_count OUTPUT
> but this doesnt seem to work either.
> Help?
> --
> Chris
sql
I am attempting to write a stored procedure to retrieve the record count but
have failed so far. Here's what I have tried...
SET @.value = 'SELECT COUNT(*) FROM ' + @.tablea
EXECUTE sp_executesql @.sql
...but this just ends up displaying the count to the screen, so I did...
SET @.value = 'DECLARE @.tablea_count NVARCHAR(128) SELECT
@.tablea_count=COUNT(*) FROM ' + @.tablea
EXECUTE sp_executesql @.sql, @.tablea_count OUTPUT
but this doesnt seem to work either.
Help?
Chris
Have a look here:
http://www.sommarskog.se/dynamic_sql.html
http://www.support.microsoft.com/?id=262499
Andrew J. Kelly SQL MVP
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:292BA190-F8FD-4696-A6EA-8EE930095F5A@.microsoft.com...
> Hello everyone,
> I am attempting to write a stored procedure to retrieve the record count
> but
> have failed so far. Here's what I have tried...
> SET @.value = 'SELECT COUNT(*) FROM ' + @.tablea
> EXECUTE sp_executesql @.sql
> ...but this just ends up displaying the count to the screen, so I did...
> SET @.value = 'DECLARE @.tablea_count NVARCHAR(128) SELECT
> @.tablea_count=COUNT(*) FROM ' + @.tablea
> EXECUTE sp_executesql @.sql, @.tablea_count OUTPUT
> but this doesnt seem to work either.
> Help?
> --
> Chris
sql
Dynamic query to store record count in variable??
Hello everyone,
I am attempting to write a stored procedure to retrieve the record count but
have failed so far. Here's what I have tried...
SET @.value = 'SELECT COUNT(*) FROM ' + @.tablea
EXECUTE sp_executesql @.sql
...but this just ends up displaying the count to the screen, so I did...
SET @.value = 'DECLARE @.tablea_count NVARCHAR(128) SELECT
@.tablea_count=COUNT(*) FROM ' + @.tablea
EXECUTE sp_executesql @.sql, @.tablea_count OUTPUT
but this doesnt seem to work either.
Help?
--
ChrisHave a look here:
http://www.sommarskog.se/dynamic_sql.html
http://www.support.microsoft.com/?id=262499
--
Andrew J. Kelly SQL MVP
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:292BA190-F8FD-4696-A6EA-8EE930095F5A@.microsoft.com...
> Hello everyone,
> I am attempting to write a stored procedure to retrieve the record count
> but
> have failed so far. Here's what I have tried...
> SET @.value = 'SELECT COUNT(*) FROM ' + @.tablea
> EXECUTE sp_executesql @.sql
> ...but this just ends up displaying the count to the screen, so I did...
> SET @.value = 'DECLARE @.tablea_count NVARCHAR(128) SELECT
> @.tablea_count=COUNT(*) FROM ' + @.tablea
> EXECUTE sp_executesql @.sql, @.tablea_count OUTPUT
> but this doesnt seem to work either.
> Help?
> --
> Chris
I am attempting to write a stored procedure to retrieve the record count but
have failed so far. Here's what I have tried...
SET @.value = 'SELECT COUNT(*) FROM ' + @.tablea
EXECUTE sp_executesql @.sql
...but this just ends up displaying the count to the screen, so I did...
SET @.value = 'DECLARE @.tablea_count NVARCHAR(128) SELECT
@.tablea_count=COUNT(*) FROM ' + @.tablea
EXECUTE sp_executesql @.sql, @.tablea_count OUTPUT
but this doesnt seem to work either.
Help?
--
ChrisHave a look here:
http://www.sommarskog.se/dynamic_sql.html
http://www.support.microsoft.com/?id=262499
--
Andrew J. Kelly SQL MVP
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:292BA190-F8FD-4696-A6EA-8EE930095F5A@.microsoft.com...
> Hello everyone,
> I am attempting to write a stored procedure to retrieve the record count
> but
> have failed so far. Here's what I have tried...
> SET @.value = 'SELECT COUNT(*) FROM ' + @.tablea
> EXECUTE sp_executesql @.sql
> ...but this just ends up displaying the count to the screen, so I did...
> SET @.value = 'DECLARE @.tablea_count NVARCHAR(128) SELECT
> @.tablea_count=COUNT(*) FROM ' + @.tablea
> EXECUTE sp_executesql @.sql, @.tablea_count OUTPUT
> but this doesnt seem to work either.
> Help?
> --
> Chris
Dynamic query in a function
Greetings:
I am trying to write a view that includes a column derived from a function
call.
The function is written thus:
CREATE FUNCTION dbo.fnc_CapArrayValue
(@.MEASUREMENTID INT,@.TRACKINGID BIGINT,@.JET INT)
RETURNS REAL
AS
BEGIN
Declare @.Result REAL
SET @.RESULT = (select @.JET from dbo.vw_CapArrayData where (measurement_id =
@.measurementid) and (tracking_id = @.trackingid))
RETURN @.Result
END
GO
What am I doing wrong here? When I run the view this function resides in, I
get a timeout error.Does the same query executed against the view return results in a resonable
time?
If not then posting the view definition and the definitions and indexes of
the underlying tables would give a few clues.
Is this an extract of somethimg much more complicated - if not I cannot see
much reason for using the function - unless you need it in a constraint or
similar.
Re-reading your post - is the function being used in a view definition, and
itself retriving data from a view (assuming vw_CapArrayData is a view?)
BTW you may get a deluge saying prefixing vw on the front of view names is a
bad idea, which I happen to think it is - be warned.
I suspect that the combination of a view based on a function, based on a
view has caused the optimiser to get a tad
and produce a poor
execution plan.
Mike John
"Thomas Mick" <nospam@.nospam.com> wrote in message
news:y2ikg.3677$Oh1.923@.news01.roc.ny...
> Greetings:
> I am trying to write a view that includes a column derived from a function
> call.
> The function is written thus:
> CREATE FUNCTION dbo.fnc_CapArrayValue
> (@.MEASUREMENTID INT,@.TRACKINGID BIGINT,@.JET INT)
> RETURNS REAL
> AS
> BEGIN
> Declare @.Result REAL
> SET @.RESULT = (select @.JET from dbo.vw_CapArrayData where (measurement_id
> =
> @.measurementid) and (tracking_id = @.trackingid))
> RETURN @.Result
> END
> GO
> What am I doing wrong here? When I run the view this function resides in,
> I
> get a timeout error.
>|||You cannot use Dynamic SQL inside functions. The workaround is to use a CASE
expression like:
SET @.r = ( SELECT CASE @.p WHEN 'value_1' THEN col_1
WHEN 'value_2' THEN col_2
..
WHEN 'value_n' THEN col_n
END
FROM dbo.vw_CapArrayData
WHERE measurement_id = @.m_id
AND tracking_id = @.t_id ) ;
Alternatively you can create a procedure to get this information, however
you'll have to make careful considerations for security and performance.
Anith|||Thomas Mick (nospam@.nospam.com) writes:
> I am trying to write a view that includes a column derived from a function
> call.
> The function is written thus:
> CREATE FUNCTION dbo.fnc_CapArrayValue
> (@.MEASUREMENTID INT,@.TRACKINGID BIGINT,@.JET INT)
> RETURNS REAL AS
> BEGIN
> Declare @.Result REAL
> SET @.RESULT = (select @.JET from dbo.vw_CapArrayData
> where (measurement_id = @.measurementid) and (tracking_id = @.trackingid))
> RETURN @.Result
> END
> GO
This function appears funny to me. It will return the value in @.JET if
there is a matching row, else it will return NULL. This could be expressed
more clearly.
Avoid scalar UDFs that performs data access. This can cause quite severe
performance degradation, in queries that calls the UDF, as the query
more or less gets converted to a cursor behind the scenes.
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|||>> I am trying to write a view that includes a column derived from a functio
n call. <<
Take a freshman course in BASIC Software Engineering. Try to be sober
on the days that that coupling and cohesion are discussed. It will
keep you from public embarassment in the future. You might also want
to Google over to Larry O'Brien's current column this month for a
reminder.
You even even put prefixes like "fnc_" on names! You never read
ISO-11179. Measurement_id? There is no name possible in a data model.
So very wrong! BIGINTs because you have more data than there are atoms
in the Universe!!
Almost everything. Forget the crashes; your whole mindset is wrong.
Post specs and we can try to get you something that is correct.
--CELKO--|||Are you always this condescending? I am relatively new to the world of
databases, not a seasoned veteran; hence the reason I am asking a question.
I appreciate what you have inputted, but not the attitude used to deliver
it.
BTW, I am in no way embarrassed as I am working on curing my ignorance and
appreciate all the help I can get.
Thomas
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1150410406.196985.12150@.y41g2000cwy.googlegroups.com...
> Take a freshman course in BASIC Software Engineering. Try to be sober
> on the days that that coupling and cohesion are discussed. It will
> keep you from public embarassment in the future. You might also want
> to Google over to Larry O'Brien's current column this month for a
> reminder.
> You even even put prefixes like "fnc_" on names! You never read
> ISO-11179. Measurement_id? There is no name possible in a data model.
> So very wrong! BIGINTs because you have more data than there are atoms
> in the Universe!!
>
> Almost everything. Forget the crashes; your whole mindset is wrong.
> Post specs and we can try to get you something that is correct.
> --CELKO--
>|||Thank you for your response, Mike,
Allow me to better describe what I am attempting to do without divulging
confidential information.
I have two views that I am joining together with related data. On the one
side is yield data related to particular failures. On the other side is data
from a related process generated during the manufacturing processes prior to
generating the yield data; this data is related by three of their fields.
The two views link together predictably and the data returned is valid.
However, I must also get a related value from an array table to insert into
the view I am creating; hence the reason I was exploring the use of a
function.
I hope this is more understandable, if not I'm screwed; this problem has me
at my wits end right now.
Thomas Mick
"Mike John" <Mike.John@.knowledgepool.com> wrote in message
news:uyz7m8KkGHA.4716@.TK2MSFTNGP03.phx.gbl...
> Does the same query executed against the view return results in a
> resonable time?
> If not then posting the view definition and the definitions and indexes of
> the underlying tables would give a few clues.
> Is this an extract of somethimg much more complicated - if not I cannot
> see much reason for using the function - unless you need it in a
> constraint or similar.
> Re-reading your post - is the function being used in a view definition,
> and itself retriving data from a view (assuming vw_CapArrayData is a
> view?) BTW you may get a deluge saying prefixing vw on the front of view
> names is a bad idea, which I happen to think it is - be warned.
> I suspect that the combination of a view based on a function, based on a
> view has caused the optimiser to get a tad and produce a poor
> execution plan.
> Mike John
> "Thomas Mick" <nospam@.nospam.com> wrote in message
> news:y2ikg.3677$Oh1.923@.news01.roc.ny...
>|||Erland,
Thank you for your constructive response. I truly appreciate it and will
learn accordingly.
Thomas Mick
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97E429816C9FYazorman@.127.0.0.1...
> Thomas Mick (nospam@.nospam.com) writes:
> This function appears funny to me. It will return the value in @.JET if
> there is a matching row, else it will return NULL. This could be expressed
> more clearly.
> Avoid scalar UDFs that performs data access. This can cause quite severe
> performance degradation, in queries that calls the UDF, as the query
> more or less gets converted to a cursor behind the scenes.
>
> --
> 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|||Just ignore him Thomas, most be do - he's an ignorant, condesending idiot
who has a high opinion of himself, oh and watch out for his stuck in the
80's model thinking.
Don't let this single fool put you off posting here again!
Tony.
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"Thomas Mick" <nospam@.nospam.com> wrote in message
news:Hdnkg.3705$Oh1.2508@.news01.roc.ny...
> Are you always this condescending? I am relatively new to the world of
> databases, not a seasoned veteran; hence the reason I am asking a
> question. I appreciate what you have inputted, but not the attitude used
> to deliver it.
> BTW, I am in no way embarrassed as I am working on curing my ignorance and
> appreciate all the help I can get.
> Thomas
> "--CELKO--" <jcelko212@.earthlink.net> wrote in message
> news:1150410406.196985.12150@.y41g2000cwy.googlegroups.com...
>|||I have a feeling I already know the answer to this, but I have to ask
anyway; can a stored procedure be run from within a function? Or
alternatively, can I run a stored procedure from within a view?
You see, I need to insert this value into a column of a view I am creating
for an engineer; a stored procedure would most likely be the best method of
retrieving what I need.
Thomas Mick
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:eWGUFjMkGHA.1600@.TK2MSFTNGP04.phx.gbl...
> You cannot use Dynamic SQL inside functions. The workaround is to use a
> CASE expression like:
> SET @.r = ( SELECT CASE @.p WHEN 'value_1' THEN col_1
> WHEN 'value_2' THEN col_2
> ...
> WHEN 'value_n' THEN col_n
> END
> FROM dbo.vw_CapArrayData
> WHERE measurement_id = @.m_id
> AND tracking_id = @.t_id ) ;
> Alternatively you can create a procedure to get this information, however
> you'll have to make careful considerations for security and performance.
> --
> Anith
>
I am trying to write a view that includes a column derived from a function
call.
The function is written thus:
CREATE FUNCTION dbo.fnc_CapArrayValue
(@.MEASUREMENTID INT,@.TRACKINGID BIGINT,@.JET INT)
RETURNS REAL
AS
BEGIN
Declare @.Result REAL
SET @.RESULT = (select @.JET from dbo.vw_CapArrayData where (measurement_id =
@.measurementid) and (tracking_id = @.trackingid))
RETURN @.Result
END
GO
What am I doing wrong here? When I run the view this function resides in, I
get a timeout error.Does the same query executed against the view return results in a resonable
time?
If not then posting the view definition and the definitions and indexes of
the underlying tables would give a few clues.
Is this an extract of somethimg much more complicated - if not I cannot see
much reason for using the function - unless you need it in a constraint or
similar.
Re-reading your post - is the function being used in a view definition, and
itself retriving data from a view (assuming vw_CapArrayData is a view?)
BTW you may get a deluge saying prefixing vw on the front of view names is a
bad idea, which I happen to think it is - be warned.
I suspect that the combination of a view based on a function, based on a
view has caused the optimiser to get a tad
and produce a poorexecution plan.
Mike John
"Thomas Mick" <nospam@.nospam.com> wrote in message
news:y2ikg.3677$Oh1.923@.news01.roc.ny...
> Greetings:
> I am trying to write a view that includes a column derived from a function
> call.
> The function is written thus:
> CREATE FUNCTION dbo.fnc_CapArrayValue
> (@.MEASUREMENTID INT,@.TRACKINGID BIGINT,@.JET INT)
> RETURNS REAL
> AS
> BEGIN
> Declare @.Result REAL
> SET @.RESULT = (select @.JET from dbo.vw_CapArrayData where (measurement_id
> =
> @.measurementid) and (tracking_id = @.trackingid))
> RETURN @.Result
> END
> GO
> What am I doing wrong here? When I run the view this function resides in,
> I
> get a timeout error.
>|||You cannot use Dynamic SQL inside functions. The workaround is to use a CASE
expression like:
SET @.r = ( SELECT CASE @.p WHEN 'value_1' THEN col_1
WHEN 'value_2' THEN col_2
..
WHEN 'value_n' THEN col_n
END
FROM dbo.vw_CapArrayData
WHERE measurement_id = @.m_id
AND tracking_id = @.t_id ) ;
Alternatively you can create a procedure to get this information, however
you'll have to make careful considerations for security and performance.
Anith|||Thomas Mick (nospam@.nospam.com) writes:
> I am trying to write a view that includes a column derived from a function
> call.
> The function is written thus:
> CREATE FUNCTION dbo.fnc_CapArrayValue
> (@.MEASUREMENTID INT,@.TRACKINGID BIGINT,@.JET INT)
> RETURNS REAL AS
> BEGIN
> Declare @.Result REAL
> SET @.RESULT = (select @.JET from dbo.vw_CapArrayData
> where (measurement_id = @.measurementid) and (tracking_id = @.trackingid))
> RETURN @.Result
> END
> GO
This function appears funny to me. It will return the value in @.JET if
there is a matching row, else it will return NULL. This could be expressed
more clearly.
Avoid scalar UDFs that performs data access. This can cause quite severe
performance degradation, in queries that calls the UDF, as the query
more or less gets converted to a cursor behind the scenes.
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|||>> I am trying to write a view that includes a column derived from a functio
n call. <<
Take a freshman course in BASIC Software Engineering. Try to be sober
on the days that that coupling and cohesion are discussed. It will
keep you from public embarassment in the future. You might also want
to Google over to Larry O'Brien's current column this month for a
reminder.
You even even put prefixes like "fnc_" on names! You never read
ISO-11179. Measurement_id? There is no name possible in a data model.
So very wrong! BIGINTs because you have more data than there are atoms
in the Universe!!
Almost everything. Forget the crashes; your whole mindset is wrong.
Post specs and we can try to get you something that is correct.
--CELKO--|||Are you always this condescending? I am relatively new to the world of
databases, not a seasoned veteran; hence the reason I am asking a question.
I appreciate what you have inputted, but not the attitude used to deliver
it.
BTW, I am in no way embarrassed as I am working on curing my ignorance and
appreciate all the help I can get.
Thomas
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1150410406.196985.12150@.y41g2000cwy.googlegroups.com...
> Take a freshman course in BASIC Software Engineering. Try to be sober
> on the days that that coupling and cohesion are discussed. It will
> keep you from public embarassment in the future. You might also want
> to Google over to Larry O'Brien's current column this month for a
> reminder.
> You even even put prefixes like "fnc_" on names! You never read
> ISO-11179. Measurement_id? There is no name possible in a data model.
> So very wrong! BIGINTs because you have more data than there are atoms
> in the Universe!!
>
> Almost everything. Forget the crashes; your whole mindset is wrong.
> Post specs and we can try to get you something that is correct.
> --CELKO--
>|||Thank you for your response, Mike,
Allow me to better describe what I am attempting to do without divulging
confidential information.
I have two views that I am joining together with related data. On the one
side is yield data related to particular failures. On the other side is data
from a related process generated during the manufacturing processes prior to
generating the yield data; this data is related by three of their fields.
The two views link together predictably and the data returned is valid.
However, I must also get a related value from an array table to insert into
the view I am creating; hence the reason I was exploring the use of a
function.
I hope this is more understandable, if not I'm screwed; this problem has me
at my wits end right now.
Thomas Mick
"Mike John" <Mike.John@.knowledgepool.com> wrote in message
news:uyz7m8KkGHA.4716@.TK2MSFTNGP03.phx.gbl...
> Does the same query executed against the view return results in a
> resonable time?
> If not then posting the view definition and the definitions and indexes of
> the underlying tables would give a few clues.
> Is this an extract of somethimg much more complicated - if not I cannot
> see much reason for using the function - unless you need it in a
> constraint or similar.
> Re-reading your post - is the function being used in a view definition,
> and itself retriving data from a view (assuming vw_CapArrayData is a
> view?) BTW you may get a deluge saying prefixing vw on the front of view
> names is a bad idea, which I happen to think it is - be warned.
> I suspect that the combination of a view based on a function, based on a
> view has caused the optimiser to get a tad
and produce a poor> execution plan.
> Mike John
> "Thomas Mick" <nospam@.nospam.com> wrote in message
> news:y2ikg.3677$Oh1.923@.news01.roc.ny...
>|||Erland,
Thank you for your constructive response. I truly appreciate it and will
learn accordingly.
Thomas Mick
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97E429816C9FYazorman@.127.0.0.1...
> Thomas Mick (nospam@.nospam.com) writes:
> This function appears funny to me. It will return the value in @.JET if
> there is a matching row, else it will return NULL. This could be expressed
> more clearly.
> Avoid scalar UDFs that performs data access. This can cause quite severe
> performance degradation, in queries that calls the UDF, as the query
> more or less gets converted to a cursor behind the scenes.
>
> --
> 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|||Just ignore him Thomas, most be do - he's an ignorant, condesending idiot
who has a high opinion of himself, oh and watch out for his stuck in the
80's model thinking.
Don't let this single fool put you off posting here again!
Tony.
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"Thomas Mick" <nospam@.nospam.com> wrote in message
news:Hdnkg.3705$Oh1.2508@.news01.roc.ny...
> Are you always this condescending? I am relatively new to the world of
> databases, not a seasoned veteran; hence the reason I am asking a
> question. I appreciate what you have inputted, but not the attitude used
> to deliver it.
> BTW, I am in no way embarrassed as I am working on curing my ignorance and
> appreciate all the help I can get.
> Thomas
> "--CELKO--" <jcelko212@.earthlink.net> wrote in message
> news:1150410406.196985.12150@.y41g2000cwy.googlegroups.com...
>|||I have a feeling I already know the answer to this, but I have to ask
anyway; can a stored procedure be run from within a function? Or
alternatively, can I run a stored procedure from within a view?
You see, I need to insert this value into a column of a view I am creating
for an engineer; a stored procedure would most likely be the best method of
retrieving what I need.
Thomas Mick
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:eWGUFjMkGHA.1600@.TK2MSFTNGP04.phx.gbl...
> You cannot use Dynamic SQL inside functions. The workaround is to use a
> CASE expression like:
> SET @.r = ( SELECT CASE @.p WHEN 'value_1' THEN col_1
> WHEN 'value_2' THEN col_2
> ...
> WHEN 'value_n' THEN col_n
> END
> FROM dbo.vw_CapArrayData
> WHERE measurement_id = @.m_id
> AND tracking_id = @.t_id ) ;
> Alternatively you can create a procedure to get this information, however
> you'll have to make careful considerations for security and performance.
> --
> Anith
>
Dynamic Query How To?
Hello,
I have a website that I designed with Visual Web Developer and SQL 2005
Express. Everything works fine, except that I had to write a seperate query
for each product category which is currently around 10. I expect this to
grow as we add new products and I can see this getting hard to manage in the
future.
Is there a way to write a dynamic query that will take a single parameter
(SeriesID) and run it when the page is loaded? I'm familiar with Access, but
new to SQL 2005 Express.
Thanks
JimCREATE PROCEDURE dbo.GetCategoryItems
@.CategoryID INT
AS
BEGIN
SET NOCOUNT ON;
SELECT <columns> FROM <table> WHERE CategoryID = @.CategoryID ORDER BY
<?>;
END
GO
Now just pass the categoryID into the stored procedure.
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Jim" <jim@.gordonferon.com> wrote in message
news:%23BhDem2rHHA.4104@.TK2MSFTNGP06.phx.gbl...
> Hello,
> I have a website that I designed with Visual Web Developer and SQL 2005
> Express. Everything works fine, except that I had to write a seperate
> query for each product category which is currently around 10. I expect
> this to grow as we add new products and I can see this getting hard to
> manage in the future.
> Is there a way to write a dynamic query that will take a single parameter
> (SeriesID) and run it when the page is loaded? I'm familiar with Access,
> but new to SQL 2005 Express.
> Thanks
> Jim
>|||Hi
Yes . You need to write stored procedure that accept input parameter and
generate the out put based on the parameter passed in
this site might you to start with
http://www.sql-server-performance.c..._procedures.asp
Regards
Vt
Knowledge is power;Share it
http://oneplace4sql.blogspot.com
"Jim" <jim@.gordonferon.com> wrote in message
news:%23BhDem2rHHA.4104@.TK2MSFTNGP06.phx.gbl...
> Hello,
> I have a website that I designed with Visual Web Developer and SQL 2005
> Express. Everything works fine, except that I had to write a seperate
> query for each product category which is currently around 10. I expect
> this to grow as we add new products and I can see this getting hard to
> manage in the future.
> Is there a way to write a dynamic query that will take a single parameter
> (SeriesID) and run it when the page is loaded? I'm familiar with Access,
> but new to SQL 2005 Express.
> Thanks
> Jim
>|||Thanks for the information and the link. This looks like exactly what I
need.
Jim
"Vt" <vinu.t.1976@.googlemail.com> wrote in message
news:eBfoom3rHHA.3884@.TK2MSFTNGP04.phx.gbl...
> Hi
> Yes . You need to write stored procedure that accept input parameter and
> generate the out put based on the parameter passed in
> this site might you to start with
> http://www.sql-server-performance.c..._procedures.asp
>
> Regards
> Vt
> Knowledge is power;Share it
> http://oneplace4sql.blogspot.com
> "Jim" <jim@.gordonferon.com> wrote in message
> news:%23BhDem2rHHA.4104@.TK2MSFTNGP06.phx.gbl...
>
>
I have a website that I designed with Visual Web Developer and SQL 2005
Express. Everything works fine, except that I had to write a seperate query
for each product category which is currently around 10. I expect this to
grow as we add new products and I can see this getting hard to manage in the
future.
Is there a way to write a dynamic query that will take a single parameter
(SeriesID) and run it when the page is loaded? I'm familiar with Access, but
new to SQL 2005 Express.
Thanks
JimCREATE PROCEDURE dbo.GetCategoryItems
@.CategoryID INT
AS
BEGIN
SET NOCOUNT ON;
SELECT <columns> FROM <table> WHERE CategoryID = @.CategoryID ORDER BY
<?>;
END
GO
Now just pass the categoryID into the stored procedure.
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Jim" <jim@.gordonferon.com> wrote in message
news:%23BhDem2rHHA.4104@.TK2MSFTNGP06.phx.gbl...
> Hello,
> I have a website that I designed with Visual Web Developer and SQL 2005
> Express. Everything works fine, except that I had to write a seperate
> query for each product category which is currently around 10. I expect
> this to grow as we add new products and I can see this getting hard to
> manage in the future.
> Is there a way to write a dynamic query that will take a single parameter
> (SeriesID) and run it when the page is loaded? I'm familiar with Access,
> but new to SQL 2005 Express.
> Thanks
> Jim
>|||Hi
Yes . You need to write stored procedure that accept input parameter and
generate the out put based on the parameter passed in
this site might you to start with
http://www.sql-server-performance.c..._procedures.asp
Regards
Vt
Knowledge is power;Share it
http://oneplace4sql.blogspot.com
"Jim" <jim@.gordonferon.com> wrote in message
news:%23BhDem2rHHA.4104@.TK2MSFTNGP06.phx.gbl...
> Hello,
> I have a website that I designed with Visual Web Developer and SQL 2005
> Express. Everything works fine, except that I had to write a seperate
> query for each product category which is currently around 10. I expect
> this to grow as we add new products and I can see this getting hard to
> manage in the future.
> Is there a way to write a dynamic query that will take a single parameter
> (SeriesID) and run it when the page is loaded? I'm familiar with Access,
> but new to SQL 2005 Express.
> Thanks
> Jim
>|||Thanks for the information and the link. This looks like exactly what I
need.
Jim
"Vt" <vinu.t.1976@.googlemail.com> wrote in message
news:eBfoom3rHHA.3884@.TK2MSFTNGP04.phx.gbl...
> Hi
> Yes . You need to write stored procedure that accept input parameter and
> generate the out put based on the parameter passed in
> this site might you to start with
> http://www.sql-server-performance.c..._procedures.asp
>
> Regards
> Vt
> Knowledge is power;Share it
> http://oneplace4sql.blogspot.com
> "Jim" <jim@.gordonferon.com> wrote in message
> news:%23BhDem2rHHA.4104@.TK2MSFTNGP06.phx.gbl...
>
>
Dynamic Query How To?
Hello,
I have a website that I designed with Visual Web Developer and SQL 2005
Express. Everything works fine, except that I had to write a seperate query
for each product category which is currently around 10. I expect this to
grow as we add new products and I can see this getting hard to manage in the
future.
Is there a way to write a dynamic query that will take a single parameter
(SeriesID) and run it when the page is loaded? I'm familiar with Access, but
new to SQL 2005 Express.
Thanks
Jim
CREATE PROCEDURE dbo.GetCategoryItems
@.CategoryID INT
AS
BEGIN
SET NOCOUNT ON;
SELECT <columns> FROM <table> WHERE CategoryID = @.CategoryID ORDER BY
<?>;
END
GO
Now just pass the categoryID into the stored procedure.
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Jim" <jim@.gordonferon.com> wrote in message
news:%23BhDem2rHHA.4104@.TK2MSFTNGP06.phx.gbl...
> Hello,
> I have a website that I designed with Visual Web Developer and SQL 2005
> Express. Everything works fine, except that I had to write a seperate
> query for each product category which is currently around 10. I expect
> this to grow as we add new products and I can see this getting hard to
> manage in the future.
> Is there a way to write a dynamic query that will take a single parameter
> (SeriesID) and run it when the page is loaded? I'm familiar with Access,
> but new to SQL 2005 Express.
> Thanks
> Jim
>
|||Thanks for the information and the link. This looks like exactly what I
need.
Jim
"Vt" <vinu.t.1976@.googlemail.com> wrote in message
news:eBfoom3rHHA.3884@.TK2MSFTNGP04.phx.gbl...
> Hi
> Yes . You need to write stored procedure that accept input parameter and
> generate the out put based on the parameter passed in
> this site might you to start with
> http://www.sql-server-performance.com/tn_stored_procedures.asp
>
> Regards
> Vt
> Knowledge is power;Share it
> http://oneplace4sql.blogspot.com
> "Jim" <jim@.gordonferon.com> wrote in message
> news:%23BhDem2rHHA.4104@.TK2MSFTNGP06.phx.gbl...
>
>
sql
I have a website that I designed with Visual Web Developer and SQL 2005
Express. Everything works fine, except that I had to write a seperate query
for each product category which is currently around 10. I expect this to
grow as we add new products and I can see this getting hard to manage in the
future.
Is there a way to write a dynamic query that will take a single parameter
(SeriesID) and run it when the page is loaded? I'm familiar with Access, but
new to SQL 2005 Express.
Thanks
Jim
CREATE PROCEDURE dbo.GetCategoryItems
@.CategoryID INT
AS
BEGIN
SET NOCOUNT ON;
SELECT <columns> FROM <table> WHERE CategoryID = @.CategoryID ORDER BY
<?>;
END
GO
Now just pass the categoryID into the stored procedure.
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Jim" <jim@.gordonferon.com> wrote in message
news:%23BhDem2rHHA.4104@.TK2MSFTNGP06.phx.gbl...
> Hello,
> I have a website that I designed with Visual Web Developer and SQL 2005
> Express. Everything works fine, except that I had to write a seperate
> query for each product category which is currently around 10. I expect
> this to grow as we add new products and I can see this getting hard to
> manage in the future.
> Is there a way to write a dynamic query that will take a single parameter
> (SeriesID) and run it when the page is loaded? I'm familiar with Access,
> but new to SQL 2005 Express.
> Thanks
> Jim
>
|||Thanks for the information and the link. This looks like exactly what I
need.
Jim
"Vt" <vinu.t.1976@.googlemail.com> wrote in message
news:eBfoom3rHHA.3884@.TK2MSFTNGP04.phx.gbl...
> Hi
> Yes . You need to write stored procedure that accept input parameter and
> generate the out put based on the parameter passed in
> this site might you to start with
> http://www.sql-server-performance.com/tn_stored_procedures.asp
>
> Regards
> Vt
> Knowledge is power;Share it
> http://oneplace4sql.blogspot.com
> "Jim" <jim@.gordonferon.com> wrote in message
> news:%23BhDem2rHHA.4104@.TK2MSFTNGP06.phx.gbl...
>
>
sql
Dynamic Query How To?
Hello,
I have a website that I designed with Visual Web Developer and SQL 2005
Express. Everything works fine, except that I had to write a seperate query
for each product category which is currently around 10. I expect this to
grow as we add new products and I can see this getting hard to manage in the
future.
Is there a way to write a dynamic query that will take a single parameter
(SeriesID) and run it when the page is loaded? I'm familiar with Access, but
new to SQL 2005 Express.
Thanks
JimCREATE PROCEDURE dbo.GetCategoryItems
@.CategoryID INT
AS
BEGIN
SET NOCOUNT ON;
SELECT <columns> FROM <table> WHERE CategoryID = @.CategoryID ORDER BY
<?>;
END
GO
Now just pass the categoryID into the stored procedure.
--
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Jim" <jim@.gordonferon.com> wrote in message
news:%23BhDem2rHHA.4104@.TK2MSFTNGP06.phx.gbl...
> Hello,
> I have a website that I designed with Visual Web Developer and SQL 2005
> Express. Everything works fine, except that I had to write a seperate
> query for each product category which is currently around 10. I expect
> this to grow as we add new products and I can see this getting hard to
> manage in the future.
> Is there a way to write a dynamic query that will take a single parameter
> (SeriesID) and run it when the page is loaded? I'm familiar with Access,
> but new to SQL 2005 Express.
> Thanks
> Jim
>|||Hi
Yes . You need to write stored procedure that accept input parameter and
generate the out put based on the parameter passed in
this site might you to start with
http://www.sql-server-performance.com/tn_stored_procedures.asp
Regards
Vt
Knowledge is power;Share it
http://oneplace4sql.blogspot.com
"Jim" <jim@.gordonferon.com> wrote in message
news:%23BhDem2rHHA.4104@.TK2MSFTNGP06.phx.gbl...
> Hello,
> I have a website that I designed with Visual Web Developer and SQL 2005
> Express. Everything works fine, except that I had to write a seperate
> query for each product category which is currently around 10. I expect
> this to grow as we add new products and I can see this getting hard to
> manage in the future.
> Is there a way to write a dynamic query that will take a single parameter
> (SeriesID) and run it when the page is loaded? I'm familiar with Access,
> but new to SQL 2005 Express.
> Thanks
> Jim
>|||Thanks for the information and the link. This looks like exactly what I
need.
Jim
"Vt" <vinu.t.1976@.googlemail.com> wrote in message
news:eBfoom3rHHA.3884@.TK2MSFTNGP04.phx.gbl...
> Hi
> Yes . You need to write stored procedure that accept input parameter and
> generate the out put based on the parameter passed in
> this site might you to start with
> http://www.sql-server-performance.com/tn_stored_procedures.asp
>
> Regards
> Vt
> Knowledge is power;Share it
> http://oneplace4sql.blogspot.com
> "Jim" <jim@.gordonferon.com> wrote in message
> news:%23BhDem2rHHA.4104@.TK2MSFTNGP06.phx.gbl...
>> Hello,
>> I have a website that I designed with Visual Web Developer and SQL 2005
>> Express. Everything works fine, except that I had to write a seperate
>> query for each product category which is currently around 10. I expect
>> this to grow as we add new products and I can see this getting hard to
>> manage in the future.
>> Is there a way to write a dynamic query that will take a single parameter
>> (SeriesID) and run it when the page is loaded? I'm familiar with Access,
>> but new to SQL 2005 Express.
>> Thanks
>> Jim
>>
>
>
I have a website that I designed with Visual Web Developer and SQL 2005
Express. Everything works fine, except that I had to write a seperate query
for each product category which is currently around 10. I expect this to
grow as we add new products and I can see this getting hard to manage in the
future.
Is there a way to write a dynamic query that will take a single parameter
(SeriesID) and run it when the page is loaded? I'm familiar with Access, but
new to SQL 2005 Express.
Thanks
JimCREATE PROCEDURE dbo.GetCategoryItems
@.CategoryID INT
AS
BEGIN
SET NOCOUNT ON;
SELECT <columns> FROM <table> WHERE CategoryID = @.CategoryID ORDER BY
<?>;
END
GO
Now just pass the categoryID into the stored procedure.
--
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Jim" <jim@.gordonferon.com> wrote in message
news:%23BhDem2rHHA.4104@.TK2MSFTNGP06.phx.gbl...
> Hello,
> I have a website that I designed with Visual Web Developer and SQL 2005
> Express. Everything works fine, except that I had to write a seperate
> query for each product category which is currently around 10. I expect
> this to grow as we add new products and I can see this getting hard to
> manage in the future.
> Is there a way to write a dynamic query that will take a single parameter
> (SeriesID) and run it when the page is loaded? I'm familiar with Access,
> but new to SQL 2005 Express.
> Thanks
> Jim
>|||Hi
Yes . You need to write stored procedure that accept input parameter and
generate the out put based on the parameter passed in
this site might you to start with
http://www.sql-server-performance.com/tn_stored_procedures.asp
Regards
Vt
Knowledge is power;Share it
http://oneplace4sql.blogspot.com
"Jim" <jim@.gordonferon.com> wrote in message
news:%23BhDem2rHHA.4104@.TK2MSFTNGP06.phx.gbl...
> Hello,
> I have a website that I designed with Visual Web Developer and SQL 2005
> Express. Everything works fine, except that I had to write a seperate
> query for each product category which is currently around 10. I expect
> this to grow as we add new products and I can see this getting hard to
> manage in the future.
> Is there a way to write a dynamic query that will take a single parameter
> (SeriesID) and run it when the page is loaded? I'm familiar with Access,
> but new to SQL 2005 Express.
> Thanks
> Jim
>|||Thanks for the information and the link. This looks like exactly what I
need.
Jim
"Vt" <vinu.t.1976@.googlemail.com> wrote in message
news:eBfoom3rHHA.3884@.TK2MSFTNGP04.phx.gbl...
> Hi
> Yes . You need to write stored procedure that accept input parameter and
> generate the out put based on the parameter passed in
> this site might you to start with
> http://www.sql-server-performance.com/tn_stored_procedures.asp
>
> Regards
> Vt
> Knowledge is power;Share it
> http://oneplace4sql.blogspot.com
> "Jim" <jim@.gordonferon.com> wrote in message
> news:%23BhDem2rHHA.4104@.TK2MSFTNGP06.phx.gbl...
>> Hello,
>> I have a website that I designed with Visual Web Developer and SQL 2005
>> Express. Everything works fine, except that I had to write a seperate
>> query for each product category which is currently around 10. I expect
>> this to grow as we add new products and I can see this getting hard to
>> manage in the future.
>> Is there a way to write a dynamic query that will take a single parameter
>> (SeriesID) and run it when the page is loaded? I'm familiar with Access,
>> but new to SQL 2005 Express.
>> Thanks
>> Jim
>>
>
>
Monday, March 26, 2012
Dynamic Query
I am trying to write some code to go through all the tables in my database
and do a row count. What I have done so far is open a cursor to sysobject
and loop round the tables in it. Then I am building a query in a string
"SELECT COUNT(*) FROM" + tablename . My next step is to EXEC the created
string. The problem is getting the result back from the count, can anybody
tell me how I do this?
thanks
Gav--Try this
Use Northwind
Declare @.sql nvarchar(1000), @.tablename nvarchar(100) ,@.Count int
SET @.tablename = 'Employees'
SET @.sql = 'SELECT @.Count =COUNT(*) FROM ' + @.tablename
exec sp_executeSQL @.sql, N'@.Count int OUTPUT' , @.Count OUTPUT
print @.Count
~Bala|||Select object_name(id), rows from sysindexes where indid<1
Madhivanan|||<balacr@.gmail.com> wrote in message
news:1111658074.290946.83940@.f14g2000cwb.googlegroups.com...
> --Try this
> Use Northwind
> Declare @.sql nvarchar(1000), @.tablename nvarchar(100) ,@.Count int
> SET @.tablename = 'Employees'
> SET @.sql = 'SELECT @.Count =COUNT(*) FROM ' + @.tablename
> exec sp_executeSQL @.sql, N'@.Count int OUTPUT' , @.Count OUTPUT
> print @.Count
> ~Bala
>
Ths was great until I move the code from my Dev box to one of the servers
I want to run this on. Seems the sp_executeSQL does not exist, any ideas
why?
Gav|||Which version of SQL server are you using?
I am not sure whether 'sp_ExecuteSQL' was on SQL 7
Bala|||They are all on the same version SQL 2000 SP3a.. Only difference is the box
I am now trying to run it on is a SAP database server, box I wrote it on was
not.
Gav
<balacr@.gmail.com> wrote in message
news:1111665107.148760.270850@.g14g2000cwa.googlegroups.com...
> Which version of SQL server are you using?
> I am not sure whether 'sp_ExecuteSQL' was on SQL 7
> Bala
>|||Can you tell me the exact error that you are getting?|||Could not find stored procedure 'sp_executeSQL'.
<balacr@.gmail.com> wrote in message
news:1111669257.820312.152460@.l41g2000cwc.googlegroups.com...
> Can you tell me the exact error that you are getting?
>|||My guess is that the server is case sensitive. The name of the procedure is
sp_executesql, not
sp_executeSQL.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Gav" <gavin.metcalfe@.nospam.portakabin.com> wrote in message
news:d1ufp2$teb$1@.newsreaderg1.core.theplanet.net...
> Could not find stored procedure 'sp_executeSQL'.
> <balacr@.gmail.com> wrote in message
> news:1111669257.820312.152460@.l41g2000cwc.googlegroups.com...
>|||Spot on. Thanks to everyone for their help. :o)
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OwvlAeHMFHA.576@.TK2MSFTNGP15.phx.gbl...
> My guess is that the server is case sensitive. The name of the procedure
is sp_executesql, not
> sp_executeSQL.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Gav" <gavin.metcalfe@.nospam.portakabin.com> wrote in message
> news:d1ufp2$teb$1@.newsreaderg1.core.theplanet.net...
>
and do a row count. What I have done so far is open a cursor to sysobject
and loop round the tables in it. Then I am building a query in a string
"SELECT COUNT(*) FROM" + tablename . My next step is to EXEC the created
string. The problem is getting the result back from the count, can anybody
tell me how I do this?
thanks
Gav--Try this
Use Northwind
Declare @.sql nvarchar(1000), @.tablename nvarchar(100) ,@.Count int
SET @.tablename = 'Employees'
SET @.sql = 'SELECT @.Count =COUNT(*) FROM ' + @.tablename
exec sp_executeSQL @.sql, N'@.Count int OUTPUT' , @.Count OUTPUT
print @.Count
~Bala|||Select object_name(id), rows from sysindexes where indid<1
Madhivanan|||<balacr@.gmail.com> wrote in message
news:1111658074.290946.83940@.f14g2000cwb.googlegroups.com...
> --Try this
> Use Northwind
> Declare @.sql nvarchar(1000), @.tablename nvarchar(100) ,@.Count int
> SET @.tablename = 'Employees'
> SET @.sql = 'SELECT @.Count =COUNT(*) FROM ' + @.tablename
> exec sp_executeSQL @.sql, N'@.Count int OUTPUT' , @.Count OUTPUT
> print @.Count
> ~Bala
>
Ths was great until I move the code from my Dev box to one of the servers
I want to run this on. Seems the sp_executeSQL does not exist, any ideas
why?
Gav|||Which version of SQL server are you using?
I am not sure whether 'sp_ExecuteSQL' was on SQL 7
Bala|||They are all on the same version SQL 2000 SP3a.. Only difference is the box
I am now trying to run it on is a SAP database server, box I wrote it on was
not.
Gav
<balacr@.gmail.com> wrote in message
news:1111665107.148760.270850@.g14g2000cwa.googlegroups.com...
> Which version of SQL server are you using?
> I am not sure whether 'sp_ExecuteSQL' was on SQL 7
> Bala
>|||Can you tell me the exact error that you are getting?|||Could not find stored procedure 'sp_executeSQL'.
<balacr@.gmail.com> wrote in message
news:1111669257.820312.152460@.l41g2000cwc.googlegroups.com...
> Can you tell me the exact error that you are getting?
>|||My guess is that the server is case sensitive. The name of the procedure is
sp_executesql, not
sp_executeSQL.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Gav" <gavin.metcalfe@.nospam.portakabin.com> wrote in message
news:d1ufp2$teb$1@.newsreaderg1.core.theplanet.net...
> Could not find stored procedure 'sp_executeSQL'.
> <balacr@.gmail.com> wrote in message
> news:1111669257.820312.152460@.l41g2000cwc.googlegroups.com...
>|||Spot on. Thanks to everyone for their help. :o)
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OwvlAeHMFHA.576@.TK2MSFTNGP15.phx.gbl...
> My guess is that the server is case sensitive. The name of the procedure
is sp_executesql, not
> sp_executeSQL.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Gav" <gavin.metcalfe@.nospam.portakabin.com> wrote in message
> news:d1ufp2$teb$1@.newsreaderg1.core.theplanet.net...
>
Dynamic Query
I need to write a stored proceed that has 15 parameters that returns a
recordset. Any one of these parameters may contain values.
EX: @.Lname = ''
@.Phone = '1234567890'
@.Fname = 'JANE'
@.City = 'LA'
@.State = ''
The main part of the proc is a dynamically created SELECT statement
where the parameters are used in the WHERE clause. EX: @.SQL = 'SELECT
* FROM Table WHERE '. Only parameters with values must be included in
the WHERE clause. And any parameter after the first one should have
'AND'. So the query should look like this:
@.SQL = 'SELECT * FROM Table WHERE '
@.SQL = @.SQL + ' phone = ' + @.phone
@.SQL = @.SQL + ' AND Fname = ' + @.Fname
How can I figure out which is the first parameter that contains a
value so not to include an AND condition and then add the AND for the
rest of the parameters?
Thanks,
NinelYou cud write
set @.SQL = 'SELECT * FROM Table WHERE 1=1'
If isnull(@.phone ,'') <> ''
select @.sql = @.sql + '
AND Phone = @.phone'
If isnull(@.Fname ,'')<> ''
select @.sql = @.sql + '
AND FName = @.Fname'
exec (@.sql)
Untested, shud work
Prad
"ninel" <ngorbunov@.onetouchdirect-dot-com.no-spam.invalid> wrote in message
news:t4CdnXjfGpsWtvLfRVn_vA@.giganews.com...
>I need to write a stored proceed that has 15 parameters that returns a
> recordset. Any one of these parameters may contain values.
> EX: @.Lname = ''
> @.Phone = '1234567890'
> @.Fname = 'JANE'
> @.City = 'LA'
> @.State = ''
> The main part of the proc is a dynamically created SELECT statement
> where the parameters are used in the WHERE clause. EX: @.SQL = 'SELECT
> * FROM Table WHERE '. Only parameters with values must be included in
> the WHERE clause. And any parameter after the first one should have
> 'AND'. So the query should look like this:
> @.SQL = 'SELECT * FROM Table WHERE '
> @.SQL = @.SQL + ' phone = ' + @.phone
> @.SQL = @.SQL + ' AND Fname = ' + @.Fname
> How can I figure out which is the first parameter that contains a
> value so not to include an AND condition and then add the AND for the
> rest of the parameters?
> Thanks,
> Ninel
>|||Hi
This will not work as @.phone or @.Fname will not be in scope. Check out
http://www.sommarskog.se/dyn-search.html for working examples.
John
"Pradeep Kutty" wrote:
> You cud write
> set @.SQL = 'SELECT * FROM Table WHERE 1=1'
> If isnull(@.phone ,'') <> ''
> select @.sql = @.sql + '
> AND Phone = @.phone'
> If isnull(@.Fname ,'')<> ''
> select @.sql = @.sql + '
> AND FName = @.Fname'
> exec (@.sql)
> Untested, shud work
> Prad
> "ninel" <ngorbunov@.onetouchdirect-dot-com.no-spam.invalid> wrote in messag
e
> news:t4CdnXjfGpsWtvLfRVn_vA@.giganews.com...
>
>|||Avoid dynamic SQL and procedures with more than five parameters.
SELECT *
FROM Foobar
WHERE first_name = COALESCE(@.my_first_name, first_name)
AND last_name = COALESCE(@.my_last_name, last_name)
AND ... ;|||On 27 Apr 2005 07:46:33 -0700, --CELKO-- wrote:
> Avoid dynamic SQL and procedures with more than five parameters.
> SELECT *
> FROM Foobar
> WHERE first_name = COALESCE(@.my_first_name, first_name)
> AND last_name = COALESCE(@.my_last_name, last_name)
> AND ... ;
Is "five parameters" an arbitrary limit based on experience?
I can vouch for the fact that when there are too many parameters, the
optimizer has a really hard time figuring out a good plan. It will do crazy
things like a table scan to compare NULLs with every row, when it could
just get the desired answer from a primary key.
In one instance I "unrolled" the query into a set of the three most often
used queries, choosing the correct one to use based on IF statements.
(Programmer insisted on a single stored procedure for looking up customer
records, when the operator would sometimes only know the last name and
state, sometimes would have member ID, sometimes would have last name,
state and some other data ...)|||>> Is "five parameters" an arbitrary limit based on experience? <<
In 1956 by a psychologist named Miller published a short article
entitled "The Magical Number Seven Plus or Minus Two: Some Limits on
Our Capacity for Processing Information" that collected a lot of data
together in one place and this has been confirmed over and over again.
It is a classic paper and it ought to be out there.
The idea is that you can juggle five things fairly well, seven is when
it gets to hard and nine requires that you train for it and it is just
about impossible to get to ten things without being a savant. What you
have to do is "chunking" things to reduce the number of distinct
elements -- so (longtitude, latitude) becomes "location" rather than
two data elements.
the optimizer has a really hard time figuring out a good plan. <<
That is another "Law of Five". There are 3 ways to squence two tables
for processing, 6 ways to squence three tables, 24 ways to squence
four tables, and 120 ways to arrange five tables. Big jump at five!
And the optimizer starts to choke.
queries, choosing the correct one to use based on IF statements. <<
While I like to avoid IF-THEN control flow, it sounds like a good way
to do it in this case.sql
recordset. Any one of these parameters may contain values.
EX: @.Lname = ''
@.Phone = '1234567890'
@.Fname = 'JANE'
@.City = 'LA'
@.State = ''
The main part of the proc is a dynamically created SELECT statement
where the parameters are used in the WHERE clause. EX: @.SQL = 'SELECT
* FROM Table WHERE '. Only parameters with values must be included in
the WHERE clause. And any parameter after the first one should have
'AND'. So the query should look like this:
@.SQL = 'SELECT * FROM Table WHERE '
@.SQL = @.SQL + ' phone = ' + @.phone
@.SQL = @.SQL + ' AND Fname = ' + @.Fname
How can I figure out which is the first parameter that contains a
value so not to include an AND condition and then add the AND for the
rest of the parameters?
Thanks,
NinelYou cud write
set @.SQL = 'SELECT * FROM Table WHERE 1=1'
If isnull(@.phone ,'') <> ''
select @.sql = @.sql + '
AND Phone = @.phone'
If isnull(@.Fname ,'')<> ''
select @.sql = @.sql + '
AND FName = @.Fname'
exec (@.sql)
Untested, shud work
Prad
"ninel" <ngorbunov@.onetouchdirect-dot-com.no-spam.invalid> wrote in message
news:t4CdnXjfGpsWtvLfRVn_vA@.giganews.com...
>I need to write a stored proceed that has 15 parameters that returns a
> recordset. Any one of these parameters may contain values.
> EX: @.Lname = ''
> @.Phone = '1234567890'
> @.Fname = 'JANE'
> @.City = 'LA'
> @.State = ''
> The main part of the proc is a dynamically created SELECT statement
> where the parameters are used in the WHERE clause. EX: @.SQL = 'SELECT
> * FROM Table WHERE '. Only parameters with values must be included in
> the WHERE clause. And any parameter after the first one should have
> 'AND'. So the query should look like this:
> @.SQL = 'SELECT * FROM Table WHERE '
> @.SQL = @.SQL + ' phone = ' + @.phone
> @.SQL = @.SQL + ' AND Fname = ' + @.Fname
> How can I figure out which is the first parameter that contains a
> value so not to include an AND condition and then add the AND for the
> rest of the parameters?
> Thanks,
> Ninel
>|||Hi
This will not work as @.phone or @.Fname will not be in scope. Check out
http://www.sommarskog.se/dyn-search.html for working examples.
John
"Pradeep Kutty" wrote:
> You cud write
> set @.SQL = 'SELECT * FROM Table WHERE 1=1'
> If isnull(@.phone ,'') <> ''
> select @.sql = @.sql + '
> AND Phone = @.phone'
> If isnull(@.Fname ,'')<> ''
> select @.sql = @.sql + '
> AND FName = @.Fname'
> exec (@.sql)
> Untested, shud work
> Prad
> "ninel" <ngorbunov@.onetouchdirect-dot-com.no-spam.invalid> wrote in messag
e
> news:t4CdnXjfGpsWtvLfRVn_vA@.giganews.com...
>
>|||Avoid dynamic SQL and procedures with more than five parameters.
SELECT *
FROM Foobar
WHERE first_name = COALESCE(@.my_first_name, first_name)
AND last_name = COALESCE(@.my_last_name, last_name)
AND ... ;|||On 27 Apr 2005 07:46:33 -0700, --CELKO-- wrote:
> Avoid dynamic SQL and procedures with more than five parameters.
> SELECT *
> FROM Foobar
> WHERE first_name = COALESCE(@.my_first_name, first_name)
> AND last_name = COALESCE(@.my_last_name, last_name)
> AND ... ;
Is "five parameters" an arbitrary limit based on experience?
I can vouch for the fact that when there are too many parameters, the
optimizer has a really hard time figuring out a good plan. It will do crazy
things like a table scan to compare NULLs with every row, when it could
just get the desired answer from a primary key.
In one instance I "unrolled" the query into a set of the three most often
used queries, choosing the correct one to use based on IF statements.
(Programmer insisted on a single stored procedure for looking up customer
records, when the operator would sometimes only know the last name and
state, sometimes would have member ID, sometimes would have last name,
state and some other data ...)|||>> Is "five parameters" an arbitrary limit based on experience? <<
In 1956 by a psychologist named Miller published a short article
entitled "The Magical Number Seven Plus or Minus Two: Some Limits on
Our Capacity for Processing Information" that collected a lot of data
together in one place and this has been confirmed over and over again.
It is a classic paper and it ought to be out there.
The idea is that you can juggle five things fairly well, seven is when
it gets to hard and nine requires that you train for it and it is just
about impossible to get to ten things without being a savant. What you
have to do is "chunking" things to reduce the number of distinct
elements -- so (longtitude, latitude) becomes "location" rather than
two data elements.
the optimizer has a really hard time figuring out a good plan. <<
That is another "Law of Five". There are 3 ways to squence two tables
for processing, 6 ways to squence three tables, 24 ways to squence
four tables, and 120 ways to arrange five tables. Big jump at five!
And the optimizer starts to choke.
queries, choosing the correct one to use based on IF statements. <<
While I like to avoid IF-THEN control flow, it sounds like a good way
to do it in this case.sql
Dynamic query
Hello
I have a problem with writing a query.Let me give an example:
Table:
ColA , ColB , ColC , Col1 , Col2 , Col3 , Col4 , Col5
Ok.I must write a SP and it gets a parameter,say @.param. if @.param=1 then in the select statement I will select Col1,if @.param=2 then I will select Col2 and so on.
How can I do this?
Thanks.
take note of this. this will provide you all the info you need
use northwind
select * from syscolumns where id =(
select id from sysobjects where name='employees')
here's the Sp:
|||
create proc dynamicselect(
@.columnid int
)as
declare @.dynamicsql nvarchar(200)
select @.dynamicsql ='select '+ name+ ' from employees'
from
(
select * from syscolumns where id =(
select id from sysobjects where name='employees')
)as awhere colid=@.columnid
exec sp_executesql @.dynamicsqlgo
execute dynamicselect 2
execute dynamicselect 1
Joey gives an answer how. You could also do:
declare @.query varchar(max)
set @.query = 'select col ' + cast(@.param as int) + ' from table'
Or if the column names aren't actually numberd, use a CASE expression to pick the columns.
The question I have is why do this? If all columns are only useful one at a time, this is probably a design issue. If this is to support some user interface issue, the user interface ought to be redesigned.
I would also suggest that you might want to think about just having a procedure per column configuration that you end up with. This will be better all around for you, particularly in performance and security. It will take a bit more coding of course
|||-
CREATE PROCEDURE dbo.dynsql
@.param int
AS
BEGIN
DECLARE @.q varchar(max)
select @.q = 'SELECT sym_no'+CAST(@.param AS nvarchar(2)) + ' FROM TABLE'
exec sp_executesql @.q
END
execute dynsql 2
--
I tried this but I get an error: "Procedure expects parameter '@.statement' of type 'ntext/nchar/nvarchar'.
What's wrong with this..?
|||Your variable @.q must be of type ntext, nchar, nvarchar to be used with procedure sp_executesql. Change your variable from the current varchar to nvarchar.|||D'oh! I forgot that :)dynamic pivot?
I need to write a report that displays the time each operation was started
across the page like this:
Ord_Num OC020 OC030
-- --- --
---
520208 2004-10-01 08:22:00.000
2004-10-01 08:25:00.000
520273 2004-10-01 08:13:00.000
2004-10-01 08:16:00.000
520292 2004-10-01 08:04:00.000
2004-10-01 08:06:00.000
520324 2004-10-01 08:30:00.000
2004-10-01 08:33:00.000
520388 2004-10-01 08:27:00.000
2004-10-01 08:31:00.000
520472 2004-10-01 08:26:00.000
2004-10-01 08:30:00.000
The problem is that I don't know at design time what the operations are all
going to be. Here is some SQL to set up the problem:
Use Pubs
go
Create Table ProcessControl (Ord_Num nvarchar(12), OpCode nvarchar(12),
PlannedStartDT datetime)
go
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520324', 'PR120', 'Oct 1 2004 8:52AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520388', 'OC020', 'Oct 1 2004 8:28AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520388', 'OC030', 'Oct 1 2004 8:32AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520388', 'PR010', 'Oct 1 2004 8:38AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520388', 'PR020', 'Oct 1 2004 8:38AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520388', 'PR120', 'Oct 1 2004 8:38AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520388', 'OC020', 'Oct 1 2004 8:27AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520388', 'OC030', 'Oct 1 2004 8:31AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520388', 'PR010', 'Oct 1 2004 8:37AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520388', 'PR020', 'Oct 1 2004 8:37AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520388', 'PR120', 'Oct 1 2004 8:37AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520472', 'OC020', 'Oct 1 2004 8:26AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520472', 'OC030', 'Oct 1 2004 8:30AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520472', 'PR010', 'Oct 1 2004 8:33AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520472', 'PR020', 'Oct 1 2004 8:33AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520472', 'PR120', 'Oct 1 2004 8:33AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520472', 'PR130', 'Oct 1 2004 8:53AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520529', 'OC020', 'Oct 1 2004 8:25AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520529', 'OC030', 'Oct 1 2004 8:30AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520529', 'PR010', 'Oct 1 2004 8:34AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520529', 'PR020', 'Oct 1 2004 8:34AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520529', 'PR120', 'Oct 1 2004 8:34AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520529', 'PR130', 'Oct 1 2004 8:50AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520658', 'OC020', 'Oct 1 2004 8:09AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520658', 'OC030', 'Oct 1 2004 8:10AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520658', 'PR010', 'Oct 1 2004 8:15AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520658', 'PR020', 'Oct 1 2004 8:15AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520658', 'PR120', 'Oct 1 2004 8:41AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520840', 'OC020', 'Oct 1 2004 8:12AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520840', 'OC030', 'Oct 1 2004 8:15AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520840', 'PR010', 'Oct 1 2004 8:22AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520840', 'PR020', 'Oct 1 2004 8:22AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520840', 'PR120', 'Oct 1 2004 8:49AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520860', 'OC020', 'Oct 1 2004 8:01AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520860', 'OC030', 'Oct 1 2004 8:02AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520860', 'PR010', 'Oct 1 2004 8:10AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520860', 'PR020', 'Oct 1 2004 8:10AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520860', 'PR120', 'Oct 1 2004 8:10AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520900', 'OC020', 'Oct 1 2004 8:02AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520900', 'OC030', 'Oct 1 2004 8:03AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520900', 'PR010', 'Oct 1 2004 8:10AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520900', 'PR020', 'Oct 1 2004 8:10AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('521056', 'OC020', 'Oct 1 2004 8:07AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('521056', 'OC030', 'Oct 1 2004 8:08AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('521056', 'PR010', 'Oct 1 2004 8:18AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('521056', 'PR020', 'Oct 1 2004 8:18AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('521056', 'PR120', 'Oct 1 2004 8:21AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('521056', 'OC020', 'Oct 1 2004 8:06AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('521056', 'OC030', 'Oct 1 2004 8:07AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('521056', 'PR010', 'Oct 1 2004 8:16AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('521056', 'PR020', 'Oct 1 2004 8:16AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('521056', 'PR120', 'Oct 1 2004 8:55AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('521103', 'OC020', 'Oct 1 2004 8:08AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('521103', 'OC030', 'Oct 1 2004 8:10AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('521103', 'PR010', 'Oct 1 2004 8:16AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('521103', 'PR020', 'Oct 1 2004 8:16AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('521144', 'OC020', 'Oct 1 2004 8:21AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('521144', 'OC030', 'Oct 1 2004 8:25AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('521144', 'PR010', 'Oct 1 2004 8:34AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('521144', 'PR020', 'Oct 1 2004 8:34AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('521144', 'PR120', 'Oct 1 2004 8:34AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('521144', 'PR130', 'Oct 1 2004 8:39AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('521144', 'PR140', 'Oct 1 2004 8:50AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('521144', 'PR150', 'Oct 1 2004 8:51AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('521163', 'OC020', 'Oct 1 2004 8:03AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('521163', 'OC030', 'Oct 1 2004 8:04AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('521163', 'PR010', 'Oct 1 2004 8:16AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('521163', 'PR020', 'Oct 1 2004 8:16AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('521163', 'PR120', 'Oct 1 2004 8:16AM' )
go
I get nice results with the query below, but again, at design time I don't
know how many operations there will be or what they are.
Select Ord_Num,
OC020 = min(case when Opcode = 'oc020' then PlannedStartDT else NULL end),
OC030 = min(case when Opcode = 'oc030' then PlannedStartDT else NULL end),
PR010 = min(case when Opcode = 'PR010' then PlannedStartDT else NULL end),
PR020 = min(case when Opcode = 'PR020' then PlannedStartDT else NULL end),
PR120 = min(case when Opcode = 'PR120' then PlannedStartDT else NULL end),
PR130 = min(case when Opcode = 'PR130' then PlannedStartDT else NULL end),
PR140 = min(case when Opcode = 'PR140' then PlannedStartDT else NULL end),
PR150 = min(case when Opcode = 'PR150' then PlannedStartDT else NULL end)
From ProcessControl
Group By Ord_Num
Is there any solution to this that does not require dynamic SQL?
Thanks.
Daniel Wilson
Senior Software Solutions Developer
Embtrak Development Team
http://www.Embtrak.com
DVBrown CompanyHi
You may want to look at:
http://www.windowsitpro.com/SQLServ...5608/15608.html
but it also uses dynamic SQL.
John
"Daniel Wilson" <d.wilson@.embtrak.com> wrote in message
news:OTnp$MEHFHA.560@.TK2MSFTNGP12.phx.gbl...
>I need to write a report that displays the time each operation was started
> across the page like this:
> Ord_Num OC020 OC030
> -- --- --
--
> ---
> 520208 2004-10-01 08:22:00.000
> 2004-10-01 08:25:00.000
> 520273 2004-10-01 08:13:00.000
> 2004-10-01 08:16:00.000
> 520292 2004-10-01 08:04:00.000
> 2004-10-01 08:06:00.000
> 520324 2004-10-01 08:30:00.000
> 2004-10-01 08:33:00.000
> 520388 2004-10-01 08:27:00.000
> 2004-10-01 08:31:00.000
> 520472 2004-10-01 08:26:00.000
> 2004-10-01 08:30:00.000
> The problem is that I don't know at design time what the operations are
> all
> going to be. Here is some SQL to set up the problem:
> Use Pubs
> go
> Create Table ProcessControl (Ord_Num nvarchar(12), OpCode nvarchar(12),
> PlannedStartDT datetime)
> go
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520324', 'PR120', 'Oct 1 2004 8:52AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520388', 'OC020', 'Oct 1 2004 8:28AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520388', 'OC030', 'Oct 1 2004 8:32AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520388', 'PR010', 'Oct 1 2004 8:38AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520388', 'PR020', 'Oct 1 2004 8:38AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520388', 'PR120', 'Oct 1 2004 8:38AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520388', 'OC020', 'Oct 1 2004 8:27AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520388', 'OC030', 'Oct 1 2004 8:31AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520388', 'PR010', 'Oct 1 2004 8:37AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520388', 'PR020', 'Oct 1 2004 8:37AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520388', 'PR120', 'Oct 1 2004 8:37AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520472', 'OC020', 'Oct 1 2004 8:26AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520472', 'OC030', 'Oct 1 2004 8:30AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520472', 'PR010', 'Oct 1 2004 8:33AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520472', 'PR020', 'Oct 1 2004 8:33AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520472', 'PR120', 'Oct 1 2004 8:33AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520472', 'PR130', 'Oct 1 2004 8:53AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520529', 'OC020', 'Oct 1 2004 8:25AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520529', 'OC030', 'Oct 1 2004 8:30AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520529', 'PR010', 'Oct 1 2004 8:34AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520529', 'PR020', 'Oct 1 2004 8:34AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520529', 'PR120', 'Oct 1 2004 8:34AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520529', 'PR130', 'Oct 1 2004 8:50AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520658', 'OC020', 'Oct 1 2004 8:09AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520658', 'OC030', 'Oct 1 2004 8:10AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520658', 'PR010', 'Oct 1 2004 8:15AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520658', 'PR020', 'Oct 1 2004 8:15AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520658', 'PR120', 'Oct 1 2004 8:41AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520840', 'OC020', 'Oct 1 2004 8:12AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520840', 'OC030', 'Oct 1 2004 8:15AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520840', 'PR010', 'Oct 1 2004 8:22AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520840', 'PR020', 'Oct 1 2004 8:22AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520840', 'PR120', 'Oct 1 2004 8:49AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520860', 'OC020', 'Oct 1 2004 8:01AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520860', 'OC030', 'Oct 1 2004 8:02AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520860', 'PR010', 'Oct 1 2004 8:10AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520860', 'PR020', 'Oct 1 2004 8:10AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520860', 'PR120', 'Oct 1 2004 8:10AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520900', 'OC020', 'Oct 1 2004 8:02AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520900', 'OC030', 'Oct 1 2004 8:03AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520900', 'PR010', 'Oct 1 2004 8:10AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520900', 'PR020', 'Oct 1 2004 8:10AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521056', 'OC020', 'Oct 1 2004 8:07AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521056', 'OC030', 'Oct 1 2004 8:08AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521056', 'PR010', 'Oct 1 2004 8:18AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521056', 'PR020', 'Oct 1 2004 8:18AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521056', 'PR120', 'Oct 1 2004 8:21AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521056', 'OC020', 'Oct 1 2004 8:06AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521056', 'OC030', 'Oct 1 2004 8:07AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521056', 'PR010', 'Oct 1 2004 8:16AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521056', 'PR020', 'Oct 1 2004 8:16AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521056', 'PR120', 'Oct 1 2004 8:55AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521103', 'OC020', 'Oct 1 2004 8:08AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521103', 'OC030', 'Oct 1 2004 8:10AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521103', 'PR010', 'Oct 1 2004 8:16AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521103', 'PR020', 'Oct 1 2004 8:16AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521144', 'OC020', 'Oct 1 2004 8:21AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521144', 'OC030', 'Oct 1 2004 8:25AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521144', 'PR010', 'Oct 1 2004 8:34AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521144', 'PR020', 'Oct 1 2004 8:34AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521144', 'PR120', 'Oct 1 2004 8:34AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521144', 'PR130', 'Oct 1 2004 8:39AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521144', 'PR140', 'Oct 1 2004 8:50AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521144', 'PR150', 'Oct 1 2004 8:51AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521163', 'OC020', 'Oct 1 2004 8:03AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521163', 'OC030', 'Oct 1 2004 8:04AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521163', 'PR010', 'Oct 1 2004 8:16AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521163', 'PR020', 'Oct 1 2004 8:16AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521163', 'PR120', 'Oct 1 2004 8:16AM' )
> go
> I get nice results with the query below, but again, at design time I don't
> know how many operations there will be or what they are.
>
> Select Ord_Num,
> OC020 = min(case when Opcode = 'oc020' then PlannedStartDT else NULL end),
> OC030 = min(case when Opcode = 'oc030' then PlannedStartDT else NULL end),
> PR010 = min(case when Opcode = 'PR010' then PlannedStartDT else NULL end),
> PR020 = min(case when Opcode = 'PR020' then PlannedStartDT else NULL end),
> PR120 = min(case when Opcode = 'PR120' then PlannedStartDT else NULL end),
> PR130 = min(case when Opcode = 'PR130' then PlannedStartDT else NULL end),
> PR140 = min(case when Opcode = 'PR140' then PlannedStartDT else NULL end),
> PR150 = min(case when Opcode = 'PR150' then PlannedStartDT else NULL end)
> From ProcessControl
> Group By Ord_Num
> Is there any solution to this that does not require dynamic SQL?
> Thanks.
>
> --
> Daniel Wilson
> Senior Software Solutions Developer
> Embtrak Development Team
> http://www.Embtrak.com
> DVBrown Company
>|||You might want to check out www.rac4sql.net for a solution to your problem.
Also, check out
http://www.windowsitpro.com/Windows...54/pg/2/2.html.
"Daniel Wilson" <d.wilson@.embtrak.com> wrote in message
news:OTnp$MEHFHA.560@.TK2MSFTNGP12.phx.gbl...
>I need to write a report that displays the time each operation was started
> across the page like this:
> Ord_Num OC020 OC030
> -- --- --
--
> ---
> 520208 2004-10-01 08:22:00.000
> 2004-10-01 08:25:00.000
> 520273 2004-10-01 08:13:00.000
> 2004-10-01 08:16:00.000
> 520292 2004-10-01 08:04:00.000
> 2004-10-01 08:06:00.000
> 520324 2004-10-01 08:30:00.000
> 2004-10-01 08:33:00.000
> 520388 2004-10-01 08:27:00.000
> 2004-10-01 08:31:00.000
> 520472 2004-10-01 08:26:00.000
> 2004-10-01 08:30:00.000
> The problem is that I don't know at design time what the operations are
> all
> going to be. Here is some SQL to set up the problem:
> Use Pubs
> go
> Create Table ProcessControl (Ord_Num nvarchar(12), OpCode nvarchar(12),
> PlannedStartDT datetime)
> go
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520324', 'PR120', 'Oct 1 2004 8:52AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520388', 'OC020', 'Oct 1 2004 8:28AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520388', 'OC030', 'Oct 1 2004 8:32AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520388', 'PR010', 'Oct 1 2004 8:38AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520388', 'PR020', 'Oct 1 2004 8:38AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520388', 'PR120', 'Oct 1 2004 8:38AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520388', 'OC020', 'Oct 1 2004 8:27AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520388', 'OC030', 'Oct 1 2004 8:31AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520388', 'PR010', 'Oct 1 2004 8:37AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520388', 'PR020', 'Oct 1 2004 8:37AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520388', 'PR120', 'Oct 1 2004 8:37AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520472', 'OC020', 'Oct 1 2004 8:26AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520472', 'OC030', 'Oct 1 2004 8:30AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520472', 'PR010', 'Oct 1 2004 8:33AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520472', 'PR020', 'Oct 1 2004 8:33AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520472', 'PR120', 'Oct 1 2004 8:33AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520472', 'PR130', 'Oct 1 2004 8:53AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520529', 'OC020', 'Oct 1 2004 8:25AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520529', 'OC030', 'Oct 1 2004 8:30AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520529', 'PR010', 'Oct 1 2004 8:34AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520529', 'PR020', 'Oct 1 2004 8:34AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520529', 'PR120', 'Oct 1 2004 8:34AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520529', 'PR130', 'Oct 1 2004 8:50AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520658', 'OC020', 'Oct 1 2004 8:09AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520658', 'OC030', 'Oct 1 2004 8:10AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520658', 'PR010', 'Oct 1 2004 8:15AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520658', 'PR020', 'Oct 1 2004 8:15AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520658', 'PR120', 'Oct 1 2004 8:41AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520840', 'OC020', 'Oct 1 2004 8:12AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520840', 'OC030', 'Oct 1 2004 8:15AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520840', 'PR010', 'Oct 1 2004 8:22AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520840', 'PR020', 'Oct 1 2004 8:22AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520840', 'PR120', 'Oct 1 2004 8:49AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520860', 'OC020', 'Oct 1 2004 8:01AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520860', 'OC030', 'Oct 1 2004 8:02AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520860', 'PR010', 'Oct 1 2004 8:10AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520860', 'PR020', 'Oct 1 2004 8:10AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520860', 'PR120', 'Oct 1 2004 8:10AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520900', 'OC020', 'Oct 1 2004 8:02AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520900', 'OC030', 'Oct 1 2004 8:03AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520900', 'PR010', 'Oct 1 2004 8:10AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520900', 'PR020', 'Oct 1 2004 8:10AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521056', 'OC020', 'Oct 1 2004 8:07AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521056', 'OC030', 'Oct 1 2004 8:08AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521056', 'PR010', 'Oct 1 2004 8:18AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521056', 'PR020', 'Oct 1 2004 8:18AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521056', 'PR120', 'Oct 1 2004 8:21AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521056', 'OC020', 'Oct 1 2004 8:06AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521056', 'OC030', 'Oct 1 2004 8:07AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521056', 'PR010', 'Oct 1 2004 8:16AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521056', 'PR020', 'Oct 1 2004 8:16AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521056', 'PR120', 'Oct 1 2004 8:55AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521103', 'OC020', 'Oct 1 2004 8:08AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521103', 'OC030', 'Oct 1 2004 8:10AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521103', 'PR010', 'Oct 1 2004 8:16AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521103', 'PR020', 'Oct 1 2004 8:16AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521144', 'OC020', 'Oct 1 2004 8:21AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521144', 'OC030', 'Oct 1 2004 8:25AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521144', 'PR010', 'Oct 1 2004 8:34AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521144', 'PR020', 'Oct 1 2004 8:34AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521144', 'PR120', 'Oct 1 2004 8:34AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521144', 'PR130', 'Oct 1 2004 8:39AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521144', 'PR140', 'Oct 1 2004 8:50AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521144', 'PR150', 'Oct 1 2004 8:51AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521163', 'OC020', 'Oct 1 2004 8:03AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521163', 'OC030', 'Oct 1 2004 8:04AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521163', 'PR010', 'Oct 1 2004 8:16AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521163', 'PR020', 'Oct 1 2004 8:16AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521163', 'PR120', 'Oct 1 2004 8:16AM' )
> go
> I get nice results with the query below, but again, at design time I don't
> know how many operations there will be or what they are.
>
> Select Ord_Num,
> OC020 = min(case when Opcode = 'oc020' then PlannedStartDT else NULL end),
> OC030 = min(case when Opcode = 'oc030' then PlannedStartDT else NULL end),
> PR010 = min(case when Opcode = 'PR010' then PlannedStartDT else NULL end),
> PR020 = min(case when Opcode = 'PR020' then PlannedStartDT else NULL end),
> PR120 = min(case when Opcode = 'PR120' then PlannedStartDT else NULL end),
> PR130 = min(case when Opcode = 'PR130' then PlannedStartDT else NULL end),
> PR140 = min(case when Opcode = 'PR140' then PlannedStartDT else NULL end),
> PR150 = min(case when Opcode = 'PR150' then PlannedStartDT else NULL end)
> From ProcessControl
> Group By Ord_Num
> Is there any solution to this that does not require dynamic SQL?
> Thanks.
>
> --
> Daniel Wilson
> Senior Software Solutions Developer
> Embtrak Development Team
> http://www.Embtrak.com
> DVBrown Company
>
across the page like this:
Ord_Num OC020 OC030
-- --- --
---
520208 2004-10-01 08:22:00.000
2004-10-01 08:25:00.000
520273 2004-10-01 08:13:00.000
2004-10-01 08:16:00.000
520292 2004-10-01 08:04:00.000
2004-10-01 08:06:00.000
520324 2004-10-01 08:30:00.000
2004-10-01 08:33:00.000
520388 2004-10-01 08:27:00.000
2004-10-01 08:31:00.000
520472 2004-10-01 08:26:00.000
2004-10-01 08:30:00.000
The problem is that I don't know at design time what the operations are all
going to be. Here is some SQL to set up the problem:
Use Pubs
go
Create Table ProcessControl (Ord_Num nvarchar(12), OpCode nvarchar(12),
PlannedStartDT datetime)
go
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520324', 'PR120', 'Oct 1 2004 8:52AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520388', 'OC020', 'Oct 1 2004 8:28AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520388', 'OC030', 'Oct 1 2004 8:32AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520388', 'PR010', 'Oct 1 2004 8:38AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520388', 'PR020', 'Oct 1 2004 8:38AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520388', 'PR120', 'Oct 1 2004 8:38AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520388', 'OC020', 'Oct 1 2004 8:27AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520388', 'OC030', 'Oct 1 2004 8:31AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520388', 'PR010', 'Oct 1 2004 8:37AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520388', 'PR020', 'Oct 1 2004 8:37AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520388', 'PR120', 'Oct 1 2004 8:37AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520472', 'OC020', 'Oct 1 2004 8:26AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520472', 'OC030', 'Oct 1 2004 8:30AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520472', 'PR010', 'Oct 1 2004 8:33AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520472', 'PR020', 'Oct 1 2004 8:33AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520472', 'PR120', 'Oct 1 2004 8:33AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520472', 'PR130', 'Oct 1 2004 8:53AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520529', 'OC020', 'Oct 1 2004 8:25AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520529', 'OC030', 'Oct 1 2004 8:30AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520529', 'PR010', 'Oct 1 2004 8:34AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520529', 'PR020', 'Oct 1 2004 8:34AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520529', 'PR120', 'Oct 1 2004 8:34AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520529', 'PR130', 'Oct 1 2004 8:50AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520658', 'OC020', 'Oct 1 2004 8:09AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520658', 'OC030', 'Oct 1 2004 8:10AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520658', 'PR010', 'Oct 1 2004 8:15AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520658', 'PR020', 'Oct 1 2004 8:15AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520658', 'PR120', 'Oct 1 2004 8:41AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520840', 'OC020', 'Oct 1 2004 8:12AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520840', 'OC030', 'Oct 1 2004 8:15AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520840', 'PR010', 'Oct 1 2004 8:22AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520840', 'PR020', 'Oct 1 2004 8:22AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520840', 'PR120', 'Oct 1 2004 8:49AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520860', 'OC020', 'Oct 1 2004 8:01AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520860', 'OC030', 'Oct 1 2004 8:02AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520860', 'PR010', 'Oct 1 2004 8:10AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520860', 'PR020', 'Oct 1 2004 8:10AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520860', 'PR120', 'Oct 1 2004 8:10AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520900', 'OC020', 'Oct 1 2004 8:02AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520900', 'OC030', 'Oct 1 2004 8:03AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520900', 'PR010', 'Oct 1 2004 8:10AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520900', 'PR020', 'Oct 1 2004 8:10AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('521056', 'OC020', 'Oct 1 2004 8:07AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('521056', 'OC030', 'Oct 1 2004 8:08AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('521056', 'PR010', 'Oct 1 2004 8:18AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('521056', 'PR020', 'Oct 1 2004 8:18AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('521056', 'PR120', 'Oct 1 2004 8:21AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('521056', 'OC020', 'Oct 1 2004 8:06AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('521056', 'OC030', 'Oct 1 2004 8:07AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('521056', 'PR010', 'Oct 1 2004 8:16AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('521056', 'PR020', 'Oct 1 2004 8:16AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('521056', 'PR120', 'Oct 1 2004 8:55AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('521103', 'OC020', 'Oct 1 2004 8:08AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('521103', 'OC030', 'Oct 1 2004 8:10AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('521103', 'PR010', 'Oct 1 2004 8:16AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('521103', 'PR020', 'Oct 1 2004 8:16AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('521144', 'OC020', 'Oct 1 2004 8:21AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('521144', 'OC030', 'Oct 1 2004 8:25AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('521144', 'PR010', 'Oct 1 2004 8:34AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('521144', 'PR020', 'Oct 1 2004 8:34AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('521144', 'PR120', 'Oct 1 2004 8:34AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('521144', 'PR130', 'Oct 1 2004 8:39AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('521144', 'PR140', 'Oct 1 2004 8:50AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('521144', 'PR150', 'Oct 1 2004 8:51AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('521163', 'OC020', 'Oct 1 2004 8:03AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('521163', 'OC030', 'Oct 1 2004 8:04AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('521163', 'PR010', 'Oct 1 2004 8:16AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('521163', 'PR020', 'Oct 1 2004 8:16AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('521163', 'PR120', 'Oct 1 2004 8:16AM' )
go
I get nice results with the query below, but again, at design time I don't
know how many operations there will be or what they are.
Select Ord_Num,
OC020 = min(case when Opcode = 'oc020' then PlannedStartDT else NULL end),
OC030 = min(case when Opcode = 'oc030' then PlannedStartDT else NULL end),
PR010 = min(case when Opcode = 'PR010' then PlannedStartDT else NULL end),
PR020 = min(case when Opcode = 'PR020' then PlannedStartDT else NULL end),
PR120 = min(case when Opcode = 'PR120' then PlannedStartDT else NULL end),
PR130 = min(case when Opcode = 'PR130' then PlannedStartDT else NULL end),
PR140 = min(case when Opcode = 'PR140' then PlannedStartDT else NULL end),
PR150 = min(case when Opcode = 'PR150' then PlannedStartDT else NULL end)
From ProcessControl
Group By Ord_Num
Is there any solution to this that does not require dynamic SQL?
Thanks.
Daniel Wilson
Senior Software Solutions Developer
Embtrak Development Team
http://www.Embtrak.com
DVBrown CompanyHi
You may want to look at:
http://www.windowsitpro.com/SQLServ...5608/15608.html
but it also uses dynamic SQL.
John
"Daniel Wilson" <d.wilson@.embtrak.com> wrote in message
news:OTnp$MEHFHA.560@.TK2MSFTNGP12.phx.gbl...
>I need to write a report that displays the time each operation was started
> across the page like this:
> Ord_Num OC020 OC030
> -- --- --
--
> ---
> 520208 2004-10-01 08:22:00.000
> 2004-10-01 08:25:00.000
> 520273 2004-10-01 08:13:00.000
> 2004-10-01 08:16:00.000
> 520292 2004-10-01 08:04:00.000
> 2004-10-01 08:06:00.000
> 520324 2004-10-01 08:30:00.000
> 2004-10-01 08:33:00.000
> 520388 2004-10-01 08:27:00.000
> 2004-10-01 08:31:00.000
> 520472 2004-10-01 08:26:00.000
> 2004-10-01 08:30:00.000
> The problem is that I don't know at design time what the operations are
> all
> going to be. Here is some SQL to set up the problem:
> Use Pubs
> go
> Create Table ProcessControl (Ord_Num nvarchar(12), OpCode nvarchar(12),
> PlannedStartDT datetime)
> go
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520324', 'PR120', 'Oct 1 2004 8:52AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520388', 'OC020', 'Oct 1 2004 8:28AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520388', 'OC030', 'Oct 1 2004 8:32AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520388', 'PR010', 'Oct 1 2004 8:38AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520388', 'PR020', 'Oct 1 2004 8:38AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520388', 'PR120', 'Oct 1 2004 8:38AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520388', 'OC020', 'Oct 1 2004 8:27AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520388', 'OC030', 'Oct 1 2004 8:31AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520388', 'PR010', 'Oct 1 2004 8:37AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520388', 'PR020', 'Oct 1 2004 8:37AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520388', 'PR120', 'Oct 1 2004 8:37AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520472', 'OC020', 'Oct 1 2004 8:26AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520472', 'OC030', 'Oct 1 2004 8:30AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520472', 'PR010', 'Oct 1 2004 8:33AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520472', 'PR020', 'Oct 1 2004 8:33AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520472', 'PR120', 'Oct 1 2004 8:33AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520472', 'PR130', 'Oct 1 2004 8:53AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520529', 'OC020', 'Oct 1 2004 8:25AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520529', 'OC030', 'Oct 1 2004 8:30AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520529', 'PR010', 'Oct 1 2004 8:34AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520529', 'PR020', 'Oct 1 2004 8:34AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520529', 'PR120', 'Oct 1 2004 8:34AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520529', 'PR130', 'Oct 1 2004 8:50AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520658', 'OC020', 'Oct 1 2004 8:09AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520658', 'OC030', 'Oct 1 2004 8:10AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520658', 'PR010', 'Oct 1 2004 8:15AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520658', 'PR020', 'Oct 1 2004 8:15AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520658', 'PR120', 'Oct 1 2004 8:41AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520840', 'OC020', 'Oct 1 2004 8:12AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520840', 'OC030', 'Oct 1 2004 8:15AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520840', 'PR010', 'Oct 1 2004 8:22AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520840', 'PR020', 'Oct 1 2004 8:22AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520840', 'PR120', 'Oct 1 2004 8:49AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520860', 'OC020', 'Oct 1 2004 8:01AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520860', 'OC030', 'Oct 1 2004 8:02AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520860', 'PR010', 'Oct 1 2004 8:10AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520860', 'PR020', 'Oct 1 2004 8:10AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520860', 'PR120', 'Oct 1 2004 8:10AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520900', 'OC020', 'Oct 1 2004 8:02AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520900', 'OC030', 'Oct 1 2004 8:03AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520900', 'PR010', 'Oct 1 2004 8:10AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520900', 'PR020', 'Oct 1 2004 8:10AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521056', 'OC020', 'Oct 1 2004 8:07AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521056', 'OC030', 'Oct 1 2004 8:08AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521056', 'PR010', 'Oct 1 2004 8:18AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521056', 'PR020', 'Oct 1 2004 8:18AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521056', 'PR120', 'Oct 1 2004 8:21AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521056', 'OC020', 'Oct 1 2004 8:06AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521056', 'OC030', 'Oct 1 2004 8:07AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521056', 'PR010', 'Oct 1 2004 8:16AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521056', 'PR020', 'Oct 1 2004 8:16AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521056', 'PR120', 'Oct 1 2004 8:55AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521103', 'OC020', 'Oct 1 2004 8:08AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521103', 'OC030', 'Oct 1 2004 8:10AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521103', 'PR010', 'Oct 1 2004 8:16AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521103', 'PR020', 'Oct 1 2004 8:16AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521144', 'OC020', 'Oct 1 2004 8:21AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521144', 'OC030', 'Oct 1 2004 8:25AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521144', 'PR010', 'Oct 1 2004 8:34AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521144', 'PR020', 'Oct 1 2004 8:34AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521144', 'PR120', 'Oct 1 2004 8:34AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521144', 'PR130', 'Oct 1 2004 8:39AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521144', 'PR140', 'Oct 1 2004 8:50AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521144', 'PR150', 'Oct 1 2004 8:51AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521163', 'OC020', 'Oct 1 2004 8:03AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521163', 'OC030', 'Oct 1 2004 8:04AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521163', 'PR010', 'Oct 1 2004 8:16AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521163', 'PR020', 'Oct 1 2004 8:16AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521163', 'PR120', 'Oct 1 2004 8:16AM' )
> go
> I get nice results with the query below, but again, at design time I don't
> know how many operations there will be or what they are.
>
> Select Ord_Num,
> OC020 = min(case when Opcode = 'oc020' then PlannedStartDT else NULL end),
> OC030 = min(case when Opcode = 'oc030' then PlannedStartDT else NULL end),
> PR010 = min(case when Opcode = 'PR010' then PlannedStartDT else NULL end),
> PR020 = min(case when Opcode = 'PR020' then PlannedStartDT else NULL end),
> PR120 = min(case when Opcode = 'PR120' then PlannedStartDT else NULL end),
> PR130 = min(case when Opcode = 'PR130' then PlannedStartDT else NULL end),
> PR140 = min(case when Opcode = 'PR140' then PlannedStartDT else NULL end),
> PR150 = min(case when Opcode = 'PR150' then PlannedStartDT else NULL end)
> From ProcessControl
> Group By Ord_Num
> Is there any solution to this that does not require dynamic SQL?
> Thanks.
>
> --
> Daniel Wilson
> Senior Software Solutions Developer
> Embtrak Development Team
> http://www.Embtrak.com
> DVBrown Company
>|||You might want to check out www.rac4sql.net for a solution to your problem.
Also, check out
http://www.windowsitpro.com/Windows...54/pg/2/2.html.
"Daniel Wilson" <d.wilson@.embtrak.com> wrote in message
news:OTnp$MEHFHA.560@.TK2MSFTNGP12.phx.gbl...
>I need to write a report that displays the time each operation was started
> across the page like this:
> Ord_Num OC020 OC030
> -- --- --
--
> ---
> 520208 2004-10-01 08:22:00.000
> 2004-10-01 08:25:00.000
> 520273 2004-10-01 08:13:00.000
> 2004-10-01 08:16:00.000
> 520292 2004-10-01 08:04:00.000
> 2004-10-01 08:06:00.000
> 520324 2004-10-01 08:30:00.000
> 2004-10-01 08:33:00.000
> 520388 2004-10-01 08:27:00.000
> 2004-10-01 08:31:00.000
> 520472 2004-10-01 08:26:00.000
> 2004-10-01 08:30:00.000
> The problem is that I don't know at design time what the operations are
> all
> going to be. Here is some SQL to set up the problem:
> Use Pubs
> go
> Create Table ProcessControl (Ord_Num nvarchar(12), OpCode nvarchar(12),
> PlannedStartDT datetime)
> go
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520324', 'PR120', 'Oct 1 2004 8:52AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520388', 'OC020', 'Oct 1 2004 8:28AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520388', 'OC030', 'Oct 1 2004 8:32AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520388', 'PR010', 'Oct 1 2004 8:38AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520388', 'PR020', 'Oct 1 2004 8:38AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520388', 'PR120', 'Oct 1 2004 8:38AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520388', 'OC020', 'Oct 1 2004 8:27AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520388', 'OC030', 'Oct 1 2004 8:31AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520388', 'PR010', 'Oct 1 2004 8:37AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520388', 'PR020', 'Oct 1 2004 8:37AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520388', 'PR120', 'Oct 1 2004 8:37AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520472', 'OC020', 'Oct 1 2004 8:26AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520472', 'OC030', 'Oct 1 2004 8:30AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520472', 'PR010', 'Oct 1 2004 8:33AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520472', 'PR020', 'Oct 1 2004 8:33AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520472', 'PR120', 'Oct 1 2004 8:33AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520472', 'PR130', 'Oct 1 2004 8:53AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520529', 'OC020', 'Oct 1 2004 8:25AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520529', 'OC030', 'Oct 1 2004 8:30AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520529', 'PR010', 'Oct 1 2004 8:34AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520529', 'PR020', 'Oct 1 2004 8:34AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520529', 'PR120', 'Oct 1 2004 8:34AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520529', 'PR130', 'Oct 1 2004 8:50AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520658', 'OC020', 'Oct 1 2004 8:09AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520658', 'OC030', 'Oct 1 2004 8:10AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520658', 'PR010', 'Oct 1 2004 8:15AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520658', 'PR020', 'Oct 1 2004 8:15AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520658', 'PR120', 'Oct 1 2004 8:41AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520840', 'OC020', 'Oct 1 2004 8:12AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520840', 'OC030', 'Oct 1 2004 8:15AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520840', 'PR010', 'Oct 1 2004 8:22AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520840', 'PR020', 'Oct 1 2004 8:22AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520840', 'PR120', 'Oct 1 2004 8:49AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520860', 'OC020', 'Oct 1 2004 8:01AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520860', 'OC030', 'Oct 1 2004 8:02AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520860', 'PR010', 'Oct 1 2004 8:10AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520860', 'PR020', 'Oct 1 2004 8:10AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520860', 'PR120', 'Oct 1 2004 8:10AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520900', 'OC020', 'Oct 1 2004 8:02AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520900', 'OC030', 'Oct 1 2004 8:03AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520900', 'PR010', 'Oct 1 2004 8:10AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520900', 'PR020', 'Oct 1 2004 8:10AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521056', 'OC020', 'Oct 1 2004 8:07AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521056', 'OC030', 'Oct 1 2004 8:08AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521056', 'PR010', 'Oct 1 2004 8:18AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521056', 'PR020', 'Oct 1 2004 8:18AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521056', 'PR120', 'Oct 1 2004 8:21AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521056', 'OC020', 'Oct 1 2004 8:06AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521056', 'OC030', 'Oct 1 2004 8:07AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521056', 'PR010', 'Oct 1 2004 8:16AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521056', 'PR020', 'Oct 1 2004 8:16AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521056', 'PR120', 'Oct 1 2004 8:55AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521103', 'OC020', 'Oct 1 2004 8:08AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521103', 'OC030', 'Oct 1 2004 8:10AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521103', 'PR010', 'Oct 1 2004 8:16AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521103', 'PR020', 'Oct 1 2004 8:16AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521144', 'OC020', 'Oct 1 2004 8:21AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521144', 'OC030', 'Oct 1 2004 8:25AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521144', 'PR010', 'Oct 1 2004 8:34AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521144', 'PR020', 'Oct 1 2004 8:34AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521144', 'PR120', 'Oct 1 2004 8:34AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521144', 'PR130', 'Oct 1 2004 8:39AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521144', 'PR140', 'Oct 1 2004 8:50AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521144', 'PR150', 'Oct 1 2004 8:51AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521163', 'OC020', 'Oct 1 2004 8:03AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521163', 'OC030', 'Oct 1 2004 8:04AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521163', 'PR010', 'Oct 1 2004 8:16AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521163', 'PR020', 'Oct 1 2004 8:16AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521163', 'PR120', 'Oct 1 2004 8:16AM' )
> go
> I get nice results with the query below, but again, at design time I don't
> know how many operations there will be or what they are.
>
> Select Ord_Num,
> OC020 = min(case when Opcode = 'oc020' then PlannedStartDT else NULL end),
> OC030 = min(case when Opcode = 'oc030' then PlannedStartDT else NULL end),
> PR010 = min(case when Opcode = 'PR010' then PlannedStartDT else NULL end),
> PR020 = min(case when Opcode = 'PR020' then PlannedStartDT else NULL end),
> PR120 = min(case when Opcode = 'PR120' then PlannedStartDT else NULL end),
> PR130 = min(case when Opcode = 'PR130' then PlannedStartDT else NULL end),
> PR140 = min(case when Opcode = 'PR140' then PlannedStartDT else NULL end),
> PR150 = min(case when Opcode = 'PR150' then PlannedStartDT else NULL end)
> From ProcessControl
> Group By Ord_Num
> Is there any solution to this that does not require dynamic SQL?
> Thanks.
>
> --
> Daniel Wilson
> Senior Software Solutions Developer
> Embtrak Development Team
> http://www.Embtrak.com
> DVBrown Company
>
Dynamic PIVOT quey challange
Hi
I have following Table A In which values of LotNo field is not fixed so we cannot write these values to our PIVOT clause in Select statement. Now can any one provide me sucha a query which can generate this PIVOT result in Table B. Not there is a distnict list of Lotno. is availble from a view.
Table A
Table B
So I want to write a select query wth PIVOT clause which takes Lotno. which is not fixed values (As given in Adventureworks sample). ThisLotno. is based on distinct values from a list of LotNo. which is availble in another Table. Please anyone help me.
Nilkanth Desai
You can use the following querys
1. Using UNION
SELECT
'Pcs',
*
FROM
(SELECT LotNo,Pcs from TableA) Master
PIVOT
(
Sum(Pcs) for LotNo in ([12],[13],[17],[18])
) AS pvt2
Union All
SELECT
'Wt',
*
FROM
(SELECT LotNo,Wt from TableA) Master
PIVOT
(
Sum(Wt) for LotNo in ([12],[13],[17],[18])
) AS pvt2
2. Merging data into Single Table then applying the Pivot
Declare @.Table Table
(
LotNo int,
Type varchar(10),
Value float
)
Insert Into @.Table Select Lotno,'Pcs', Pcs From TableA;
Insert Into @.Table Select LotNo,'Wt', Wt From TableA;
SELECT
*
FROM
(SELECT LotNo,Type,Value from @.Table) Master
PIVOT
(
Sum(Value) for LotNo in ([12],[13],[17],[18])
) AS pvt2
To generate this query dynamically you can see my earlier post http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1006316&SiteID=1
|||Here you go:
-- Create a Temporary Table holding all Values As LotNo, Group, Value
SELECT LotNo AS LotNo, 'Pcs' as Grp, Pcs as Value INTO #T1 FROM TableA
UNION
SELECT LotNo AS LotNo, 'Weight' as Grp, Weight as Value FROM TableA;
-- Declare a Table holding all Values of LotNo
DECLARE @.T2 AS TABLE(LotNo INT);
INSERT INTO @.T2 SELECT DISTINCT LotNo FROM #T1;
-- Construct the Columns for the SELECT and PIVOT-Clause
DECLARE
@.cols AS NVARCHAR(MAX),
@.lotno AS INT,
@.sql AS NVARCHAR(MAX);
SET @.lotno = (SELECT MIN(LotNo) FROM @.T2);
SET @.cols = '';
WHILE @.lotno IS NOT NULL
BEGIN
SET @.cols = @.cols + ', ' + QUOTENAME(@.lotno)
SET @.lotno = (SELECT MIN(LotNo) FROM @.T2 WHERE LotNo > @.lotno)
END
SET @.cols = SUBSTRING(@.cols, 3, LEN(@.cols));
-- Construct the SQL Statement
SET @.sql = 'SELECT Grp, ' + @.cols + '
FROM #T1
PIVOT(MAX(Value)
FOR LotNo IN (' + @.cols + ')) AS P'
-- Run it
EXEC sp_executesql @.sql;
-- Cleanup
DROP TABLE #T1;
Thursday, March 22, 2012
Dynamic Order By!
Hi all, I am new to store procedures and am trying to write one with a
dynamic or..
Below is my feeble attempt if anyone can point me in the right direction
that would be great.
CREATE PROCEDURE get_event_list
@.order_column INT = null
@.order_mode = varchar(4)
AS
BEGIN
SELECT event_code,event_title,event_topic,event_category, event_id FROM
event
ORDER BY
//select a table column based on a numeric value in @.order_column
parameter
//Then determine orientation by value in @.order_mode
//If order_mode = "asc" then the query is to be order ASC and vica versa.
END
AJ
CREATE PROCEDURE get_event_list
(@.order_column INT = NULL,
@.order_mode VARCHAR(4))
AS
BEGIN
SELECT event_code, event_title, event_topic, event_category, event_id
FROM Event
ORDER BY
CASE WHEN @.order_mode='ASC'
AND @.order_column=1 THEN event_code END ASC,
CASE WHEN @.order_mode='ASC'
AND @.order_column=2 THEN event_title END ASC,
...
CASE WHEN @.order_mode='DESC'
AND @.order_column=1 THEN event_code END DESC,
CASE WHEN @.order_mode='DESC'
AND @.order_column=2 THEN event_title END DESC,
...
END
David Portas
SQL Server MVP
|||What I also should say is that you'll probably find you will get better
performance by including all the possible SELECTs in your SP, enclosing each
in an IF statement. If you prefer a single query solution then you can use
the CASE method I have given, or you could try using Dynamic SQL.
David Portas
SQL Server MVP
|||Anthony Judd wrote:
> Hi all, I am new to store procedures and am trying to write one with a
> dynamic or..
> Below is my feeble attempt if anyone can point me in the right direction
> that would be great.
Here is some example I have. I know it is not a fancy solution, but it
is flexible...
CREATE PROCEDURE SomeList
@.akt CHAR(1) = '1',
@.OrderBy VARCHAR(50) = 'NazivUcionice',
@.OrderSort VARCHAR(10) = 'ASC'
AS
DECLARE @.SQLstmt varchar (1000)
SELECT @.SQLstmt =
'SELECT U.ID AS ElementID,
U.Naziv
U.Vrsta,
U.Tip,
U.Vlasnistvo,
U.Aktivna,
U.Broj_mjesta
FROM U
WHERE U.Aktivna=' +@.akt + '
ORDER BY ' + @.OrderBy + ' ' + @.OrderSort
EXEC (@.SQLstmt)
GO
Tomislav Bilic
ICQ: 1824223
http://www.escapestudio.net
sql
dynamic or..
Below is my feeble attempt if anyone can point me in the right direction
that would be great.
CREATE PROCEDURE get_event_list
@.order_column INT = null
@.order_mode = varchar(4)
AS
BEGIN
SELECT event_code,event_title,event_topic,event_category, event_id FROM
event
ORDER BY
//select a table column based on a numeric value in @.order_column
parameter
//Then determine orientation by value in @.order_mode
//If order_mode = "asc" then the query is to be order ASC and vica versa.
END
AJ
CREATE PROCEDURE get_event_list
(@.order_column INT = NULL,
@.order_mode VARCHAR(4))
AS
BEGIN
SELECT event_code, event_title, event_topic, event_category, event_id
FROM Event
ORDER BY
CASE WHEN @.order_mode='ASC'
AND @.order_column=1 THEN event_code END ASC,
CASE WHEN @.order_mode='ASC'
AND @.order_column=2 THEN event_title END ASC,
...
CASE WHEN @.order_mode='DESC'
AND @.order_column=1 THEN event_code END DESC,
CASE WHEN @.order_mode='DESC'
AND @.order_column=2 THEN event_title END DESC,
...
END
David Portas
SQL Server MVP
|||What I also should say is that you'll probably find you will get better
performance by including all the possible SELECTs in your SP, enclosing each
in an IF statement. If you prefer a single query solution then you can use
the CASE method I have given, or you could try using Dynamic SQL.
David Portas
SQL Server MVP
|||Anthony Judd wrote:
> Hi all, I am new to store procedures and am trying to write one with a
> dynamic or..
> Below is my feeble attempt if anyone can point me in the right direction
> that would be great.
Here is some example I have. I know it is not a fancy solution, but it
is flexible...
CREATE PROCEDURE SomeList
@.akt CHAR(1) = '1',
@.OrderBy VARCHAR(50) = 'NazivUcionice',
@.OrderSort VARCHAR(10) = 'ASC'
AS
DECLARE @.SQLstmt varchar (1000)
SELECT @.SQLstmt =
'SELECT U.ID AS ElementID,
U.Naziv
U.Vrsta,
U.Tip,
U.Vlasnistvo,
U.Aktivna,
U.Broj_mjesta
FROM U
WHERE U.Aktivna=' +@.akt + '
ORDER BY ' + @.OrderBy + ' ' + @.OrderSort
EXEC (@.SQLstmt)
GO
Tomislav Bilic
ICQ: 1824223
http://www.escapestudio.net
sql
Dynamic Order By!
Hi all, I am new to store procedures and am trying to write one with a
dynamic or..
Below is my feeble attempt if anyone can point me in the right direction
that would be great.
CREATE PROCEDURE get_event_list
@.order_column INT = null
@.order_mode = varchar(4)
AS
BEGIN
SELECT event_code,event_title,event_topic,event_category,event_id FROM
event
ORDER BY
//select a table column based on a numeric value in @.order_column
parameter
//Then determine orientation by value in @.order_mode
//If order_mode = "asc" then the query is to be order ASC and vica versa.
END
AJCREATE PROCEDURE get_event_list
(@.order_column INT = NULL,
@.order_mode VARCHAR(4))
AS
BEGIN
SELECT event_code, event_title, event_topic, event_category, event_id
FROM Event
ORDER BY
CASE WHEN @.order_mode='ASC'
AND @.order_column=1 THEN event_code END ASC,
CASE WHEN @.order_mode='ASC'
AND @.order_column=2 THEN event_title END ASC,
...
CASE WHEN @.order_mode='DESC'
AND @.order_column=1 THEN event_code END DESC,
CASE WHEN @.order_mode='DESC'
AND @.order_column=2 THEN event_title END DESC,
...
END
--
David Portas
SQL Server MVP
--|||What I also should say is that you'll probably find you will get better
performance by including all the possible SELECTs in your SP, enclosing each
in an IF statement. If you prefer a single query solution then you can use
the CASE method I have given, or you could try using Dynamic SQL.
--
David Portas
SQL Server MVP
--|||Hey guys,
How do I create views in MSSQL where the source is coming from an Oracle
table?
Thanks.
neil|||Anthony Judd wrote:
> Hi all, I am new to store procedures and am trying to write one with a
> dynamic or..
> Below is my feeble attempt if anyone can point me in the right direction
> that would be great.
Here is some example I have. I know it is not a fancy solution, but it
is flexible...
CREATE PROCEDURE SomeList
@.akt CHAR(1) = '1',
@.OrderBy VARCHAR(50) = 'NazivUcionice',
@.OrderSort VARCHAR(10) = 'ASC'
AS
DECLARE @.SQLstmt varchar (1000)
SELECT @.SQLstmt = 'SELECT U.ID AS ElementID,
U.Naziv
U.Vrsta,
U.Tip,
U.Vlasnistvo,
U.Aktivna,
U.Broj_mjesta
FROM U
WHERE U.Aktivna=' +@.akt + '
ORDER BY ' + @.OrderBy + ' ' + @.OrderSort
EXEC (@.SQLstmt)
GO
--
Tomislav Bilic
ICQ: 1824223
http://www.escapestudio.net|||Using the Oracle OLEDB Provider, either create a linked server or use
OPENROWSET and specify the connection string.
--
David Portas
SQL Server MVP
--
dynamic or..
Below is my feeble attempt if anyone can point me in the right direction
that would be great.
CREATE PROCEDURE get_event_list
@.order_column INT = null
@.order_mode = varchar(4)
AS
BEGIN
SELECT event_code,event_title,event_topic,event_category,event_id FROM
event
ORDER BY
//select a table column based on a numeric value in @.order_column
parameter
//Then determine orientation by value in @.order_mode
//If order_mode = "asc" then the query is to be order ASC and vica versa.
END
AJCREATE PROCEDURE get_event_list
(@.order_column INT = NULL,
@.order_mode VARCHAR(4))
AS
BEGIN
SELECT event_code, event_title, event_topic, event_category, event_id
FROM Event
ORDER BY
CASE WHEN @.order_mode='ASC'
AND @.order_column=1 THEN event_code END ASC,
CASE WHEN @.order_mode='ASC'
AND @.order_column=2 THEN event_title END ASC,
...
CASE WHEN @.order_mode='DESC'
AND @.order_column=1 THEN event_code END DESC,
CASE WHEN @.order_mode='DESC'
AND @.order_column=2 THEN event_title END DESC,
...
END
--
David Portas
SQL Server MVP
--|||What I also should say is that you'll probably find you will get better
performance by including all the possible SELECTs in your SP, enclosing each
in an IF statement. If you prefer a single query solution then you can use
the CASE method I have given, or you could try using Dynamic SQL.
--
David Portas
SQL Server MVP
--|||Hey guys,
How do I create views in MSSQL where the source is coming from an Oracle
table?
Thanks.
neil|||Anthony Judd wrote:
> Hi all, I am new to store procedures and am trying to write one with a
> dynamic or..
> Below is my feeble attempt if anyone can point me in the right direction
> that would be great.
Here is some example I have. I know it is not a fancy solution, but it
is flexible...
CREATE PROCEDURE SomeList
@.akt CHAR(1) = '1',
@.OrderBy VARCHAR(50) = 'NazivUcionice',
@.OrderSort VARCHAR(10) = 'ASC'
AS
DECLARE @.SQLstmt varchar (1000)
SELECT @.SQLstmt = 'SELECT U.ID AS ElementID,
U.Naziv
U.Vrsta,
U.Tip,
U.Vlasnistvo,
U.Aktivna,
U.Broj_mjesta
FROM U
WHERE U.Aktivna=' +@.akt + '
ORDER BY ' + @.OrderBy + ' ' + @.OrderSort
EXEC (@.SQLstmt)
GO
--
Tomislav Bilic
ICQ: 1824223
http://www.escapestudio.net|||Using the Oracle OLEDB Provider, either create a linked server or use
OPENROWSET and specify the connection string.
--
David Portas
SQL Server MVP
--
Subscribe to:
Posts (Atom)