Cleanup / Foreign key

I have an address table I set up so that I can start our address campaign.
There is a field (open entry) called Business Type that I allowed the works
to enter to describe the business type. Now that the entry is complete ,
I'm in cleanup phase. Now, my question is: I know I need to make Business
Type a foreign key and select distinct to insert them into their own table,
but should I do that before I clean up the inconsistencies or after. There
are about 2000 different business types, some are minor like - Car Sales -
Used and Car Sales _ Used. I'm creating a system that allows users to
search based on business type.
Before or after, why?Before!
Why? Do not allow bad data into the database in the first place, it is a
bitch getting it out.
Jay
<programmingcodeATjards.com> wrote in message
news:uVedQl9%23HHA.320@.TK2MSFTNGP04.phx.gbl...
>I have an address table I set up so that I can start our address campaign.
>There is a field (open entry) called Business Type that I allowed the works
>to enter to describe the business type. Now that the entry is complete ,
>I'm in cleanup phase. Now, my question is: I know I need to make Business
>Type a foreign key and select distinct to insert them into their own table,
>but should I do that before I clean up the inconsistencies or after. There
>are about 2000 different business types, some are minor like - Car Sales -
>Used and Car Sales _ Used. I'm creating a system that allows users to
>search based on business type.
> Before or after, why?
>sqlsql

cleansing data

I have a table with 1.5 million rows. Each field has a text qualifier of " ". Could you please tell me if and how I can write a script to remove the quotes from each field?
Thank you. ;)Using which database engine? The string operations aren't very standard between engines, so which engine makes a considerable difference.

-PatP|||Using MS SQL Server 2000|||Hi mary10k, try this
update tablename set filedname=replace(filedname,""" ""","")

Madhivanan

cleaing up text type fields

Hello.
I have a text type field in an SQL2000 table I need to clean up. After
converting from MySQL I am finding that I have leading Tabs and when I
display the text field in an asp textbox I end up see the record
double spaced.
Any suggestion on how I can clean the field up?
Also, as I test an application I just migrated from apache/php/mysql
to iis/asp.net/sql2000 and from hardware pentium 1 with 64mb mem to
pentium 4 with 512mb I am finding the the MS solution is like 3 times
slower?
Thanks for any help or info.> I have a text type field in an SQL2000 table I need to clean up. After
> converting from MySQL I am finding that I have leading Tabs and when I
> display the text field in an asp textbox I end up see the record
> double spaced.
> Any suggestion on how I can clean the field up?
Trim it on the client before displaying it?
> Also, as I test an application I just migrated from apache/php/mysql
> to iis/asp.net/sql2000 and from hardware pentium 1 with 64mb mem to
> pentium 4 with 512mb I am finding the the MS solution is like 3 times
> slower?
We don't even have a fraction of the information required to analyze your
environment and point out potential reasons for any performance differences.|||Assuming that each record has two leading tabs, you could do something like:
DECLARE @.ptr binary(16)
SELECT @.ptr=textptr(textColumn) FROM yourTable
UPDATETEXT yourTable.textColumn @.ptr 0 2 ''
That will delete the first two characters from each record. If you need to
do more complex evaluation, you can run a cursor over the table and use
CHARINDEX to determine where the replacement(s) should be made.
<jason@.cyberpine.com> wrote in message
news:ef0a04d7.0311070754.5567591d@.posting.google.com...
> Hello.
> I have a text type field in an SQL2000 table I need to clean up. After
> converting from MySQL I am finding that I have leading Tabs and when I
> display the text field in an asp textbox I end up see the record
> double spaced.
> Any suggestion on how I can clean the field up?
> Also, as I test an application I just migrated from apache/php/mysql
> to iis/asp.net/sql2000 and from hardware pentium 1 with 64mb mem to
> pentium 4 with 512mb I am finding the the MS solution is like 3 times
> slower?
> Thanks for any help or info.|||On second thought, you will need a CURSOR - Running that code as-is will
only strip the first two characters from the last record selected.
"Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> wrote in message
news:eB#svjUpDHA.2312@.TK2MSFTNGP12.phx.gbl...
> Assuming that each record has two leading tabs, you could do something
like:
> DECLARE @.ptr binary(16)
> SELECT @.ptr=textptr(textColumn) FROM yourTable
> UPDATETEXT yourTable.textColumn @.ptr 0 2 ''
> That will delete the first two characters from each record. If you need
to
> do more complex evaluation, you can run a cursor over the table and use
> CHARINDEX to determine where the replacement(s) should be made.
> <jason@.cyberpine.com> wrote in message
> news:ef0a04d7.0311070754.5567591d@.posting.google.com...
> > Hello.
> >
> > I have a text type field in an SQL2000 table I need to clean up. After
> > converting from MySQL I am finding that I have leading Tabs and when I
> > display the text field in an asp textbox I end up see the record
> > double spaced.
> >
> > Any suggestion on how I can clean the field up?
> >
> > Also, as I test an application I just migrated from apache/php/mysql
> > to iis/asp.net/sql2000 and from hardware pentium 1 with 64mb mem to
> > pentium 4 with 512mb I am finding the the MS solution is like 3 times
> > slower?
> >
> > Thanks for any help or info.
>|||You can see this example for using replace in this case:
http://www.aspfaq.com/2445
"Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> wrote in message
news:eZxpAnUpDHA.684@.TK2MSFTNGP09.phx.gbl...
> On second thought, you will need a CURSOR - Running that code as-is will
> only strip the first two characters from the last record selected.
> "Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> wrote in
message
> news:eB#svjUpDHA.2312@.TK2MSFTNGP12.phx.gbl...
> > Assuming that each record has two leading tabs, you could do something
> like:
> >
> > DECLARE @.ptr binary(16)
> > SELECT @.ptr=textptr(textColumn) FROM yourTable
> > UPDATETEXT yourTable.textColumn @.ptr 0 2 ''
> >
> > That will delete the first two characters from each record. If you need
> to
> > do more complex evaluation, you can run a cursor over the table and use
> > CHARINDEX to determine where the replacement(s) should be made.
> >
> > <jason@.cyberpine.com> wrote in message
> > news:ef0a04d7.0311070754.5567591d@.posting.google.com...
> > > Hello.
> > >
> > > I have a text type field in an SQL2000 table I need to clean up. After
> > > converting from MySQL I am finding that I have leading Tabs and when I
> > > display the text field in an asp textbox I end up see the record
> > > double spaced.
> > >
> > > Any suggestion on how I can clean the field up?
> > >
> > > Also, as I test an application I just migrated from apache/php/mysql
> > > to iis/asp.net/sql2000 and from hardware pentium 1 with 64mb mem to
> > > pentium 4 with 512mb I am finding the the MS solution is like 3 times
> > > slower?
> > >
> > > Thanks for any help or info.
> >
> >
>|||Ahhh, nice... I didn't realize this slightly annoying caveat:
"Initially I used CHARINDEX, but that failed if the pattern was deeper than
8,000 characters into the value. PATINDEX never dies."
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:uAGRfqUpDHA.2772@.TK2MSFTNGP12.phx.gbl...
> You can see this example for using replace in this case:
> http://www.aspfaq.com/2445
>
>
> "Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> wrote in
message
> news:eZxpAnUpDHA.684@.TK2MSFTNGP09.phx.gbl...
> > On second thought, you will need a CURSOR - Running that code as-is will
> > only strip the first two characters from the last record selected.
> >
> > "Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> wrote in
> message
> > news:eB#svjUpDHA.2312@.TK2MSFTNGP12.phx.gbl...
> > > Assuming that each record has two leading tabs, you could do something
> > like:
> > >
> > > DECLARE @.ptr binary(16)
> > > SELECT @.ptr=textptr(textColumn) FROM yourTable
> > > UPDATETEXT yourTable.textColumn @.ptr 0 2 ''
> > >
> > > That will delete the first two characters from each record. If you
need
> > to
> > > do more complex evaluation, you can run a cursor over the table and
use
> > > CHARINDEX to determine where the replacement(s) should be made.
> > >
> > > <jason@.cyberpine.com> wrote in message
> > > news:ef0a04d7.0311070754.5567591d@.posting.google.com...
> > > > Hello.
> > > >
> > > > I have a text type field in an SQL2000 table I need to clean up.
After
> > > > converting from MySQL I am finding that I have leading Tabs and when
I
> > > > display the text field in an asp textbox I end up see the record
> > > > double spaced.
> > > >
> > > > Any suggestion on how I can clean the field up?
> > > >
> > > > Also, as I test an application I just migrated from apache/php/mysql
> > > > to iis/asp.net/sql2000 and from hardware pentium 1 with 64mb mem to
> > > > pentium 4 with 512mb I am finding the the MS solution is like 3
times
> > > > slower?
> > > >
> > > > Thanks for any help or info.
> > >
> > >
> >
> >
>|||It took a few practical uses before I cleaned that up. The code is a little
simpler for this specific case, because you don't have to find the index,
and you don't have to loop through... you just replace the first two
characters if they're what you think they are.
"Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> wrote in message
news:ePbcpuUpDHA.2528@.TK2MSFTNGP12.phx.gbl...
> Ahhh, nice... I didn't realize this slightly annoying caveat:
> "Initially I used CHARINDEX, but that failed if the pattern was deeper
than
> 8,000 characters into the value. PATINDEX never dies."sqlsql

