October 11, 2007 at 9:25 pm
I am currently working to solve this problem that i am facing.
I just cant' get my desired solution to my problem.
As per below is the current db view.
emp_id skill level years remarks
2541 COMPUTER BAIK 1
2541 WORD PROCESSING BAIK 1
2541 EXCEL BAIK 1
2541 POWERPOINT BAIK 1
how do i get this repeated emp_id to have a view of..
emp_id skill level
2541 COMPUTER.wordprocessin,excel,powerpoint BAIK,baik,baik,baik
years remarks
1,1,1,1
i just can't seem to get this comin up..
i tried the maxfunction.. seems to be givin the max result
SELECT COUNT(UV_EMPLOYEE_ID) AS Expr1, MAX(UV_SKILL) AS uv_skill, UV_EMPLOYEE_ID
FROM ORC_TB_SKILLS
GROUP BY UV_EMPLOYEE_ID
result:
3 WORD 64
3 WORD PROCESSING 579
which means that there are other two skills which is not shown.. i need to actually show all skill on duplicate
to uv_skill column...
sample output: 1234 excel,programming *assumingly 1234 is duplicated twice
pls advise.. yer help are most appreciated
regards,
k
October 12, 2007 at 12:02 am
Hi
See if this helps you.
declare @empskill VARCHAR(100)
set @empskill = ''
SELECT @empskill = @empskill + Skill level from table
where emp_id = 2541
select distinct emp_id,@empskill from table
where emp_id = 2541
"Keep Trying"
October 17, 2007 at 7:49 pm
hey Craig,
thanks for the reply to my query..
i guess it is working..
this is the code i m looking at rite now..
DECLARE @empskill2 VARCHAR(100)
SET @empskill2 = ''
SELECT @empskill2 = @empskill2 + uv_level + uv_year + uv_remarks
FROM orc_tb_skills
WHERE uv_employee_id = 2541
SELECT DISTINCT uv_employee_id, @empskill2
FROM orc_tb_skills
WHERE uv_employee_id = 2541
question?
1) After running this script.. i have a pop up that say 1 row effected
n there isnt any output shown like the other normal query.. is this the case?
2) IF i would like to check for duplicated id's with the code below.. how do i go about checking for duplicate ----> merging duplicate...
here is my script for duplicating.. should i use the if else case?
please advise..
select uv_employee_id,count(uv_employee_id) as numofoccu
from orc_tb_skills
where count(uv_employee_id)>1
thank you for your time sir..
regards,
k
October 17, 2007 at 7:53 pm
the error message after executing query below is .. This designer does not graphically support the DECLARE CURSOR SQL construct. Is it becoz i m using sql2000?
DECLARE @empskill2 VARCHAR(100)
SET @empskill2 = ''
SELECT @empskill2 = @empskill2 + uv_level + uv_year + uv_remarks
FROM orc_tb_skills
WHERE uv_employee_id = 2541
SELECT DISTINCT uv_employee_id, @empskill2
FROM orc_tb_skills
WHERE uv_employee_id = 2541
October 18, 2007 at 4:53 am
kokwei_w (10/17/2007)
hey Craig,thanks for the reply to my query..
i guess it is working..
this is the code i m looking at rite now..
DECLARE @empskill2 VARCHAR(100)
SET @empskill2 = ''
SELECT @empskill2 = @empskill2 + uv_level + uv_year + uv_remarks
FROM orc_tb_skills
WHERE uv_employee_id = 2541
SELECT DISTINCT uv_employee_id, @empskill2
FROM orc_tb_skills
WHERE uv_employee_id = 2541
question?
1) After running this script.. i have a pop up that say 1 row effected
n there isnt any output shown like the other normal query.. is this the case?
2) IF i would like to check for duplicated id's with the code below.. how do i go about checking for duplicate ----> merging duplicate...
here is my script for duplicating.. should i use the if else case?
please advise..
select uv_employee_id,count(uv_employee_id) as numofoccu
from orc_tb_skills
where count(uv_employee_id)>1
thank you for your time sir..
regards,
k
Hi
Sorry for the delay in replying.
Regarding your questions
1) To display the results you will have to run a select on the variable (@empskill2). This has to be done just after the statement that concatenates different values i.e. And both the select statements must be in a single batch. If you can put this in a stored proc it would be better.
SELECT @empskill2 = @empskill2 + uv_level + uv_year + uv_remarks......
select uv_employee_id, @empskill2 from orc_tb_skills where uv_employee_id = 2541.
2) query for getting duplicate id's is correct.
"Keep Trying"
October 18, 2007 at 4:56 am
kokwei_w (10/17/2007)
the error message after executing query below is .. This designer does not graphically support the DECLARE CURSOR SQL construct. Is it becoz i m using sql2000?DECLARE @empskill2 VARCHAR(100)
SET @empskill2 = ''
SELECT @empskill2 = @empskill2 + uv_level + uv_year + uv_remarks
FROM orc_tb_skills
WHERE uv_employee_id = 2541
SELECT DISTINCT uv_employee_id, @empskill2
FROM orc_tb_skills
WHERE uv_employee_id = 2541
Hi
Where are you doing this . I tried something similar in SQL 2000 QueryAnalyzer it was working. pls give more details.
"Keep Trying"
October 19, 2007 at 1:26 am
dear chirag,
thank u sir.. this works fine now.. minor alterations here and there got this script working
i have modified n reused the codes to group the concatenation as such
DECLARE @empskill2 VARCHAR(200)
SET @empskill2 = ''
DECLARE @empskill3 VARCHAR(100)
SET @empskill3 = ''
DECLARE @empskill4 VARCHAR(100)
SET @empskill4 = ''
DECLARE @empskill5 VARCHAR(100)
SET @empskill5 = ''
SELECT @empskill2 = uv_skill + @empskill2, @empskill3 = uv_level + @empskill3 ,@empskill4 = uv_year + @empskill4,@empskill5 = uv_remarks + @empskill5
FROM orc_tb_skills
where uv_employee_id='2541'
SELECT distinct uv_employee_id, @empskill2 as 'Skills' ,@empskill3 as 'level',@empskill4 as 'year',@empskill5 as 'remarks'
FROM orc_tb_skills
where uv_employee_id='2541'
Question
1) it works fine for id=2541 but what if there is more then one duplicated id throughout the whole table of say est 3000 records.. i tried using the "and' "or" even "," to try getting the where clause to work.. but it is not giving me the desired output.. how do i go about this.. any suggestion.. ? Is it possible to do sumthin like this
e.g
where uv_employee_id='2541','1110','6666','1234','4567' to just concet all the duplicated id's from my duplication check script?
thanks for all yer replies ...looking forward to hear from you guru
October 19, 2007 at 4:35 am
kokwei_w (10/19/2007)
dear chirag,thank u sir.. this works fine now.. minor alterations here and there got this script working
i have modified n reused the codes to group the concatenation as such
DECLARE @empskill2 VARCHAR(200)
SET @empskill2 = ''
DECLARE @empskill3 VARCHAR(100)
SET @empskill3 = ''
DECLARE @empskill4 VARCHAR(100)
SET @empskill4 = ''
DECLARE @empskill5 VARCHAR(100)
SET @empskill5 = ''
SELECT @empskill2 = uv_skill + @empskill2, @empskill3 = uv_level + @empskill3 ,@empskill4 = uv_year + @empskill4,@empskill5 = uv_remarks + @empskill5
FROM orc_tb_skills
where uv_employee_id='2541'
SELECT distinct uv_employee_id, @empskill2 as 'Skills' ,@empskill3 as 'level',@empskill4 as 'year',@empskill5 as 'remarks'
FROM orc_tb_skills
where uv_employee_id='2541'
Question
1) it works fine for id=2541 but what if there is more then one duplicated id throughout the whole table of say est 3000 records.. i tried using the "and' "or" even "," to try getting the where clause to work.. but it is not giving me the desired output.. how do i go about this.. any suggestion.. ? Is it possible to do sumthin like this
e.g
where uv_employee_id='2541','1110','6666','1234','4567' to just concet all the duplicated id's from my duplication check script?
thanks for all yer replies ...looking forward to hear from you guru
Hi
Plz dont call me sir.
Do you want to run this query for multiple userids.
If so i suggest you put the query in a stored procedure.
Inside the procedure you can have a "while" loop.
Inside the loop the query can be executed for all userids ONE BY ONE. Build a temp table with the results of each execution of the query. All this is inside the loop.
BTW will be out for the week end .. can answer back only on monday...:)
Regards
"Keep Trying"
October 21, 2007 at 7:54 pm
Dear chirag..
for the while... how do i determine the last record for employee_id
while uv_employee_id = .....
1) does sql 2000 has a function to determine the end of record set?
ooh i have no idea where to go about to start or do a stored procedure to run the query :unsure:
show me some light chirag
been workin n looking the whole week i just cant get any of them...
regards,
k
October 21, 2007 at 11:59 pm
Hi K
I suggest you undertake some training on basics of SQL . Traning could be done within the organization you work in ... somebody senior could be your mentor or you can do some course in SQL.
Procedures are one of the most basic things in SQL and teaching how to write them thru this forum may not be the best way to go about it.
You can start off by looking into SQL server help. It has the syntax and also examples.
I could write the procedure that you want but what will you do when some new requirement comes up. Also if you may need to modify the procedure.
"Keep Trying"
October 22, 2007 at 7:48 pm
dear chirag,
i totally agree on your comments.. unfortunately there ain't anyone around to guide me where everyone is just being so busy with their owns.. and i have to seek for external guidance..
i will do alittle research on the stored procedure and i shall get back to you soon .. i guess i somehow already understand the concept of stored procedure..where as just a few questions i need to ask you chirag:
1)insert into statement after the query is done is to be placed after the codes or before the codes?
2)for the while statement.. i assume that we will be running it on uv_employee_id but how do we define the end of recordset?
I will get back to you in regards to this stored procedure if i face any difficulities...
thank you for yer time cirag hope to hear from you soon
good day,
k
October 23, 2007 at 12:28 am
CREATE PROCEDURE bpm.kelulusandup as
DECLARE @empskill2 VARCHAR(200)
SET @empskill2 = ''
DECLARE @empskill3 VARCHAR(100)
SET @empskill3 = ''
DECLARE @empskill4 VARCHAR(100)
SET @empskill4 = ''
DECLARE @empskill5 VARCHAR(100)
SET @empskill5 = ''
Declare @Next int
Set @Next=''
While 1=1
Begin
Select @Next=uv_employee_id
From orc_tb_skills
if @next is null
break
else
select numofoccu from dupidkel-------->another new table created for duplicated id n its numofoccurence
SELECT CASE WHEN numofoccu > 1 THEN
exec (SELECT @empskill2 = uv_skill +'/'+ @empskill2, @empskill3 = uv_level + '/' + @empskill3 ,@empskill4 = uv_year + '/' + @empskill4,@empskill5 = uv_remarks + '/' + @empskill5
FROM orc_tb_skills)
exec (SELECT distinct uv_employee_id , @empskill2 as 'Skills' ,@empskill3 as 'level',@empskill4 as 'year',@empskill5 as 'remarks'
FROM orc_tb_skills) ------->concat code
insert into dupkelulusan ------> (new table created for concant values on id duplication)[/color]
END
continue
End
==============================================================================
Dear Chirag,
Finally got to look up on stored procedure.. but it seems to be there are some errors on syntax.. which took me the whole day to figure it out.. i m running out of ideas ..it is not running at my logic thinks it should be...
Question(s)
1) my current flow is .. at the stored procedure ---> declarations --->while --->case of numofoccurence from another table dupidkel-----> if > 1 run the concat code & insert into a new table for the concatenated results into concatkelulusan .. n that's about it.. Is there anything worng with the flow or i m just stuck with the syntax error? Enlighten me please
It has been almost like hell a new person in sql meeting so much of complicated real world datas.. with no help nor assistance .. please advise..
hope to hear from u soon chirag
regards,
k
October 23, 2007 at 12:41 am
better try this link example.
October 23, 2007 at 2:13 am
Hi
Your proc is all wrong. Its not the way to go about it.
If you want me to write the proc send me the table definitions and some sample data. (NOT A GOOD IDEA)
I THINK U SHUD LEARN SQL FIRST AND THEN DO ALL THIS.
"Keep Trying"
October 23, 2007 at 2:25 am
sorry for the inconvinience..
i guess i will look into this myself again ..
will hit u back when i have any probs
thanks chirag,
u have been really helpfull much appreciated.
regards,
k
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply