clear log: value

Showing posts with label value. Show all posts

Wednesday, March 28, 2012

How to construct a cell value

My users have decided they want to use an alpha-numeric concatenation
for an identifier in their projects. The DB will use a bigint for a
record number, but I need to construct the value they'll use for their
"human"-readable value.
It'll go like this: A000, A001, A002, A003... B000, B001... AA00, AA01,
etc. Where each place is cycled through the alphabet first then 0-9.
I figure the first 2 places will give them enough project ids to last 10
years or so, but programming this thing needs to plan for longevity and
maintenance.
What is the best way to build this? Regular expressions, pure SQL in a
sproc? I need to have rules to build it appropriately and
systematically. I'll be writing the front ends in C#.NET.
Thanks in advance.
_E
*** Sent via Developersdex http://www.examnotes.net ***Why A001 is more human readable than 1?
Creating that kind of column will stop you from using bulk inserts without
traversing the set to calculate that value.
AMB
"Esteban404" wrote:

> My users have decided they want to use an alpha-numeric concatenation
> for an identifier in their projects. The DB will use a bigint for a
> record number, but I need to construct the value they'll use for their
> "human"-readable value.
> It'll go like this: A000, A001, A002, A003... B000, B001... AA00, AA01,
> etc. Where each place is cycled through the alphabet first then 0-9.
> I figure the first 2 places will give them enough project ids to last 10
> years or so, but programming this thing needs to plan for longevity and
> maintenance.
> What is the best way to build this? Regular expressions, pure SQL in a
> sproc? I need to have rules to build it appropriately and
> systematically. I'll be writing the front ends in C#.NET.
> Thanks in advance.
> _E
> *** Sent via Developersdex http://www.examnotes.net ***
>|||Thanks for the suggestions, Jim, leoinfo y el estimado Sr. Mesa, but this is
a central ID for all applications across multiple locations, so they need to
be unique.
What I've decided to do it use a table of seed values containing the low and
high values along with a "row" identity. I'll do the check in my object code
.
I tried to create a new numbering system, which would have been great, but
they do not want alpha in the places below 1000, i.e. A999 goes to B000, not
to A99A as in a number system. See the last one in the A999 example should b
e
AYYY then it goes to B000. The seed values fix that, but it's a cludge. No
doubt.
I'm sure it'll have at least 15 changes before it's finished, but I think
I've anticipated most of them. I've written some DOD stuff so version and
revision control code already exists that I can modify. I'll just act like
it's a life altering change. :-]
_E
"Jim Underwood" wrote:

> Why not just use the identifier that you already have and let them input a
> human readable description or short description?
> "Esteban404" <esteban> wrote in message
> news:eaB%23cc$VGHA.2040@.TK2MSFTNGP15.phx.gbl...
>
>sql

Monday, March 12, 2012

How to configur a column to be unique value from the Enterprize Ma

Hi, I want one of the column in a table to not have duplicate values(it's
Varchar) but it's not the primary key column which is a auto-generated id
column for the table.
Thanks,
AlphaCreate a unique constraint on the column
http://sqlservercode.blogspot.com/
"Alpha" wrote:
> Hi, I want one of the column in a table to not have duplicate values(it's
> Varchar) but it's not the primary key column which is a auto-generated id
> column for the table.
> Thanks,
> Alpha|||Sorry I wasn't clear on my question. I know I need to set the unique
constraint but just didn't know where is Enterprise Manager is the setting.
I just found it now in the design and right click on the column. thanks for
your help anyway.
"SQL" wrote:
> Create a unique constraint on the column
> http://sqlservercode.blogspot.com/
>
> "Alpha" wrote:
> > Hi, I want one of the column in a table to not have duplicate values(it's
> > Varchar) but it's not the primary key column which is a auto-generated id
> > column for the table.
> >
> > Thanks,
> > Alpha

How to configur a column to be unique value from the Enterprize Ma

Friday, March 9, 2012

How to concatenate two fields in a textbox one integer and other charecter field

I have the folliwing two fields, want tp concatenate:

=Fields!sequenceno.Value & =Fields!LogType.Value

Thank you very much for the information.

Convert the integer to a varchar explicitly.

Adamus

|||

Remove the second equals sign.

=Fields!sequenceno.Value & Fields!LogType.Value

How to concatenate constants and expressions in report field

I was receiving [BC30205] End of statement expected Error
when I tryed to concatenate:

=INT(AVG(Fields!HT.Value)/360) ":" &INT(AVG(Fields!HT.Value)/60)

Please hlp!
Thank you.

You must convert to a string type.

= CSTR(INT(AVG(Fields!HT.Value)/360))+":"+CSTR(INT(AVG(Fields!HT.Value)/60))

How to compare values in different rows?

I’ve a table like this (in SQL Server 2000).

v1 v2
2 8
7 10
11 15

13 17

v1 value of a row must be grater than v2 value of previous row, other wise I need to display that row, in above example, I need to display 2nd & 4th rows.

please advise

Thanks

In order to best get this to work you need another row to explicitly sequence the data -- such as ROW_ID or something. The otherwise, you can use Transact SQL extensions, but without an explicit sequence of some kind, the results will be volatile / unpredictable. It would probably be best to avoid the extensions and stick to a CTE here if you can.

Code Snippet

declare @.ex table(id int, v1 int, v2 int)
insert @.ex
select 1, 2, 8 union all
select 2, 7, 10 union all
select 3, 11,15 union all
select 4, 13,17
--select * from @.ex

select a.id, a.v1, a.v2
from @.ex a
join @.ex b
on a.id - 1 = b.id
and a.v1 <= b.v2

/*
id v1 v2
-- -- --
2 7 10
4 13 17
*/

|||

Do you have a field in the table that will indicate what is the 'previous row'? (Something like an IDENTITY field, or datetime of entry, etc.)

|||

The Table is

ID v1 v2
1 2 8
2 7 10
3 11 15

4 13 17

|||

Here it is,

Code Snippet

Create Table #data (

[ID] INT ,

[v1] INT ,

[v2] INT

);

Insert Into #data Values('1','2','8');

Insert Into #data Values('2','7','10');

Insert Into #data Values('3','11','15');

Insert Into #data Values('4','13','17');

Select

Down.*

from

#data Up

Join #data down On Up.Id = Down.Id-1

Where

Up.V2>=down.V1

|||

As long as you have that ID field, you can use the query

Code Snippet

CREATE TABLE prevRowTest (

ID INT,

v1 INT,

v2 INT

)

INSERT INTO prevRowTest VALUES (1,2,8)

INSERT INTO prevRowTest VALUES (2,7,10)

INSERT INTO prevRowTest VALUES (3,11,15)

INSERT INTO prevRowTest VALUES (4,13,17)

SELECT prt.ID, prt.v1, prt.v2

FROM prevRowTest prt JOIN

prevRowTest prev ON prt.id = (prev.ID + 1)

WHERE prt.v1 <= prev.v2

|||

Thanks a lot.

I got my Answer

With Regards

Vijay

How to compare value pair of one table with value pair of another table

Here is the problem:
--------
create table A( a1 int, a2 int)
create table B( b1 int, b2 int)

insert A values(1,1)
insert A values(1,2)
insert A values(2,1)
insert A values(2,3)
insert A values(3,1)
insert A values(3,3)

insert B values(1,1)
insert B values(1,2)
insert B values(3,2)
insert B values(2,2)

What is the SQL query to find out the pairs existing in B, but not in A ?

I can solve this by using string functions, but my problem is I have to write a query that can execute on MSSQL, DB2 and ORACLE, and as you know they have differnt syntax for string functions.

Thanks in advance.

Regards,
s99shah.pairs in B but not in A? left outer join, check for unmatched rows --
select b1, b2
from B
left outer
join A
on b1 = a1
and b2 = a2
where a1 is null

rudy
http://r937.com/|||Hi,

TRY THIS

Select B.* from a,b
where (+) a1 = b1;

how to compare value of two fileds and based on that insert value into third fileds

Hi,

I have a database with table name as test in that i have 6 colums
they are

name varchar (20)
address varchar (20)
position varchar (20)
nametype1 varchar (20)
nametype2 varchar (20)
nameval varchar(20)

now in the nametype1 and nametype2 there are values like
nametype1 nametype2
"AB" "BA"
"BB" "BB"
"AA" "AA"
"BA" "AB"

now depending upon the combination i want to assign value to the thrid
field that is nameval like example below

nametype1 nametype2 nameval
"AB" "BA" 1
"BB" "BB" 2
"AA" "AA" 2
"BA" "AB" 1

please suggest query in sql which i can run to do this .

RegardsHi,

You do not specify what is the logic for generating the values for the
nameval column, but based on your example seems it is 2 when the values are
equal and 1 when they are different.

Based on that assumption, here is a query that will do the update:

update test
set nameval = (case when nametype1 = nametype2 then 2 else 1 end)

Regards,

Plamen Ratchev
http://www.SQLStudio.com|||On Jan 30, 6:47 pm, "Plamen Ratchev" <Pla...@.SQLStudio.comwrote:

Quote:

Originally Posted by

Hi,
>
You do not specify what is the logic for generating the values for the
nameval column, but based on your example seems it is 2 when the values are
equal and 1 when they are different.
>
Based on that assumption, here is a query that will do the update:
>
update test
set nameval = (case when nametype1 = nametype2 then 2 else 1 end)
>
Regards,
>
Plamen Ratchevhttp://www.SQLStudio.com

hi i have an update to the query , please suggest if the table
structure and results are below then what should i run for no of
fileds

name varchar (20)
address varchar (20)
position varchar (20)
nametype1 varchar (20)
nametype2 varchar (20)
nametype3 varchar(20)
nametype4 varchar(20)
nameval varchar(20)
nameval1 varchar(20)
nameval2 varchar(20)
nameval3 varchar(20)

now in the nametype1 and nametype2 there are values like
nametype1 nametype2 nametype3 nametype4
"AB" "BA" "BB" "BB"
"AA" "AA" "BA" "AB"
"AB" "BA" "BB" "BB"
"AA" "AA" "BA" "AB"

now depending upon the combination i want to assign value to the thrid
field that is nameval like example below

nametype1 nametype2 nameval
"AB" "BA" 1
"AA" "AA" 2
"AB" "BA" 1
"AA" "AA" 2

nametype1 nametype3 nameval1
"AB" "BB" 1
"AA" "BA" 1
"AB" "BB" 1
"AA" "BA" 1

nametype1 nametype4 nameval2
"AB" "BB" 1
"AA" "AB" 1
"AB" "BB" 1
"AA" "AB" 1

please suggest query in sql which i can run to do this also i would
like to know is it possible to have some kind of loop which can check
each nametype with other like the combination above please suggest.

Regards|||I am still confused about the logic and the purpose of this, but since it
seems to follow the same pattern (when equal then 2 else 1), here it is (you
just keep repeating the same for the other "nameval" columns):

update test
set nameval = (case when nametype1 = nametype2 then 2 else 1 end),
nameval1 = (case when nametype1 = nametype3 then 2 else 1 end),
nameval2 = (case when nametype1 = nametype4 then 2 else 1 end)

Also, you can create those "nameval" columns as computed columns and then
you do not have to run the update statements. Something like this:

create table test(
name varchar (20),
address varchar (20),
position varchar (20),
nametype1 varchar (20),
nametype2 varchar (20),
nametype3 varchar(20),
nametype4 varchar(20),
nameval as (case when nametype1 = nametype2 then 2 else 1 end),
nameval1 as (case when nametype1 = nametype3 then 2 else 1 end),
nameval2 as (case when nametype1 = nametype4 then 2 else 1 end))

insert into test (nametype1, nametype2, nametype3, nametype4) values ('AA',
'AA', 'AB', 'BA')

select * from test

drop table test

Perhaps the table should be normalized too, but since no requirements are
given I do not want to guess...

HTH,

Plamen Ratchev
http://www.SQLStudio.com|||On Jan 31, 11:42 am, "Plamen Ratchev" <Pla...@.SQLStudio.comwrote:

Quote:

Originally Posted by

I am still confused about the logic and the purpose of this, but since it
seems to follow the same pattern (when equal then 2 else 1), here it is (you
just keep repeating the same for the other "nameval" columns):
>
update test
set nameval = (case when nametype1 = nametype2 then 2 else 1 end),
nameval1 = (case when nametype1 = nametype3 then 2 else 1 end),
nameval2 = (case when nametype1 = nametype4 then 2 else 1 end)
>
Also, you can create those "nameval" columns as computed columns and then
you do not have to run the update statements. Something like this:
>
create table test(
name varchar (20),
address varchar (20),
position varchar (20),
nametype1 varchar (20),
nametype2 varchar (20),
nametype3 varchar(20),
nametype4 varchar(20),
nameval as (case when nametype1 = nametype2 then 2 else 1 end),
nameval1 as (case when nametype1 = nametype3 then 2 else 1 end),
nameval2 as (case when nametype1 = nametype4 then 2 else 1 end))
>
insert into test (nametype1, nametype2, nametype3, nametype4) values ('AA',
'AA', 'AB', 'BA')
>
select * from test
>
drop table test
>
Perhaps the table should be normalized too, but since no requirements are
given I do not want to guess...
>
HTH,
>
Plamen Ratchevhttp://www.SQLStudio.com

is it possible to run the query in existing table , please suggest how
and also if there more than 20 nametypes is there query which can loop
through all the name types and do the job.

please suggest any reference too if any.

Regards|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.

Even your narrative is wrong. Did you know that double quotes are not
used for string values in SQL? Have you heard of ISO-11179 rules for
data element names? Absurd things like "name_type_1" look like a
repeating group in violationof 1NF which will lead to some really
horrible kludges and a loss of data integrity.

Please try again and pretend that you have to work from these specs
without any prior knowledge -- we do SQL, not mind-reading :)|||Yes, the query will update an existing table, just keep adding lines for
each column, like this:

As for you reference to do the update in a loop, then this is doable via
dynamic SQL. Here is a quick sketch of how it may look (by adjusting the
number 10 you will get different number of columns, beware of the 4000
character limit on the SQL string):

DECLARE @.sql nvarchar(4000),
@.count int,
@.numcolumns int

SELECT @.sql = 'update test set ', @.count = 1, @.numcolumns = 10

WHILE @.count <= @.numcolumns
BEGIN
IF @.count = 1
SELECT @.sql = @.sql + 'nameval = (case when nametype1 = nametype' +
CAST(@.count + 1 as nvarchar) + ' then 2 else 1 end)'
ELSE
SELECT @.sql = @.sql + ', nameval' + CAST(@.count - 1 as nvarchar) + ' =
(case when nametype1 = nametype' + CAST(@.count + 1 as nvarchar) + ' then 2
else 1 end)'

SELECT @.count = @.count + 1
END

EXEC(@.sql)

Erland Sommarskog has an excellent guide on dynamic SQL at
http://www.sommarskog.se/dynamic_sql.html. I would recommend reading it
before jumping into using dynamic SQL. Also, please read the comment from
Celko, he is correct that without posting DLL and specifications it is very
difficult to get good answers.

Regards,

Plamen Ratchev
http://www.SQLStudio.com|||Tradeorganizer wrote:

Quote:

Originally Posted by

I have a database with table name as test in that i have 6 colums
they are
>
name varchar (20)
address varchar (20)
position varchar (20)
nametype1 varchar (20)
nametype2 varchar (20)
nameval varchar(20)
>
now in the nametype1 and nametype2 there are values like
nametype1 nametype2
"AB" "BA"
"BB" "BB"
"AA" "AA"
"BA" "AB"
>
now depending upon the combination i want to assign value to the thrid
field that is nameval like example below
>
nametype1 nametype2 nameval
"AB" "BA" 1
"BB" "BB" 2
"AA" "AA" 2
"BA" "AB" 1
>
please suggest query in sql which i can run to do this .

A couple of things that others have touched on, but not made
quite this explicit:

/Why/ should nametype1 = "AB" and nametype2 = "BA" lead to
nameval = 1? What is the general rule that you want to apply?

Why do you have more than 20 nametypes? Please give serious
consideration to splitting this table into two tables, e.g.

[table1]
person_id, name, address, position
1, 'John Doe', '123 Cherry Lane', 'Regional Manager'
2, 'Thomas Atkins', '987 Easy Street', 'President'

[table2]
person_id, nameindex, nametype, nameval
1, 1, 'AB', 1
1, 2, 'BA', 1
2, 1, 'BB', 2
2, 2, 'BB', 2|||Thanks for great help , yes its working for me.

Thanks to all for taking time to explain me.

Regards

Ed Murphy wrote:

Quote:

Originally Posted by

Tradeorganizer wrote:
>

Quote:

Originally Posted by

I have a database with table name as test in that i have 6 colums
they are

name varchar (20)
address varchar (20)
position varchar (20)
nametype1 varchar (20)
nametype2 varchar (20)
nameval varchar(20)

now in the nametype1 and nametype2 there are values like
nametype1 nametype2
"AB" "BA"
"BB" "BB"
"AA" "AA"
"BA" "AB"

now depending upon the combination i want to assign value to the thrid
field that is nameval like example below

nametype1 nametype2 nameval
"AB" "BA" 1
"BB" "BB" 2
"AA" "AA" 2
"BA" "AB" 1

please suggest query in sql which i can run to do this .

>
A couple of things that others have touched on, but not made
quite this explicit:
>
/Why/ should nametype1 = "AB" and nametype2 = "BA" lead to
nameval = 1? What is the general rule that you want to apply?
>
Why do you have more than 20 nametypes? Please give serious
consideration to splitting this table into two tables, e.g.
>
[table1]
person_id, name, address, position
1, 'John Doe', '123 Cherry Lane', 'Regional Manager'
2, 'Thomas Atkins', '987 Easy Street', 'President'
>
[table2]
person_id, nameindex, nametype, nameval
1, 1, 'AB', 1
1, 2, 'BA', 1
2, 1, 'BB', 2
2, 2, 'BB', 2

how to compare value of two fileds and based on that insert value into third fileds

Hi,
I have a database with table name as test in that i have 6 colums
they are
name varchar (20)
address varchar (20)
position varchar (20)
nametype1 varchar (20)
nametype2 varchar (20)
nameval varchar(20)
now in the nametype1 and nametype2 there are values like
nametype1 nametype2
"AB" "BA"
"BB" "BB"
"AA" "AA"
"BA" "AB"
now depending upon the combination i want to assign value to the thrid
field that is nameval like example below
nametype1 nametype2 nameval
"AB" "BA" 1
"BB" "BB" 2
"AA" "AA" 2
"BA" "AB" 1
please suggest query in sql which i can run to do this .
RegardsYou can use a computed column. Check this script:
CREATE TABLE dbo.t1
(nametype1 char(2),
nametype2 char(2),
nameval AS
CASE
WHEN nametype1 = nametype2 THEN 2
WHEN nametype1 = REVERSE(nametype2) THEN 1
ELSE 0 -- error?
END)
GO
INSERT INTO dbo.t1(nametype1, nametype2) VALUES ('AB','BA')
INSERT INTO dbo.t1(nametype1, nametype2) VALUES ('BB','BB')
INSERT INTO dbo.t1(nametype1, nametype2) VALUES ('AA','AA')
INSERT INTO dbo.t1(nametype1, nametype2) VALUES ('BA','AB')
SELECT *
FROM dbo.t1
--
Dejan Sarka
http://www.solidqualitylearning.com/blogs/
"Tradeorganizer" <tradeorganizer@.gmail.com> wrote in message
news:1170146292.237194.199580@.q2g2000cwa.googlegroups.com...
> Hi,
> I have a database with table name as test in that i have 6 colums
> they are
> name varchar (20)
> address varchar (20)
> position varchar (20)
> nametype1 varchar (20)
> nametype2 varchar (20)
> nameval varchar(20)
> now in the nametype1 and nametype2 there are values like
> nametype1 nametype2
> "AB" "BA"
> "BB" "BB"
> "AA" "AA"
> "BA" "AB"
> now depending upon the combination i want to assign value to the thrid
> field that is nameval like example below
> nametype1 nametype2 nameval
> "AB" "BA" 1
> "BB" "BB" 2
> "AA" "AA" 2
> "BA" "AB" 1
> please suggest query in sql which i can run to do this .
> Regards
>|||On Jan 30, 3:52 pm, "Dejan Sarka"
<dejan_please_reply_to_newsgroups.sa...@.avtenta.si> wrote:
> You can use a computed column. Check this script:
> CREATE TABLE dbo.t1
> (nametype1 char(2),
> nametype2 char(2),
> nameval AS
> CASE
> WHEN nametype1 = nametype2 THEN 2
> WHEN nametype1 = REVERSE(nametype2) THEN 1
> ELSE 0 -- error?
> END)
> GO
> INSERT INTO dbo.t1(nametype1, nametype2) VALUES ('AB','BA')
> INSERT INTO dbo.t1(nametype1, nametype2) VALUES ('BB','BB')
> INSERT INTO dbo.t1(nametype1, nametype2) VALUES ('AA','AA')
> INSERT INTO dbo.t1(nametype1, nametype2) VALUES ('BA','AB')
> SELECT *
> FROM dbo.t1
> --
> Dejan Sarkahttp://www.solidqualitylearning.com/blogs/
> "Tradeorganizer" <tradeorgani...@.gmail.com> wrote in message
> news:1170146292.237194.199580@.q2g2000cwa.googlegroups.com...
> > Hi,
> > I have a database with table name as test in that i have 6 colums
> > they are
> > name varchar (20)
> > address varchar (20)
> > position varchar (20)
> > nametype1 varchar (20)
> > nametype2 varchar (20)
> > nameval varchar(20)
> > now in the nametype1 and nametype2 there are values like
> > nametype1 nametype2
> > "AB" "BA"
> > "BB" "BB"
> > "AA" "AA"
> > "BA" "AB"
> > now depending upon the combination i want to assign value to the thrid
> > field that is nameval like example below
> > nametype1 nametype2 nameval
> > "AB" "BA" 1
> > "BB" "BB" 2
> > "AA" "AA" 2
> > "BA" "AB" 1
> > please suggest query in sql which i can run to do this .
> > Regards
instead of creatating in new table can i update the value in
existing table please suggest.|||UPDATE t1 SET nameval =CASE WHEN nametype1 = nametype2 THEN 1
WHEN nametype1 = REVERSE(nametype2) THEN 2
END
Regards
Amish Shah
http://shahamishm.tripod.com
On Jan 30, 4:16 pm, "Tradeorganizer" <tradeorgani...@.gmail.com> wrote:
> On Jan 30, 3:52 pm, "Dejan Sarka"
>
>
> <dejan_please_reply_to_newsgroups.sa...@.avtenta.si> wrote:
> > You can use a computed column. Check this script:
> > CREATE TABLE dbo.t1
> > (nametype1 char(2),
> > nametype2 char(2),
> > nameval AS
> > CASE
> > WHEN nametype1 = nametype2 THEN 2
> > WHEN nametype1 = REVERSE(nametype2) THEN 1
> > ELSE 0 -- error?
> > END)
> > GO
> > INSERT INTO dbo.t1(nametype1, nametype2) VALUES ('AB','BA')
> > INSERT INTO dbo.t1(nametype1, nametype2) VALUES ('BB','BB')
> > INSERT INTO dbo.t1(nametype1, nametype2) VALUES ('AA','AA')
> > INSERT INTO dbo.t1(nametype1, nametype2) VALUES ('BA','AB')
> > SELECT *
> > FROM dbo.t1
> > --
> > Dejan Sarkahttp://www.solidqualitylearning.com/blogs/
> > "Tradeorganizer" <tradeorgani...@.gmail.com> wrote in message
> >news:1170146292.237194.199580@.q2g2000cwa.googlegroups.com...
> > > Hi,
> > > I have a database with table name as test in that i have 6 colums
> > > they are
> > > name varchar (20)
> > > address varchar (20)
> > > position varchar (20)
> > > nametype1 varchar (20)
> > > nametype2 varchar (20)
> > > nameval varchar(20)
> > > now in the nametype1 and nametype2 there are values like
> > > nametype1 nametype2
> > > "AB" "BA"
> > > "BB" "BB"
> > > "AA" "AA"
> > > "BA" "AB"
> > > now depending upon the combination i want to assign value to the thrid
> > > field that is nameval like example below
> > > nametype1 nametype2 nameval
> > > "AB" "BA" 1
> > > "BB" "BB" 2
> > > "AA" "AA" 2
> > > "BA" "AB" 1
> > > please suggest query in sql which i can run to do this .
> > > Regards
> instead of creatating in new table can i update the value in
> existing table please suggest.- Hide quoted text -
> - Show quoted text -|||On Jan 30, 5:33 pm, "amish" <shahami...@.gmail.com> wrote:
> UPDATE t1 SET nameval => CASE WHEN nametype1 = nametype2 THEN 1
> WHEN nametype1 = REVERSE(nametype2) THEN 2
> END
> Regards
> Amish Shahhttp://shahamishm.tripod.com
> On Jan 30, 4:16 pm, "Tradeorganizer" <tradeorgani...@.gmail.com> wrote:
> > On Jan 30, 3:52 pm, "Dejan Sarka"
> > <dejan_please_reply_to_newsgroups.sa...@.avtenta.si> wrote:
> > > You can use a computed column. Check this script:
> > > CREATE TABLE dbo.t1
> > > (nametype1 char(2),
> > > nametype2 char(2),
> > > nameval AS
> > > CASE
> > > WHEN nametype1 = nametype2 THEN 2
> > > WHEN nametype1 = REVERSE(nametype2) THEN 1
> > > ELSE 0 -- error?
> > > END)
> > > GO
> > > INSERT INTO dbo.t1(nametype1, nametype2) VALUES ('AB','BA')
> > > INSERT INTO dbo.t1(nametype1, nametype2) VALUES ('BB','BB')
> > > INSERT INTO dbo.t1(nametype1, nametype2) VALUES ('AA','AA')
> > > INSERT INTO dbo.t1(nametype1, nametype2) VALUES ('BA','AB')
> > > SELECT *
> > > FROM dbo.t1
> > > --
> > > Dejan Sarkahttp://www.solidqualitylearning.com/blogs/
> > > "Tradeorganizer" <tradeorgani...@.gmail.com> wrote in message
> > >news:1170146292.237194.199580@.q2g2000cwa.googlegroups.com...
> > > > Hi,
> > > > I have a database with table name as test in that i have 6 colums
> > > > they are
> > > > name varchar (20)
> > > > address varchar (20)
> > > > position varchar (20)
> > > > nametype1 varchar (20)
> > > > nametype2 varchar (20)
> > > > nameval varchar(20)
> > > > now in the nametype1 and nametype2 there are values like
> > > > nametype1 nametype2
> > > > "AB" "BA"
> > > > "BB" "BB"
> > > > "AA" "AA"
> > > > "BA" "AB"
> > > > now depending upon the combination i want to assign value to the thrid
> > > > field that is nameval like example below
> > > > nametype1 nametype2 nameval
> > > > "AB" "BA" 1
> > > > "BB" "BB" 2
> > > > "AA" "AA" 2
> > > > "BA" "AB" 1
> > > > please suggest query in sql which i can run to do this .
> > > > Regards
> > instead of creatating in new table can i update the value in
> > existing table please suggest.- Hide quoted text -
> > - Show quoted text -
hi i have an update to the query , please suggest if the table
structure and results are below then what should i run for no of
fileds
name varchar (20)
address varchar (20)
position varchar (20)
nametype1 varchar (20)
nametype2 varchar (20)
nametype3 varchar(20)
nametype4 varchar(20)
nameval varchar(20)
nameval1 varchar(20)
nameval2 varchar(20)
nameval3 varchar(20)
now in the nametype1 and nametype2 there are values like
nametype1 nametype2 nametype3 nametype4
"AB" "BA" "BB" "BB"
"AA" "AA" "BA" "AB"
"AB" "BA" "BB" "BB"
"AA" "AA" "BA" "AB"
now depending upon the combination i want to assign value to the thrid
field that is nameval like example below
nametype1 nametype2 nameval
"AB" "BA" 1
"AA" "AA" 2
"AB" "BA" 1
"AA" "AA" 2
nametype1 nametype3 nameval1
"AB" "BB" 1
"AA" "BA" 1
"AB" "BB" 1
"AA" "BA" 1
nametype1 nametype4 nameval2
"AB" "BB" 1
"AA" "AB" 1
"AB" "BB" 1
"AA" "AB" 1
please suggest query in sql which i can run to do this also i would
like to know is it possible to have some kind of loop which can check
each nametype with other like the combination above please suggest.
Regards

Wednesday, March 7, 2012

how to compare current date with sql db datetime data type

Hi,

I am using one datetime data type ( name: date_added ) and getdate() as default value. I want to display only those records added today. How I can compare current date with date_added.

Thanks
ManojSELECT * FROM tablename WHERE CONVERT(varchar(10),date_added,112)=CONVERT(varchar(10),GetDate(),112)

This converts both dates to yyyymmdd format, and then compares them. A better alternative might be:

SELECT * FROM tablename WHERE date_added>=CONVERT(datetime,CONVERT(varchar(10),GetDate(),112))

This presumes that there are no records added later than today. It compares the date_added to getdate() return value, converted to yyyymmdd, then converted back to a date.

How to Compare 2007-9-11 and Month(GetDate()) ?

I am going to compare thie value 2007-9-11 (this value was retrived from the column(TxnDate) in my DataBase, type is DateTime)

I write code

select * from ZT_ModifyLog where Year(TXnDate) = Year(GetDate()) AND ( ( Month(TXnDate) < Month(GetDate()) ) and Month(TXnDate) >= Month(GetDate())-1)

it will like

select * from ZT_ModifyLog where Year(2007-9-11 ) = Year(GetDate()) AND ( ( Month(2007-9-11 ) < Month(GetDate()) ) and Month(2007-9-11 ) >= Month(GetDate())-1)

→ select * from ZT_ModifyLog where TxnDate(2007) = 2007 AND 10 < GetDate(10) and 9 >= 9 so return TXnDate between 2007/9/1~ 2007/9/30

but what if Month(GetDate())-13)?? when the -1 biger than 12... I guess the code will cause error ... but can't think out how to avoid and change my code

pleae help... thank you very much

Please do not open multiple threads for same question. Refer:http://forums.asp.net/t/1176161.aspx.

Friday, February 24, 2012

how to combine fromdate and todate values in one field

Hi All,

I have to fetch FromDate and Todate values from the table like this.Suppose Fromdate value is 02-Feb-2007 and Todate Value is 04-Feb-2007,then my need is to get the date value like this.....Feb 2-4,2007or 2-4 Feb,2007.Can anybody know the syntax or code?.I am using sql Server and fromdate and todate values are stored in two different feilds in table.

Thanks and Regards

This seems to be a duplicate post? I posted an answer in the other post, here it is again. This assumes two columns: from_date and to_date. If it fixes your problem mark one or both as answered!

Selectconvert(varchar(2),DatePart(day, from_date)) +'-' +convert(varchar(2),DatePart(day, to_date))+' '+substring(convert(varchar(12), to_date, 106), 4, 8)from [yourtable]

How to code ASP.NET page with return value Store Procedure?

Does anyone know how to call a SQL store procedure that return a value to the page?

I've a simple data entry aspx page with several textboxes and a save button. When user fill out the form and click save/submit, it calls a store procedure to insert a row into a SQL table and automatically generate an ID that need to return the the page to display for the user.

Are there a similar article somewhere?

Thank you all!

check out the docs about using ExecuteScalar() Method.|||

Using ExecuteScalar() requires a separate procedure call. I would like to use the same procedure call that to insert a record into a table and also return an ID back. My store procedure look like this:

sqlString = "EXEC spInsertRow 'parm1','parm2','parm3', @.ReturnID OUTPUT"

Thanks

|||

In the end of your sproc:

SELECT @.ReturnID = @.@.IDENTITY

|||

I suggested ExecuteScalar assuming you are doing some insert and want to return the ID.

If that is not the case you can use the OUTPUT parameters..

Dim res as integer

myParam = mycommand.CreateParameter()
myParam.ParameterName = "@.returnId"
myParam.Direction = ParameterDirection.Output
myParam.SqlDbType = SqlDbType.bigint
mycommand.Parameters.Add(myParam)

