Replacement for my LIKE Clause

Dear all,
I need some help from all my Transact SQL Guru friends out there..
Here is the scenario in its most simplified form.. ..
I have two tables.. A(Lookup table) and B(Transaction Table)
TableA Fields
EmployeeLocationID
EmployeeLocation (This could have values say
"B","BO","BOM","C","CA","CALC") etc...
TableB Fields
EmployeeID
EmployeeName......
EmployeeLocationID (will have null initially when rows are populated
first time)
EmployeeLocation (This could have values
"BA123","BOMBAY","BOTS123","BRACK"... etc)
I hope you get where I am leading this to, from my examples..
Requirement is to populate the EmployeeLocationID in Table B with
EmployeeLocationID from TableA by matching the field EmployeeLocation
in both tables.Please note that table B's EmployeeLocation could be A's
EmployeeLocation + some additionalcodes like "123","RACK" etc in the
above example...
Therefore, this is what I had wrote initially..
update B
set B.EmployeeLocationID =A.EmployeeLocationID
>From B inner join A on B.EmployeeLocation Like A.EmployeeLocation +
'%'
where B.EmployeeLocationID is null
This works fine alright.. However the trouble is that it doesn't cater
to the complete requirement...
For example the row in Table B with EmployeeLocation as "BOMBAY" will
get the EmployeeLocationID for "B" or "BO" and not "BOM" because they
are earlier rows in table A while comparing..The requirement is that we
should get the EmployeeLocationID of "BOM" in this case... That is,
the comparison should be done first for the maximum "maximum no of
characters" match, then for the next "no of characters" match, then for
the next "no of characters"match... etc...
Therefore this is the expected match for my examples based on
requirement..
"BA123" from Table B should be mapped to EmployeeLocationID for "B" of
Table A
"BOMBAY" from Table B should be mapped to EmployeeLocationID for "BOM"
of Table A
"BOTS123" from Table B should be mapped to EmployeeLocationID for "BO"
of Table A
"BRACK" from Table B should be mapped to EmployeeLocationID for "B" of
Table A
Can someone please help me with my query, or atleast direct me to the
right material so that I can take care of this requirement..
Looking forward to hearing from someone ASAP.. Please help..
Best regards,
VM...Interesting. Maybe this will give you and angle to try.
UPDATE B
SET EmployeeLocationID = (SELECT TOP 1 A.EmployeeLocationID
FROM A
WHERE B.EmployeeLocation LIKE A.EmployeeLocation + '%'
ORDER BY LEN(A.EmployeeLocation) DESC)
WHERE B.EmployeeLocationID IS NULL
Roy Harvey
Beacon Falls, CT
On 27 Dec 2006 15:24:44 -0800, varkey.mathew@.wipro.com wrote:
>Dear all,
>I need some help from all my Transact SQL Guru friends out there..
>Here is the scenario in its most simplified form.. ..
>I have two tables.. A(Lookup table) and B(Transaction Table)
>TableA Fields
> EmployeeLocationID
> EmployeeLocation (This could have values say
>"B","BO","BOM","C","CA","CALC") etc...
>
>TableB Fields
> EmployeeID
> EmployeeName......
> EmployeeLocationID (will have null initially when rows are populated
>first time)
> EmployeeLocation (This could have values
>"BA123","BOMBAY","BOTS123","BRACK"... etc)
>I hope you get where I am leading this to, from my examples..
>Requirement is to populate the EmployeeLocationID in Table B with
>EmployeeLocationID from TableA by matching the field EmployeeLocation
>in both tables.Please note that table B's EmployeeLocation could be A's
>EmployeeLocation + some additionalcodes like "123","RACK" etc in the
>above example...
>Therefore, this is what I had wrote initially..
>update B
>set B.EmployeeLocationID =A.EmployeeLocationID
>>From B inner join A on B.EmployeeLocation Like A.EmployeeLocation +
>'%'
>where B.EmployeeLocationID is null
>This works fine alright.. However the trouble is that it doesn't cater
>to the complete requirement...
>For example the row in Table B with EmployeeLocation as "BOMBAY" will
>get the EmployeeLocationID for "B" or "BO" and not "BOM" because they
>are earlier rows in table A while comparing..The requirement is that we
>should get the EmployeeLocationID of "BOM" in this case... That is,
>the comparison should be done first for the maximum "maximum no of
>characters" match, then for the next "no of characters" match, then for
>the next "no of characters"match... etc...
>Therefore this is the expected match for my examples based on
>requirement..
>"BA123" from Table B should be mapped to EmployeeLocationID for "B" of
>Table A
>"BOMBAY" from Table B should be mapped to EmployeeLocationID for "BOM"
>of Table A
>"BOTS123" from Table B should be mapped to EmployeeLocationID for "BO"
>of Table A
>"BRACK" from Table B should be mapped to EmployeeLocationID for "B" of
>Table A
>
>Can someone please help me with my query, or atleast direct me to the
>right material so that I can take care of this requirement..
>
>Looking forward to hearing from someone ASAP.. Please help..
>Best regards,
>VM...|||Why did you fail to post DDL, screw up the syntax and violate ISO-11179
naming rules? Probably because you also confuse fields and columns.
Let's start by cleaning up you code, so it looks like SQL.
SQL uses single quotes for strings. A data element can be a location or
an identifier, never both. A transaction is some kind of transaction.
Etc. You need a data modeling course. Your sample data failed to give
values of the improperly named 'EmployeeLocationID' - I hope to
ghod you are not using IDENTITY and thinking that it is a key!!
Don't you know about SAN and other industry standard address numbers?
>> A(Lookup table) and B(Transaction Table) <<
Why did you avoid clear names?
CREATE TABLE LocationCodes
(loc_prefix VARCHAR(5) NOT NULL PRIMARY KEY,
loc_code INTEGER NOT NULL); -- industry SAN '
-- put wildcards in the table for indexing
INSERT INTO LocationCodes VALUES ('B%', 100);
INSERT INTO LocationCodes VALUES ('BO%', 101);
INSERT INTO LocationCodes VALUES ('BOM%', 102);
Etc.
Can two prefixes belong to the same SAN? No specs given.
Without a key in that vague transactions table, you do not have a
proper table at all. I had to make up one. Why do you have employee
id and not find the employee name via a join to the Personnel table?
Isn't the idea of RDBMS to get rid of redudant data?
CREATE TABLE FoobarTrans
(foobar_trans_nbr INTEGER NOT NULL PRIMARY KEY,
-- CHECK (<<needs validation rule here>>),
emp_id INTEGER NOT NULL
REFERENCES Personnel(emp_id)
ON UPDATE CASCADE,
loc_code INTEGER NOT NULL
REFERENCES LocationCodes(loc_code)
ON UPDATE CASCADE,
Etc.);
The prefix should have been used when you inserted the initial row (NOT
field!!!) into the table. Because you are confusing fields and
columns, files and tables, you are thinking in procedural *steps* with
updates just like a punch card file, not in sets like an SQL
programmer.
>> I hope you get where I am leading this to, from my examples.. <<
No. Clear specs would have been nice, along with real DDL.
Here is a skeleton of a proc for this. You can put Roy's SELECT TOP
in the VALUES list, but if you have SQL-2005, try this little untested
statement:
INSERT INTO FoobarTrans (foobar_trans_nbr, emp_id, ..)
VALUES (@.my_foobar_trans_nbr, @.my_emp_id,
(WITH (SELECT L1.loc_code, LEN(L1.loc_prefix)
FROM LocationCodes AS L1
WHERE L1.loc_prefix LIKE @.my_loc_prefix)
AS M(loc_code, fit)
SELECT loc_code
FROM M AS M1
WHERE M1.fit
= (SELECT MAX(M2.fit) FROM M AS M2)),
Etc.);
You will need error handling code for prefixes that do not match.|||Roy,
Thanks a tonne for your prompt and timely response... I could modify my
script on the lines of your code and it worked (smile)..
Celko,
Thanks to you as well, for your valuable suggestions... And I can
understand your outburst... I just jotted down something(without even
proof reading it) because the intend was to get the question out
yesterday, to hopefully get a response by today... Clear names were not
used, Redundancy was there etc... because it was a cooked up scenario,
but my requirement was very like the one I had outlined ...
I really appreciate the time you have taken to progressively take apart
my question... But as long as you understood the original intend on
where I was stuck and I got a solution to my problem, Believe me I am
happy...
I will remember that I might upset Guru's like you with my questions,
in future, and be more careful with its structure and wording...
Thanks once again...
VM
--CELKO-- wrote:
> Why did you fail to post DDL, screw up the syntax and violate ISO-11179
> naming rules? Probably because you also confuse fields and columns.
> Let's start by cleaning up you code, so it looks like SQL.
> SQL uses single quotes for strings. A data element can be a location or
> an identifier, never both. A transaction is some kind of transaction.
> Etc. You need a data modeling course. Your sample data failed to give
> values of the improperly named 'EmployeeLocationID' - I hope to
> ghod you are not using IDENTITY and thinking that it is a key!!
> Don't you know about SAN and other industry standard address numbers?
>
> >> A(Lookup table) and B(Transaction Table) <<
> Why did you avoid clear names?
> CREATE TABLE LocationCodes
> (loc_prefix VARCHAR(5) NOT NULL PRIMARY KEY,
> loc_code INTEGER NOT NULL); -- industry SAN '
> -- put wildcards in the table for indexing
> INSERT INTO LocationCodes VALUES ('B%', 100);
> INSERT INTO LocationCodes VALUES ('BO%', 101);
> INSERT INTO LocationCodes VALUES ('BOM%', 102);
> Etc.
> Can two prefixes belong to the same SAN? No specs given.
> Without a key in that vague transactions table, you do not have a
> proper table at all. I had to make up one. Why do you have employee
> id and not find the employee name via a join to the Personnel table?
> Isn't the idea of RDBMS to get rid of redudant data?
> CREATE TABLE FoobarTrans
> (foobar_trans_nbr INTEGER NOT NULL PRIMARY KEY,
> -- CHECK (<<needs validation rule here>>),
> emp_id INTEGER NOT NULL
> REFERENCES Personnel(emp_id)
> ON UPDATE CASCADE,
> loc_code INTEGER NOT NULL
> REFERENCES LocationCodes(loc_code)
> ON UPDATE CASCADE,
> Etc.);
> The prefix should have been used when you inserted the initial row (NOT
> field!!!) into the table. Because you are confusing fields and
> columns, files and tables, you are thinking in procedural *steps* with
> updates just like a punch card file, not in sets like an SQL
> programmer.
> >> I hope you get where I am leading this to, from my examples.. <<
> No. Clear specs would have been nice, along with real DDL.
> Here is a skeleton of a proc for this. You can put Roy's SELECT TOP
> in the VALUES list, but if you have SQL-2005, try this little untested
> statement:
> INSERT INTO FoobarTrans (foobar_trans_nbr, emp_id, ..)
> VALUES (@.my_foobar_trans_nbr, @.my_emp_id,
> (WITH (SELECT L1.loc_code, LEN(L1.loc_prefix)
> FROM LocationCodes AS L1
> WHERE L1.loc_prefix LIKE @.my_loc_prefix)
> AS M(loc_code, fit)
> SELECT loc_code
> FROM M AS M1
> WHERE M1.fit
> = (SELECT MAX(M2.fit) FROM M AS M2)),
> Etc.);
> You will need error handling code for prefixes that do not match.

