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 which system table to answer this

I have a DB with 1 default defined: UW_Zerodefault
It simply puts a 0 into particular fields upon new record creation.
Is there a query I can run against a particular system table to give me a list of fields this default is applied against in the DB?
ThanksSqlSpec will give you this information, but I can't remember now what table it's hitting to get it. it may be as simple as querying sysdepends.

I'll take a look at the code tonight and post again.|||If I understand your query correctly (It's late and I'm tired!!) you can run exec sp_mshelpcolumns 'enter table name' and the col_dridefname column is the contraint name and the first text column is the default value.|||this give you all default usage by columns:

select
s.name as colname
,o1.name as tablename
,o2.name as defaultname
from
syscolumns s
inner join
sysobjects o1 on o1.id=s.id
inner join
sysobjects o2 on o2.id=s.cdefault

and this will give you default usage by udts:

exec sp_mshelptype @.typename=null, @.flags='uddt'

Query using comparison between two fields as criteria

Hi all,
Apologies if this is the incorrect group. I couldn't find a group that
specifically handled queries in SQL Server. I originally posted this in the
Access Queries group, but since my back-end database is actually SQL Server
and I am just using Access as my front-end I need suggestions that are
specific to SQL Server.
Let's say I have a table with three fields in it: effective_date, entry_date
and amount. Is there a way that I could contruct a query such that I could
return all the records from the table where the latter of the effective_date
and entry_date fields falls within some target date range?
So for example, let's say the following data is in my table:
effective_date entry_date amount
1/1/2003 4/1/2004 $200
6/1/2004 9/1/2003 $120
5/1/2004 6/1/2005 $150
7/1/2004 4/1/2004 $170
2/1/2006 8/1/2004 $130
I want all the records where the *latter* of the two date fields falls
between 1/1/2004 and 12/31/2004. What I'd need to see in my result set based
on the data above is:
effective_date entry_date amount
1/1/2003 4/1/2004 $200
6/1/2004 9/1/2003 $120
7/1/2004 4/1/2004 $170
The third data item dropped out because even though the effective_date for
that record falls within our range, the latter of the two dates, the entry
date in this case, falls outside of our interested date range. The fifth
data item similarly falls out because its latest date, the effective date,
also falls outside our range.
How do I write a query to accomplish this?
Thanks for any help!
-Jeff VanBishlerThis is a good candidate for the CASE expression in SQL:
SELECT effective_date, entry_date, amount
FROM [Your Table]
WHERE
CASE
WHEN DATEDIFF(dd, effective_date, entry_date) < 0 THEN effective_date
ELSE entry_date
END
BETWEEN '1/1/2004' AND '12/31/2004'
The DATEDIFF function above is an easy way to find out which date is later
than the other. If effective_date is prior to entry_date, the DATEDIFF
function will return a number greater than one. If entry_date is prior to
effective_date, it will return a number less than one.
"Jeff VanBishler" wrote:

> Hi all,
> Apologies if this is the incorrect group. I couldn't find a group that
> specifically handled queries in SQL Server. I originally posted this in th
e
> Access Queries group, but since my back-end database is actually SQL Serve
r
> and I am just using Access as my front-end I need suggestions that are
> specific to SQL Server.
> Let's say I have a table with three fields in it: effective_date, entry_da
te
> and amount. Is there a way that I could contruct a query such that I could
> return all the records from the table where the latter of the effective_da
te
> and entry_date fields falls within some target date range?
> So for example, let's say the following data is in my table:
> effective_date entry_date amount
> 1/1/2003 4/1/2004 $200
> 6/1/2004 9/1/2003 $120
> 5/1/2004 6/1/2005 $150
> 7/1/2004 4/1/2004 $170
> 2/1/2006 8/1/2004 $130
> I want all the records where the *latter* of the two date fields falls
> between 1/1/2004 and 12/31/2004. What I'd need to see in my result set bas
ed
> on the data above is:
> effective_date entry_date amount
> 1/1/2003 4/1/2004 $200
> 6/1/2004 9/1/2003 $120
> 7/1/2004 4/1/2004 $170
> The third data item dropped out because even though the effective_date for
> that record falls within our range, the latter of the two dates, the entry
> date in this case, falls outside of our interested date range. The fifth
> data item similarly falls out because its latest date, the effective date,
> also falls outside our range.
> How do I write a query to accomplish this?
> Thanks for any help!
>
> -Jeff VanBishler
>
>|||Worked like a dream!
Thanks Mark!
-Jeff VanBishler
"Mark Williams" <MarkWilliams@.discussions.microsoft.com> wrote in message
news:E5710BB0-9319-43C2-88A8-2E6307B9A5CD@.microsoft.com...
> This is a good candidate for the CASE expression in SQL:
> SELECT effective_date, entry_date, amount
> FROM [Your Table]
> WHERE
> CASE
> WHEN DATEDIFF(dd, effective_date, entry_date) < 0 THEN effective_date
> ELSE entry_date
> END
> BETWEEN '1/1/2004' AND '12/31/2004'
> The DATEDIFF function above is an easy way to find out which date is later
> than the other. If effective_date is prior to entry_date, the DATEDIFF
> function will return a number greater than one. If entry_date is prior to
> effective_date, it will return a number less than one.
> "Jeff VanBishler" wrote:
>

Query Tuning

Hi,
I have a query that selects 4 fields. One is of type Varchar(500). When I
execute the query, the response is about 9 seconds (very slow). When I
comment out the varchar field, it returns in less than 1 second.
It took me a while to figure out that it's not a missing index, i can't
figure this one out.
please advise.
rafaelHow many records are being returned?
"Rafael Chemtob" wrote:

> Hi,
> I have a query that selects 4 fields. One is of type Varchar(500). When
I
> execute the query, the response is about 9 seconds (very slow). When I
> comment out the varchar field, it returns in less than 1 second.
> It took me a while to figure out that it's not a missing index, i can't
> figure this one out.
> please advise.
> rafael
>
>|||10
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:6F062F1B-91A3-45B6-AB3E-F08DF4A37831@.microsoft.com...
> How many records are being returned?
> "Rafael Chemtob" wrote:
>
When I|||> How many records are being returned?
..And what is the average length of the data in those rows?
Thomas
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:6F062F1B-91A3-45B6-AB3E-F08DF4A37831@.microsoft.com...
> How many records are being returned?
> "Rafael Chemtob" wrote:
>|||ok, sorry for not being very detailed.
4 fields
id_rating INT
summary VARCHAR(500)
dt_rating smalldatetime
id_user INT
these are the 4 fields. The record count that's returned is 11 rows.
Hope that helps
thanks
"Thomas" <replyingroup@.anywhere.com> wrote in message
news:#45wbrTRFHA.2384@.tk2msftngp13.phx.gbl...
> ..And what is the average length of the data in those rows?
>
> Thomas
>
> "CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
> news:6F062F1B-91A3-45B6-AB3E-F08DF4A37831@.microsoft.com...
When I
>|||Is the performance discrepancy consistent? i.e., have you tested this a
number of times? How many records are in the table?,
and finally, is there an index on the table that contains all the other
three columns from the table, but not the varchar(500) column?
Also, please post the DDL for the tables, and the actual Query.
Charly
"Rafael Chemtob" wrote:

> ok, sorry for not being very detailed.
> 4 fields
> id_rating INT
> summary VARCHAR(500)
> dt_rating smalldatetime
> id_user INT
> these are the 4 fields. The record count that's returned is 11 rows.
> Hope that helps
> thanks
>
> "Thomas" <replyingroup@.anywhere.com> wrote in message
> news:#45wbrTRFHA.2384@.tk2msftngp13.phx.gbl...
> When I
>
>|||I mean, is there an index on the table which includes columns
(id_rating, dt_rating, id_user), but not Column summary ?
"Rafael Chemtob" wrote:

> ok, sorry for not being very detailed.
> 4 fields
> id_rating INT
> summary VARCHAR(500)
> dt_rating smalldatetime
> id_user INT
> these are the 4 fields. The record count that's returned is 11 rows.
> Hope that helps
> thanks
>
> "Thomas" <replyingroup@.anywhere.com> wrote in message
> news:#45wbrTRFHA.2384@.tk2msftngp13.phx.gbl...
> When I
>
>|||I query the table using id_rating (which is the PK).
and this is consistent. I comment out the varchar field and i get the
results MUCH quicker.
rafael
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:456CEB9F-6A05-464C-9EE5-93F4FF0B7DB3@.microsoft.com...
> I mean, is there an index on the table which includes columns
> (id_rating, dt_rating, id_user), but not Column summary ?
>
> "Rafael Chemtob" wrote:
>
When I
can't|||As I asked above, one possible reason for this is if there's an index that
includes the columns (id_rating, dt_rating, id_user), but NOT the summary
column. If that were the case, the query processor could use the index alon
e
for the query without Summary, but would be forced to do a table scan when
you include summary.. Is there such an index?
"Rafael Chemtob" wrote:

> I query the table using id_rating (which is the PK).
> and this is consistent. I comment out the varchar field and i get the
> results MUCH quicker.
> rafael
> "CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
> news:456CEB9F-6A05-464C-9EE5-93F4FF0B7DB3@.microsoft.com...
> When I
> can't
>
>|||Hi Rafael,
For performance questions like these, it is very important to post all
relevant DDL (so including indexes, constraints, etc.) and the exact
query.
So just a wild guess for now: make sure you have a clustered index on
the table. If the table does not have a clustered index, and you delete
many rows, then querying the table can become very slow.
HTH,
Gert-Jan
Rafael Chemtob wrote:
> Hi,
> I have a query that selects 4 fields. One is of type Varchar(500). When
I
> execute the query, the response is about 9 seconds (very slow). When I
> comment out the varchar field, it returns in less than 1 second.
> It took me a while to figure out that it's not a missing index, i can't
> figure this one out.
> please advise.
> rafael

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 sequentially number Null fields in a column

I'm trying to write a Query that will Update all the Null fields in Table1
column1 to 'P' and a 6 digit sequential number starting from 000001
including the leading zeros. Can someone help me figure out the correct
syntax? So far, nothing I've come up with is working right.
TIA
MattWell, if you don't want to add an IDENTITY column, and just want to add the
zero-padded char, you could:
1.) Create temp table with IDENTITY column and primary key from source table
1.) Generate identity values for all rows in target table in the temp table
2.) Update target table to include a zero-padded version of the identity
value
Example:
Let's say your table is called Customer and the primary key is CustomerKey
varchar(10)
BEGIN TRANSACTION
CREATE TABLE
#KeyGen
(
CustomerKey varchar(10) NOT NULL,
NewID int NOT NULL IDENTITY (1,1)
)
INSERT INTO KeyGen (CustomerKey) SELECT CustomerKey FROM Customer
WITH(TABLOCKX)
ALTER TABLE Customer ADD NewKey char(10) NOT NULL DEFAULT('')
UPDATE Customer SET NewKey = (SELECT RIGHT('000000' + CAST(NewID AS
varchar(6)), 6) FROM #KeyGen WHERE KeyGen.CustomerKey =
Customer.CustomerKey)
DROP TABLE #KeyGen
COMMIT TRANSACTION
Error handling is an exercise for the reader.
Cheers,
James Hokes
"Matt Williamson" <ih8spam@.spamsux.org> wrote in message
news:%23yx8L0oeGHA.4304@.TK2MSFTNGP05.phx.gbl...
> I'm trying to write a Query that will Update all the Null fields in Table1
> column1 to 'P' and a 6 digit sequential number starting from 000001
> including the leading zeros. Can someone help me figure out the correct
> syntax? So far, nothing I've come up with is working right.
> TIA
> Matt
>|||The problem is the source table doesn't have a primary key. That's what I'm
creating with this query.
I've been working with this code that I found in the archive, but I can't
get it to work
update temp_Reports tr1
set identifier_id = (select count(*) from temp_Reports tr2
where tr2.identifier_id <= tr1.identifier_id) + (select MAX(identifier_id)
FROM temp_Reports)
Where identifier_id is Null
I created this table as a temporary test
CREATE TABLE [temp_Reports] (
[identifier_id] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[somedata] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
And added these values:
1 | Test1
2 | Test2
3 | Test3
Null | Test4
Null | Test5
I get
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'tr1'.
Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near '+'.
but I'm not clear why.
Matt
"James Hokes" <noway@.nospamthanksanyway.com> wrote in message
news:eMW1O9oeGHA.3364@.TK2MSFTNGP05.phx.gbl...
> Well, if you don't want to add an IDENTITY column, and just want to add
> the zero-padded char, you could:
> 1.) Create temp table with IDENTITY column and primary key from source
> table
> 1.) Generate identity values for all rows in target table in the temp
> table
> 2.) Update target table to include a zero-padded version of the identity
> value
> Example:
> Let's say your table is called Customer and the primary key is CustomerKey
> varchar(10)
> BEGIN TRANSACTION
> CREATE TABLE
> #KeyGen
> (
> CustomerKey varchar(10) NOT NULL,
> NewID int NOT NULL IDENTITY (1,1)
> )
> INSERT INTO KeyGen (CustomerKey) SELECT CustomerKey FROM Customer
> WITH(TABLOCKX)
> ALTER TABLE Customer ADD NewKey char(10) NOT NULL DEFAULT('')
> UPDATE Customer SET NewKey = (SELECT RIGHT('000000' + CAST(NewID AS
> varchar(6)), 6) FROM #KeyGen WHERE KeyGen.CustomerKey =
> Customer.CustomerKey)
> DROP TABLE #KeyGen
> COMMIT TRANSACTION
>
> Error handling is an exercise for the reader.
> Cheers,
> James Hokes
> "Matt Williamson" <ih8spam@.spamsux.org> wrote in message
> news:%23yx8L0oeGHA.4304@.TK2MSFTNGP05.phx.gbl...
>|||>> The problem is the source table doesn't have a primary key. That's what
Make sure, in the future, to declare a primary key at the time of table
definition itself. Also, unless you have at least one set of columns that
are unique in the table, you have no way out.
The error is due to the alias used in the UPDATE clause. Moreover the logic
does not take into account the rows are already NULL. Assuming the second
column is unique within the table here is a workaround:
UPDATE tbl
SET col1 = ( SELECT COUNT( * )
FROM tbl t
WHERE t.col2 <= tbl.col2
AND t.col1 IS NULL )
+ ( SELECT MAX( col1 )
FROM tbl )
WHERE col1 IS NULL ;
Anith|||Matt,
1 -