City, State expression problem!

I have a report which lists the city and state. The problem is that
the city and state are in one field and were in Upper case. I used the
strconv but I don't know how to make the state portion Upper instead or
Proper.
Right now the output looks like this: Birminham, Al
Any help is appreciated.Try the following:
select replace(ColName,right(ColName,1), Upper(right(ColName,1)))
Good Luck
swtjen01 wrote:
> I have a report which lists the city and state. The problem is that
> the city and state are in one field and were in Upper case. I used the
> strconv but I don't know how to make the state portion Upper instead or
> Proper.
> Right now the output looks like this: Birminham, Al
> Any help is appreciated.

Chosen datatype for Primary key field and performance questions?

Hi there,

I have been hired for a couple of weeks to investigate the performance of a sql server 2000 system.

One of the things that strikes me is that all the Primary key (identity field) fileds uses an decimal(18,0) as it's datatype.

An decimal with a precision of 18,0 takes 9 bytes for each column, while an int takes only 4 bytes and and bigint 8 bytes.

Many tables aren't that big, so the values will fit in an int datatype.

1. Is iot a good option to change the decimals columns to an int column ?

2. Many of these columns are indexed by a clustered index. Can the decimal datatype be a performance issue ?

3. sometimes they have deadlocks due page splits. Can this by reduced by changing the data types, while more data fit's into an page?

Thanks in advance,

Greetz,

Patrick de Jong

To me decimal (18,0) does not make sense at all... if at all u need to store larger number u could have gone for BigInt. Ofcourse the index size increases. To reduce the page split the change of datatype may not be sufficient. you many need to re-look your fillfactor for the index.

Madhu

|||

Using int for an indentity column is the most common solution, but just be aware of the roughly 2.1 billion upper limit for that data type. If 2.1 billion is not large enough, most people use bigint.

Changing your fillfactor can help minimize page splits, at the cost of making your index larger. Your deadlocks are probably not caused by page splits. Its more likely that they are caused by lack of, or improper indexes. You might try running this query to see if you are seeing any blocking:

-- Detect blocking

SELECT blocked_query.session_id AS blocked_session_id,

blocking_query.session_id AS blocking_session_id,

sql_text.text AS blocked_text,

sql_btext.text AS blocking_text,

waits.wait_type AS blocking_resource

FROM sys.dm_exec_requests AS blocked_query

INNER JOIN sys.dm_exec_requests AS blocking_query

ON blocked_query.blocking_session_id = blocking_query.session_id

CROSS APPLY

(SELECT *

FROM sys.dm_exec_sql_text(blocking_query.sql_handle)

) sql_btext

CROSS APPLY

(SELECT *

FROM sys.dm_exec_sql_text(blocked_query.sql_handle)

) sql_text

INNER JOIN sys.dm_os_waiting_tasks AS waits

ON waits.session_id = blocking_query.session_id

Choosing the PK

If you have two fields that together form a unique identifier, would it be
better to use them as PK or is it better to create another field (ex. Auto
Increment) and use that.
Specially if there are many tables that contain a FK to this table. It seems
a bit inpractical to keep several copies of these fields in several tables,
even though they are used as a PK<->FK.
(Assuming the two fields cannot be null)
What are your thoughts from experience?
Hi,
I will go with an Identity column with a clustered Index on it. This will
provide you a better data modeling and better data retrieval.
Thanks
Hari
SQL Server MVP
"Ash" wrote:

> If you have two fields that together form a unique identifier, would it be
> better to use them as PK or is it better to create another field (ex. Auto
> Increment) and use that.
> Specially if there are many tables that contain a FK to this table. It seems
> a bit inpractical to keep several copies of these fields in several tables,
> even though they are used as a PK<->FK.
> (Assuming the two fields cannot be null)
> What are your thoughts from experience?
>
|||Ash,
in most cases I would say that using an identity key column as PK suits your
situation well. Consider extreme case: you have two columns as the
components of the key, each char(4000). In order to do a join, you use the
index that's created by setting up the PK. But each evaluation of each
index page would provide you only the information that the next index
reading should go up or down in pages. On the other hand, if you use an
identity column, with bigint it's only 8 bit, so your index search would be
much shorter. Normal life is not so extreme, but this is to illustrate.
Same principle applies where you do not want to use the identity key column.
If your key components are all narrow, and there are not many columns, AND
the values of those columns are frequently used in the child tables' access,
don't you think you would rather have them in those tables?
DB design and performance go hand in hand, and their optimization depends on
your situation.
hth
"Ash" <Ash@.discussions.microsoft.com> wrote in message
news:F3C186BB-70D3-4316-A17F-B676D674CC25@.microsoft.com...
> If you have two fields that together form a unique identifier, would it be
> better to use them as PK or is it better to create another field (ex. Auto
> Increment) and use that.
> Specially if there are many tables that contain a FK to this table. It
> seems
> a bit inpractical to keep several copies of these fields in several
> tables,
> even though they are used as a PK<->FK.
> (Assuming the two fields cannot be null)
> What are your thoughts from experience?
>
|||I see your point..
So which scenario would the join run faster (I'm talking about > 2,000,000
records in each table)
1)ID is PK, F1 & F2 are unique and index
table1 table2
ID <IDENTITY> ID<IDENTIT>
F1<CHAR(10)> F1<CHAR(10)>
F2<CHAR(10)> F2<CHAR(10)>
F3<CHAR(10)> F4<CHAR(10)>
SELECT * FROM table1 t1,table2 t2 WHERE t1.ID=t2.ID AND t1.f1='Somthing' AND
t1.f2='Somthing Else'
2)F1 & F2 are PK and index
table1 table2
F1<CHAR(10)> F1<CHAR(10)>
F2<CHAR(10)> F2<CHAR(10)>
F3<CHAR(10)> F4<CHAR(10)>
SELECT * FROM table1 t1,table2 t2 WHERE t1.f1=t2.f1 AND t1.f2=t2.f2 AND
t1.f1='Somthing' AND t1.f2='Somthing Else'
Thanks..

Choosing the PK