Replacement for my LIKE Clause

Dear all,

I need some help from all my Transact SQL Guru friends out there..

Here is the scenario in its most simplified form.. ..

I have two tables.. A(Lookup table) and B(Transaction Table)

TableA Fields
EmployeeLocationID
EmployeeLocation (This could have values say
"B","BO","BOM","C","CA","CALC") etc...

TableB Fields
EmployeeID
EmployeeName......
EmployeeLocationID (will have null initially when rows are populated
first time)
EmployeeLocation (This could have values
"BA123","BOMBAY","BOTS123","BRACK"... etc)

I hope you get where I am leading this to, from my examples..
Requirement is to populate the EmployeeLocationID in Table B with
EmployeeLocationID from TableA by matching the field EmployeeLocation
in both tables.Please note that table B's EmployeeLocation could be A's
EmployeeLocation + some additionalcodes like "123","RACK" etc in the
above example...

Therefore, this is what I had wrote initially..

update B
set B.EmployeeLocationID =A.EmployeeLocationID

Quote:

Originally Posted by

>From B inner join A on B.EmployeeLocation Like A.EmployeeLocation +

'%'
where B.EmployeeLocationID is null

This works fine alright.. However the trouble is that it doesn't cater
to the complete requirement...

For example the row in Table B with EmployeeLocation as "BOMBAY" will
get the EmployeeLocationID for "B" or "BO" and not "BOM" because they
are earlier rows in table A while comparing..The requirement is that we
should get the EmployeeLocationID of "BOM" in this case... That is,
the comparison should be done first for the maximum "maximum no of
characters" match, then for the next "no of characters" match, then for
the next "no of characters"match... etc...

Therefore this is the expected match for my examples based on
requirement..

"BA123" from Table B should be mapped to EmployeeLocationID for "B" of
Table A
"BOMBAY" from Table B should be mapped to EmployeeLocationID for "BOM"
of Table A
"BOTS123" from Table B should be mapped to EmployeeLocationID for "BO"
of Table A
"BRACK" from Table B should be mapped to EmployeeLocationID for "B" of
Table A

Can someone please help me with my query, or atleast direct me to the
right material so that I can take care of this requirement..

Looking forward to hearing from someone ASAP.. Please help..

Best regards,

VM...Interesting. Maybe this will give you and angle to try.

UPDATE B
SET EmployeeLocationID =
(SELECT TOP 1 A.EmployeeLocationID
FROM A
WHERE B.EmployeeLocation LIKE A.EmployeeLocation + '%'
ORDER BY LEN(A.EmployeeLocation) DESC)
WHERE B.EmployeeLocationID IS NULL

Roy Harvey
Beacon Falls, CT

On 27 Dec 2006 15:24:44 -0800, varkey.mathew@.wipro.com wrote:

Quote:

Originally Posted by

>Dear all,
>
>I need some help from all my Transact SQL Guru friends out there..
>
>Here is the scenario in its most simplified form.. ..
>
>I have two tables.. A(Lookup table) and B(Transaction Table)
>
>TableA Fields
EmployeeLocationID
EmployeeLocation (This could have values say
>"B","BO","BOM","C","CA","CALC") etc...
>
>
>TableB Fields
EmployeeID
EmployeeName......
EmployeeLocationID (will have null initially when rows are populated
>first time)
EmployeeLocation (This could have values
>"BA123","BOMBAY","BOTS123","BRACK"... etc)
>
>I hope you get where I am leading this to, from my examples..
>Requirement is to populate the EmployeeLocationID in Table B with
>EmployeeLocationID from TableA by matching the field EmployeeLocation
>in both tables.Please note that table B's EmployeeLocation could be A's
>EmployeeLocation + some additionalcodes like "123","RACK" etc in the
>above example...
>
>Therefore, this is what I had wrote initially..
>
>update B
>set B.EmployeeLocationID =A.EmployeeLocationID

Quote:

Originally Posted by

>>From B inner join A on B.EmployeeLocation Like A.EmployeeLocation +

>'%'
>where B.EmployeeLocationID is null
>
>This works fine alright.. However the trouble is that it doesn't cater
>to the complete requirement...
>
>For example the row in Table B with EmployeeLocation as "BOMBAY" will
>get the EmployeeLocationID for "B" or "BO" and not "BOM" because they
>are earlier rows in table A while comparing..The requirement is that we
>should get the EmployeeLocationID of "BOM" in this case... That is,
>the comparison should be done first for the maximum "maximum no of
>characters" match, then for the next "no of characters" match, then for
>the next "no of characters"match... etc...
>
>Therefore this is the expected match for my examples based on
>requirement..
>
>"BA123" from Table B should be mapped to EmployeeLocationID for "B" of
>Table A
>"BOMBAY" from Table B should be mapped to EmployeeLocationID for "BOM"
>of Table A
>"BOTS123" from Table B should be mapped to EmployeeLocationID for "BO"
>of Table A
>"BRACK" from Table B should be mapped to EmployeeLocationID for "B" of
>Table A
>
>
>Can someone please help me with my query, or atleast direct me to the
>right material so that I can take care of this requirement..
>
>
>Looking forward to hearing from someone ASAP.. Please help..
>
>Best regards,
>
>VM...

|||Why did you fail to post DDL, screw up the syntax and violate ISO-11179
naming rules? Probably because you also confuse fields and columns.
Let's start by cleaning up you code, so it looks like SQL.

SQL uses single quotes for strings. A data element can be a location or
an identifier, never both. A transaction is some kind of transaction.
Etc. You need a data modeling course. Your sample data failed to give
values of the improperly named 'EmployeeLocationID' - I hope to
ghod you are not using IDENTITY and thinking that it is a key!!
Don't you know about SAN and other industry standard address numbers?

Quote:

Originally Posted by

Quote:

Originally Posted by

>A(Lookup table) and B(Transaction Table) <<

Why did you avoid clear names?

CREATE TABLE LocationCodes
(loc_prefix VARCHAR(5) NOT NULL PRIMARY KEY,
loc_code INTEGER NOT NULL); -- industry SAN ??

-- put wildcards in the table for indexing
INSERT INTO LocationCodes VALUES ('B%', 100);
INSERT INTO LocationCodes VALUES ('BO%', 101);
INSERT INTO LocationCodes VALUES ('BOM%', 102);
Etc.

Can two prefixes belong to the same SAN? No specs given.

Without a key in that vague transactions table, you do not have a
proper table at all. I had to make up one. Why do you have employee
id and not find the employee name via a join to the Personnel table?
Isn't the idea of RDBMS to get rid of redudant data?

CREATE TABLE FoobarTrans
(foobar_trans_nbr INTEGER NOT NULL PRIMARY KEY,
-- CHECK (<<needs validation rule here>>),
emp_id INTEGER NOT NULL
REFERENCES Personnel(emp_id)
ON UPDATE CASCADE,
loc_code INTEGER NOT NULL
REFERENCES LocationCodes(loc_code)
ON UPDATE CASCADE,
Etc.);

The prefix should have been used when you inserted the initial row (NOT
field!!!) into the table. Because you are confusing fields and
columns, files and tables, you are thinking in procedural *steps* with
updates just like a punch card file, not in sets like an SQL
programmer.

Quote:

Originally Posted by

Quote:

Originally Posted by

>I hope you get where I am leading this to, from my examples.. <<

No. Clear specs would have been nice, along with real DDL.

Here is a skeleton of a proc for this. You can put Roy's SELECT TOP
in the VALUES list, but if you have SQL-2005, try this little untested
statement:

INSERT INTO FoobarTrans (foobar_trans_nbr, emp_id, ..)
VALUES (@.my_foobar_trans_nbr, @.my_emp_id,

(WITH (SELECT L1.loc_code, LEN(L1.loc_prefix)
FROM LocationCodes AS L1
WHERE L1.loc_prefix LIKE @.my_loc_prefix)
AS M(loc_code, fit)
SELECT loc_code
FROM M AS M1
WHERE M1.fit
= (SELECT MAX(M2.fit) FROM M AS M2)),

Etc.);

You will need error handling code for prefixes that do not match.|||Roy,

Thanks a tonne for your prompt and timely response... I could modify my
script on the lines of your code and it worked (smile)..

Celko,

Thanks to you as well, for your valuable suggestions... And I can
understand your outburst... I just jotted down something(without even
proof reading it) because the intend was to get the question out
yesterday, to hopefully get a response by today... Clear names were not
used, Redundancy was there etc... because it was a cooked up scenario,
but my requirement was very like the one I had outlined ...

I really appreciate the time you have taken to progressively take apart
my question... But as long as you understood the original intend on
where I was stuck and I got a solution to my problem, Believe me I am
happy...

I will remember that I might upset Guru's like you with my questions,
in future, and be more careful with its structure and wording...

Thanks once again...

VM

--CELKO-- wrote:

Quote:

Originally Posted by

Why did you fail to post DDL, screw up the syntax and violate ISO-11179
naming rules? Probably because you also confuse fields and columns.
Let's start by cleaning up you code, so it looks like SQL.
>
SQL uses single quotes for strings. A data element can be a location or
an identifier, never both. A transaction is some kind of transaction.
Etc. You need a data modeling course. Your sample data failed to give
values of the improperly named 'EmployeeLocationID' - I hope to
ghod you are not using IDENTITY and thinking that it is a key!!
Don't you know about SAN and other industry standard address numbers?
>
>

Quote:

Originally Posted by

Quote:

Originally Posted by

A(Lookup table) and B(Transaction Table) <<

>
Why did you avoid clear names?
>
CREATE TABLE LocationCodes
(loc_prefix VARCHAR(5) NOT NULL PRIMARY KEY,
loc_code INTEGER NOT NULL); -- industry SAN ??
>
-- put wildcards in the table for indexing
INSERT INTO LocationCodes VALUES ('B%', 100);
INSERT INTO LocationCodes VALUES ('BO%', 101);
INSERT INTO LocationCodes VALUES ('BOM%', 102);
Etc.
>
Can two prefixes belong to the same SAN? No specs given.
>
Without a key in that vague transactions table, you do not have a
proper table at all. I had to make up one. Why do you have employee
id and not find the employee name via a join to the Personnel table?
Isn't the idea of RDBMS to get rid of redudant data?
>
CREATE TABLE FoobarTrans
(foobar_trans_nbr INTEGER NOT NULL PRIMARY KEY,
-- CHECK (<<needs validation rule here>>),
emp_id INTEGER NOT NULL
REFERENCES Personnel(emp_id)
ON UPDATE CASCADE,
loc_code INTEGER NOT NULL
REFERENCES LocationCodes(loc_code)
ON UPDATE CASCADE,
Etc.);
>
The prefix should have been used when you inserted the initial row (NOT
field!!!) into the table. Because you are confusing fields and
columns, files and tables, you are thinking in procedural *steps* with
updates just like a punch card file, not in sets like an SQL
programmer.
>

Quote:

Originally Posted by

Quote:

Originally Posted by

I hope you get where I am leading this to, from my examples.. <<

>
No. Clear specs would have been nice, along with real DDL.
>
Here is a skeleton of a proc for this. You can put Roy's SELECT TOP
in the VALUES list, but if you have SQL-2005, try this little untested
statement:
>
INSERT INTO FoobarTrans (foobar_trans_nbr, emp_id, ..)
VALUES (@.my_foobar_trans_nbr, @.my_emp_id,
>
(WITH (SELECT L1.loc_code, LEN(L1.loc_prefix)
FROM LocationCodes AS L1
WHERE L1.loc_prefix LIKE @.my_loc_prefix)
AS M(loc_code, fit)
SELECT loc_code
FROM M AS M1
WHERE M1.fit
= (SELECT MAX(M2.fit) FROM M AS M2)),
>
Etc.);
>
You will need error handling code for prefixes that do not match.