> update temp_Reports tr1
Can not use alias in this way. Try:
update temp_Reports
set identifier_id = (select count(*) from temp_Reports tr2
where tr2.identifier_id <= temp_Reports.identifier_id) + (select
MAX(identifier_id)
FROM temp_Reports)
Where identifier_id is Null
go
2 -
The code will not give the result you are expecting, because the update runs
in a transaction, so the rows updated will not be seen by the "select"
statement that is doing the counting.
AMB
"Matt Williamson" wrote:

> The problem is the source table doesn't have a primary key. That's what I'
m
> creating with this query.
> I've been working with this code that I found in the archive, but I can't
> get it to work
> update temp_Reports tr1
> set identifier_id = (select count(*) from temp_Reports tr2
> where tr2.identifier_id <= tr1.identifier_id) + (select MAX(identifier_id)
> FROM temp_Reports)
> Where identifier_id is Null
> I created this table as a temporary test
> CREATE TABLE [temp_Reports] (
> [identifier_id] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [somedata] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> And added these values:
> 1 | Test1
> 2 | Test2
> 3 | Test3
> Null | Test4
> Null | Test5
> I get
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near 'tr1'.
> Server: Msg 170, Level 15, State 1, Line 3
> Line 3: Incorrect syntax near '+'.
> but I'm not clear why.
> Matt
> "James Hokes" <noway@.nospamthanksanyway.com> wrote in message
> news:eMW1O9oeGHA.3364@.TK2MSFTNGP05.phx.gbl...
>
>

Query to Search all fields in simple table

I am trying to write a simple search page that will searchall the fields in a database to find all records that match a user input string. The string could happen anywhere in any of the fields. I have a dataset and can write a query but am unsure what the format is for this simple task. I figured it would look like this:

SELECT Table.*

FROM Table

WHERE * = @.USERINPUT

But thats not working. Can someone help.? Thanks..

Not a simple task, but this should get you started.

SELECT *

FROM Table

WHERE field1 LIKE '%' + @.UserInput + '%' OR field2 LIKE'%'+@.UserInput+'%' OR...

sql

query to return only non null fields

Is it possible to write a query that returns only non null fields from a specified record? I have a big table with a record for each customer. the record contains a field for each item that can be purchased (only 6 items). I need to write an invoice but not every customer buys every product. I get the feeling Im going about this all wrong. Any help would be great

ThanksOriginally posted by nicky w
Is it possible to write a query that returns only non null fields from a specified record? I have a big table with a record for each customer. the record contains a field for each item that can be purchased (only 6 items). I need to write an invoice but not every customer buys every product. I get the feeling Im going about this all wrong. Any help would be great

Thanks
It would have been better to have the up to 6 items as up to 6 records in a separate table. No SQL query can return a variable number of columns, you would have to write some procedural code to run the query and then present the NOT NULL data.|||are you still in a designing stage? then you should change the design.
What if more items will be offered?

referential integrity will prevent "lost childs"

otherwise andrew is right. write some procedural code

Query to return latest record, multiple join fields

Hi
I need to write a query that returns the latest value(s) from a table,
'grouped' by the primary key (multiple fields), and the criteria to
derive the latest record is also based on multiple fields.
I have put together the DDL below as a simplified example, and want to
write a query that returns the following resultset:
company--project--value--
1 1 'fifth value'
1 2 '.2 fifth value'
2 1 '2 fifth value'
(KEY: company + project)
(LATEST RECORD: year + batch + item)
Thanks for any help
Sean
---
CREATE TABLE mytable (company INT, project INT, [year] int, batch int,
item int, value varchar(35))
INSERT INTO mytable VALUES (1, 1, 2003, 1, 1, 'first value')
INSERT INTO mytable VALUES (1, 1, 2003, 1, 2, 'second value')
INSERT INTO mytable VALUES (1, 1, 2003, 1, 3, 'third value')
INSERT INTO mytable VALUES (1, 1, 2003, 2, 1, 'fourth value')
INSERT INTO mytable VALUES (1, 1, 2003, 2, 2, 'fifth value')
INSERT INTO mytable VALUES (1, 1, 2002, 1, 1, 'sixth value')
INSERT INTO mytable VALUES (1, 1, 2002, 1, 2, 'seventh value')
INSERT INTO mytable VALUES (1, 1, 2002, 2, 1, 'eighth value')
INSERT INTO mytable VALUES (1, 2, 2003, 1, 1, '.2 first value')
INSERT INTO mytable VALUES (1, 2, 2003, 1, 2, '.2 second value')
INSERT INTO mytable VALUES (1, 2, 2003, 1, 3, '.2 third value')
INSERT INTO mytable VALUES (1, 2, 2003, 2, 1, '.2 fourth value')
INSERT INTO mytable VALUES (1, 2, 2003, 2, 2, '.2 fifth value')
INSERT INTO mytable VALUES (1, 2, 2002, 1, 1, '.2 sixth value')
INSERT INTO mytable VALUES (1, 2, 2002, 1, 2, '.2 seventh value')
INSERT INTO mytable VALUES (1, 2, 2002, 2, 1, '.2 eighth value')
INSERT INTO mytable VALUES (2, 1, 2003, 1, 1, '2 first value')
INSERT INTO mytable VALUES (2, 1, 2003, 1, 2, '2 second value')
INSERT INTO mytable VALUES (2, 1, 2003, 1, 3, '2 third value')
INSERT INTO mytable VALUES (2, 1, 2003, 2, 1, '2 fourth value')
INSERT INTO mytable VALUES (2, 1, 2003, 2, 2, '2 fifth value')
INSERT INTO mytable VALUES (2, 1, 2002, 1, 1, '2 sixth value')
INSERT INTO mytable VALUES (2, 1, 2002, 1, 2, '2 seventh value')
INSERT INTO mytable VALUES (2, 1, 2002, 2, 1, '2 eighth value')
---This table doesn't appear to have a primary key. I'll assume that the key is
supposed to be (company,project,year,batch,item). I've also assumed that the
batch and item numbers are in the range 0-999. If not, you'll have to amend
the YBI calculation accordingly.
SELECT T.company, T.project, T.value
FROM MyTable AS T
JOIN
(SELECT company, project,
MAX([year]*1000000+batch*1000+item) AS ybi
FROM Mytable
GROUP BY company, project) AS M
ON T.company = M.company
AND T.project = M.project
AND T.[year]*1000000+T.batch*1000+T.item = M.ybi
--
David Portas
--
Please reply only to the newsgroup
--sql

query to remove HTML tags

Hi,
I have a table with 3 fields. One of the fields contains HTML tags which I
want to get rid of. Any quick way to do this?
thanksYou can use function REPLACE or STUFF to replace characteres not wanted. See
BOL for more information.
AMB
"Rafael Chemtob" wrote:

> Hi,
> I have a table with 3 fields. One of the fields contains HTML tags which
I
> want to get rid of. Any quick way to do this?
> thanks
>
>|||See if this helps:
http://groups.google.ca/groups?selm...FTNGP09.phx.gbl
Anith

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 identify all fields in a table

Newby question. We have a new vendor who will be needing to do data
convertion from our current SQL app into his new application, as part of the
implimentation. I need to identify the complete file structure in order to
get a quote for that part of the project.
I know how to export the list of the tables, but is there a similar way to
export the list of fields. OR..any query possibities that could be run per
table or mutiple tables that would list just the fields - no data?
Any help or ideas would be greatly appreciated - thanks in advance for your
time and help!
Cindy B
Hi,
im sure there is a way to query the system tables in order get the lis of
fields, but don't ask me how ;)
however, you may want to use the following excel vba macro:
Sub GetFields()
Dim A As Long
Dim TableName As String
Dim RS As ADODB.Recordset
Dim Conn As New ADODB.Connection
Conn.ConnectionString = "Provider=SQLOLEDB.1;Password=[PASSWORD];Persist
Security Info=True;User ID=[USERNAME];Initial Catalog=[DATABASE];Data
Source=[SERVERNAME]"
Conn.Open
TableName = InputBox("Enter table name: ")
If TableName = "" Then Exit Sub
Set RS = Conn.Execute("SELECT TOP 1 * FROM " & TableName)
Range("A1:A1000").Clear
For A = 1 To RS.Fields.Count
Range("A" & A).Value = RS.Fields(A - 1).Name
Next
RS.Close
Conn.Close
End Sub
just provide the necessary connection information in the connection string
(without the [ ] ), add a reference to the latest "microsoft activex data
objects" and run the macro. it will list all fields of a given table in the
current excel worksheet
"Cindy B" wrote:

> Newby question. We have a new vendor who will be needing to do data
> convertion from our current SQL app into his new application, as part of the
> implimentation. I need to identify the complete file structure in order to
> get a quote for that part of the project.
> I know how to export the list of the tables, but is there a similar way to
> export the list of fields. OR..any query possibities that could be run per
> table or mutiple tables that would list just the fields - no data?
> Any help or ideas would be greatly appreciated - thanks in advance for your
> time and help!
> --
> Cindy B
|||One way against the ANSI INFORMATIO_SCHEMA views
select c.TABLE_NAME,c.COLUMN_NAME,c.DATA_TYPE,c.NUMERIC_P RECISION from
INFORMATION_SCHEMA.TABLES t
join INFORMATION_SCHEMA.COLUMNS c on t.TABLE_NAME = c.TABLE_NAME
WHERE TABLE_TYPE='BASE TABLE'
ORDER BY c.TABLE_NAME,c.ORDINAL_POSITION
http://sqlservercode.blogspot.com/

query to identify all fields in a table

Newby question. We have a new vendor who will be needing to do data
convertion from our current SQL app into his new application, as part of the
implimentation. I need to identify the complete file structure in order to
get a quote for that part of the project.
I know how to export the list of the tables, but is there a similar way to
export the list of fields. OR..any query possibities that could be run per
table or mutiple tables that would list just the fields - no data?
Any help or ideas would be greatly appreciated - thanks in advance for your
time and help!
Cindy BHi,
im sure there is a way to query the system tables in order get the lis of
fields, but don't ask me how ;)
however, you may want to use the following excel vba macro:
Sub GetFields()
Dim A As Long
Dim TableName As String
Dim RS As ADODB.Recordset
Dim Conn As New ADODB.Connection
Conn.ConnectionString = "Provider=SQLOLEDB.1;Password=[PASSWORD];Persist
Security Info=True;User ID=[USERNAME];Initial Catalog=[DATABASE];Dat
a
Source=[SERVERNAME]"
Conn.Open
TableName = InputBox("Enter table name: ")
If TableName = "" Then Exit Sub
Set RS = Conn.Execute("SELECT TOP 1 * FROM " & TableName)
Range("A1:A1000").Clear
For A = 1 To RS.Fields.Count
Range("A" & A).Value = RS.Fields(A - 1).Name
Next
RS.Close
Conn.Close
End Sub
just provide the necessary connection information in the connection string
(without the [ ] ), add a reference to the latest "microsoft activex dat
a
objects" and run the macro. it will list all fields of a given table in the
current excel worksheet
"Cindy B" wrote:

> Newby question. We have a new vendor who will be needing to do data
> convertion from our current SQL app into his new application, as part of t
he
> implimentation. I need to identify the complete file structure in order t
o
> get a quote for that part of the project.
> I know how to export the list of the tables, but is there a similar way to
> export the list of fields. OR..any query possibities that could be run pe
r
> table or mutiple tables that would list just the fields - no data?
> Any help or ideas would be greatly appreciated - thanks in advance for you
r
> time and help!
> --
> Cindy B|||One way against the ANSI INFORMATIO_SCHEMA views
select c.TABLE_NAME,c.COLUMN_NAME,c.DATA_TYPE,c.NUMERIC_PRECISION from
INFORMATION_SCHEMA.TABLES t
join INFORMATION_SCHEMA.COLUMNS c on t.TABLE_NAME = c.TABLE_NAME
WHERE TABLE_TYPE='BASE TABLE'
ORDER BY c.TABLE_NAME,c.ORDINAL_POSITION
http://sqlservercode.blogspot.com/

query to identify all fields in a table

Newby question. We have a new vendor who will be needing to do data
convertion from our current SQL app into his new application, as part of the
implimentation. I need to identify the complete file structure in order to
get a quote for that part of the project.
I know how to export the list of the tables, but is there a similar way to
export the list of fields. OR..any query possibities that could be run per
table or mutiple tables that would list just the fields - no data?
Any help or ideas would be greatly appreciated - thanks in advance for your
time and help!
--
Cindy BHi,
im sure there is a way to query the system tables in order get the lis of
fields, but don't ask me how ;)
however, you may want to use the following excel vba macro:
Sub GetFields()
Dim A As Long
Dim TableName As String
Dim RS As ADODB.Recordset
Dim Conn As New ADODB.Connection
Conn.ConnectionString = "Provider=SQLOLEDB.1;Password=[PASSWORD];Persist
Security Info=True;User ID=[USERNAME];Initial Catalog=[DATABASE];Data
Source=[SERVERNAME]"
Conn.Open
TableName = InputBox("Enter table name: ")
If TableName = "" Then Exit Sub
Set RS = Conn.Execute("SELECT TOP 1 * FROM " & TableName)
Range("A1:A1000").Clear
For A = 1 To RS.Fields.Count
Range("A" & A).Value = RS.Fields(A - 1).Name
Next
RS.Close
Conn.Close
End Sub
just provide the necessary connection information in the connection string
(without the [ ] ), add a reference to the latest "microsoft activex data
objects" and run the macro. it will list all fields of a given table in the
current excel worksheet
"Cindy B" wrote:
> Newby question. We have a new vendor who will be needing to do data
> convertion from our current SQL app into his new application, as part of the
> implimentation. I need to identify the complete file structure in order to
> get a quote for that part of the project.
> I know how to export the list of the tables, but is there a similar way to
> export the list of fields. OR..any query possibities that could be run per
> table or mutiple tables that would list just the fields - no data?
> Any help or ideas would be greatly appreciated - thanks in advance for your
> time and help!
> --
> Cindy B|||One way against the ANSI INFORMATIO_SCHEMA views
select c.TABLE_NAME,c.COLUMN_NAME,c.DATA_TYPE,c.NUMERIC_PRECISION from
INFORMATION_SCHEMA.TABLES t
join INFORMATION_SCHEMA.COLUMNS c on t.TABLE_NAME = c.TABLE_NAME
WHERE TABLE_TYPE='BASE TABLE'
ORDER BY c.TABLE_NAME,c.ORDINAL_POSITION
http://sqlservercode.blogspot.com/

Query to find record selection on between dates

Table has five fields fields
PrimaryKey - Integer type
InfoField1 - String Type
InfoField2 - String type
StartDate - DateTimeType
EndDate - DateTimeType
My where clause should be something like
Where InfoField2 = 'something' And Today's date between StartDate and
EndDate.
How do I express Today's date in that query? The data for the startdate and
end date is stored in the database in shortdate format, so in some cases it
can be mm/dd/yyyy and in others it could be dd/mm/yyyy or any other
shortdate format possible.
Thanks for any help
RDWhere InfoField2 = 'something' And getdate()between StartDate and
EndDate.
"RD" <nospam@.nospam.net> wrote in message
news:OvI$1hQRFHA.504@.TK2MSFTNGP12.phx.gbl...
> Table has five fields fields
> PrimaryKey - Integer type
> InfoField1 - String Type
> InfoField2 - String type
> StartDate - DateTimeType
> EndDate - DateTimeType
> My where clause should be something like
> Where InfoField2 = 'something' And Today's date between StartDate and
> EndDate.
> How do I express Today's date in that query? The data for the startdate
> and
> end date is stored in the database in shortdate format, so in some cases
> it
> can be mm/dd/yyyy and in others it could be dd/mm/yyyy or any other
> shortdate format possible.
> Thanks for any help
> RD
>
>

Query to display a field based on a parameter

I have a client table, with 8 fields, all of which contain a phone number.
Bad design, I know, but let's not get into that here. My problem now is
that in addition to those fields, we have a "Preferred" field, which simply
names the field of the preferred phone number. So we have fields like
HomePhone, BusinessPhone, Fax, and the Preferred field says "HomePhone"
How can I create a query so that the record displays only that field that is
preferred? I hope that makes sense.
For instance: John Doe, Home Phone 123-4567, Business Phone 765-4321,
Preferred "Home Phone", I want to the query to only display the name, and
home phone.
For Jane Doe, Home Phone 123-4567, Business Phone 765-4321, Preferred
"Business Phone", I want to the query to only display the name, and business
phone.
All of these records are displayed in a datagrid.
Thanks for your help.SELECT CASE Preferred
WHEN 'Home Phone' THEN HomePhone
WHEN 'Work Phone' THEN WorkPhone
..
WHEN 'Yet Another Phone' THEN YetAnotherPhone
End as PreferredPhone
FROM LotsOfPhones
Roy Harvey
Beacon Falls, CT
On Fri, 7 Apr 2006 16:29:47 -0600, "KatMagic" <SSKatMagic@.yahoo.com>
wrote:

>I have a client table, with 8 fields, all of which contain a phone number.
>Bad design, I know, but let's not get into that here. My problem now is
>that in addition to those fields, we have a "Preferred" field, which simply
>names the field of the preferred phone number. So we have fields like
>HomePhone, BusinessPhone, Fax, and the Preferred field says "HomePhone"
>How can I create a query so that the record displays only that field that i
s
>preferred? I hope that makes sense.
>For instance: John Doe, Home Phone 123-4567, Business Phone 765-4321,
>Preferred "Home Phone", I want to the query to only display the name, and
>home phone.
>For Jane Doe, Home Phone 123-4567, Business Phone 765-4321, Preferred
>"Business Phone", I want to the query to only display the name, and busines
s
>phone.
>All of these records are displayed in a datagrid.
>Thanks for your help.
>

Query taking longer to run after adding only 2 fields and additional JOIN

why does my query take 2x as long to run after adding the Fees in?

SELECT m.customer,

c.name,

c.customer,

c.state,

m.Branch,

CASE WHEN ph.batchtype = 'PUR' OR ph.batchtype = 'PAR' OR ph.batchtype = 'PCR' Then

(ph.totalpaid - ph.ForwardeeFee)

ELSE

0.00

END AS [Posted Amount],

ph.systemmonth,

ph.datepaid,

ph.totalpaid,

ph.batchtype,

m.desk,

'' AS [New Old CC],

'In-House' AS Type,

'' AS Active,

ph.UID,

m.number,

dc.amount CC,

p.amount AS PDC,

m.original,

ph.OverPaidAmt,

fg.FeeGoal_AZ,

fg.FeeGoal_IL

FROM dbo.Master m LEFT JOIN dbo.payhistory ph ON m.number = ph.number

INNER JOIN dbo.DeC dc ON dc.number = m.number

INNER JOIN dbo.pdc p ON p.number = m.number

INNER JOIN dbo.Customer c ON c.Customer = m.Customer

INNER JOIN ReportingServer.dbo.FeeGoal fg ON fg.CustomerID = c.Customer

GROUP BY m.customer,

c.name,

c.customer,

c.state,

m.Branch,

ph.OverPaidAmt,

ph.systemmonth,

ph.datepaid,

ph.totalpaid,

ph.batchtype,

m.desk,

ph.UID,

m.number,

dc.amount,

p.amount,

m.original ,

ph.systemmonth,

ph.systemyear,

ph.ForwardeeFee,

fg.FeeGoal_AZ,

fg.FeeGoal_IL

HAVING ph.systemmonth = datepart(mm, getdate()) AND ph.batchtype <> 'DA' AND

ph.batchtype <> 'DAR' AND ph.systemyear = datepart(yy, getdate())

ORDER BY m.customer

I assume that you mean that you added the two columns from the FeeGoal table to the select list and this caused the query to run 2x as long.

My guess would be that these columns are not included in an index and you forced the optimizer to do a table scan on the FeeGoal table.

Have you looked the the execution plan for one versus the other? What indexes do you have on the FeeGoal table? How big is the FeeGoal table?

I would start by looking at the plan, but if you could provide a bit more about these tables and what indexes they have on them, I might be able to give some tips.

HTH