If you have two fields that together form a unique identifier, would it be
better to use them as PK or is it better to create another field (ex. Auto
Increment) and use that.
Specially if there are many tables that contain a FK to this table. It seems
a bit inpractical to keep several copies of these fields in several tables,
even though they are used as a PK<->FK.
(Assuming the two fields cannot be null)
What are your thoughts from experience?Hi,
I will go with an Identity column with a clustered Index on it. This will
provide you a better data modeling and better data retrieval.
Thanks
Hari
SQL Server MVP
"Ash" wrote:
> If you have two fields that together form a unique identifier, would it be
> better to use them as PK or is it better to create another field (ex. Auto
> Increment) and use that.
> Specially if there are many tables that contain a FK to this table. It seems
> a bit inpractical to keep several copies of these fields in several tables,
> even though they are used as a PK<->FK.
> (Assuming the two fields cannot be null)
> What are your thoughts from experience?
>|||Ash,
in most cases I would say that using an identity key column as PK suits your
situation well. Consider extreme case: you have two columns as the
components of the key, each char(4000). In order to do a join, you use the
index that's created by setting up the PK. But each evaluation of each
index page would provide you only the information that the next index
reading should go up or down in pages. On the other hand, if you use an
identity column, with bigint it's only 8 bit, so your index search would be
much shorter. Normal life is not so extreme, but this is to illustrate.
Same principle applies where you do not want to use the identity key column.
If your key components are all narrow, and there are not many columns, AND
the values of those columns are frequently used in the child tables' access,
don't you think you would rather have them in those tables?
DB design and performance go hand in hand, and their optimization depends on
your situation.
hth
"Ash" <Ash@.discussions.microsoft.com> wrote in message
news:F3C186BB-70D3-4316-A17F-B676D674CC25@.microsoft.com...
> If you have two fields that together form a unique identifier, would it be
> better to use them as PK or is it better to create another field (ex. Auto
> Increment) and use that.
> Specially if there are many tables that contain a FK to this table. It
> seems
> a bit inpractical to keep several copies of these fields in several
> tables,
> even though they are used as a PK<->FK.
> (Assuming the two fields cannot be null)
> What are your thoughts from experience?
>|||I see your point..
So which scenario would the join run faster (I'm talking about > 2,000,000
records in each table)
1)ID is PK, F1 & F2 are unique and index
table1 table2
ID <IDENTITY> ID<IDENTIT>
F1<CHAR(10)> F1<CHAR(10)>
F2<CHAR(10)> F2<CHAR(10)>
F3<CHAR(10)> F4<CHAR(10)>
SELECT * FROM table1 t1,table2 t2 WHERE t1.ID=t2.ID AND t1.f1='Somthing' AND
t1.f2='Somthing Else'
2)F1 & F2 are PK and index
table1 table2
F1<CHAR(10)> F1<CHAR(10)>
F2<CHAR(10)> F2<CHAR(10)>
F3<CHAR(10)> F4<CHAR(10)>
SELECT * FROM table1 t1,table2 t2 WHERE t1.f1=t2.f1 AND t1.f2=t2.f2 AND
t1.f1='Somthing' AND t1.f2='Somthing Else'
Thanks..

Choosing the PK

If you have two fields that together form a unique identifier, would it be
better to use them as PK or is it better to create another field (ex. Auto
Increment) and use that.
Specially if there are many tables that contain a FK to this table. It seems
a bit inpractical to keep several copies of these fields in several tables,
even though they are used as a PK<->FK.
(Assuming the two fields cannot be null)
What are your thoughts from experience?Hi,
I will go with an Identity column with a clustered Index on it. This will
provide you a better data modeling and better data retrieval.
Thanks
Hari
SQL Server MVP
"Ash" wrote:

> If you have two fields that together form a unique identifier, would it be
> better to use them as PK or is it better to create another field (ex. Auto
> Increment) and use that.
> Specially if there are many tables that contain a FK to this table. It see
ms
> a bit inpractical to keep several copies of these fields in several tables
,
> even though they are used as a PK<->FK.
> (Assuming the two fields cannot be null)
> What are your thoughts from experience?
>|||Ash,
in most cases I would say that using an identity key column as PK suits your
situation well. Consider extreme case: you have two columns as the
components of the key, each char(4000). In order to do a join, you use the
index that's created by setting up the PK. But each evaluation of each
index page would provide you only the information that the next index
reading should go up or down in pages. On the other hand, if you use an
identity column, with bigint it's only 8 bit, so your index search would be
much shorter. Normal life is not so extreme, but this is to illustrate.
Same principle applies where you do not want to use the identity key column.
If your key components are all narrow, and there are not many columns, AND
the values of those columns are frequently used in the child tables' access,
don't you think you would rather have them in those tables?
DB design and performance go hand in hand, and their optimization depends on
your situation.
hth
"Ash" <Ash@.discussions.microsoft.com> wrote in message
news:F3C186BB-70D3-4316-A17F-B676D674CC25@.microsoft.com...
> If you have two fields that together form a unique identifier, would it be
> better to use them as PK or is it better to create another field (ex. Auto
> Increment) and use that.
> Specially if there are many tables that contain a FK to this table. It
> seems
> a bit inpractical to keep several copies of these fields in several
> tables,
> even though they are used as a PK<->FK.
> (Assuming the two fields cannot be null)
> What are your thoughts from experience?
>|||I see your point..
So which scenario would the join run faster (I'm talking about > 2,000,000
records in each table)
1)ID is PK, F1 & F2 are unique and index
table1 table2
ID <IDENTITY> ID<IDENTIT>
F1<CHAR(10)> F1<CHAR(10)>
F2<CHAR(10)> F2<CHAR(10)>
F3<CHAR(10)> F4<CHAR(10)>
SELECT * FROM table1 t1,table2 t2 WHERE t1.ID=t2.ID AND t1.f1='Somthing' AND
t1.f2='Somthing Else'
2)F1 & F2 are PK and index
table1 table2
F1<CHAR(10)> F1<CHAR(10)>
F2<CHAR(10)> F2<CHAR(10)>
F3<CHAR(10)> F4<CHAR(10)>
SELECT * FROM table1 t1,table2 t2 WHERE t1.f1=t2.f1 AND t1.f2=t2.f2 AND
t1.f1='Somthing' AND t1.f2='Somthing Else'
Thanks..

Choosing field from same row based on an aggregate.

I have a table with two columns. Let's call them Value and Hour. Looks like this:

Value Hour

4 9:00

3 11:00

6 2:00

2 12:00

I want to be able to make a total line and give the Max(Value) and the time it happened. What would be the function to get the Hour value based on the Max(Value). Just for example, for this one it would be Max(Value) = 6 and it's Hour would be 2:00.

Hi,

what about getting this right back from the database system with a query ?

SELECT Hour,MAX(Value)
From SomeTable
Group by Hour

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||I'm actually getting my data from a Custom Data Processing extension. I would need a way to get it with a function straight out of the resultant dataset.|||

Can't you make a calculated field with the expression = Max(Value)?

That can be put in the footer of the table (which will display '6' in this example). To get the hour belonging to the value with an expression ...I'm not so sure how to do that ... maybe with a switch expression, based on the textbox where you show the MAX(Value)?

Choosing decimal symbol (comma, please! :))

Hello!

I'm having a slight problem figuring out how to make a field in an Access ADP accept input using comma as decimal symbol.

The setup consists of an Access ADP connected to a SQL2000-server running on W2K(Adv). Both machines have regional settings pointing to Denmark and thus decimal symbol is ','.

When entering data through the interface made as forms in the ADP, I'm forced to enter '.' as decimal symbol for fields defined as DECIMAL(12,2) (for instance). ...This is actually a situation I could live with, if it weren't for the problems I face when trying to base calculus on these fields (doing some input checking).

