Query xml field in Query Plan DMV

I anot expertin XML and need to query a value in the field "query_plan" of
the "sys.dm_exec_query_plan" DMV. The value I is the whether queries has
Parallelism value more than 0. I remeber have seen something like
sys.dm_exec_query_plan.query_plan.value ('declare namespace.......
Is there in BOL a chapter on how querying special SQL xml schemas ?
Thanks in advanceHello eliassal,
You could query the XML column in the DMV as any other XML column in SQL by
using the XQuery.
For example, I use this Statement to query the query_Plan:
select query_plan.query('declare namespace
showplan="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
/showplan:ShowPlanXML/showplan:BatchSequence/showplan:Batch') from
sys.dm_exec_query_plan(<my plan handle id> )
For more detailed information about how to query the XML date using XQuery,
please refer this article:
XQuery Against the xml Data Type
http://msdn2.microsoft.com/en-us/library/ms189075.aspx
For more detailed information about the schema of the query_plan, pleaser
refer this article:
sys.dm_exec_query_plan
http://msdn2.microsoft.com/en-us/ms189747.aspx
http://schemas.microsoft.com/sqlserver/
Hope this will be helpful!
Sincerely,
Wei Lu
Microsoft Online Community Support
========================================
==========
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscript...t/default.aspx.
========================================
==========
(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||So many thanks, I tried so many times, so many syntaxes to work with the
"RelOp" element without success
In the sql schemq it is defined as follows :
<xsd:element name="RelOp" type="shp:RelOpType" />
<xsd:element name="ParameterList" type="shp:ColumnReferenceListType"
minOccurs="0" maxOccurs="1" />
As I said yesterday, I understood in a sql article that if the
max(...RelOp/@.parelle.....) > 0 gives us an idea if the query would be
paralleize.
I am deserate!!!HELP :-)
Thanks again
"Wei Lu [MSFT]" wrote:

> Hello eliassal,
> You could query the XML column in the DMV as any other XML column in SQL b
y
> using the XQuery.
> For example, I use this Statement to query the query_Plan:
> select query_plan.query('declare namespace
> showplan="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
> /showplan:ShowPlanXML/showplan:BatchSequence/showplan:Batch') from
> sys.dm_exec_query_plan(<my plan handle id> )
> For more detailed information about how to query the XML date using XQuery
,
> please refer this article:
> XQuery Against the xml Data Type
> http://msdn2.microsoft.com/en-us/library/ms189075.aspx
> For more detailed information about the schema of the query_plan, pleaser
> refer this article:
> sys.dm_exec_query_plan
> http://msdn2.microsoft.com/en-us/ms189747.aspx
> http://schemas.microsoft.com/sqlserver/
> Hope this will be helpful!
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ========================================
==========
> Get notification to my posts through email? Please refer to
> l]
> ications.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> [url]http://msdn.microsoft.com/subscriptions/support/default.aspx." target="_blank">http://msdn.microsoft.com/subscript...t/default.aspx.
> ========================================
==========
> (This posting is provided "AS IS", with no warranties, and confers no
> rights.)
>
>|||Hello Eliassal,
I found that article. The statement should be:
select
p.*,
q.*,
cp.plan_handle
from
sys.dm_exec_cached_plans cp
cross apply sys.dm_exec_query_plan(cp.plan_handle) p
cross apply sys.dm_exec_sql_text(cp.plan_handle) as q
where
cp.cacheobjtype = 'Compiled Plan' and
p.query_plan.value('declare namespace
p="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
max(//p:RelOp/@.Parallel)', 'float') > 0
Troubleshooting Performance Problems in SQL Server 2005
https://www.microsoft.com.nsatc.net...005/tsprfprb.ms
px
Is this article you want?
Sincerely,
Wei Lu
Microsoft Online Community Support
========================================
==========
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscript...t/default.aspx.
========================================
==========
(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.|||LOVELY, so many thanks
"Wei Lu [MSFT]" wrote:

> Hello Eliassal,
> I found that article. The statement should be:
> select
> p.*,
> q.*,
> cp.plan_handle
> from
> sys.dm_exec_cached_plans cp
> cross apply sys.dm_exec_query_plan(cp.plan_handle) p
> cross apply sys.dm_exec_sql_text(cp.plan_handle) as q
> where
> cp.cacheobjtype = 'Compiled Plan' and
> p.query_plan.value('declare namespace
> p="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
> max(//p:RelOp/@.Parallel)', 'float') > 0
>
> Troubleshooting Performance Problems in SQL Server 2005
> l]
> ications.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> [url]http://msdn.microsoft.com/subscriptions/support/default.aspx." target="_blank">https://www.microsoft.com.nsatc.net...t/default.aspx.
> ========================================
==========
> (This posting is provided "AS IS", with no warranties, and confers no
> rights.)
>|||Lines: 33
X-Tomcat-ID: 34576541
MIME-Version: 1.0
Content-Type: text/plain
Content-Transfer-Encoding: 7bit
Organization: Microsoft
X-Tomcat-NG: microsoft.public.sqlserver.xml
NNTP-Posting-Host: tomcatimport2.phx.gbl 10.201.218.182
Xref: leafnode.mcse.ms microsoft.public.sqlserver.xml:1829
Hello Eliassal,
My pleasure. If you have any question, please feel free to let me know.
Sincerely,
Wei Lu
Microsoft Online Community Support
========================================
==========
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscript...t/default.aspx.
========================================
==========
(This posting is provided "AS IS", with no warranties, and confers no
rights.)sql

Query xml field in Query Plan DMV

I anot expertin XML and need to query a value in the field "query_plan" of
the "sys.dm_exec_query_plan" DMV. The value I is the whether queries has
Parallelism value more than 0. I remeber have seen something like
sys.dm_exec_query_plan.query_plan.value ('declare namespace.......
Is there in BOL a chapter on how querying special SQL xml schemas ?
Thanks in advance
Hello eliassal,
You could query the XML column in the DMV as any other XML column in SQL by
using the XQuery.
For example, I use this Statement to query the query_Plan:
select query_plan.query('declare namespace
showplan="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
/showplan:ShowPlanXML/showplan:BatchSequence/showplan:Batch') from
sys.dm_exec_query_plan(<my plan handle id>)
For more detailed information about how to query the XML date using XQuery,
please refer this article:
XQuery Against the xml Data Type
http://msdn2.microsoft.com/en-us/library/ms189075.aspx
For more detailed information about the schema of the query_plan, pleaser
refer this article:
sys.dm_exec_query_plan
http://msdn2.microsoft.com/en-us/ms189747.aspx
http://schemas.microsoft.com/sqlserver/
Hope this will be helpful!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================
(This posting is provided "AS IS", with no warranties, and confers no
rights.)
|||So many thanks, I tried so many times, so many syntaxes to work with the
"RelOp" element without success
In the sql schemq it is defined as follows :
<xsd:element name="RelOp" type="shp:RelOpType" />
<xsd:element name="ParameterList" type="shp:ColumnReferenceListType"
minOccurs="0" maxOccurs="1" />
As I said yesterday, I understood in a sql article that if the
max(...RelOp/@.parelle.....) > 0 gives us an idea if the query would be
paralleize.
I am deserate!!!HELP :-)
Thanks again
"Wei Lu [MSFT]" wrote:

> Hello eliassal,
> You could query the XML column in the DMV as any other XML column in SQL by
> using the XQuery.
> For example, I use this Statement to query the query_Plan:
> select query_plan.query('declare namespace
> showplan="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
> /showplan:ShowPlanXML/showplan:BatchSequence/showplan:Batch') from
> sys.dm_exec_query_plan(<my plan handle id>)
> For more detailed information about how to query the XML date using XQuery,
> please refer this article:
> XQuery Against the xml Data Type
> http://msdn2.microsoft.com/en-us/library/ms189075.aspx
> For more detailed information about the schema of the query_plan, pleaser
> refer this article:
> sys.dm_exec_query_plan
> http://msdn2.microsoft.com/en-us/ms189747.aspx
> http://schemas.microsoft.com/sqlserver/
> Hope this will be helpful!
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================
> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscriptions/support/default.aspx.
> ==================================================
> (This posting is provided "AS IS", with no warranties, and confers no
> rights.)
>
>
|||Hello Eliassal,
I found that article. The statement should be:
select
p.*,
q.*,
cp.plan_handle
from
sys.dm_exec_cached_plans cp
cross apply sys.dm_exec_query_plan(cp.plan_handle) p
cross apply sys.dm_exec_sql_text(cp.plan_handle) as q
where
cp.cacheobjtype = 'Compiled Plan' and
p.query_plan.value('declare namespace
p="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
max(//p:RelOp/@.Parallel)', 'float') > 0
Troubleshooting Performance Problems in SQL Server 2005
https://www.microsoft.com.nsatc.net/technet/prodtechnol/sql/2005/tsprfprb.ms
px
Is this article you want?
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================
(This posting is provided "AS IS", with no warranties, and confers no
rights.)
|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
|||LOVELY, so many thanks
"Wei Lu [MSFT]" wrote:

> Hello Eliassal,
> I found that article. The statement should be:
> select
> p.*,
> q.*,
> cp.plan_handle
> from
> sys.dm_exec_cached_plans cp
> cross apply sys.dm_exec_query_plan(cp.plan_handle) p
> cross apply sys.dm_exec_sql_text(cp.plan_handle) as q
> where
> cp.cacheobjtype = 'Compiled Plan' and
> p.query_plan.value('declare namespace
> p="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
> max(//p:RelOp/@.Parallel)', 'float') > 0
>
> Troubleshooting Performance Problems in SQL Server 2005
> https://www.microsoft.com.nsatc.net/technet/prodtechnol/sql/2005/tsprfprb.ms
> px
> Is this article you want?
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================
> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscriptions/support/default.aspx.
> ==================================================
> (This posting is provided "AS IS", with no warranties, and confers no
> rights.)
>
|||Hello Eliassal,
My pleasure. If you have any question, please feel free to let me know.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

query with user-define function

I have a table with 3 fields.
In the first field named a there are values i.e. 5
In the second field named b there are values i.e. 9
In the third field named c there are expressions i.e. a+3*b (where a,b
supposed to be the contents of the previous fields).
How can I issue a query to get back 5, 9, 32 (5+3*9)'
Many Thanks
HelenNot sure why you want to do this in SQL. Note that if dbo.foo has more than
one row, you will need to limit both queries using a WHERE clause to
identify that single row (unless the expression in c is always the same, in
which case, it shouldn't be in the table at all).
CREATE TABLE dbo.foo
(
a INT,
b INT,
c VARCHAR(32)
)
GO
SET NOCOUNT ON
GO
INSERT dbo.foo SELECT 5,9,'a+3*b'
GO
DECLARE @.sql VARCHAR(255)
SELECT @.sql = 'SELECT a,b,'+c+' FROM dbo.foo'
FROM dbo.foo
EXEC(@.sql)
GO
DROP TABLE dbo.foo
GO
"Helen" <Helen@.discussions.microsoft.com> wrote in message
news:092F2AF9-CF18-4D28-9112-E4D9D459BE79@.microsoft.com...
>I have a table with 3 fields.
> In the first field named a there are values i.e. 5
> In the second field named b there are values i.e. 9
> In the third field named c there are expressions i.e. a+3*b (where a,b
> supposed to be the contents of the previous fields).
> How can I issue a query to get back 5, 9, 32 (5+3*9)'
> Many Thanks
> Helen|||Hi,
You can have 3 solutions
1. Direct TSQL . Select a,b,(a+3*b) as c from table_name
2. Create a view. Create view v1 as Select a,b,(a+3*b) as c from table_name
and later use
select * v1
3. Use compute columns while table creation
create table cc(a int, b int, c AS (a + 3 * b))
WHILE INSERTION INSERT DATA ONLY FORM COLUMN a AND b
Thanks
Hari
SQL Server MVP
"Helen" <Helen@.discussions.microsoft.com> wrote in message
news:092F2AF9-CF18-4D28-9112-E4D9D459BE79@.microsoft.com...
>I have a table with 3 fields.
> In the first field named a there are values i.e. 5
> In the second field named b there are values i.e. 9
> In the third field named c there are expressions i.e. a+3*b (where a,b
> supposed to be the contents of the previous fields).
> How can I issue a query to get back 5, 9, 32 (5+3*9)'
> Many Thanks
> Helen

Query with user-define function

I have a table with 3 fields. In the first field named a there are values
i.e. 5
In the second field named b there are values i.e. 9
In the third field named c there are expressions i.e. a+@.q+3*b where a,b
supposed to be the contents of the previous fields, different in each row
and @.q is a variable I wound like to type each time I run the query.
I have typed:
DECLARE @.sql VARCHAR(255)
SELECT @.sql = 'SELECT a,b' + c+ ' FROM dbo.foo where Index=1 '
FROM dbo.foo where Index=1
EXEC(@.sql)
How can I write a query where @.q=7 to get back 5, 9, 39 (5+7+3*9)'
Many Thanks
HelenDECLARE @.q INT
SET @.q = 7
SELECT a,b, a + @.q + 3 *b AS c
FROM dbo.foo
where Index=1
Jacco Schalkwijk
SQL Server MVP
"Helen" <Helen@.discussions.microsoft.com> wrote in message
news:A3A0BB12-2B9D-4173-81B3-F17D30F6D59B@.microsoft.com...
>I have a table with 3 fields. In the first field named a there are values
> i.e. 5
> In the second field named b there are values i.e. 9
> In the third field named c there are expressions i.e. a+@.q+3*b where a,b
> supposed to be the contents of the previous fields, different in each row
> and @.q is a variable I wound like to type each time I run the query.
> I have typed:
> DECLARE @.sql VARCHAR(255)
> SELECT @.sql = 'SELECT a,b' + c+ ' FROM dbo.foo where Index=1 '
> FROM dbo.foo where Index=1
> EXEC(@.sql)
> How can I write a query where @.q=7 to get back 5, 9, 39 (5+7+3*9)'
> Many Thanks
> Helen
>|||I'm sorry. I didn't explain myself correctly. I mean I have this table in a
SQL Server with many rows and different function in each row. Inside the
function I would like to have a variable (@.q) which I don't know how to writ
e
so as when I query I can put a different value each time.

> "Helen" <Helen@.discussions.microsoft.com> wrote in message
> news:A3A0BB12-2B9D-4173-81B3-F17D30F6D59B@.microsoft.com...
>
>|||Hi
Maybe
CREATE TABLE foo ( [index] int not null identity(1,1), a int, b int, c
varchar(10) )
INSERT INTO Foo ( a, b, c ) SELECT 5,9,'a+@.q+3*b'
DECLARE @.sql VARCHAR(255)
SELECT @.sql = 'DECLARE @.q int SET @.q=7 SELECT a,b,' + c+ ' FROM dbo.foo
where [Index]=1'
FROM dbo.foo where [Index]=1
EXEC(@.sql)
John
"Helen" <Helen@.discussions.microsoft.com> wrote in message
news:A3A0BB12-2B9D-4173-81B3-F17D30F6D59B@.microsoft.com...
>I have a table with 3 fields. In the first field named a there are values
> i.e. 5
> In the second field named b there are values i.e. 9
> In the third field named c there are expressions i.e. a+@.q+3*b where a,b
> supposed to be the contents of the previous fields, different in each row
> and @.q is a variable I wound like to type each time I run the query.
> I have typed:
> DECLARE @.sql VARCHAR(255)
> SELECT @.sql = 'SELECT a,b' + c+ ' FROM dbo.foo where Index=1 '
> FROM dbo.foo where Index=1
> EXEC(@.sql)
> How can I write a query where @.q=7 to get back 5, 9, 39 (5+7+3*9)'
> Many Thanks
> Helen
>|||Helen,
The T-SQL infix expression evaluator here might help:
http://users.drew.edu/skass/SQL/Infix.sql.txt
If you first replace the 'a', 'b', and @.q in your expression
with their values, InFixVal should then evaluate the result.
select
a, b,
dbo. InFixVal(replace(replace(replace(c,'a','
('+str(a,19,4)+')'),'b','('+str(
b,19,4)+')'),'@.q,str(@.q,19,4)),1)
from ...
Also look here, for some examples of its use, and comments
about its limitations. It only evaluates a simple set of possible
arithmetic expressions, but it may be enough for you.
http://groups.google.com/groups?hl=...ver&qt_s=Search
Steve Kass
Drew University
"Helen" <Helen@.discussions.microsoft.com> wrote in message
news:A3A0BB12-2B9D-4173-81B3-F17D30F6D59B@.microsoft.com...
>I have a table with 3 fields. In the first field named a there are values
> i.e. 5
> In the second field named b there are values i.e. 9
> In the third field named c there are expressions i.e. a+@.q+3*b where a,b
> supposed to be the contents of the previous fields, different in each row
> and @.q is a variable I wound like to type each time I run the query.
> I have typed:
> DECLARE @.sql VARCHAR(255)
> SELECT @.sql = 'SELECT a,b' + c+ ' FROM dbo.foo where Index=1 '
> FROM dbo.foo where Index=1
> EXEC(@.sql)
> How can I write a query where @.q=7 to get back 5, 9, 39 (5+7+3*9)'
> Many Thanks
> Helen
>sql

Query with SELECT and SUBSTRING

Hello,
I'm a beginner in SQLServer and I'm trying to crite a query with a subst
ring but without success. I've got a field (String) in a table which con
tains a price formatted like that "AUD 2,000.10". I would like with a su
bstring (or something else) obtain something like "2000.10". Can somebod
y help me with that ?
Thanks a lot
Vincent
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Article poste via Voila News - http://www.news.voila.fr
Le : Tue Mar 30 02:55:05 2004 depuis l'IP : mail.ebycms.com.au [VIP 3500
978]Hi Vincent,
Is it always AUD ?
try
select convert(money,right(col1,len(col1)-3))
I hope this helps
--
Greg O
http://www.sql-scripts.com
"MOTTE" <liste@.france-dev.com> wrote in message
news:c4agh9$src$1@.news.x-echo.com...
> Hello,
> I'm a beginner in SQLServer and I'm trying to crite a query with a subst
> ring but without success. I've got a field (String) in a table which con
> tains a price formatted like that "AUD 2,000.10". I would like with a su
> bstring (or something else) obtain something like "2000.10". Can somebod
> y help me with that ?
> Thanks a lot
> Vincent
> =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
> Article poste via Voila News - http://www.news.voila.fr
> Le : Tue Mar 30 02:55:05 2004 depuis l'IP : mail.ebycms.com.au [VIP
3500978]

Query with 5 tables, grouping by year

How would I group results from 4 tables, each with a year field, so the
results are one row for each year, all based on one clientid from the client
table
For instance, my tables:
Client table: fields ID, ClientName
Tables 1 through 4 all have the same fields, in addition to others: Fields
ID, ClientID (fk to Client table), ValidYear, Data ...
I want my results to be one row per year (we'll be querying only one
client), for example
Year Table1Data Table2Data Table3Data Table4Data
2001 1,000 3,300 15,000 445
2002 1,212 etc.
I've started with:
Select Table1.Data, Table1.ValidYear,
Table2.Data, Table2.ValidYear,
Table3.Data, Table3.ValidYear,
Table4.Data, Table4.ValidYear,
tblClient.ID, tblClient.ClientName
from Table1
inner join tblClient as a on a.ID = Table1.ClientID
inner join tblClient as b on b.ID = Table1.ClientID
inner join tblClient as c on c.ID = Table1.ClientID
inner join tblClient as d on d.ID = Table1.ClientID
and that's as far as I got, as soon as I enter criteria for ValidYear, I get
either too many rows of data or none at all. Not all data tables have data
for all years, by the way.
Thanks very much for your help.Hi,
would you like to check out he usage of DATEPART?
In your SELECT statement, you actually can put DatePart(year,
Table2.ValidYear) instead of Table2.ValidYear.
In the end of your SELECT statement, you just need to put GROUP BY
Table1.Data, Table2.Data etc to get a distinct value.
I hope this is what you are looking for.
Leo Leong
"et" wrote:

> How would I group results from 4 tables, each with a year field, so the
> results are one row for each year, all based on one clientid from the clie
nt
> table
> For instance, my tables:
> Client table: fields ID, ClientName
> Tables 1 through 4 all have the same fields, in addition to others: Field
s
> ID, ClientID (fk to Client table), ValidYear, Data ...
> I want my results to be one row per year (we'll be querying only one
> client), for example
> Year Table1Data Table2Data Table3Data Table4Data
> 2001 1,000 3,300 15,000 445
> 2002 1,212 etc.
> I've started with:
> Select Table1.Data, Table1.ValidYear,
> Table2.Data, Table2.ValidYear,
> Table3.Data, Table3.ValidYear,
> Table4.Data, Table4.ValidYear,
> tblClient.ID, tblClient.ClientName
> from Table1
> inner join tblClient as a on a.ID = Table1.ClientID
> inner join tblClient as b on b.ID = Table1.ClientID
> inner join tblClient as c on c.ID = Table1.ClientID
> inner join tblClient as d on d.ID = Table1.ClientID
> and that's as far as I got, as soon as I enter criteria for ValidYear, I g
et
> either too many rows of data or none at all. Not all data tables have dat
a
> for all years, by the way.
> Thanks very much for your help.
>
>

Query with "not null"?

Is there a way to do a query and include a field if it is *not* null?
For instance, I know I can:
Select LastName + isnull(FirstName, '') from tblClients
I want to include a field only if it isn't null, for instance, if a client
is inactive, I want to display "(inactive)" in the results:
Smith, Jane (inactive)
Smith, John
Smith, Joe
Smith, Carol (inactive)
My fields are LastName, FirstName, Inactive (bit)Hi dew
I'm not sure what the connection with NULL is - is Inactive nullable,
so that you want to show (inactive) when Inactive is NULL or 0?
To do this, you can use the CASE statement:
SELECT LastName + isnull(FirstName, '') + CASE WHEN Inactive IS NULL
THEN '(inactive)' ELSE CASE WHEN Inactive=0 THEN ('inactive') ELSE ''
END END
(two nested CASE statements - would only need one if Inactive can only
have values 0 or 1 - i.e. is not NULLable).
hope this helps
Seb|||I'm not sure what you want to do.
But, I can tell you that you results will always contain the same number of
columns for all rows. So, you can't return a different number of columns fo
r
different criteria.
You could definitely build a dynamic string based on your query.
Like
SELECT LastName + ', ' + FirstName + CASE WHEN Inactive =1 THEN '
(inactive)' ELSE '' END FROM YourTable
--
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"dew" wrote:

> Is there a way to do a query and include a field if it is *not* null?
> For instance, I know I can:
> Select LastName + isnull(FirstName, '') from tblClients
> I want to include a field only if it isn't null, for instance, if a client
> is inactive, I want to display "(inactive)" in the results:
> Smith, Jane (inactive)
> Smith, John
> Smith, Joe
> Smith, Carol (inactive)
> My fields are LastName, FirstName, Inactive (bit)
>
>|||The output of the query must be in table-format; all rows returned must have
the same number of columns. You can get something similar in appearance to
your desired output with something like this
SELECT LastName + ', ' + FirstName AS "Name", "Active"=
CASE
WHEN Inactive = 1 THEN '(inactive)'
ELSE ''
END
FROM [Your Table]
-
"dew" wrote:

> Is there a way to do a query and include a field if it is *not* null?
> For instance, I know I can:
> Select LastName + isnull(FirstName, '') from tblClients
> I want to include a field only if it isn't null, for instance, if a client
> is inactive, I want to display "(inactive)" in the results:
> Smith, Jane (inactive)
> Smith, John
> Smith, Joe
> Smith, Carol (inactive)
> My fields are LastName, FirstName, Inactive (bit)
>
>|||Not sure im understanding you properly but isn't this all you need...
Select LastName + isnull(FirstName, '') from tblClients where Inactive is NU
LL
Select LastName + isnull(FirstName, '') from tblClients where Inactive is
NOT NULL
"dew" wrote:

> Is there a way to do a query and include a field if it is *not* null?
> For instance, I know I can:
> Select LastName + isnull(FirstName, '') from tblClients
> I want to include a field only if it isn't null, for instance, if a client
> is inactive, I want to display "(inactive)" in the results:
> Smith, Jane (inactive)
> Smith, John
> Smith, Joe
> Smith, Carol (inactive)
> My fields are LastName, FirstName, Inactive (bit)
>
>|||Thanks so much, the select with Case works great, that is just what I
needed. Currently the Inactive column can be null but I can change that to
always be 0 or 1 so either one works. Thanks!
"dew" <dew@.yahoo.com> wrote in message
news:%23yUecnhEGHA.2072@.TK2MSFTNGP10.phx.gbl...
> Is there a way to do a query and include a field if it is *not* null?
> For instance, I know I can:
> Select LastName + isnull(FirstName, '') from tblClients
> I want to include a field only if it isn't null, for instance, if a client
> is inactive, I want to display "(inactive)" in the results:
> Smith, Jane (inactive)
> Smith, John
> Smith, Joe
> Smith, Carol (inactive)
> My fields are LastName, FirstName, Inactive (bit)
>

QUERY TROUBLE

I am using the last updated date field to give me the dates I want, but I
want to narrow that search to give me only the greatest date per project id.
Here is my query:
SELECT POReqHdr.ProjectID, POReqHdr.User5, POReqHdr.PONbr,
POReqHdr.CuryTotalExtCost, POReqHdr.Descr, POReqHdr.CuryPrevPOTotal,
POReqHdr.CuryReqTotal, POReqHdr.User2,
POReqHdr.LUpd_DateTime, PJPROJ.project_desc
FROM POReqHdr LEFT OUTER JOIN
PJPROJ ON POReqHdr.ProjectID = PJPROJ.project
WHERE (POReqHdr.User5 IN (@.reason)) AND (POReqHdr.ProjectID = 'HE017')
AND (MONTH(POReqHdr.LUpd_DateTime) = @.MONTH)
I know I need to use "max(lupd_datetime)" somewhere but I am not sure how.
The is in a sql report, by the way.Hi,
Inside the sub query you can use the MAX function. See the below sample:-
select projid, lupd_datetime from projects x
where lupd_datetime >= (select max(y.lupd_datetime) from projects y
where y.projid = x.projid)
Thanks
Hari
SQL Server MVP
"Ben Watts" wrote:

> I am using the last updated date field to give me the dates I want, but I
> want to narrow that search to give me only the greatest date per project i
d.
> Here is my query:
> SELECT POReqHdr.ProjectID, POReqHdr.User5, POReqHdr.PONbr,
> POReqHdr.CuryTotalExtCost, POReqHdr.Descr, POReqHdr.CuryPrevPOTotal,
> POReqHdr.CuryReqTotal, POReqHdr.User2,
> POReqHdr.LUpd_DateTime, PJPROJ.project_desc
> FROM POReqHdr LEFT OUTER JOIN
> PJPROJ ON POReqHdr.ProjectID = PJPROJ.project
> WHERE (POReqHdr.User5 IN (@.reason)) AND (POReqHdr.ProjectID = 'HE017')
> AND (MONTH(POReqHdr.LUpd_DateTime) = @.MONTH)
> I know I need to use "max(lupd_datetime)" somewhere but I am not sure how.
> The is in a sql report, by the way.
>
>|||Ben Watts wrote:
> I am using the last updated date field to give me the dates I want, but I
> want to narrow that search to give me only the greatest date per project i
d.
> Here is my query:
> SELECT POReqHdr.ProjectID, POReqHdr.User5, POReqHdr.PONbr,
> POReqHdr.CuryTotalExtCost, POReqHdr.Descr, POReqHdr.CuryPrevPOTotal,
> POReqHdr.CuryReqTotal, POReqHdr.User2,
> POReqHdr.LUpd_DateTime, PJPROJ.project_desc
> FROM POReqHdr LEFT OUTER JOIN
> PJPROJ ON POReqHdr.ProjectID = PJPROJ.project
> WHERE (POReqHdr.User5 IN (@.reason)) AND (POReqHdr.ProjectID = 'HE017')
> AND (MONTH(POReqHdr.LUpd_DateTime) = @.MONTH)
> I know I need to use "max(lupd_datetime)" somewhere but I am not sure how.
> The is in a sql report, by the way.
>
I have a short post on my web site explaining how to do this, but the
site is currently being reconstructed. You can find the original post
in Google's cache by searching for
"www.realsqlguy.com/twiki/bin/view/RealSQLGuy/FindingTheLatestValue"
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I put that query in and it told me there was an error on the report server.
Here is my last query.
SELECT POReqHdr.ProjectID, POReqHdr.User5, POReqHdr.PONbr,
POReqHdr.CuryTotalExtCost, POReqHdr.Descr, POReqHdr.CuryPrevPOTotal,
POReqHdr.CuryReqTotal, POReqHdr.User2,
POReqHdr.LUpd_DateTime, PJPROJ.project_desc
FROM POReqHdr LEFT OUTER JOIN
PJPROJ ON POReqHdr.ProjectID = PJPROJ.project
WHERE (POReqHdr.User5 IN (@.reason)) AND (POReqHdr.ProjectID = 'HE017')
AND (MONTH(POReqHdr.LUpd_DateTime) = @.MONTH) AND
(POReqHdr.LUpd_DateTime >=
(SELECT MAX(LUpd_DateTime) AS Expr1
FROM POReqHdr AS POReqHdr_1
WHERE (ProjectID = PJPROJ.project)))
"Hari Prasad" <HariPrasad@.discussions.microsoft.com> wrote in message
news:9330F636-358C-462C-8030-1CFA88035CC9@.microsoft.com...[vbcol=seagreen]
> Hi,
> Inside the sub query you can use the MAX function. See the below sample:-
> select projid, lupd_datetime from projects x
> where lupd_datetime >= (select max(y.lupd_datetime) from projects
> y
> where y.projid = x.projid)
> Thanks
> Hari
> SQL Server MVP
> "Ben Watts" wrote:
>|||never mind, it worked. Thanks very much
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:44E31BD4.60003@.realsqlguy.com...
> Ben Watts wrote:
> I have a short post on my web site explaining how to do this, but the site
> is currently being reconstructed. You can find the original post in
> Google's cache by searching for
> "www.realsqlguy.com/twiki/bin/view/RealSQLGuy/FindingTheLatestValue"
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com

QUERY TROUBLE

I am using the last updated date field to give me the dates I want, but I
want to narrow that search to give me only the greatest date per project id.
Here is my query:
SELECT POReqHdr.ProjectID, POReqHdr.User5, POReqHdr.PONbr,
POReqHdr.CuryTotalExtCost, POReqHdr.Descr, POReqHdr.CuryPrevPOTotal,
POReqHdr.CuryReqTotal, POReqHdr.User2,
POReqHdr.LUpd_DateTime, PJPROJ.project_desc
FROM POReqHdr LEFT OUTER JOIN
PJPROJ ON POReqHdr.ProjectID = PJPROJ.project
WHERE (POReqHdr.User5 IN (@.reason)) AND (POReqHdr.ProjectID = 'HE017')
AND (MONTH(POReqHdr.LUpd_DateTime) = @.MONTH)
I know I need to use "max(lupd_datetime)" somewhere but I am not sure how.
The is in a sql report, by the way.Hi,
Inside the sub query you can use the MAX function. See the below sample:-
select projid, lupd_datetime from projects x
where lupd_datetime >= (select max(y.lupd_datetime) from projects y
where y.projid = x.projid)
Thanks
Hari
SQL Server MVP
"Ben Watts" wrote:
> I am using the last updated date field to give me the dates I want, but I
> want to narrow that search to give me only the greatest date per project id.
> Here is my query:
> SELECT POReqHdr.ProjectID, POReqHdr.User5, POReqHdr.PONbr,
> POReqHdr.CuryTotalExtCost, POReqHdr.Descr, POReqHdr.CuryPrevPOTotal,
> POReqHdr.CuryReqTotal, POReqHdr.User2,
> POReqHdr.LUpd_DateTime, PJPROJ.project_desc
> FROM POReqHdr LEFT OUTER JOIN
> PJPROJ ON POReqHdr.ProjectID = PJPROJ.project
> WHERE (POReqHdr.User5 IN (@.reason)) AND (POReqHdr.ProjectID = 'HE017')
> AND (MONTH(POReqHdr.LUpd_DateTime) = @.MONTH)
> I know I need to use "max(lupd_datetime)" somewhere but I am not sure how.
> The is in a sql report, by the way.
>
>|||Ben Watts wrote:
> I am using the last updated date field to give me the dates I want, but I
> want to narrow that search to give me only the greatest date per project id.
> Here is my query:
> SELECT POReqHdr.ProjectID, POReqHdr.User5, POReqHdr.PONbr,
> POReqHdr.CuryTotalExtCost, POReqHdr.Descr, POReqHdr.CuryPrevPOTotal,
> POReqHdr.CuryReqTotal, POReqHdr.User2,
> POReqHdr.LUpd_DateTime, PJPROJ.project_desc
> FROM POReqHdr LEFT OUTER JOIN
> PJPROJ ON POReqHdr.ProjectID = PJPROJ.project
> WHERE (POReqHdr.User5 IN (@.reason)) AND (POReqHdr.ProjectID = 'HE017')
> AND (MONTH(POReqHdr.LUpd_DateTime) = @.MONTH)
> I know I need to use "max(lupd_datetime)" somewhere but I am not sure how.
> The is in a sql report, by the way.
>
I have a short post on my web site explaining how to do this, but the
site is currently being reconstructed. You can find the original post
in Google's cache by searching for
"www.realsqlguy.com/twiki/bin/view/RealSQLGuy/FindingTheLatestValue"
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I put that query in and it told me there was an error on the report server.
Here is my last query.
SELECT POReqHdr.ProjectID, POReqHdr.User5, POReqHdr.PONbr,
POReqHdr.CuryTotalExtCost, POReqHdr.Descr, POReqHdr.CuryPrevPOTotal,
POReqHdr.CuryReqTotal, POReqHdr.User2,
POReqHdr.LUpd_DateTime, PJPROJ.project_desc
FROM POReqHdr LEFT OUTER JOIN
PJPROJ ON POReqHdr.ProjectID = PJPROJ.project
WHERE (POReqHdr.User5 IN (@.reason)) AND (POReqHdr.ProjectID = 'HE017')
AND (MONTH(POReqHdr.LUpd_DateTime) = @.MONTH) AND
(POReqHdr.LUpd_DateTime >= (SELECT MAX(LUpd_DateTime) AS Expr1
FROM POReqHdr AS POReqHdr_1
WHERE (ProjectID = PJPROJ.project)))
"Hari Prasad" <HariPrasad@.discussions.microsoft.com> wrote in message
news:9330F636-358C-462C-8030-1CFA88035CC9@.microsoft.com...
> Hi,
> Inside the sub query you can use the MAX function. See the below sample:-
> select projid, lupd_datetime from projects x
> where lupd_datetime >= (select max(y.lupd_datetime) from projects
> y
> where y.projid = x.projid)
> Thanks
> Hari
> SQL Server MVP
> "Ben Watts" wrote:
>> I am using the last updated date field to give me the dates I want, but I
>> want to narrow that search to give me only the greatest date per project
>> id.
>> Here is my query:
>> SELECT POReqHdr.ProjectID, POReqHdr.User5, POReqHdr.PONbr,
>> POReqHdr.CuryTotalExtCost, POReqHdr.Descr, POReqHdr.CuryPrevPOTotal,
>> POReqHdr.CuryReqTotal, POReqHdr.User2,
>> POReqHdr.LUpd_DateTime, PJPROJ.project_desc
>> FROM POReqHdr LEFT OUTER JOIN
>> PJPROJ ON POReqHdr.ProjectID = PJPROJ.project
>> WHERE (POReqHdr.User5 IN (@.reason)) AND (POReqHdr.ProjectID =>> 'HE017')
>> AND (MONTH(POReqHdr.LUpd_DateTime) = @.MONTH)
>> I know I need to use "max(lupd_datetime)" somewhere but I am not sure
>> how.
>> The is in a sql report, by the way.
>>|||never mind, it worked. Thanks very much
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:44E31BD4.60003@.realsqlguy.com...
> Ben Watts wrote:
>> I am using the last updated date field to give me the dates I want, but I
>> want to narrow that search to give me only the greatest date per project
>> id. Here is my query:
>> SELECT POReqHdr.ProjectID, POReqHdr.User5, POReqHdr.PONbr,
>> POReqHdr.CuryTotalExtCost, POReqHdr.Descr, POReqHdr.CuryPrevPOTotal,
>> POReqHdr.CuryReqTotal, POReqHdr.User2,
>> POReqHdr.LUpd_DateTime, PJPROJ.project_desc
>> FROM POReqHdr LEFT OUTER JOIN
>> PJPROJ ON POReqHdr.ProjectID = PJPROJ.project
>> WHERE (POReqHdr.User5 IN (@.reason)) AND (POReqHdr.ProjectID =>> 'HE017') AND (MONTH(POReqHdr.LUpd_DateTime) = @.MONTH)
>> I know I need to use "max(lupd_datetime)" somewhere but I am not sure
>> how. The is in a sql report, by the way.
> I have a short post on my web site explaining how to do this, but the site
> is currently being reconstructed. You can find the original post in
> Google's cache by searching for
> "www.realsqlguy.com/twiki/bin/view/RealSQLGuy/FindingTheLatestValue"
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com

Query to split Firstname Lastname into 2 fields

Good Afternoon Everyone,
I hope everyone is doing GREAT today. I've got a database where my
customers First and Last name are in ONE field (ContactName), and we are
upgrading to another SQL application that actually has (2) seperate fields,
FirstName and LastName. Does anyone know how I can run a query to seperate
the First and Last name and put it into two fields?
Right now this is how the new SQL database is:
FieldNames
FirstName LastName
Anthony Smith
I imported the whole contactname field into the FirstName field. So
Lastname is blank. I'd like to take the last name from the 1st field and
put that into the LastName field.
This is what I'd like to acheive:
FieldNames
FirstName LastName
Anthony Smith
Thanks!
Sincerely,
Anthony Smith
In God We Trust!
Are all the names names formatted the same? If so you can use CHARINDEX or
the LEFT & RIGHT fuctions like:
SELECT LEFT( @.name , CHARINDEX( ' ', @.name ) - 1 )
SELECT RIGHT( @.name , CHARINDEX( ' ', REVERSE( @.name ) ) - 1 )
If they are not formatted the same, you have some issues to ponder. What
should happen if there is a middle name or a middle initial? What if either
the firstname or the last name was missing? How would you address a part of
the name that has more than a single space in it? What about double
barrelled names?
Anith
|||Hi Anthony
The following example should point you in the right direction. A couple of
things to watch out for are people that have two first names ie. Mary Jane
Smith and that the formatting of the data is consistent ie. no double spacing
etc.
CREATE TABLE Names
(
FirstName VARCHAR(20),
LastName VARCHAR(20) NULL
)
INSERT Names SELECT 'Anthony Smith', NULL
INSERT Names SELECT 'Peter Ward', NULL
INSERT Names SELECT 'John Brown', NULL
INSERT Names SELECT 'Prince', NULL
INSERT Names SELECT 'Mary Jane Smith', NULL
UPDATENames
SETFirstName =
CASE
WHEN CHARINDEX(' ', FirstName) > 0 THEN SUBSTRING(FirstName, 1,
CHARINDEX(' ', FirstName) - 1)
ELSE FirstName
END,
LastName =
CASE WHEN CHARINDEX(' ', FirstName) > 0 THEN SUBSTRING(FirstName,
CHARINDEX(' ', FirstName) + 1, LEN(FirstName) - CHARINDEX(FirstName, ' '))
END
SELECT * FROM Names
Returns:
FirstName LastName
-- --
Anthony Smith
Peter Ward
John Brown
Prince NULL
Mary Jane Smith
HTH
- Peter Ward
www.wardyit.com
"Anthony Smith" wrote:

> Good Afternoon Everyone,
> I hope everyone is doing GREAT today. I've got a database where my
> customers First and Last name are in ONE field (ContactName), and we are
> upgrading to another SQL application that actually has (2) seperate fields,
> FirstName and LastName. Does anyone know how I can run a query to seperate
> the First and Last name and put it into two fields?
> Right now this is how the new SQL database is:
> FieldNames
> FirstName LastName
> Anthony Smith
> I imported the whole contactname field into the FirstName field. So
> Lastname is blank. I'd like to take the last name from the 1st field and
> put that into the LastName field.
> This is what I'd like to acheive:
> FieldNames
> FirstName LastName
> Anthony Smith
>
> Thanks!
> Sincerely,
> Anthony Smith
> In God We Trust!
>
>
|||> John Steve St.Smith deWaal III
:-))
create table #t ( [Name] varchar(40))
insert into #t ([Name]) values ('Smith,John E')
insert into #t ([Name]) values ('Smith,Bill')
insert into #t ([Name]) values ('Smith,Adam F')
insert into #t ([Name]) values ('St,Smith deWaal III')
--go
select LastName, FirstName, MiddleName
from (
select
Name,
substring(Name,1,Comma-1) LastName,
substring(Name,Comma+1,Spce-Comma-1) FirstName,
nullif(substring(Name,Spce+1,40),'') MiddleName
from (
select
Name,
charindex(',',Name) Comma,
charindex(' ',Name+space(1),charindex(',',Name)) Spce
from #t
) D
) SplitNames
drop table #t
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:u0UwP5aVHHA.192@.TK2MSFTNGP04.phx.gbl...
> ... and be prepared to manually scrub names such as:
> John Steve St.Smith deWaal III
> I haven't used such, but there are tools out these for this particular
> purpose. Depending on how many names you have and the complexity of the
> names, such a tool might be cheaper in the end.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Anthony Smith" <anthony@.peconet.com> wrote in message
> news:%23mepx5UVHHA.3948@.TK2MSFTNGP05.phx.gbl...
>
|||Thank you everyone for the prompts replies. I think most of them are
formatted the same but there may be a few that aren't. If it takes care of
the bulk of the customers that'll be fine, we can manually change the rest.
Have a blessed day everyone!
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:uj1IsLVVHHA.4828@.TK2MSFTNGP05.phx.gbl...
> Are all the names names formatted the same? If so you can use CHARINDEX or
> the LEFT & RIGHT fuctions like:
> SELECT LEFT( @.name , CHARINDEX( ' ', @.name ) - 1 )
> SELECT RIGHT( @.name , CHARINDEX( ' ', REVERSE( @.name ) ) - 1 )
> If they are not formatted the same, you have some issues to ponder. What
> should happen if there is a middle name or a middle initial? What if
> either the firstname or the last name was missing? How would you address a
> part of the name that has more than a single space in it? What about
> double barrelled names?
> --
> Anith
>

query to select substring of field?

If I want to do something like:
select substr(Field1,1,2), <other fields>
from Table
group by substr(Field1,1,2)
where Field1 is a 4-character text field, is my syntax above even close to
what I need? What I want to replicate is the situation where I'd have, say,
Field2 which would be the first two characters of Field1.
Thanks.
David"msnews.microsoft.com" <David@.David.com> wrote in message
news:%23uoYnkJ6FHA.268@.TK2MSFTNGP10.phx.gbl...
> If I want to do something like:
> select substr(Field1,1,2), <other fields>
> from Table
> group by substr(Field1,1,2)
> where Field1 is a 4-character text field, is my syntax above even close to
> what I need? What I want to replicate is the situation where I'd have,
> say, Field2 which would be the first two characters of Field1.
> Thanks.
> David
>
The function is SUBSTRING(). Look it up in Books Online for the full syntax.
I'm not sure what the purpose of GROUP BY is in your example but it is valid
in SQL Server to use expressions in GROUP BY. It isn't permitted in standard
SQL however.
David Portas
SQL Server MVP
--|||David,
Thanks for your help.
The purpose of the group by is that I'll be having averages taken in the
select statement (amongst the <other fields> ).
David
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:hLmdnT-YGIX0LureRVnyvw@.giganews.com...
> "msnews.microsoft.com" <David@.David.com> wrote in message
> news:%23uoYnkJ6FHA.268@.TK2MSFTNGP10.phx.gbl...
> The function is SUBSTRING(). Look it up in Books Online for the full
> syntax. I'm not sure what the purpose of GROUP BY is in your example but
> it is valid in SQL Server to use expressions in GROUP BY. It isn't
> permitted in standard SQL however.
> --
> David Portas
> SQL Server MVP
> --
>sql

Query to see if an int field starts with a certain number

How would I write a query on a table containing a column of ints, where I want to retrieve the rows where that int value starts with a number? I know that you can do this with strings by using "....WHERE thisfield LIKE ('123%')", but if 'thisfield' is an int, how would I do this? Thanks!Convert it to string, perform a substring, and then do your comparison.

Perhaps: substring(cast([thisfield] as varchar(50)),1,1)|||I don't know how the performance of this will compare, but if thisfield is non-negative, this should work as well:

[thisfield] / power(10, cast(log10([thisfield] as int))

Cheers,
-Isaac
|||

I hate to ask this, but the giant pink elephant in the room is "how do you have an int that doesn't start with a number?" What it sounds like you have is a column of string values that may or may not be an integer, and you want to see if the first character of the string is a number, right? For this it is:

thisColumn like '[1234567890]%'

But if the column is supposed to only contain integers, the best way to make sure that they are integers is to create the column using an integer datatype.

query to parse out values from one column into different columns

I have a table where different types of values are stored in one field, but I need to seperate them into different fields based on a value in another field.

For (hypothetical) example:

There is an existing table with following info in three columns:
userid record recordtag
1 joe 1
1 j 2
1 jr 3
2 bob 1
2 a 2
2 sr 3
where recordtag indicates (1 for first name, 2 for middle initial, 3 for suffix)

I need to query these records for a report so it the output is:

userID firstname middleinitial suffix
1 joe j jr
2 bob a sr

What's the most efficient approach to create a query that will give me desired results? I have managed to create a very complex query that derives tables for each column I want to create and queries off of that derived table for the 'record' value based on the 'recordtag' values for a given 'userid'. The query is extremely slow, so I know there's some better way out there to get the results I want. Any help would be greatly appreciated. Thanks.Look up CROSSTAB queries in Books Online.select userid,
max(case recordtag when 1 then record end) as firstname,
max(case recordtag when 2 then record end) as middleinitial,
max(case recordtag when 3 then record end) as suffix
from [YourTable]
group by userid|||Thanks for the info. I'll let you know how I do.|||I incorporated the crosstab query into my code and the performance is stellar. Thanks for your help. !!

Query to obtain missing number

I've been trying to figure out how to create a query that would list the missing numbers between a high and low number for a field. For example, If I have the recordset below:

1
3
4
6
7
9

I'd like the resulting recordset to be:

2
5
8

Is there a way to achieve this? Thanks, Jason.Yes, there are several ways.

What have you covered so far in class?

-PatP|||In Class? I'm not taking a class. I know the programming language fairly well, I just cannot figure this one out. Can you give me a quick example? Thanks, Jason.|||There are multiple ways to do this. Probably the simplest is to create a "numbers" table with one row for every interesting (possible) value that a number might have. For a two byte integer, this range could be -32768 through 32767. Once you've got the numbers table, you can do a simple exists test, something like:SELECT n.val
FROM numbers AS n
WHERE NOT EXISTS (SELECT *
FROM myRecordset AS r
WHERE r.val = n.val)Of course you'd also need to limit the result to just the values of interest in this case (between the Min and Max values already in your recordset).

-PatP|||I had thought of this, the problem is, I cannot create another table. I'm using Foxpro with a proprietary program which will not allow non-program specific tables to be used in conjunction with it's own. I need to find a different way. Thanks for the post though!!|||I had thought of this, the problem is, I cannot create another table. I'm using Foxpro with a proprietary program which will not allow non-program specific tables to be used in conjunction with it's own. I need to find a different way. Thanks for the post though!!|||I had thought of this, the problem is, I cannot create another table. I'm using Foxpro with a proprietary program which will not allow non-program specific tables to be used in conjunction with it's own. I need to find a different way. Thanks for the post though!!|||I had thought of this, the problem is, I cannot create another table. I'm using Foxpro with a proprietary program which will not allow non-program specific tables to be used in conjunction with it's own. I need to find a different way. Thanks for the post though!!|||Does Foxpro support recursive queries? If so, you could recursively increment an integer up to some limit and exclude the non-qualifying rows.

query to list field data types

Can anyone tell me if there's a query command that will
return information about a table's properties, such as
field names, field data types, field lengths, index names,
etc?
For example, I'm looking for output that would show the
following, if we queried the Customers table in the MS
Northwind SQL DB:
CustomerID, nchar, 5, nulls=no
CompanyName, nvarchar, 40, nulls=no
ContactName, nvarchar, 30, nulls=yes
etc...
Thanks!!!
David
On Mon, 19 Apr 2004 10:04:25 -0700, David Hepburn III wrote:

>Can anyone tell me if there's a query command that will
>return information about a table's properties, such as
>field names, field data types, field lengths, index names,
>etc?
>For example, I'm looking for output that would show the
>following, if we queried the Customers table in the MS
>Northwind SQL DB:
>CustomerID, nchar, 5, nulls=no
>CompanyName, nvarchar, 40, nulls=no
>ContactName, nvarchar, 30, nulls=yes
>etc...
>Thanks!!!
>David
Hi David,
SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Customers'
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||hi david,
you can make use of any of the following system stored procedures.
sp_columns <table_name> --to find column definition
sp_helpindex <table_name> --to find indexes
sp_help <table_name> complete table definition including indexes,pks/fks
etc.
Vishal
vgparkar@.yahoo.co.in

Query to know if a field exits in a table

In MsSQLServer what is the query to kown if a field exists in a table
if exist (select * from dbo.sysobjects where id =............
??SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE 'MyTable' = TABLE_NAME-PatP|||select 1
from syscolumns
where ID = Object_ID ( '<table_name>' ) and
Name = '<field_name'

bEH

Query to get Linked List kind of data from the Table

hi Experts,

I have a Issue table which stores the below data for many issue. some issue are duplicate to other and they are stored in a field Duplicate_of

ID

Duplicate_of

State

77637

65702

Duplicate

65702

42217

Duplicate

42217

-

Verified

i wanted to write a query or some stored procedure when passed 77637 should help me get 42217.

Hint : 77637 when passed has field Duplicate_of which point to 65702 and his state will be Duplicate, 65702 will be duplicate to 42217 and state will be duplicate and 44217 is not duplicate to anything and state will be other then Duplicate

i appreciate if somebody can help me think in some line to give me some idea.

/soni

This is a common problem, what you will find is that you actually have a tree structure where 42217 is the root of the tree, 65702 is a branch, and 77637 is a leaf. Have a search around Google for Celko's "nested set" which has a solution which should help you here.

Query to get Linked List kind of data from the Table

hi Experts,

I have a Issue table which stores the below data for many issue. some issue are duplicate to other and they are stored in a field Duplicate_of

ID

Duplicate_of

State

77637

65702

Duplicate

65702

42217

Duplicate

42217

-

Verified

i wanted to write a query or some stored procedure when passed 77637 should help me get 42217.

Hint : 77637 when passed has field Duplicate_of which point to 65702 and his state will be Duplicate, 65702 will be duplicate to 42217 and state will be duplicate and 44217 is not duplicate to anything and state will be other then Duplicate

i appreciate if somebody can help me think in some line to give me some idea.

/soni

Assuming you are using 2005 you can use a common table expression (CTE) and use the following syntax:

DECLARE @.ID int

SET @.ID = 77637

;WITH Dupes (ID, Duplicate_of, State) AS

(

SELECT

ID, Duplicate_of, State

FROM

dbo.test

WHERE

ID = @.ID

UNION ALL

SELECT

T.ID, T.Duplicate_of, T.State

FROM

dbo.test T

INNER JOIN Dupes D ON D.Duplicate_of = T.ID

)

SELECT *

FROM Dupes

|||

hi Weaver,

Many Thanks for looking at my problem.

i forgot to mention i use SQL Server 2000 as of now. cannot upgrade to SQL Server 2005. :(

/Soni

|||

You can use this function to return the root of the tree (assuming you wanted to do this one at a time :)

set nocount on
drop table issue
drop function issue$getRoot
go
create table issue
(
issueId int primary key,
duplicateOf int references issue(issueId)
)
insert into issue
select 1, NULL
insert into issue
select 2, 1
insert into issue
select 3, 2
insert into issue
select 4, NULL
insert into issue
select 5, 4
go
create function issue$getRoot
(
@.issueId int
)
returns int
as
begin
while(1=1)
begin
select @.issueId = issue.issueId
from issue
join issue as dup
on dup.duplicateOf = issue.issueId
where dup.issueId = @.issueId

if @.@.rowcount = 0
break
end
return @.issueId
end
go

select dbo.issue$getRoot (5)

|||

hi Louis

Thanks a million for yr expert logic. its working!!!. :)

/Soni

Query to get a list of field/column names with the tablm name.

Thanks for reading my question.

I need some help on a query which will provide me with the following information.

TableName ColumName

Table1 Column1

Table1 Column2

Table1 Column3

Table2 Column1

Table2 Column2

Table2 Column3

Table2 Column1

Table3 Column1

Table3 Column2

Thanks.

I got it...

SELECT dbo.sysobjects.name AS TableName, dbo.syscolumns.name AS

ColumnName, dbo.systypes.name AS DataType, dbo.syscolumns.length,

dbo.syscolumns.xprec, dbo.syscolumns.xscale,

dbo.syscolumns.colid AS ColumnSort, dbo.sysindexkeys.colid

FROM dbo.sysobjects INNER JOIN

dbo.syscolumns ON dbo.sysobjects.id =

dbo.syscolumns.id INNER JOIN

dbo.systypes ON dbo.syscolumns.xusertype =

dbo.systypes.xusertype LEFT OUTER JOIN

dbo.sysindexkeys ON dbo.syscolumns.colid =

dbo.sysindexkeys.colid AND dbo.syscolumns.id = dbo.sysindexkeys.id

WHERE (dbo.sysobjects.xtype = 'U')

|||

Thanks for following up your own post. It prevents others from wasting their time trying to help you after you have solved the problem, and it helps others when you share your solution.

|||

use information_schema.columns

Code Snippet

select table_name,column_name from information_schema.columns

order by table_name,ordinal_position

|||

select Table_catalog as Databasename, table_name, column_name from INFORMATION_SCHEMA.COLUMNS

|||

DON'T write your query against the SYSTEM tables, the schema might change without any notice. Always trust with information_schema.xxxx views.

I do like to play with system tables, but when it come to solid solutions, we have to stick with few coditions. |||

Sorry for that unnecessary answer from me. Arnie/Mani and Team are really quick to answer any question. When i open this thread nobody was answered. But when i completed typing there were two reply... Really quick buddies...

g8 keep it up

Madhu

|||

Madhu,

Sometimes that happens this time of day. For slower responses, and a longer 'window of opportunity, try about four hours later in the day.

Query to display fiels names and types ?

Can anyone help me with a query which will display each filed name from a
table along with the type of field and length ? I have tried to google the
question with no luck
Thanks
John Jasper
John
select ordinal_position 'Seq',
cast(column_name as varchar(40)) 'Column',
isnull(character_maximum_length, numeric_precision) 'Size',
cast(data_type as varchar(12)) 'Type'
from information_schema.columns
where table_name = 'w_works'
"John Jasper" <JohnJasper@.discussions.microsoft.com> wrote in message
news:FA9148E1-E210-47F9-BE92-FF16B6FC260B@.microsoft.com...
> Can anyone help me with a query which will display each filed name from a
> table along with the type of field and length ? I have tried to google
> the
> question with no luck
> Thanks
> John Jasper
|||Try this
DECLARE @.ID INT
SELECT @.ID = [id]
FROM [sysobjects]
WHERE [name] = 'Test'
SELECT A.[name], B.[name], A.[Length]
FROM [syscolumns] A
JOIN [systypes] B
ON A.[xtype ] = B.[xusertype]
WHERE A.[id] = @.ID
ORDER BY [colid]
Nik Marshall-Blank MCSD/MCDBA
"John Jasper" <JohnJasper@.discussions.microsoft.com> wrote in message
news:FA9148E1-E210-47F9-BE92-FF16B6FC260B@.microsoft.com...
> Can anyone help me with a query which will display each filed name from a
> table along with the type of field and length ? I have tried to google
> the
> question with no luck
> Thanks
> John Jasper
|||That did not work - did not get anything ((0 row(s) affected)
"Nik Marshall-Blank (delete fcom for my e" wrote:

> Try this
> DECLARE @.ID INT
> SELECT @.ID = [id]
> FROM [sysobjects]
> WHERE [name] = 'Test'
> SELECT A.[name], B.[name], A.[Length]
> FROM [syscolumns] A
> JOIN [systypes] B
> ON A.[xtype ] = B.[xusertype]
> WHERE A.[id] = @.ID
> ORDER BY [colid]
> --
> Nik Marshall-Blank MCSD/MCDBA
> "John Jasper" <JohnJasper@.discussions.microsoft.com> wrote in message
> news:FA9148E1-E210-47F9-BE92-FF16B6FC260B@.microsoft.com...
>
>
|||Actually - that did work - I had originally used the name of the database
instead of a table - Thank-You
"Nik Marshall-Blank (delete fcom for my e" wrote:

> Try this
> DECLARE @.ID INT
> SELECT @.ID = [id]
> FROM [sysobjects]
> WHERE [name] = 'Test'
> SELECT A.[name], B.[name], A.[Length]
> FROM [syscolumns] A
> JOIN [systypes] B
> ON A.[xtype ] = B.[xusertype]
> WHERE A.[id] = @.ID
> ORDER BY [colid]
> --
> Nik Marshall-Blank MCSD/MCDBA
> "John Jasper" <JohnJasper@.discussions.microsoft.com> wrote in message
> news:FA9148E1-E210-47F9-BE92-FF16B6FC260B@.microsoft.com...
>
>