res = mycommand.Parameters("@.result").Value

|||

Thank you. It works

|||

Can you share your general procedure. I'm having the same issue and have tested many ways. Still not working.

See posthttp://forums.asp.net/1178243/ShowPost.aspx

Thanks

Sunday, February 19, 2012

How to choose next value for identity column

Hi,

I have a table with its ID field set as an Identity column with "seed" and "increment" set to 1. Now, I have rows in there that I don't want to change. I'd like the next addtitions to the table to start at a specific value for their ID columns.

Basically, how can I specify a new starting value for an identity column?

Thanks,

Skip.I found this in SQL Server books online as well as the web. This sounds like what you might be looking for.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_dbcc_5lv8.asp

how to choose a Primary Key

Hi All,

I have a dilemn:
On one side, I have a column C1 which could be a primary key because it is never null, the value is unique and identify the record. The problem is its a char type and its lenght can be close to 30.
Then, I've planned to add another column C2 of int type as PK. But then I need to add a unique constraint index on C1. Does it improve performance anyway?

ThanksOnce you've got a primary key, adding a Unique constraint doesn't help performance. You should still add the constraint, it just won't help your performance.

-PatP|||I beg to differ, because unique constraint implicitly creates a unique index, thus - possibility of improving performance where the field(-s) is/are involved.|||when all around you are losing their heads.....

to answer your question generally
SQL Server 2000 Requires the primary key to be:
Unique
Not Null
16 columns or less for a composite key.

i cant help you with your di-lemon because the pk is such a personal thing. realistically your pk is your own and shouldnt be fondled by any other so i will go out on a limb here and say that what you have is fine.

However, {Opinion} i am a big fan of the monotonic key. simple, to the point, and oh, so very integer...mmmmn i can see my key right now in a dimly lit room with nothing on but an identity of 1,1 and sumptuous nonclustered index fill-factored just right.

i need a smoke..|||i need a smoke..My first guess would be that you need a bit more than a smoke, but you'll probably need to visit another web site for that!

I beg to differ, because unique constraint implicitly creates a unique index, thus - possibility of improving performance where the field(-s) is/are involved.On a slightly more serious note, I see a declared Primary Key (PK) as being somewhere between very important and critical for a table. Very little relational algebra is possible without a PK.

An Alternate Key (AK) or the equivalent unique constraint is a different matter. The AK should be declared in order to allow the database engine to do its job and ensure uniqueness of the AK. However, there is a cost assosciated with the AK, in that CPU and I/O time and disk space need to be used to enforce it. The benefits of a declared (and enforced) AK are important, but they aren't nearly as important in my opinion as the PK is. I've been willing to forego AK definitions if the potential benefits didn't outweigh the time/disk needed to acheive them.

While rdjabarov is right, and the AK might be useful, there are times that it can do more harm than good. From the pure relational standpoint of managing the table, I see the AK as optional. Of course, if you need to ensure uniqueness or if you have queries that will use the index, and the cost of maintaining it is less than the expected benefit, then I'm in favor of it. In fact, I'll even say that I'm generally in favor of AK definitions, but that you still need to use some judgement instead of just jumping in and declaring them everywhere they could be used.

-PatP|||While ... the AK might be useful, there are times that it can do more harm than good.bumph and nonsense

let's do an informal poll

of all the different threads we've seen in database forums (and some of us visit more than this one), what's the most common question?

that's right, "how do i remove my duplicates?"

i would say that, on balance, you will do good for 99 people by insisting on the unique constraint on the alternate key, and harm for 1 person, although i am hard pressed to think of the circumstances where this might occur

besides, if you (not you, pat, the reader) are really worried about the alleged "harm" of the unique constraint, then the solution is simple -- drop the stupid identity column and make the alternate key the real primary key

:) :) :)|||However, {Opinion} i am a big fan of the monotonic key. simple, to the point, and oh, so very integer

if you can revive some imperical statistics on the PK as Intelligent key vs PK as Monotonic key that would be excellent. but remember, this is a preference situation. sometimes the candidate is easily qualified to be a key and sometimes the addition of a surrogate is just as qualified.

so what is it that makes this an issue.
the inclusion of an additional column into the table that is taking up space in the dbf? or is it the suspected performance derived from SARGs based on monotonic integers?

basically this whole crappy argument comes down to who can write their name in the snow the quickest.

but for the sake of argument i will side with trotsky. :p|||I guess I've just run into too many cases that push the limits of available hardware.

One example of this is when gathering digital data from medical equipment. You often get data at rates that stress available hardware to the limit, and that data comes from well defined data sources. You often can't afford anything other than a PK on the collection table.

Some of the columns are 200+ bytes wide, and there are six of those columns that form the Alternate Key. This practically doubles the size of the row for each AK index you use. The PK being only four bytes is negligable in comparison, and is a lot more valuable to me.

While a bigger machine with a lot more disk would solve this particular problem, it would add a lot of cost and almost no benefit to the process.

There are lots of cases where data is coming in at high speed to a single centralized server. Basically a web farm being serviced by an app farm being serviced by a SQL Server. In this case, one SQL Server might be effectively servicing 50000 simultaneous users as well as a dozen or so analysts. Even though you could create a natural key, and in this case you might even choose to declare it as an alternate, the hardware won't support using that natural key as a foreign key.

Don't get me wrong... As I said in my previoius post I'm generally in favor of declaring AK even when I can't afford to use them as a PK, as long as it makes sense in the real world. Lots of things are really lovely in the gedankenexperiment that make a mess where the rubber actually meets the road!