Instead of resorting to making tedious code to handle the situation I know turn to you guys for help!

Regards ...and THANKS in advance! :)
Ult

NB: As an appetizer I can tell that if I multiply 1.00 with 1.00 I get 10000.00! ...So the problem is quite annoying, if you're not into opening a can of coding-with-a-distinct-feeling-of-this-is-not-quite-the-best-solution! :)http://www.dbforums.com/forumdisplay.php?forumid=84 .. post your question here|||Thanx for replying, Enigma!

I admit the problem has a touch of both worlds (Access/MSSQL) but believe/hope the problem has to do with MS SQL - all my 'pure' Access applications have none of the described problems.

I have of course not found any solution in the Access part of this forum.

U
...Still open for bright ideas! :)|||Btw, if anybody has a way to make insert-statements via Analyser accept decimals written with ',' instead of '.' it might just be part of the solution, I'm looking for!

...Or if anybody could 'kill this idea' by confirming it's not possible (at least without using 'format'-functions...)?|||The setup consists of an Access ADP connected to a SQL2000-server running on W2K(Adv).

Oops .. I missed that (Did not read the entire post :( ) !!!! Let me think about it then !!

choose main or preferred supplier ( was "A thing that cannot be solved...?") -

I have a table which contains suppliers that can supply certain articles.
There can be many suppliers to each article, and therefore there is a field that tells you if this supplier is the main supplier for the article, this field is called "arthuvudavt". However, you aren't forced to use this field in the table.
As a alternative you can use priority. Each supplier has given priority where
0 means "most preferred" and any other means "less preferred"
What I want to do is to pick the right supplier for a given article in one SQL-statement regardless of if you choose to use the priority or if you choose to use the main supplier field.

Abbreviated description of the table, called AL.

SuppNo Article Arthuvudavt Prio
10101 A-10 NULL 0
10202 A-10 1 0
10303 A-10 NULL 1
10101 B-10 NULL 0
10202 B-10 NULL 0
10303 B-10 NULL 1
10101 C-10 NULL 1
10202 C-10 NULL 0
10303 C-10 NULL 1

In the above case, I'd like the statement to return supplier 10202 for article A-10 (the one that has the arthuvudavt checked), and
only one row for B-10, which one doesn't really matter, since someone has been making a fault here, this should not happen, but my SQL-statement should break because of this scenario. For C-10 supplier 10303 should be returned.

Is there any way this could be done?

The reason I ask is that I try to learn new ways of solving things, and this problem was kind'a easily solved using variables, but I was wondering if it was possible to do it without it.

Thanks in advance
Jonas

Sorry for the poor formatting, I didn't have the time to make it right.Just sort the data appropriately, and take the first row from the sorted set.

-PatP|||The thing is yes, you could do what you say, sort it and set rowcount = 1 or something similar, but what I really wanna do is to make a single T-SQL statement that picks one resultset, and only one under every circumstance described, and I don't know how to do just that.

(Yes, the earlier explanation was pretty crappy, gotta blame me being swedish ...) :confused:|||With your reference to rowcount=1, I'll assume that you are using Microsoft SQL (although it could also be Sybase). If that is the case, you can use the TOP 1 clause within your SELECT statement.

Bra tur!

-PatP|||With your reference to rowcount=1, I'll assume that you are using Microsoft SQL (although it could also be Sybase). If that is the case, you can use the TOP 1 clause within your SELECT statement.

Bra tur!

-PatP

Yeah, came to think of it just when I read your reply PatP. What can I say, you are The Man. Thanks a bunch! "Tack s mycket!"

Oh, and it's actually "Lycka till!" if I should be picky, but I got the message ... :)|||In a insert-trigger, I'd like to fetch and update a purchase order once it's placed, using info. from the earlier described AL table

SuppNo Article Arthuvudavt Prio First_field Other_Field
10101 A-10 NULL 0 12.00 12.00
10202 A-10 1 0 12.00 13.00
10303 A-10 NULL 1 25.00 35.00
10101 B-10 NULL 0 1.00 2.00
10202 B-10 NULL 0 5.00 5.00
10303 B-10 NULL 1 5.00 6.00
10101 C-10 NULL 1 11.00 11.00
10202 C-10 NULL 0 22.00 35.00
10303 C-10 NULL 1 35.98 52.34

I would preferreable do this in one single update statement, and not using variables. I would rather not use cursors to loop the Inserted-tables either, since this is supposed to be a major performance issue.
The purchase order table is called BP and contains the "First_Field" and "Other_Field" which also exists in the AL-table.
The problem is that I need to do the update and match the supplier to the article ordered in the way the earlier posts by PatP describes, but as I said earlier, I'd like to do this by using only one update-statement. It's also possible for the inserted-table to contain many rows, and therefore I cannot use the "top 1" method discussed earlier.

If I use the suggested solution by PatP that he described earlier, that works for the problem described in the beginning, but not for this one.
Anyone have any ideas? (Oh, yes, I am using T-SQL for MS SQL Server.)

Thanks in advance Jonas|||How about using:CREATE TABLE Nephilim_Supplier (
SuppNo INT NULL
, Article NVARCHAR(20) NULL
, Arthuvudavt INT NULL
, Prio INT NULL
, First_field MONEY NULL
, Other_Field MONEY NULL
)

CREATE TABLE Nephilim_Invoice_Detail (
InvoiceID INT NULL
, Article NVARCHAR(20) NULL
, SuppNo INT NULL
, OtherStuff NVARCHAR(50) NULL
)

INSERT INTO Nephilim_Supplier (
SuppNo, Article, Arthuvudavt
, Prio, First_field, Other_Field
) SELECT 10101, 'A-10', NULL, 0, 12.00, 12.00
UNION ALL SELECT 10202, 'A-10', 1, 0, 12.00, 13.00
UNION ALL SELECT 10303, 'A-10', NULL, 1, 25.00, 35.00
UNION ALL SELECT 10101, 'B-10', NULL, 0, 1.00, 2.00
UNION ALL SELECT 10202, 'B-10', NULL, 0, 5.00, 5.00
UNION ALL SELECT 10303, 'B-10', NULL, 1, 5.00, 6.00
UNION ALL SELECT 10101, 'C-10', NULL, 1, 11.00, 11.00
UNION ALL SELECT 10202, 'C-10', NULL, 0, 22.00, 35.00
UNION ALL SELECT 10303, 'C-10', NULL, 1, 35.98, 52.34

INSERT INTO Nephilim_Invoice_Detail (
InvoiceID, Article
) SELECT 1001, 'A-10'
UNION ALL SELECT 1001, 'B-10'
UNION ALL SELECT 1001, 'C-10'
UNION ALL SELECT 1002, 'B-10'
UNION ALL SELECT 1002, 'C-10'

UPDATE Nephilim_Invoice_Detail
SET SuppNo = (SELECT TOP 1 SuppNo
FROM Nephilim_Supplier AS s
WHERE s.Article = Nephilim_Invoice_Detail.Article
ORDER BY Arthuvudavt DESC, Prio, SuppNo)

SELECT *
FROM Nephilim_Invoice_Detail-PatP|||My intention was to update the first_field and other field with info from the supplier in a insert-trigger on the Nephilim_Invoice_Detail table.

The Nephilim_Invoice_Detail table should be altered like this :
CREATE TABLE Nephilim_Invoice_Detail (
InvoiceID INT NULL
, Article NVARCHAR(20) NULL
, SuppNo INT NULL
, OtherStuff NVARCHAR(50) NULL
, First_field money
, Other_Field money
)

I assume you want me to make the trigger in the following way, given your earlier update-statement.

create trigger Nephilim_Invoice_Detail_insert on Nephilim_Invoice_Detail for insert
as
UPDATE Nephilim_Invoice_Detail
SET
first_field = (SELECT TOP 1 first_field
FROM Nephilim_Supplier AS s
WHERE s.Article = Nephilim_Invoice_Detail.Article),
other_field = (SELECT TOP 1 other_field
FROM Nephilim_Supplier AS s
WHERE s.Article = Nephilim_Invoice_Detail.Article
ORDER BY Arthuvudavt DESC, Prio, SuppNo)

If that is the case, that won't work. I apologize for my poor explanation of the problem. :o

EDIT :
It's not working in neither of the two stated cases below:

insert into Nephilim_Invoice_Detail
(InvoiceID, Article, SuppNo, OtherStuff)
select
1, 'B-10', NULL, 'Some other stuff'

insert into Nephilim_Invoice_Detail
(InvoiceID, Article, SuppNo, OtherStuff)
select
1, 'B-10', NULL, 'Some other stuff'
UNION ALL
select
1, 'C-10', NULL, 'Some C-10 stuff'|||This is kind of hard to get your head around the first time you see it, but I'd use something like:UPDATE a
SET
a.first_field = b.first_field
, a.other_field = b.other_field
FROM Nephilim_Invoice_Detail AS a
JOIN (SELECT TOP 1 z.first_field, z.first_field, z.other_field
FROM Nephilim_Supplier AS z
ORDER BY Arthuvudavt DESC, Prio, SuppNo) AS b
ON (b.Article = a.Article)You might also need to include the inserted pseudo-table too, to kind of keep a leash on things.

-PatP|||Aye, the inserted table should probably be added to the inserted-trigger, but I still cannot get it to work. The insert-trigger now looks like this :

alter trigger Nephilim_Invoice_Detail_insert on Nephilim_Invoice_Detail for insert
as
UPDATE a
SET
a.first_field = b.first_field
, a.other_field = b.other_field
, a.suppNo = b.suppNo
FROM Nephilim_Invoice_Detail AS a
JOIN (SELECT TOP 1 z.first_field, z.other_field, z.Article
FROM Nephilim_Supplier AS z
ORDER BY Arthuvudavt DESC, Prio, SuppNo) AS b
ON (b.Article = a.Article)
JOIN inserted on
a.Article = inserted.article

Is there something in that code that I've missed? All I did was to add the join to the inserted table in a similar way you added a join to the subtable made by a select.
Neither of the two inserts in my previous post works ...

Starting to feel like total "n00b" here, but then again, I probably am ... :o|||You appear to be missing the SuppNo in the derived table (the Z alias). What are you seeing (error messages, inappropriate rows updated, appropriate rows not updated, blue flames coming out of your server, etc)?

I can't get much mental traction on "Neither of the two inserts in my previous post works ..." It doesn't help me to understand what the problem is.

-PatP|||What I meant with "neither of the two inserts work" was that yes, they are syntactically correct, but in neither of the two inserts the trigger updates the fields I intended to update. The update-trigger "shoots blanks", or whatever you wanna call it ...

I suppose you want me to alter the trigger into this:

alter trigger Nephilim_Invoice_Detail_insert on Nephilim_Invoice_Detail for insert
as
UPDATE a
SET
a.first_field = b.first_field
, a.other_field = b.other_field
, a.suppNo = b.suppNo
FROM Nephilim_Invoice_Detail AS a
JOIN (SELECT TOP 1 z.first_field, z.other_field, z.Article
FROM Nephilim_Supplier AS z
ORDER BY Arthuvudavt DESC, Prio, SuppNo) AS b
ON (b.Article = a.Article)
JOIN inserted on
a.Article = inserted.article
join z on
a.suppno=z.suppno

if so, I get a
Server: Msg 208, Level 16, State 1, Procedure Nephilim_Invoice_Detail_insert, Line 3
Invalid object name 'z'.

when I try to do the insert. The z-table is alias:ed into table name b, so I thought I shouldn't have to use z in a join since I joined the a and b tables ...

(Thoughtfully wonders to himself, "wonder how much patience this PatP dude really has with someone like me. Starting to feel annoyed by myself for not getting this ...)|||This is mostly "window dressing" to clean things up so its easier for my feeble mind to grasp, along with an outer join just in case there aren't any rows with the inserted.Article value in the Nephilim_Supplier table:ALTER TRIGGER Nephilim_Invoice_Detail_insert
ON Nephilim_Invoice_Detail
FOR INSERT
AS

UPDATE a
SET
a.first_field = b.first_field
, a.other_field = b.other_field
, a.suppNo = b.suppNo
FROM inserted
JOIN Nephilim_Invoice_Detail AS a
ON (a.Article = inserted.Article)
LEFT OUTER JOIN (SELECT TOP 1
z.first_field, z.other_field, z.Article, z.SuppNo
FROM Nephilim_Supplier AS z
ORDER BY Arthuvudavt DESC, Prio, SuppNo) AS b
ON (b.Article = a.Article)

SELECT inserted.* -- Debugging, let's see what won't match
FROM inserted
LEFT OUTER JOIN (SELECT TOP 1
z.first_field, z.other_field, z.Article, z.SuppNo
FROM Nephilim_Supplier AS z
ORDER BY Arthuvudavt DESC, Prio, SuppNo) AS b
ON (b.Article = inserted.Article)
WHERE b.Article IS NULL

RETURNThe extra SELECT is just for debugging. It will return any rows that are in the inserted pseudo-table that don't have a match in the derived table.

-PatP|||With your altered procedure, that now looks like this :

ALTER TRIGGER Nephilim_Invoice_Detail_insert
ON Nephilim_Invoice_Detail
FOR INSERT
AS

UPDATE a
SET
a.first_field = b.first_field
, a.other_field = b.other_field
, a.suppNo = b.suppNo
FROM inserted
JOIN Nephilim_Invoice_Detail AS a
ON (a.Article = inserted.Article)
LEFT OUTER JOIN (SELECT TOP 1
z.first_field, z.other_field, z.Article, z.SuppNo
FROM Nephilim_Supplier AS z
ORDER BY Arthuvudavt DESC, Prio, SuppNo) AS b
ON (b.Article = a.Article)

SELECT inserted.* -- Debugging, let's see what won't match
FROM inserted
LEFT OUTER JOIN (SELECT TOP 1
z.first_field, z.other_field, z.Article, z.SuppNo
FROM Nephilim_Supplier AS z
ORDER BY Arthuvudavt DESC, Prio, SuppNo) AS b
ON (b.Article = inserted.Article)
WHERE b.Article IS NULL

RETURN

And the following data in the Nephilim_Supplier - table

SuppNo Article Arthuvudavt Prio First_field Other_Field
---- ------- ---- ---- ------- -------
10101 A-10 NULL 0 12.0000 12.0000
10202 A-10 1 0 12.0000 13.0000
10303 A-10 NULL 1 25.0000 35.0000
10101 B-10 NULL 0 1.0000 2.0000
10202 B-10 NULL 0 5.0000 5.0000
10303 B-10 NULL 1 5.0000 6.0000
10101 C-10 NULL 1 11.0000 11.0000
10202 C-10 NULL 0 22.0000 35.0000
10303 C-10 NULL 1 35.9800 52.3400

The following insert still doesn't fill in the fields I wanted in the trigger, first_field, other_field and suppNo.

insert into Nephilim_Invoice_Detail
(InvoiceID, Article, SuppNo, OtherStuff)
select
1, 'B-10', NULL, 'Some other stuff'

The example previously posted with two posts in the inserted table doesn't work either:

insert into Nephilim_Invoice_Detail
(InvoiceID, Article, SuppNo, OtherStuff)
select
1, 'B-10', NULL, 'Some other stuff'
UNION ALL
select
1, 'C-10', NULL, 'Some C-10 stuff'

With the given info, the result should be that the first_field, other_field and suppNo should be filled with info from the Nephilim_Supplier.

In the "single-insert"-case, the result should be

First_Field and Other_Field from supplier 10101 or 10202, it really doesn't matter since this data is breaking the usage of the table. My trigger should however work despite this.)

In the "dual-insert"-case I wish the result to be

First_Field and Other_Field for article C-10 should be the data associated with supplier 10202 for that article, since that supplier has the lowest prio.
First_Field and Other_Field would be updated as stated in the single insert case.

What I don't understand here is why the debug part concludes that there is no match. Obviously there is, I am using articles with associated suppliers. :confused:|||If the trigger is spitting out rows, at least from it's perspective there isn't a match. There might be datatype issues (char versus nvarchar perhaps), collation differences, or other issues, but the code doesn't see the relationship and that is all that really matters at this point.

If you can't work this one out:
1. Create a totally empty database.
2. Create the tables.
3. Create the trigger.
4. Test to be sure it misbehaves.
5. Detach the database.
6. Zip the database (mdf and ldf) up.
7. Post it here.
8. I'll see what I can do, but it won't be quick because I'm swamped.

-PatP|||Attached to this post is the database. Use the previously posted insert's to provoke the situation.
To me it looks like something is wrong in the trigger, but I just can't figure out how it should be changed.|||SELECT inserted.* -- Debugging, let's see what won't match
FROM inserted
LEFT OUTER JOIN (SELECT TOP 1
z.first_field, z.other_field, z.Article, z.SuppNo
FROM Nephilim_Supplier AS z
ORDER BY Arthuvudavt DESC, Prio, SuppNo) AS b
ON (b.Article = inserted.Article)
WHERE b.Article IS NULL

The 'problem' is that b.Article is NOT NULL because you have rows in Nephilim_Supplier that have the same 'Article'. You need uniqueness - but, on your INSERT, suppNo is Null, so I don't know where you going to get it from.

Disallow NULLs on every column you can, and then SQL Server will start pointing out your problems. Also, you are committing ACIDic sin w/o a primary key on Nephilim_Invoice_Detail - fix that.|||Ok, I buy the thing about ACID:ic sin, however these tables are only created to simplify a real world problem, so I'll let it slide for now ...

The problem at hand is that when a insert is made into Nephilim_Invoice_Detail the trigger should figure out which supplier it should retrieve info from, and put that supplierno along with the First_Field and Other_Field data on the post being created. The only way it can do that is via the field Article. However, this field isn't unique, therefore ArtHuvudAvt is just in parallell with prio. Since none of these two fields or NOT NULL by design for reasons I won't discuss here, there can be many matches. My trigger should use the rules described earlier in this thread to find a match, and if it ends up with more than one match, use one of the matching posts.

In the realworld table, the "Nephilim_Supplier"-table has a PK of Article, arthuvudAvt, Prio in that way creating uniqueness. That uniqueness however isn't useable from my point of view, since I'm only given the articleNo to begin with in my trigger. Hopefully I straigthened the problem out.

- Nephy|||*bump!* :o

Chinese/Japanese characters in a table filed

Hi,
how should I go about designing my database (table, field, collate) if
I'd like it to support chinese, japanese and other non-english
characters?
I can't find any articles or best practises!
MortenAlthough Japan and China still have many computers with their own encodings
(e.g. JIS, GB, Big 5).
Any Windows computer will use Unicode and thus you can use nvarchar() or
nchar()
For general background...http://www.unicode.org/
more specifically try
http://msdn.microsoft.com/library/d...LServer2000.htm
"hansiman" <hansi@.hotmail.com> wrote in message
news:6ud861hpcistq3jfer9167fmor02j9f9nr@.
4ax.com...
> Hi,
> how should I go about designing my database (table, field, collate) if
> I'd like it to support chinese, japanese and other non-english
> characters?
> I can't find any articles or best practises!
> Morten

Chinese character in the field (Sql 7)

Guys,
I have to replace some old chinese characters (words) with some new
ones. In the database, the fields are displayed as ?.
The current chinese characters will display fine when you retrieved the
fields through the website. As soon as I cut and paste the new chinese
characters onto the field that I want to replace, the website will
display ? with the fields that I just updated.
I have installed the Chinese (Taiwan) letters and enable my language
toolbar. During the cut and paste process, I also switched to "CH".
Any idea?
Please help.
Thanks in advance,
TonyHi,
You can try to use CAST(0xABCD1234 as NVARCHAR(4)) etc. instead of copy and
paste.
Replace the 0xABCD1234 with the binary code of the Chinese characters.
Kenneth
<tractng@.gmail.com> wrote in message
news:1132790630.847208.228190@.z14g2000cwz.googlegroups.com...
> Guys,
> I have to replace some old chinese characters (words) with some new
> ones. In the database, the fields are displayed as ?.
> The current chinese characters will display fine when you retrieved the
> fields through the website. As soon as I cut and paste the new chinese
> characters onto the field that I want to replace, the website will
> display ? with the fields that I just updated.
>
> I have installed the Chinese (Taiwan) letters and enable my language
> toolbar. During the cut and paste process, I also switched to "CH".
> Any idea?
> Please help.
>
> Thanks in advance,
> Tony
>|||Kenneth,
Can you explain, how I can do this. I am a newbie.
Thanks,
Tony

checksum_agg on field list not working

Help this was working. Moved on to another DB.
Whenever I use checksum_agg on the result of Binary checksum (with a
field list) always returns zero.
When I try with * - all columns it works.
Cannot use all columns - too slow
select distinct binary_checksum('sp1,sp2,sp3,sp4,sp5,sp6
') from myTable
===> 1949676592 (result)
select checksum_agg(binary_checksum('sp1,sp2,sp
3,sp4,sp5,sp6')) from
myTable
===> 0 (result)
select checksum_agg(1949676592)
===> 1949676592 (result)
select checksum_agg(binary_checksum(*)) from myTable
===> -2019434987 (result)
select checksum_agg(binary_checksum(-2019434987))
===> -2019434987 (result)
? have no ideaSorted Field list should not be in quotes

CheckSum in SQL2000

I want to use checksum function for ntext data field in a table. But this
function accepts only varchar. Is there a way i can find checksum for ntext
fields in SQL 2000.
Thanks!!!I think you can cast it to a varchar
ie checksum(cast(field1 as varchar(8000))|||If you absolutely need to use CHECKSUM on the ntext field and, depending on
the size of the ntext values you already have in the specific table, you
might be able to convert the ntext to nchar/nvarchar or char/varchar and
apply checksum afterwards:
create table ENTEXT
(ID int, txt ntext)
insert into ENTEXT
values (1, 'any value')
select CHECKSUM(convert(char(8000),txt))
, CHECKSUM(convert(nchar(4000),txt))
from ENTEXT
drop table ENTEXT
The only limit would be the number of characters you can house temporarily
(4000 for nchar/nvarchar or 8000 for char/varchar) during the conversion
"skg" wrote:

> I want to use checksum function for ntext data field in a table. But this
> function accepts only varchar. Is there a way i can find checksum for ntex
t
> fields in SQL 2000.
> Thanks!!!
>
>|||Thanks All!!!
Mostly my text fields are more than 8k. I am just curious will the checksum
work if i do a substring and
compute checksum of individual chunks of 4k and finally call
checksum_agg(checksum(value)) to
get the aggregate checksum for the data field.
TIA
"Edgardo Valdez, MCSD, MCDBA"
<EdgardoValdezMCSDMCDBA@.discussions.microsoft.com> wrote in message
news:86760E51-9059-4609-B55F-6000E5113C1D@.microsoft.com...
> If you absolutely need to use CHECKSUM on the ntext field and, depending
> on
> the size of the ntext values you already have in the specific table, you
> might be able to convert the ntext to nchar/nvarchar or char/varchar and
> apply checksum afterwards:
> create table ENTEXT
> (ID int, txt ntext)
> insert into ENTEXT
> values (1, 'any value')
> select CHECKSUM(convert(char(8000),txt))
> , CHECKSUM(convert(nchar(4000),txt))
> from ENTEXT
> drop table ENTEXT
> The only limit would be the number of characters you can house temporarily
> (4000 for nchar/nvarchar or 8000 for char/varchar) during the conversion
>
>
> "skg" wrote:
>|||Would checksuming only the first 8000 characters suffice? For what purpose
are you wanting to use the checksum?
"skg" <skg@.yahoo.com> wrote in message
news:uNJQatfIGHA.3000@.TK2MSFTNGP14.phx.gbl...
>I want to use checksum function for ntext data field in a table. But this
>function accepts only varchar. Is there a way i can find checksum for ntext
>fields in SQL 2000.
> Thanks!!!
>|||JT thanks!!!
We have pdf documents which are saved in db in text field. we want to remove
duplicates.
thx
"JT" <someone@.microsoft.com> wrote in message
news:%23Gw84IpIGHA.208@.tk2msftngp13.phx.gbl...
> Would checksuming only the first 8000 characters suffice? For what purpose
> are you wanting to use the checksum?
> "skg" <skg@.yahoo.com> wrote in message
> news:uNJQatfIGHA.3000@.TK2MSFTNGP14.phx.gbl...
>|||This is the problem with storing unstructured data (or at least data with an
unfamiliar structure) in a relational database, so identifying rows
containing duplicate 'values' is not simple.
Think about how PDF documents are structured internally. I'm not familiar
with the PDF format specifically, but I'm guessing it begins with a header
containing meta data fields about the documents's size, the file name the
originaly saved as, the date/time created and last written to, and probably
even it's own internal checksum values. Therefore, even if the documents are
several hundred KBs or even MBs in size, only the first 8000 or 4000 bytes
or so may uniquely identify them. You don't need to actually parse this
information, just understand it's a stream of unique bytes and create a
checksum value off of it. This would apply not just to PDFs but also to MS
Word, JPGs, ZIPs or most any structured file type. You will need to read up
on the specifications involved and experiment to confirm this theory would
consistently work. I'm interested in the results, becuase I could possibly
use this technique myself, so reply back to the group when you discover
something.
"skg" <skg@.yahoo.com> wrote in message
news:OKPXYxvIGHA.3176@.TK2MSFTNGP12.phx.gbl...
> JT thanks!!!
> We have pdf documents which are saved in db in text field. we want to
> remove duplicates.
> thx
> "JT" <someone@.microsoft.com> wrote in message
> news:%23Gw84IpIGHA.208@.tk2msftngp13.phx.gbl...
>

checking to see if field value is unique

When entering a value into a SQL database is there a way to find out if that value has already been used in that field?

eg. If I were entering a last name into a field called "user_name" could I add some validation whereby the record couldn't be entered if someone else had already inserted that last name?You could:

1) use unique index in the user_name column to prevent duplicate values (i.e first name and last name into separate columns as well and target the index to the last name column). In this case DB throws an error if duplicate insert is tried.

2) you could also check the existence before inserting into table, something like:

IF (SELECT count(*) FROM MyTable WHERE Last_name='thename') > 0
BEGIN /*There is such last name already*/

END

ELSE /*There isn't such in the table*/

BEGIN

END|||Definitely create the unique contraint on the columns you don't want duplicated, as suggested. This will ensure that duplicate values cannot physically be entered.

You should also check for the existence before inserting into the table, so that you can gracefully capture the error and return user-friendly information back to the user. But, instead of the method suggested, you should try using EXISTS, which will stop processing as soon as the condition is met

IF EXISTS(SELECT NULL FROM MyTable WHERE Last_name='thename')
BEGIN /*There is such last name already*/
END
ELSE /*There isn't such in the table*/
BEGIN
END

Terri

Checking for special characters

When passed a last name as input into a stored procedure I need to verify the
last name in a field containing the last name and location. However the last
name is the first word starting from the left up until a space or a special
character like a '-,/,*,_' (i.e. Thomas-Hawaii, Thomas California,
Thomas*Texas etc). I just want to go through the table and find matches for
"Thomas". I tried to use char index and substring but I recieve the error
"Invalid length parameter passed to the substring function" when checking
for a special character that is not present. Does anyone know of an easy way
to accomplish this? Thanks in advance.
Gordon
Hi Gordon
"gordon" wrote:

> When passed a last name as input into a stored procedure I need to verify the
> last name in a field containing the last name and location. However the last
> name is the first word starting from the left up until a space or a special
> character like a '-,/,*,_' (i.e. Thomas-Hawaii, Thomas California,
> Thomas*Texas etc). I just want to go through the table and find matches for
> "Thomas". I tried to use char index and substring but I recieve the error
> "Invalid length parameter passed to the substring function" when checking
> for a special character that is not present. Does anyone know of an easy way
> to accomplish this? Thanks in advance.
> Gordon
You could use a function to determine the length or the appropriate
substring such as
CREATE FUNCTION dbo.GetFirstName(@.name varchar(50))
RETURNS varchar(50)
AS
BEGIN
DECLARE @.returnname varchar(50)
SELECT @.returnname = LEFT(@.name,len)
FROM
( SELECT MIN(len)-1 AS len
FROM
( SELECT CHARINDEX('-',@.name) as LEN
UNION ALL SELECT CHARINDEX('*',@.name)
UNION ALL SELECT CHARINDEX('_',@.name)
UNION ALL SELECT CHARINDEX('/',@.name)
UNION ALL SELECT CHARINDEX(SPACE(1),@.name)
UNION ALL SELECT LEN(@.name)+1 ) A
WHERE len > 0 ) l
RETURN (@.returnname)
END
SELECT dbo.GetFirstName(name)
FROM
( SELECT 'George*Texas' AS name
UNION ALL SELECT 'Thomas-Hawaii'
UNION ALL SELECT 'Arnie California' ) a
John

Checking for special characters

When passed a last name as input into a stored procedure I need to verify the
last name in a field containing the last name and location. However the last
name is the first word starting from the left up until a space or a special
character like a '-,/,*,_' (i.e. Thomas-Hawaii, Thomas California,
Thomas*Texas etc). I just want to go through the table and find matches for
"Thomas". I tried to use char index and substring but I recieve the error
"Invalid length parameter passed to the substring function" when checking
for a special character that is not present. Does anyone know of an easy way
to accomplish this? Thanks in advance.
GordonHi Gordon
"gordon" wrote:
> When passed a last name as input into a stored procedure I need to verify the
> last name in a field containing the last name and location. However the last
> name is the first word starting from the left up until a space or a special
> character like a '-,/,*,_' (i.e. Thomas-Hawaii, Thomas California,
> Thomas*Texas etc). I just want to go through the table and find matches for
> "Thomas". I tried to use char index and substring but I recieve the error
> "Invalid length parameter passed to the substring function" when checking
> for a special character that is not present. Does anyone know of an easy way
> to accomplish this? Thanks in advance.
> Gordon
You could use a function to determine the length or the appropriate
substring such as
CREATE FUNCTION dbo.GetFirstName(@.name varchar(50))
RETURNS varchar(50)
AS
BEGIN
DECLARE @.returnname varchar(50)
SELECT @.returnname = LEFT(@.name,len)
FROM
( SELECT MIN(len)-1 AS len
FROM
( SELECT CHARINDEX('-',@.name) as LEN
UNION ALL SELECT CHARINDEX('*',@.name)
UNION ALL SELECT CHARINDEX('_',@.name)
UNION ALL SELECT CHARINDEX('/',@.name)
UNION ALL SELECT CHARINDEX(SPACE(1),@.name)
UNION ALL SELECT LEN(@.name)+1 ) A
WHERE len > 0 ) l
RETURN (@.returnname)
END
SELECT dbo.GetFirstName(name)
FROM
( SELECT 'George*Texas' AS name
UNION ALL SELECT 'Thomas-Hawaii'
UNION ALL SELECT 'Arnie California' ) a
John

Checking for special characters

When passed a last name as input into a stored procedure I need to verify th
e
last name in a field containing the last name and location. However the las
t
name is the first word starting from the left up until a space or a special
character like a '-,/,*,_' (i.e. Thomas-Hawaii, Thomas California,
Thomas*Texas etc). I just want to go through the table and find matches fo
r
"Thomas". I tried to use char index and substring but I recieve the error
"Invalid length parameter passed to the substring function" when checking
for a special character that is not present. Does anyone know of an easy wa
y
to accomplish this? Thanks in advance.
GordonHi Gordon
"gordon" wrote:

> When passed a last name as input into a stored procedure I need to verify
the
> last name in a field containing the last name and location. However the l
ast
> name is the first word starting from the left up until a space or a specia
l
> character like a '-,/,*,_' (i.e. Thomas-Hawaii, Thomas California,
> Thomas*Texas etc). I just want to go through the table and find matches
for
> "Thomas". I tried to use char index and substring but I recieve the error
> "Invalid length parameter passed to the substring function" when checking
> for a special character that is not present. Does anyone know of an easy
way
> to accomplish this? Thanks in advance.
> Gordon
You could use a function to determine the length or the appropriate
substring such as
CREATE FUNCTION dbo.GetFirstName(@.name varchar(50))
RETURNS varchar(50)
AS
BEGIN
DECLARE @.returnname varchar(50)
SELECT @.returnname = LEFT(@.name,len)
FROM
( SELECT MIN(len)-1 AS len
FROM
( SELECT CHARINDEX('-',@.name) as LEN
UNION ALL SELECT CHARINDEX('*',@.name)
UNION ALL SELECT CHARINDEX('_',@.name)
UNION ALL SELECT CHARINDEX('/',@.name)
UNION ALL SELECT CHARINDEX(SPACE(1),@.name)
UNION ALL SELECT LEN(@.name)+1 ) A
WHERE len > 0 ) l
RETURN (@.returnname)
END
SELECT dbo.GetFirstName(name)
FROM
( SELECT 'George*Texas' AS name
UNION ALL SELECT 'Thomas-Hawaii'
UNION ALL SELECT 'Arnie California' ) a
John

Checking for NULL dates in Report

Hi,
I have a date field in my report dataset. I want to check for NULL date
before displaying it in the report. I have tried using the exp
=IIF(IsNothing(Fields!Date.Value),"",Month(Fields!Date.Value)&"/"&Day(Fields!Date.Value)&"/"&Year(Fields!Date.Value)).
But this doesnt work. I see #error in my report where ever date value is
NULL or blank. I have also tried using Len and IsDate functions but they too
dont seem to work. Can anyone help me ?
Thanks,
RKHi,
Tue and False part are evaluated in a IIF function. That's why you have an
#error.
You can use Visibility property.
--
Jean-Pierre Riehl
http://blog.djeepy1.net
http://www.bewise.fr
"ramakrishna" <ramakrishna@.translogicsys.com> wrote in message
news:O2Cm3S%23ZIHA.6140@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I have a date field in my report dataset. I want to check for NULL date
> before displaying it in the report. I have tried using the exp
> =IIF(IsNothing(Fields!Date.Value),"",Month(Fields!Date.Value)&"/"&Day(Fields!Date.Value)&"/"&Year(Fields!Date.Value)).
> But this doesnt work. I see #error in my report where ever date value is
> NULL or blank. I have also tried using Len and IsDate functions but they
> too dont seem to work. Can anyone help me ?
> Thanks,
> RK
>
>
>|||Hi,
Thanks for the reply. I want to display the date only when its not null. So
I have used the IIF expression. But the checking using the function
IsNothing or IsDate doesnt seem to work.
--
RK
"Jean-Pierre Riehl" <jean-pierre.riehl@.bewise.fr> wrote in message
news:19928F8F-CDF8-4F4F-8C6A-C8B50D13C5C7@.microsoft.com...
> Hi,
> Tue and False part are evaluated in a IIF function. That's why you have an
> #error.
> You can use Visibility property.
> --
> Jean-Pierre Riehl
> http://blog.djeepy1.net
> http://www.bewise.fr
>
> "ramakrishna" <ramakrishna@.translogicsys.com> wrote in message
> news:O2Cm3S%23ZIHA.6140@.TK2MSFTNGP02.phx.gbl...
>> Hi,
>> I have a date field in my report dataset. I want to check for NULL date
>> before displaying it in the report. I have tried using the exp
>> =IIF(IsNothing(Fields!Date.Value),"",Month(Fields!Date.Value)&"/"&Day(Fields!Date.Value)&"/"&Year(Fields!Date.Value)).
>> But this doesnt work. I see #error in my report where ever date value is
>> NULL or blank. I have also tried using Len and IsDate functions but they
>> too dont seem to work. Can anyone help me ?
>> Thanks,
>> RK
>>
>>
>|||On Feb 5, 11:49=A0pm, "ramakrishna" <ramakris...@.translogicsys.com>
wrote:
> Hi,
> Thanks for the reply. I want to display the date only when its not null. S=o
> I have used the IIF expression. But the checking using the function
> IsNothing or IsDate doesnt seem to work.
> --
> RK"Jean-Pierre Riehl" <jean-pierre.ri...@.bewise.fr> wrote in message
> news:19928F8F-CDF8-4F4F-8C6A-C8B50D13C5C7@.microsoft.com...
>
> > Hi,
> > Tue and False part are evaluated in a IIF function. That's why you have =an
> > #error.
> > You can use Visibility property.
> > --
> > Jean-Pierre Riehl
> >http://blog.djeepy1.net
> >http://www.bewise.fr
> > "ramakrishna" <ramakris...@.translogicsys.com> wrote in message
> >news:O2Cm3S%23ZIHA.6140@.TK2MSFTNGP02.phx.gbl...
> >> Hi,
> >> I have a date field in my report dataset. I want to check for NULL date=
> >> before displaying it in the report. I have tried using the exp
> >> =3DIIF(IsNothing(Fields!Date.Value),"",Month(Fields!Date.Value)&"/"&Day=(Field=ADs!Date.Value)&"/"&Year(Fields!Date.Value)).
> >> But this doesnt work. I see #error in my report where ever date value i=s
> >> NULL or blank. I have also tried using Len and IsDate functions but the=y
> >> too dont seem to work. Can anyone help me ?
> >> Thanks,
> >> RK- Hide quoted text -
> - Show quoted text -
Any value that returns "NULL" from your database is treated like a
"Nothing" object in .Net. Wherever possible, you will want to use the
IsNothing function. If I recall, Len(Nothing) returns Nothing, not 0
Second, the Month, Day and Year functions expect a Date object. It is
possible that you are passing them a Text fieldand it doesn't know how
to parse it. This could be why the function is returning a #error.
Third, when doing Date formatting, you should probably use the Format
function instead of breaking the date apart.
=3DIIF( IsNothing(Fields!Date.Value), "", Format( CDate(Fields!
Date.Value), "M/d/yyyy" ) )
-- Scott