Replacement for MAX statement?

Hi all :
I got a problem here.

The scenario :
The serial key code of a table is auto generate, and currently I'm using MAX to return the latest serial key code. The problem is, if the table grow bigger and access by multiple user at one time ( I think select statement does not have any row or table lock) the MAX key will return the wrong serial key code or even fail at some point. The serial key code is the only unique key in the table.

Can anyone give me some guide or tips on how to resolve this? Or other way of implementing the select statement to avoid usage of MAX?

Thanks in advance.the best way to avoid this is not to return the latest serial key at all

what do you need it for?|||I need to get the serial key code as a foreign key to insert into another table. The Serial key code will server as a customer ID in my case. since no customer ID can be and should be duplicate, I need to get the serial key code.

I had figure out a way, using a select * statement and use a while loop to loop thru the whole result set collection. From there I can get the last serial key code.

I got a question here, if I use this way, will it be less effective than using MAX? Consider I got a table with more than 5000 record.

Thanks.|||On the table you're inserting into, will the SerialKeyCode be unique, or will you have a one-to-many relationship?|||Serial Key Code will be generate automatically by the database and been set to unique with increament by 1 everytime new record is add.|||The problem is, if the table grow bigger and access by multiple user at one time ( I think select statement does not have any row or table lock) the MAX key will return the wrong serial key code or even fail at some point. Thanks in

If you return the maximum serial ID at 14:00 and then again at 14:01 they may indeed be different. I don't see a problem. If you wish to keep an accurate record, then you could implement an insert trigger to store the most recent serial code in another table.|||here's what you do --

insert a row, and let the database automatically assign the key

use the provided database function to retrieve the value of the key that was assigned

in SQL Server, this is the @.@.identity function, in mysql it is mysql_insert_id(), use whatever function your database provides

if your database does not provide such a function, then simply query back the row that you just inserted, not with MAX but with the values of the other columns

then use the value of the key as the foreign key when you insert the related record

for example, if you insert a username/password combination of 'fred','sesame' into a user table, then you instead of selecting MAX or (shudder!) returning the entire table to see the last one, just run this query:

select userid from users where username='fred' and password='sesame'

the automatically incremented userid value that you get back will be what you use to insert the related row in the other table|||Thanks for your suggestion. The problem is that there r no unique key in the table except the auto serial key code assign by the database. I can not use the second method u suggest. About the first suggestion, it's great, but only if I know what kind of statement the databse provide for me to get the number assign. I'm using Informix. It would be great if you can give some hints on the command to use to get the sequence number. Thanks|||ah, informix

look up NEXTVAL and CURRVAL in the manual

the serial number is actually separate from the table, and you use NEXTVAL to get the next value that you insert into the table|||The process is like this :

1)Insert into table A
2)Get the result set from table A (for the latest serial key code)

What you try to said is that I need to use NEXTVAL before step 1 to get the serial key code I need.

I'm using Java. I need to write SQL statement for NEXTVAL or in the Java Code.

Would be appreciate if you can give any hints. TY.|||Insert into tableA values (seq.nextval, column1, column2)
select * from tableA where id = seq.currVal

Re-phrased w more details (SQL is giving different row counts)

Hi,

...giving a very 'summarized' scenario of the problem I have trying to
solve all day (make it 2 days now).

Below are the relevant DDLs... I am not listing the DDLs of my other tables:

CREATE TABLE [SalesFACT] (
[varchar] (10),
[TransDate] [varchar] (10),
[SaleAmt] [float],
[CustCode] [varchar] (10)
. . .
)

I populate the above table via a DTS and have checked and have verified that correct data is coming in... I also have a product master table; for business reasons we can have the same product created with different ProductCodes though the rest of the Product details are EXACTLY the same. We have covered this using a field named 'UniqueProdCode'.

CREATE TABLE ProdMaster(
[ProdCode] [varchar] (10),
[ProdName] [varchar] (35),

[UniqueProdCode] [varchar] (10),

... many other product fields e.g. unit price, category etc...
...
)

First a small Request:
Please note that I have NOT defined links between my tables (in the diagram editor) nor have I defined Primary keys (or any constraint) for any of the tables. When you kindly reply, please suggest I should define primary keys for the tables and also link them in the diagram editor.

[u]THE PROBLEM:
When I do a count(*) query on the table 'SalesFACT', I get the correct number of records.

If I create a view, add table 'SalesFACT' and table ProdMaster, link the
UniqueProdCode field of table 'SalesFACT' with the UniqueProdCode field of ProdMaster (so that I can also get the name, category, etc. for the products in the SalesFACT), and run a count(*) query I get a much higher and incorrect number of rows. The SQL for the view is:

SELECT dbo.SalesFACT.TransDate, dbo.SalesFACT.UniqueProdCode,
dbo.SalesFACT.SaleAmt
FROM dbo.SalesFACT INNER JOIN dbo.ProdMaster ON dbo.SalesFACT.UniqueProdCode = dbo.ProdMaster.UniqueProdCode

Kindly note that I have checked and the contents of the table SalesFACT' UniqueProdCode field DOES contain the correct data i.e. it contains the UniqueProdCode and NOT the ProdCode.

But if i link the "wrong fields", I get the correct count count :confused: i.e. I create a very similar view (as mentioned above) but instead link the UniqueProdCode of table SalesFACT with the ProdCode field (not the UniqueProdCode field) of ProdMaster
table I get the correct count. This is really driving me nuts and I just can't understand what's going on. For your convenience here is the SQL for the 2nd view:

SELECT dbo.SalesFACT.TransDate, dbo.SalesFACT.UniqueProdCode,
dbo.SalesFACT.SaleAmt
FROM dbo.SalesFACT INNER JOIN dbo.ProdMaster ON dbo.SalesFACT.UniqueProdCode = dbo.ProdMaster.ProdCode

Please guide... I have run out of all the things that I could check and thus this SOS and F1

Billions of thansk in advance.in prodMaster you heve not unique UniqueProdCode

try this

select UniqueProdCode from prodMaster
group by UniqueProdCode
having count(*)>1

reorg database files

I want to reorganize the data files for optimum
performance. I have described below the existing
and the intended scenario that I wish to attain.
** existing database scenario **
database files
mydb.mdf (50gb) - primary data file
mydblog.ldf (1gb) - log file
** intended database scenario **
database files:
mydb1.mdf (10gb) - primary data file
mydb2.ndf (10gb) - secondary data file
mydb3.ndf (10gb) - secondary data file
mydb4.ndf (10gb) - secondary data file
mydb5.ndf (10gb) - secondary data file
mydblog.ldf (1gb) - log file
Are there tools that can me help do this?
Thank you in advance.Using T-SQL tools, you can do this:
Use ALTER DATABASE to create new files and place them in filegroups.
Now, use sp_spaceused to determine which tables and/or indexes you want to
move to the new filegroups.
You can use ALTER TABLE with ON FileGroupName to move data around. Notice
that the filegroup is actually home to an index, but a clustered index is
the table, of course. From the BOL:
ON {filegroup | DEFAULT}
Specifies the storage location of the index created for the constraint. If
filegroup is specified, the index is created in the named filegroup. If
DEFAULT is specified, the index is created in the default filegroup. If ON
is not specified, the index is created in the filegroup that contains the
table. If ON is specified when adding a clustered index for a PRIMARY KEY or
UNIQUE constraint, the entire table is moved to the specified filegroup when
the clustered index is created.
Once you have successfully moved tables by recreating the clustered indexes,
you can use DBCC SHRINKFILE to shrink the original large file down to the
appropriate size.
Russell Fields
"fragb" <anonymous@.discussions.microsoft.com> wrote in message
news:0ca401c47b0c$fb6e7280$a401280a@.phx.gbl...
> I want to reorganize the data files for optimum
> performance. I have described below the existing
> and the intended scenario that I wish to attain.
> ** existing database scenario **
> database files
> mydb.mdf (50gb) - primary data file
> mydblog.ldf (1gb) - log file
> ** intended database scenario **
> database files:
> mydb1.mdf (10gb) - primary data file
> mydb2.ndf (10gb) - secondary data file
> mydb3.ndf (10gb) - secondary data file
> mydb4.ndf (10gb) - secondary data file
> mydb5.ndf (10gb) - secondary data file
> mydblog.ldf (1gb) - log file
> Are there tools that can me help do this?
> Thank you in advance.
>|||None that I know of that would do much in that situation. If you want to
spread your data evenly across a filegroup with multiple files from one that
has a single file you pretty much have to export all the data. Then
truncate all the tables and reimport it back again. It's not that difficult
of a task but obviously you will need to take your users off line for some
period of time. In the past when I have done this I basically scripted out
the database and all the objects in such a way that I could recreate the
database schema with the new files and all the tables , sp's UDF's ect but
leaving off the triggers, RI and Indexes. Then after you BCP out all the
data you can drop the DB and recreate it without those to make it easier and
faster to load. Then Bulk Insert the data and add back the RI, Triggers
etc. Just make sure you have good and tested backups first.
--
Andrew J. Kelly SQL MVP
"fragb" <anonymous@.discussions.microsoft.com> wrote in message
news:0ca401c47b0c$fb6e7280$a401280a@.phx.gbl...
> I want to reorganize the data files for optimum
> performance. I have described below the existing
> and the intended scenario that I wish to attain.
> ** existing database scenario **
> database files
> mydb.mdf (50gb) - primary data file
> mydblog.ldf (1gb) - log file
> ** intended database scenario **
> database files:
> mydb1.mdf (10gb) - primary data file
> mydb2.ndf (10gb) - secondary data file
> mydb3.ndf (10gb) - secondary data file
> mydb4.ndf (10gb) - secondary data file
> mydb5.ndf (10gb) - secondary data file
> mydblog.ldf (1gb) - log file
> Are there tools that can me help do this?
> Thank you in advance.
>|||Hi,
The CREATE TABLE & ALTER TABLE statements only allow you to specify the
FILEGROUP on which you wish to create the table. Not the actual data
file within the Filegroup.
In your intended scenario, you do not draw a distinction between Data
Files, and FileGroups. A possible alternative is :
FileGroupPRIMARY mydb1.mdf (10gb)
FileGroup02 mydb2.ndf (10gb)
FileGroup03 mydb3.ndf (10gb)
FileGroup04 mydb4.ndf (10gb)
FileGroup05 mydb5.ndf (10gb)
mydblog.ldf (1gb)
I use Power Designer - Data Architect to model my databases. After I
make changes to the model (ie, change the FileGroup for a table), Data
Architect compares my Model against the database on the server, and
generates a "Modify" script, which I then run against the database.
It generates the typical script to move to a different Filegroup :
alter table dbo.tbl_Customer
drop constraint PK_Customer
go
alter table dbo.tbl_Customer
add constraint PK_Customer primary key clustered (CustomerId)
on "NEW_FILEGROUP"
go
thanks
Ian
fragb wrote:
> I want to reorganize the data files for optimum
> performance. I have described below the existing
> and the intended scenario that I wish to attain.
> ** existing database scenario **
> database files
> mydb.mdf (50gb) - primary data file
> mydblog.ldf (1gb) - log file
> ** intended database scenario **
> database files:
> mydb1.mdf (10gb) - primary data file
> mydb2.ndf (10gb) - secondary data file
> mydb3.ndf (10gb) - secondary data file
> mydb4.ndf (10gb) - secondary data file
> mydb5.ndf (10gb) - secondary data file
> mydblog.ldf (1gb) - log file
> Are there tools that can me help do this?
> Thank you in advance.
>