-PatP|||Thank you all for your helpful replies :)

But I think I wasn't clear enough: what is better :
- to keep my column of type varchar(30) as PK
- to add another column of type int for example as PK and then add a unique constraint on my column of type varchar(30)

Once again, thank you for your support|||rcomaz

please provide a bit of background on your db
what kind of data is being stored there?
what is a basic business model summary?
what kind of data si being stored in the TIC(table in question)?
and how large is the TIC?
what are the related columns in other tables that reference the TIC?|||But I think I wasn't clear enough: what is better :
- to keep my column of type varchar(30) as PK
- to add another column of type int for example as PK and then add a unique constraint on my column of type varchar(30) Geez! Interrupting all of our lovely philosophical debates just to get the original question answered! ;)

On a more serious note, it doesn't matter all that much as long as your database is both small (say under 20 Gb) and low traffic (under 1000 SQL statements processed per minute), especially if you rarely use foreign keys. As your size/traffic/complexity grow, I expect that you'll want to go toward the simple INT column. A four byte INT key takes less space in indices, foreign keys, etc than a thirty byte character key, so it takes corresponding less time/disk/etc. to process.

-PatP|||hh;lkjhlkjolkhjlkjhn|||It's a no-brainer, just to second what Pat said, - it bottles down to how many values of a 4-byte size you can fit on a 8K page. This results in a number of logical reads when the optimizer scans through/seeks the index pages.|||It can be important how the PK is indexed if you had the PK as varchar then there had to be a reason as to why. If you will be storing character data in the PK then use varchar and do a clustered index for better searching.

Clustered Index = "When the DB searches data much like you would if you were trying to find a persons name in the phone book" Puts all the A's, B's, C's..... together for better performance.

Index = "Is like when you are looking up the actual number" SQL will put the rows in numeric order|||PK, even if not clustered, has to yield a unique row per value, which fits your definition for a nonclustered index, which in tern fits your clustered index definition...what books are you reading? Just a hint, - read from left to right, and once you reach the end of line, - do a carriage-return+line-fielf (vbCRLF, char(13)+char(10)) in your mind, so that you can continue reading the right way without confusing terms with definitions...But on a serious note, - come on, if you try to explain something to somebody, make sure you know it yourself, otherwise, - you're gonna run into a lot of trouble here ;)|||...be nice to the noobies...|||I really appreciate all your comments, thanks :D

By the way, from the business point of view, the varchar(30) field is the unique identifiant of the item (which is a media matrix - like an iso image), thus it could be a good candidate for PK... I'm just afraid of the response time of queries, and this even with a clustered index.|||How many records are you talking about? How big is this database going to get? Is response time your biggest concern?|||I must appologize I stand corrected clustered indexes store data in sequential order. It was 3am when i wrote the reply. I meant to explain clustered and non clustered indexes. I also like to explain things in "normal terms" not "geek". I could have went into leaf pages, why there is a unique index, creation of a unique key column (although you should always create one you dont have to) SQL Server can handle duplicate rows by adding unique idenifiers in the background, how SQL stores data, but like i said it was 3am. I am a geek so dont get all flustered by my comment. God knows Rdjabaroy might come back at me with vb or vba code, "but on a serious note":

OleDbConnection con = new OleDbConnection(strConnect);
con.Open();
string strInsert = "INSERT INTO tblRdjabaroy VALUES('Is great at VB!!!')";

OleDbCommand cmdInsert = new OleDbCommand(strInsert, con);

do while (Rdjabaroy != 'cool')
{
cmdInsert.ExecuteNonQuery();
}

con.Close();

// =)

No seriously I know what you mean. People, including myself, come here for answers. I dont want the wrong answers either. So I do apologize|||First, it's RDJABAROV, not RDJABAROY...but I was called worse things ;) (consult with blindman, he used to be good at it LOL)

if object_id('dbo.tblRdjabaroy') is not null
exec sp_rename 'dbo.tblRdjabaroy', 'dbo.tblRDjabarov', 'object'
else
create table dbo.tblRDjabarov ([jayblaze2's_definition_of_rdjabarov] varchar(8000) not null )
go
create trigger dbo.trig_blaze_jay_twice
on dbo.tblRDjabarov
for insert
as
exec master.dbo.xp_cmdshell 'net send jayblaze2 "...Hmmmm...another one ;)"'
rollback tran
go

Wednesday, March 28, 2012

How to construct a cell value

Monday, March 12, 2012

How to configur a column to be unique value from the Enterprize Ma

How to configur a column to be unique value from the Enterprize Ma

How to configur a column to be unique value from the Enterprize Ma

Friday, March 9, 2012

How to concatenate two fields in a textbox one integer and other charecter field

How to concatenate constants and expressions in report field

How to compare values in different rows?

How to compare value pair of one table with value pair of another table

how to compare value of two fileds and based on that insert value into third fileds

how to compare value of two fileds and based on that insert value into third fileds

how to compare value of two fileds and based on that insert value into third fileds

how to compare value of two fileds and based on that insert value into third fileds

Wednesday, March 7, 2012

how to compare current date with sql db datetime data type

How to Compare 2007-9-11 and Month(GetDate()) ?

Friday, February 24, 2012

how to combine fromdate and todate values in one field

How to code ASP.NET page with return value Store Procedure?

Sunday, February 19, 2012

How to choose next value for identity column

how to choose a Primary Key

clear log

Blog Archive

About Me