Posted by varkey.mathew on 12/28/06 23:08

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.

[Back to original message]