reorg database files

I want to reorganize the data files for optimum
performance. I have described below the existing
and the intended scenario that I wish to attain.
** existing database scenario **
database files
mydb.mdf (50gb) - primary data file
mydblog.ldf (1gb) - log file
** intended database scenario **
database files:
mydb1.mdf (10gb) - primary data file
mydb2.ndf (10gb) - secondary data file
mydb3.ndf (10gb) - secondary data file
mydb4.ndf (10gb) - secondary data file
mydb5.ndf (10gb) - secondary data file
mydblog.ldf (1gb) - log file
Are there tools that can me help do this?
Thank you in advance.
Using T-SQL tools, you can do this:
Use ALTER DATABASE to create new files and place them in filegroups.
Now, use sp_spaceused to determine which tables and/or indexes you want to
move to the new filegroups.
You can use ALTER TABLE with ON FileGroupName to move data around. Notice
that the filegroup is actually home to an index, but a clustered index is
the table, of course. From the BOL:
ON {filegroup | DEFAULT}
Specifies the storage location of the index created for the constraint. If
filegroup is specified, the index is created in the named filegroup. If
DEFAULT is specified, the index is created in the default filegroup. If ON
is not specified, the index is created in the filegroup that contains the
table. If ON is specified when adding a clustered index for a PRIMARY KEY or
UNIQUE constraint, the entire table is moved to the specified filegroup when
the clustered index is created.
Once you have successfully moved tables by recreating the clustered indexes,
you can use DBCC SHRINKFILE to shrink the original large file down to the
appropriate size.
Russell Fields
"fragb" <anonymous@.discussions.microsoft.com> wrote in message
news:0ca401c47b0c$fb6e7280$a401280a@.phx.gbl...
> I want to reorganize the data files for optimum
> performance. I have described below the existing
> and the intended scenario that I wish to attain.
> ** existing database scenario **
> database files
> mydb.mdf (50gb) - primary data file
> mydblog.ldf (1gb) - log file
> ** intended database scenario **
> database files:
> mydb1.mdf (10gb) - primary data file
> mydb2.ndf (10gb) - secondary data file
> mydb3.ndf (10gb) - secondary data file
> mydb4.ndf (10gb) - secondary data file
> mydb5.ndf (10gb) - secondary data file
> mydblog.ldf (1gb) - log file
> Are there tools that can me help do this?
> Thank you in advance.
>
|||None that I know of that would do much in that situation. If you want to
spread your data evenly across a filegroup with multiple files from one that
has a single file you pretty much have to export all the data. Then
truncate all the tables and reimport it back again. It's not that difficult
of a task but obviously you will need to take your users off line for some
period of time. In the past when I have done this I basically scripted out
the database and all the objects in such a way that I could recreate the
database schema with the new files and all the tables , sp's UDF's ect but
leaving off the triggers, RI and Indexes. Then after you BCP out all the
data you can drop the DB and recreate it without those to make it easier and
faster to load. Then Bulk Insert the data and add back the RI, Triggers
etc. Just make sure you have good and tested backups first.
Andrew J. Kelly SQL MVP
"fragb" <anonymous@.discussions.microsoft.com> wrote in message
news:0ca401c47b0c$fb6e7280$a401280a@.phx.gbl...
> I want to reorganize the data files for optimum
> performance. I have described below the existing
> and the intended scenario that I wish to attain.
> ** existing database scenario **
> database files
> mydb.mdf (50gb) - primary data file
> mydblog.ldf (1gb) - log file
> ** intended database scenario **
> database files:
> mydb1.mdf (10gb) - primary data file
> mydb2.ndf (10gb) - secondary data file
> mydb3.ndf (10gb) - secondary data file
> mydb4.ndf (10gb) - secondary data file
> mydb5.ndf (10gb) - secondary data file
> mydblog.ldf (1gb) - log file
> Are there tools that can me help do this?
> Thank you in advance.
>
|||Hi,
The CREATE TABLE & ALTER TABLE statements only allow you to specify the
FILEGROUP on which you wish to create the table. Not the actual data
file within the Filegroup.
In your intended scenario, you do not draw a distinction between Data
Files, and FileGroups. A possible alternative is :
FileGroupPRIMARY mydb1.mdf (10gb)
FileGroup02 mydb2.ndf (10gb)
FileGroup03 mydb3.ndf (10gb)
FileGroup04 mydb4.ndf (10gb)
FileGroup05 mydb5.ndf (10gb)
mydblog.ldf (1gb)
I use Power Designer - Data Architect to model my databases. After I
make changes to the model (ie, change the FileGroup for a table), Data
Architect compares my Model against the database on the server, and
generates a "Modify" script, which I then run against the database.
It generates the typical script to move to a different Filegroup :
alter table dbo.tbl_Customer
drop constraint PK_Customer
go
alter table dbo.tbl_Customer
add constraint PK_Customer primary key clustered (CustomerId)
on "NEW_FILEGROUP"
go
thanks
Ian
fragb wrote:
> I want to reorganize the data files for optimum
> performance. I have described below the existing
> and the intended scenario that I wish to attain.
> ** existing database scenario **
> database files
> mydb.mdf (50gb) - primary data file
> mydblog.ldf (1gb) - log file
> ** intended database scenario **
> database files:
> mydb1.mdf (10gb) - primary data file
> mydb2.ndf (10gb) - secondary data file
> mydb3.ndf (10gb) - secondary data file
> mydb4.ndf (10gb) - secondary data file
> mydb5.ndf (10gb) - secondary data file
> mydblog.ldf (1gb) - log file
> Are there tools that can me help do this?
> Thank you in advance.
>

