October 7, 2008 at 8:59 am
Hello
I am having two table Table1 and table2. In table 1 there is a column called errcode which containing error code like "75 150" i.e. means two error code are there. Now I need to read description from table 2 depending on errCode column as I mentioned above.
What should be the SQL? I am new to sql, please guide me.
Ashish
October 7, 2008 at 9:50 am
Please post your table layouts for us to give you an example query. What you are looking for is just going to be a very simple join, but without us knowing your field names it might not make sense to you.
[Edit] I misread that. I didn't notice the double values the first time, which means it's not going to be quite as simple as a join. Definitely need your tables / sample data now.
October 7, 2008 at 10:08 am
If possible I would also review the design. Having TWO values in a single column is normally (dare I say always?) not a good thing to do. It would appear that these should be in a separate table.
Mike John
October 7, 2008 at 10:10 am
Hello Gardian
Here is structure
table 1
Fname VC (50)
Lname VC (50)
ERROR_CODE VC(100)
Table 2
Err_Cd VC (5)
Err_MSG (100)
Please advise
October 7, 2008 at 10:16 am
My edit went through after your post. Please also provide sample data as so. Make sure you include data that will demonstrate the double entry issue.
http://qa.sqlservercentral.com/articles/Best+Practices/61537/
October 7, 2008 at 10:28 am
Table1
"ash","kan","75 100"
"ash1","kan1","75 202"
"avi","nig","100 202"
Table2
"75","This record doesnot have privilege"
"100","Calculation Mistake"
"202","Category is Missing"
October 7, 2008 at 10:52 am
For the future, when someone asks for table structure / data, please post it like this:
[font="Courier New"]CREATE TABLE Table1(
fname VARCHAR(50),
lname VARCHAR(50),
error_code VARCHAR(100))
CREATE TABLE Table2(
err_cd VARCHAR(50),
err_msg VARCHAR(100))
INSERT INTO Table1(fname, lname, error_code)
SELECT 'ash','kan','75 100' UNION ALL
SELECT 'ash1','kan1','75 202' UNION ALL
SELECT 'avi','nig','100 202'
INSERT INTO Table2(err_cd, err_msg)
SELECT '75','This record doesnot have privilege' UNION ALL
SELECT '100','Calculation Mistake' UNION ALL
SELECT '202','Category is Missing'
[/font]
This probably isn't the best solution, and it needs to be more complicated if you have more than 2 values in the field, but it should work for now. Also, I would *highly* recommend changing your table structure so that you aren't inserting multiple error codes into a single field.
[font="Courier New"]SELECT T1.fname, T1.lname, T2.err_msg
FROM Table1 T1 INNER JOIN Table2 T2 ON LTRIM(RTRIM(LEFT(T1.error_code,CHARINDEX(' ',T1.error_code,1)))) = T2.err_cd
UNION ALL
SELECT T1.fname, T1.lname, T2.err_msg
FROM Table1 T1 INNER JOIN Table2 T2 ON LTRIM(RTRIM(RIGHT(T1.error_code,LEN(T1.Error_Code)-CHARINDEX(' ',T1.error_code,1)))) = T2.err_cd
[/font]
October 7, 2008 at 11:06 am
Hello
Thanks for the response. Actually this database is very old 5-6 year old, client doesnt want to change the data structure, so I have no option, I have to continue as it is.
I am managing 3 code maximum error codes. how can I add logic for checking logic for 3rd error code?
October 7, 2008 at 11:10 am
October 7, 2008 at 11:13 am
Agreed with Garadin that you might reconsider the design of the table.
here is one option using XML that produce the desired result and can handle even more error codes in each row.
SELECT
t1.fname,
t1.lname,
t2.err_msg
FROM (
SELECT
fname,
lname,
CAST(' ' AS XML) AS error_codes
FROM Table1
) t1
CROSS APPLY error_codes.nodes('//i') x(i)
INNER JOIN Table2 t2 ON t2.err_cd = x.i.value('.', 'INT')
/*
fname lname err_msg
--------------- --------------- --------------------------------------------------
ash kan This record doesnot have privilege
ash kan Calculation Mistake
ash1 kan1 This record doesnot have privilege
ash1 kan1 Category is Missing
avi nig Calculation Mistake
avi nig Category is Missing
*/
.
October 7, 2008 at 11:23 am
Hello All
Thanks for reply and valuable feedback. I realize using sql is bit difficult becuase may be in future there might be more code. Is it not better I should write a Store procedure or Function? Any idea on this?
Ashish
October 7, 2008 at 11:29 am
Did you try the code I posted? It can process more codes.
.
October 7, 2008 at 11:49 am
Hello Jacob
I tested on sample table as I mentioned thier structure it is working fine, but on actual table is it returning nothing. Here is actual table structure and data
CREATE TABLE MyLine(
MyLine1 VARCHAR(50),
MyLine2 VARCHAR(50),
MyLine3 VARCHAR(50),
ERROR_CDS VARCHAR(90))
CREATE TABLE MyErrors(
CODE VARCHAR(3),
err_msg VARCHAR(200))
INSERT INTO MyLine1(MyLine1,MyLine2, MyLine3, ERROR_CDS)
SELECT 'ash','kan','avk','75 100' UNION ALL
SELECT 'ash1','kan1','avk1','75 202' UNION ALL
SELECT 'avi','nig','sri','75 202'
INSERT INTO MyErros(code, err_msg)
SELECT '75','This record doesnot have privilege' UNION ALL
SELECT '100','Calculation Mistake' UNION ALL
SELECT '202','Category is Missing'
As you suggested, here is the SQL
SELECT t1.MyLine1, t1.MyLine2 ,
t1.MyLine3 , t2.err_msg
FROM (
SELECT
MyLine1, MyLine2 ,
MyLine3,
CAST(' ' AS XML) AS ERROR_CDS
FROM dbo.MyLine
) t1
CROSS APPLY ERROR_CDS.nodes('//i') x(i)
INNER JOIN MyErrors t2 ON t2.code = x.i.value('.', 'INT')
Am I missing anything here?
October 7, 2008 at 12:39 pm
Jacob
Any advise on my last post?
October 7, 2008 at 8:41 pm
Hi santosh,
I see that the editor removed XML tags from my post and that is why you dont get the correct results. The cast should be as follows:
I am replacing XML tags " " with "{" and "}". replace them back before you run the code.
CAST('{i}' + REPLACE(error_cds, ' ', '{/i}{i}') + '{/i}' AS XML)
.
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply