|
|
Yes to both.
Check out Books Online for further details[^]
"My interest is in the future because I'm going to spend the rest of my life there." - Charles F. Kettering
|
|
|
|
|
Select *
Into #BC
From dfCert -- Creates master Certification table -> #BC
Declare @BoardCert Char(3)
Set @BoardCert = 'BCn'
Declare @Counter int
Set @Counter = 1
While @Counter < 10
Begin
Select
Biog_Nbr,
Max(Biog_Cert_ID) as aMaxBiogCertID,
Max(Cert_Year) as aMaxCertYear
Into
#tblBoardCert
From
#BC
Group By
Biog_Nbr
Delete From #BC
From #BC
Inner Join #tblBoardCert
On
#BC.Biog_Nbr = #tblBoardCert.Biog_Nbr
and
#BC.Biog_Cert_ID = #tblBoardCert.AMaxBiogCertID
and
#BC.Cert_Year = #tblBoardCert.aMaxCertYear
Where
#BC.Biog_Nbr = #tblBoardCert.Biog_Nbr
and
#BC.Biog_Cert_ID = #tblBoardCert.AMaxBiogCertID
and
#BC.Cert_Year = #tblBoardCert.aMaxCertYear
Set @BoardCert = Replace(Left(@BoardCert,2)+Cast(@Counter as Char),' ','')
Select *
Into #@BoardCert --<< Here's the problem. I need this to be #BC1 then BC2, etc up to BC9
From #tblBoardCert
Drop Table #tblBoardCert
Set @Counter = @Counter + 1
Print 'The counter has just increased to ' + Cast(@Counter as Char)
End
|
|
|
|
|
I guess there is some problem in the counter placement.
You are dropping the #TtblBoardCert and then you are incrementing.
Drop Table #tblBoardCert<br />
<br />
Set @Counter = @Counter + 1
So before the first increment itself the table is dropped.
Also I have a doubt in this line
Replace(Left(@BoardCert,2)+Cast(@Counter as Char),' ','')
Check if it is giving you the correct result or not.
Hope this helps.
Niladri Biswas
|
|
|
|
|
I drop the table because it needs to be rebuild on each cycle. The "Max" records are removed on the first cycle and then I pull the next set of "Max" records for the second cycle. This goes on until #BC is empty. Replace(Left(@BoardCert,2)+Cast(@Counter as Char),' ','') actually works. The real problem is the following:
Select *
Into #@BoardCert
From #tblBoardCert
It doesn't see #@BoardCert as #BC1 then #BC2 etc., it only sees it as #@BoardCert and so it errors because it thinks it's already written to #@BoardCert on the first cycle.
|
|
|
|
|
This just sounds weird, without going through the details of your script I think you can achieve this in 1 table by including a setID. If you need to create an incremented value over a set you can use row_number (over setID).
Creating and dropping temp tables is just bad design.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hello everyone,
I have no idea where to start with this so I will just explain my goal.
I want to create a website for users to share information and pictures with each other. I would like to provide a forum for users to communicate with each other, as well as allow the users create a database of items they own with pictures. So my question is in two parts.
Is there any free forum software that anyone would highly recommend?
How do I create a database online for users to share pictures and information, which can be viewed by all?
Thank you all very much for your efforts in pointing me in the right direction.
V/R
Rob
|
|
|
|
|
|
well, I am not well in sql want some help
so have dbo.Users and dbo.Customers tables, between them is 1:1 relationship
just watch them:Users Custimers
UserName I have done as unical from indexers.
when I select right this select * from Users,Customers where UserName='John' or Email='xxxx@yahoo.com' shows: That
and i want to show only UserName and it's corresponding Filds
how manage it?
and at last I don't want like this select * from Users,Customers where UserName='John' and Email='xxxx@yahoo.com'
C# Developer
|
|
|
|
|
Hope this will help you.
select customers.*,
(select top 1 users.username from users where users.username=customers.forename) as UsersTableUserName
from customers where username='John' and Email='xxxx@yahoo.com'
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
Hope you are looking for this
SELECT U.USERNAME,C.*
FROM Customers C
INNER JOIN Users U
ON U.ID = C.ID
AND C.FORENAME = 'JOHN'
AND C.EMAIL = 'xxxx@yahoo.com'
Let me know in case of any concern.
Niladri Biswas
|
|
|
|
|
Hello All,
Here i'll explain very clear please read my problem with little patience.
I've two table X and Y. after every hour i'm moving all the records from X to y (now x table don't have any rows). After insert into Y table i'm checking 1 column value with the recent existed record in the Y table, If the value changes then i'm updating the Ischanged column value to 1 , if not with 0.
this is working fine for me.( I'm doing this in trigger)
Now the problem is , there are multiple records coming from X table with different column values in my desired column.
X Table :
Col1 Col2 Col3
1 1234 4781258
2 1234 4781258
3 1234 4781259
4 1234 4781258
5 1234 4781259
6 1234 4781258
7 1234 4781258
8 5487 5124873
9 5487 5124873
10 5487 5124873
Assume 1234 and 5478 records exist in Y table earlier
with coll 3 value 4781258 and 5124873 respectively.
Y Table (Expected)
Col1 Col2 Col3 IsChanged
1 1234 4781258 0
2 1234 4781258 0
3 1234 4781259 1 (changed from 4781258 to 4781259)
4 1234 4781258 1 (changed from 4781259 to 4781258)
5 1234 4781259 1 (changed from 4781258 to 4781259)
6 1234 4781259 0 (not changed with the prev record)
7 1234 4781258 1 (changed from 4781259 to 4781258)
8 5487 5124873 0 (not changed with the prev record)
9 5487 5124873 0
10 5487 5124873 0
Thanks in advance. If anybody knows the solution please help me out.
Avinash
|
|
|
|
|
You haven't actually asked a question.
You have explained your data. You have explained what you have done, and that it works.
For anyone to help you, you need to explain what you want to happen when you insert the records into Y, if different from what you are already doing. Or alternatively what to do with it once it is there.
What is your question?
Henry Minute
Do not read medical books! You could die of a misprint. - Mark Twain
Girl: (staring) "Why do you need an icy cucumber?"
“I want to report a fraud. The government is lying to us all.”
|
|
|
|
|
here my expected output is not coming properly. tell me how to get my expected output
|
|
|
|
|
I am sorry, perhaps it is me, but I still do not fully understand your problem.
What are the rules for when a record from table X should update the record in table Y. Is it simply when Column 3 is different?
It would also help to see the code/SP or whatever, that you are currently using.
Henry Minute
Do not read medical books! You could die of a misprint. - Mark Twain
Girl: (staring) "Why do you need an icy cucumber?"
“I want to report a fraud. The government is lying to us all.”
|
|
|
|
|
Hi Henry,
My Y table is like a log table.When i move values from X table to Y table i want to set the value in column 3 based on the col2 value if col2 value changes from the previous record then i need to set column 3 value to 1, otherwise 0 (default).
|
|
|
|
|
Hi Avinash,
If I have understood your problem correctly, initially you are having two tables say tblX, tblY.
Initially, tblX and tblY will have the same values , with the exception of the Col4(exclusively for tblY)
i.e.
Col1 Col2 Col3 Col4
1 1234 4781258 0
2 1234 4781258 0
3 1234 4781258 0
4 1234 4781258 0
5 1234 4781258 0
6 1234 4781258 0
7 1234 4781258 0
8 5487 5124873 0
9 5487 5124873 0
10 5487 5124873 0
Now if we change any value of Col3 for tblX, then next time when we want to update tblY, Col4 of tblY will change to 1 and ofcourse Col3(of tblY).
i.e. if I change the 3rd , 5th and 10th row's Col3 values to say 3,5,10
respectively of tblX, then in tblY the values will be
Col1 Col2 Col3 Col4
1 1234 4781258 0
2 1234 4781258 0
3 1234 3 1
4 1234 4781258 0
5 1234 5 1
6 1234 4781258 0
7 1234 4781258 0
8 5487 5124873 0
9 5487 5124873 0
10 5487 10 1
Whereas, Col1 and Col2 will always be the same for both the tables.
If this assumption is correct, then this is the stored procedure I wrote for you
ALTER PROCEDURE dbo.UpdateTableY
-- Add the parameters for the stored procedure here
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @MYCURSOR CURSOR
DECLARE @ROWID INT
DECLARE @COL2_X INT
DECLARE @COL3_X INT
DECLARE @COL2_Y INT
DECLARE @COL3_Y INT
SET @MYCURSOR = CURSOR FOR
SELECT A.ROWID,A.COL2,A.COL3
FROM (SELECT
ROW_NUMBER() OVER (ORDER BY COL1) AS ROWID,
COL2 , COL3 FROM tblX) A
OPEN @MYCURSOR
FETCH NEXT
FROM @MYCURSOR INTO @ROWID,@COL2_X,@COL3_X
WHILE @@FETCH_STATUS = 0
BEGIN
-- GET THE COL2 VALUES BASED ON ROW ID'S FROM tblY
SELECT @COL2_Y = COL2
FROM tblY
WHERE COL1 = @ROWID
--MATCH THE COL2 RECORDS OF BOTH TABLES
IF(@COL2_Y = @COL2_X)
BEGIN
-- GET THE COL3 VALUES BASED ON ROW ID'S FROM tblY
SELECT @COL3_Y = COL3
FROM tblY
WHERE COL1 = @ROWID
--IF A MISMATCH BETWEEN THE COL3 RECORDS OF BOTH TABLES
-- THEN UPDATE COL4 OF TABLE Y
IF(@COL3_Y <> @COL3_X)
BEGIN
UPDATE tblY
SET COL3 = @COL3_X,
COL4 = 1
WHERE COL1 = @ROWID
END
END
FETCH NEXT
FROM @MYCURSOR INTO @ROWID,@COL2_X,@COL3_X
END
CLOSE @MYCURSOR
DEALLOCATE @MYCURSOR
END
GO
It will work as per the logic I depicted to you earlier.
Hope this helps.
Niladri Biswas
|
|
|
|
|
Hi,
I have few rows in which the data reads as
rolename roledesc roletype roleuser
----------------------------------------
Team Leader (required) xyzxyz Primary Mike
Team Leader (required) xyzxyz Backup Cary
Process Focal abcabc Primary Jeff
Process Focal abcabc Backup Dave
Now, instead of having 2 rows for the the primay and backup... i should have this
rolename roledesc primary Backup
------------------------------------------
Team Leader (required) xyzxyz Mike Cary
Process Focal abcabc Jeff Dave
please help me in doing this
Thank you
Ramm
|
|
|
|
|
Hi, here is the solution
SELECT
P.ROLENAME,
P.ROLEDESC,
P.ROLEUSER AS 'PRIMARY',
B.ROLEUSER AS 'BACKUP'
FROM (
SELECT ROLENAME,ROLEDESC,ROLETYPE ,ROLEUSER
FROM TBLROLE
WHERE ROLETYPE = 'PRIMARY') P
INNER JOIN (
SELECT ROLEUSER ,ROLEDESC
FROM TBLROLE
WHERE ROLETYPE = 'BACKUP')B
ON P.ROLEDESC = B.ROLEDESC
Output:
ROLENAME ROLEDESC PRIMARY BACKUP
-------------------------------------------------------------
Team Leader (required) xyzxyz Mike Cary
Process Focal abcabc Jeff Dave
Hope this helps.
Niladri Biswas
|
|
|
|
|
Hi Biswas,
Thanks for the help,
I achieved this using PIVOT concept. Now I am able to get the data I need
Thanks a lot for the help.
Thanks
Ramm
|
|
|
|
|
Hi experts
i am using sql server 2005.
How to use like clause of sql server to search Pharse of Word
e.g
select * from abc where name like '%this is my book%'
but not working properly.pls help me
Thanku
Dinesh
|
|
|
|
|
Try this one;
SELECT *
FROM sysobjects
WHERE NAME LIKE ('%name%')
I are troll
|
|
|
|
|
There is nothing wrong with the syntax in the example you have given. When you say it is not working properly, what is happening? Is it giving you an error, or not returning any data, or returning unexpected data?
|
|
|
|
|
Hi man,
First of all I agree with David Skelly.
There is nothing wrong in the syntax.
However, I doubt regarding certain things which you need to check from your end.
For example I have a string like "Project Manager needed in this project (mandatory)"
Case 1:
If I give a SELECT QUERY like this
SELECT ROLENAME
FROM TBLROLE
WHERE RoleName LIKE ('%Project Manager needed in this project (mandatory)%')
-OR-
SELECT ROLENAME
FROM TBLROLE
WHERE RoleName LIKE ('%Project Manager %')
-OR-
SELECT ROLENAME
FROM TBLROLE
WHERE RoleName LIKE ('%(mandatory)%')
I get the correct result.
Case 2:
Now if I give the query like this
SELECT ROLENAME
FROM TBLROLE
WHERE RoleName LIKE ('%ProjectManager needed in this project (mandatory)%')
-OR-
SELECT ROLENAME
FROM TBLROLE
WHERE RoleName LIKE ('% (mandatory) %')
I am not getting any result.
The reason is that, in the first case there is no space between Project & Manager while the same holds good for (mandatory)
But the query behaved in the manner it is supposed to.
What I inferred from your statement, is that, may be you also ran through the same problem.
Have a look into that.
Let me know in case of any concern.
Niladri Biswas
|
|
|
|
|