reorg database files

I want to reorganize the data files for optimum
performance. I have described below the existing
and the intended scenario that I wish to attain.
** existing database scenario **
database files
mydb.mdf (50gb) - primary data file
mydblog.ldf (1gb) - log file
** intended database scenario **
database files:
mydb1.mdf (10gb) - primary data file
mydb2.ndf (10gb) - secondary data file
mydb3.ndf (10gb) - secondary data file
mydb4.ndf (10gb) - secondary data file
mydb5.ndf (10gb) - secondary data file
mydblog.ldf (1gb) - log file
Are there tools that can me help do this?
Thank you in advance.Using T-SQL tools, you can do this:
Use ALTER DATABASE to create new files and place them in filegroups.
Now, use sp_spaceused to determine which tables and/or indexes you want to
move to the new filegroups.
You can use ALTER TABLE with ON FileGroupName to move data around. Notice
that the filegroup is actually home to an index, but a clustered index is
the table, of course. From the BOL:
ON {filegroup | DEFAULT}
Specifies the storage location of the index created for the constraint. If
filegroup is specified, the index is created in the named filegroup. If
DEFAULT is specified, the index is created in the default filegroup. If ON
is not specified, the index is created in the filegroup that contains the
table. If ON is specified when adding a clustered index for a PRIMARY KEY or
UNIQUE constraint, the entire table is moved to the specified filegroup when
the clustered index is created.
Once you have successfully moved tables by recreating the clustered indexes,
you can use DBCC SHRINKFILE to shrink the original large file down to the
appropriate size.
Russell Fields
"fragb" <anonymous@.discussions.microsoft.com> wrote in message
news:0ca401c47b0c$fb6e7280$a401280a@.phx.gbl...
> I want to reorganize the data files for optimum
> performance. I have described below the existing
> and the intended scenario that I wish to attain.
> ** existing database scenario **
> database files
> mydb.mdf (50gb) - primary data file
> mydblog.ldf (1gb) - log file
> ** intended database scenario **
> database files:
> mydb1.mdf (10gb) - primary data file
> mydb2.ndf (10gb) - secondary data file
> mydb3.ndf (10gb) - secondary data file
> mydb4.ndf (10gb) - secondary data file
> mydb5.ndf (10gb) - secondary data file
> mydblog.ldf (1gb) - log file
> Are there tools that can me help do this?
> Thank you in advance.
>|||None that I know of that would do much in that situation. If you want to
spread your data evenly across a filegroup with multiple files from one that
has a single file you pretty much have to export all the data. Then
truncate all the tables and reimport it back again. It's not that difficult
of a task but obviously you will need to take your users off line for some
period of time. In the past when I have done this I basically scripted out
the database and all the objects in such a way that I could recreate the
database schema with the new files and all the tables , sp's UDF's ect but
leaving off the triggers, RI and Indexes. Then after you BCP out all the
data you can drop the DB and recreate it without those to make it easier and
faster to load. Then Bulk Insert the data and add back the RI, Triggers
etc. Just make sure you have good and tested backups first.
Andrew J. Kelly SQL MVP
"fragb" <anonymous@.discussions.microsoft.com> wrote in message
news:0ca401c47b0c$fb6e7280$a401280a@.phx.gbl...
> I want to reorganize the data files for optimum
> performance. I have described below the existing
> and the intended scenario that I wish to attain.
> ** existing database scenario **
> database files
> mydb.mdf (50gb) - primary data file
> mydblog.ldf (1gb) - log file
> ** intended database scenario **
> database files:
> mydb1.mdf (10gb) - primary data file
> mydb2.ndf (10gb) - secondary data file
> mydb3.ndf (10gb) - secondary data file
> mydb4.ndf (10gb) - secondary data file
> mydb5.ndf (10gb) - secondary data file
> mydblog.ldf (1gb) - log file
> Are there tools that can me help do this?
> Thank you in advance.
>|||Hi,
The CREATE TABLE & ALTER TABLE statements only allow you to specify the
FILEGROUP on which you wish to create the table. Not the actual data
file within the Filegroup.
In your intended scenario, you do not draw a distinction between Data
Files, and FileGroups. A possible alternative is :
FileGroupPRIMARY mydb1.mdf (10gb)
FileGroup02 mydb2.ndf (10gb)
FileGroup03 mydb3.ndf (10gb)
FileGroup04 mydb4.ndf (10gb)
FileGroup05 mydb5.ndf (10gb)
mydblog.ldf (1gb)
I use Power Designer - Data Architect to model my databases. After I
make changes to the model (ie, change the FileGroup for a table), Data
Architect compares my Model against the database on the server, and
generates a "Modify" script, which I then run against the database.
It generates the typical script to move to a different Filegroup :
alter table dbo.tbl_Customer
drop constraint PK_Customer
go
alter table dbo.tbl_Customer
add constraint PK_Customer primary key clustered (CustomerId)
on "NEW_FILEGROUP"
go
thanks
Ian
fragb wrote:
> I want to reorganize the data files for optimum
> performance. I have described below the existing
> and the intended scenario that I wish to attain.
> ** existing database scenario **
> database files
> mydb.mdf (50gb) - primary data file
> mydblog.ldf (1gb) - log file
> ** intended database scenario **
> database files:
> mydb1.mdf (10gb) - primary data file
> mydb2.ndf (10gb) - secondary data file
> mydb3.ndf (10gb) - secondary data file
> mydb4.ndf (10gb) - secondary data file
> mydb5.ndf (10gb) - secondary data file
> mydblog.ldf (1gb) - log file
> Are there tools that can me help do this?
> Thank you in advance.
>