|
One possibile problem is that you are calling FETCH NEXT on your inner cursor at the start of the inner loop. Move the call from the start to the end.
Also, why are you inserting records like
INSERT INTO @Result VALUES (@mgID, @EmpDetail)
When @EmpDetail is a comma separated list of values? There really isn't any reason to do this when you are using a relational database. The relationship between Manager and Employee is maintained when you do this:
CREATE TABLE Manager(
ManagerID INT,
ManagerName VARCHAR(50)
)
CREATE TABLE Employee(
EmployeeID INT,
ManagerID INT,
EmployeeName VARCHAR(50)
)
Then when you want to get data from both tables you do this:
DECLARE @EmployeeID INT
SET @EmployeeID = 5
SELECT EmployeeID,
EmployeeName,
ManagerName
FROM Employee E
INNER JOIN Manager M ON M.ManagerID = E.ManagerID
WHERE EmployeeID = @EmployeeID
When you try and maintain a delimited list in a column you're breaking normalization and it will make the data a nightmare to maintain. If you need to enforce the relationships between tables, create foreign key references and they will prevent you from corrupting the relationships you've defined. If you're going to define your data the way it seems you are, you might as well be using Excel or just plain text files.
|
|
|
|
|
Now its creating problem in second manager entry that employee record only binding more than one:
Its modified cursor look at this:
DECLARE curMgr CURSOR FOR
SELECT distinct ManagerID FROM @tbl_TempManagers
OPEN curMgr
FETCH NEXT FROM curMgr INTO @mgID
Insert Into @tbl_TempEmployee Select MId,ID,UserName,EId from tbl_login Where Mid=@mgid
WHILE (@@FETCH_STATUS = 0 )
BEGIN
SET @EmpDetail = ''
SELECT @Count=Count(*) FROM @tbl_TempEmployee
--
SET @Counter=1
DECLARE curDetailList CURSOR FOR
SELECT distinct EmployeeName from @tbl_Tempemployee where ManagerId = @mgID order by EmployeeName
OPEN curDetailList
WHILE (@@FETCH_STATUS = 0 AND @Counter <= @Count)
BEGIN
SELECT dbo.tbl_Login.MID,
convert(varchar(20),TSDate,101) as [Date],TSSubject as [Subject],TSDescription as [Description],
TSHour as [Hour],TSMin as [Minute],convert(varchar(20),CreationDate,101) as [Creation Date],
CreationBy as [Name Of Employee] FROM tbl_TSEnterData
INNER JOIN
dbo.tbl_Login ON dbo.tbl_TSEnterData.CreationBy = dbo.tbl_Login.UserName
where CreationBy=@employeename1
AND TSDate BETWEEN
CONVERT( NVARCHAR, DATEADD(wk, -1, @d) , 101) AND CONVERT( NVARCHAR, DATEADD(dd, -1, @d) , 101)
order by CreationBy asc,TSDate Desc
SET @counter=@counter+1
FETCH NEXT FROM curDetailList INTO @EmployeeName1
END
CLOSE curDetailList
DEALLOCATE curDetailList
FETCH NEXT FROM curmgr INTO @mgID
Insert Into @tbl_TempEmployee Select MId,ID,UserName,EId from tbl_login Where Mid=@mgid
END
CLOSE curmgr
DEALLOCATE curmgr
Thanks & Regards,
NeW OnE,
please don't forget to vote on the post
modified on Friday, July 18, 2008 3:27 AM
|
|
|
|
|
Instead of a cursor, why not try this:
<br />
SELECT <br />
dbo.tbl_Login.MID,<br />
convert(varchar(20),TSDate,101) as [Date],<br />
TSSubject as [Subject],<br />
TSDescription as [Description],<br />
TSHour as [Hour],<br />
TSMin as [Minute],<br />
convert(varchar(20),CreationDate,101) as [Creation Date],<br />
CreationBy as [Name Of Employee] <br />
FROM @tbl_TempManagers M<br />
INNER JOIN tbl_login E ON E.Mid = M.ManagerID<br />
INNER JOIN tbl_TSEnterData D ON D.CreationBy = E.UserName<br />
WHERE D.TSDate BETWEEN DATEADD(wk, -1, @d) <br />
AND DATEADD(dd, -1, @d) <br />
order by D.CreationBy asc,D.TSDate Desc<br />
Temp tables are just like regular tables and can be used in all the same ways. You don't need a foreign key reference to join tables - foreign key references are for data integrity, they aren't required at all in the database, but they are certainly needed to prevent data corruption. So based on what I can tell from your script you can use joins on your temp tables.
Also, as a side note - remove the CONVERT function from your where clause - you're using it to format the date values, but the only reason to format those values is if TSDate is a VARCHAR.
|
|
|
|
|
Hi,
Iam compacting my MSAccess database using the CDaoWorkspace's CompactDatabase function in VC++. Its throwing exception as " Record(s) can't be read; no read permission on DB" . Can anyone let me know the reason for it.
|
|
|
|
|
Sounds like a permission issue. I'd double check that.
"The clue train passed his station without stopping." - John Simmons / outlaw programmer
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
|
|
|
|
|
Hi i have two tables
I have to compare this 2 tables...,
Now which dates are not in table1 that date and that ename have to come in select statement
Table1:
TSID TSdate EName
100 7-21-2008 Name1
101 7-21-2008 Name1
102 7-22-2008 Name1
103 7-26-2008 Name1
104 7-23-2008 Name2
105 7-25-2008 Name2
Table2:
Nodates
7-21-2008
7-22-2008
7-23-2008
7-24-2008
7-25-2008
7-26-2008
output:
Date Name
7-23-2008 Name1
7-24-2008 Name1
7-25-2008 Name1
7-21-2008 Name2
7-22-2008 Name2
7-24-2008 Name2
7-26-2008 Name2
how to do that
Thanks & Regards,
NeW OnE,
please don't forget to vote on the post
|
|
|
|
|
Either your output data isn't correct for the example you gave us, or I'm not deciphering your question correctly.
What I think you might want is the Not In syntax:
Select TSDate, EName
From Table1
Where TSDate Not In (Select Nodates From Table2)
|
|
|
|
|
No actually the table 2 data and table 1 data have to come
And see my condition...,
Its problem solved using this query:
select * from (select distinct CreationBy from dbo.tbl_TSEnterData) a cross join @tbl_LastWeekDates b
where not exists (select * from dbo.tbl_TSEnterData where TSdate = b.NoDates and CreationBy = a.CreationBy)
Thanks & Regards,
NeW OnE,
please don't forget to vote on the post
|
|
|
|
|
i want to select both values using union operation how can i do this i use the following sql query
SELECT ProdReqId ,ProdSlNo, convert(varchar(10),ProdCurrDate,105) AS Date1
FROM tbl_ProductRequirementDetails where BrachId=4 and Status=1
UNION ALL
select PurOrderId ,PurOrderCode,convert(varchar(10),PurOrderCurrDate,105) AS Date2 from tbl_PurchaseOrder where BrachId=4 and Status=1
I want to select -- ProdReqId ,ProdSlNo, convert(varchar(10),ProdCurrDate,105) AS Date1 -- and -- PurOrderId ,PurOrderCode,convert(varchar(10),PurOrderCurrDate,105) AS Date2 --
how can i select that
|
|
|
|
|
you realy have to explain in details what do you want to achieve
Vuyiswa Maseko,
Sorrow is Better than Laughter, it may Sadden your Face, but It sharpens your Understanding
VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
vuyiswam@tshwane.gov.za
|
|
|
|
|
hi
my query =
SELECT * FROM dbo.Categories INNER JOIN dbo.Products ON dbo.Categories.CategoryID = dbo.Products.CategoryID INNER JOIN dbo.Suppliers ON dbo.Products.SupplierID = dbo.Suppliers.SupplierID
and returned column name like ProductID | ProductName | SupplierID | CategoryID
but i want
dbo.Products.ProductID | dbo.Products.ProductName | dbo.Suppliers.SupplierID
how?
thanks
|
|
|
|
|
try this
SELECT Products.ProductID AS 'dbo.Products.ProductID', Products.ProductName AS 'dbo.Products.ProductName', Suppliers.SupplierID AS 'dbo.Suppliers.SupplierID '
FROM dbo.Categories
INNER JOIN dbo.Products
ON dbo.Categories.CategoryID = dbo.Products.CategoryID
INNER JOIN dbo.Suppliers
ON dbo.Products.SupplierID = dbo.Suppliers.SupplierID
you should NEVER EVER SELECT * FROM, even if you do want to get all the columns of a table, specify them.
Harvey Saayman - South Africa
Junior Developer
.Net, C#, SQL
you.suck = (you.passion != Programming)
|
|
|
|
|
HarveySaayman wrote: you should NEVER EVER SELECT * FROM
Thats an extremely broad statement, can you please elaborate with a reason why!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
i dont know the EXACT reason why, but i have been told(by my step mom who's been a data architect at a large insurance company's data warehouse for the past 20 years, and many other people) that SELECT * FROM has significant and unnecessary server overhead. And when your working with db's that have terras and terras of data you can and will bring the system down.
The using of SELECT * FROM worn have a noticeable effect if your working on a much smaller scale db but its considered bad practice.
SQL just has these little things that bring performance down, another example would be using the prefix "sp_" in front of a stored procedure's name... the reason for that is that sp stands for system procedure and NOT stored procedure, so SQL goes and looks through the system procedures first before moving on to stored procedures.
Cheers 
Harvey Saayman - South Africa
Junior Developer
.Net, C#, SQL
you.suck = (you.passion != Programming)
|
|
|
|
|
HarveySaayman wrote: know the EXACT reason why
Yah, I have the same recollection and only use it under special circumstances.
HHmmm, maybe I should talk to your step mum  . I think the sp_ issues is well documented and well known but the Select * issues is a little more obscure.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
There is nothing difficult with this!
If you need all columns of a table - use *, if you need only a few columns, specify them by their name! And of course you stress the DB Server more by querying all columns of a table instead of selecting only a few of them.
|
|
|
|
|
| Hi
on Data base one column has data like this As a representative of Preston Insulation, I
when i show this on my report those styles has to apply for the data .
the data base column can be varchar or xml any data taype.
Thanks in advance
|
|
|
|
|
ch.ramesh wrote: those styles has to apply for the data .
How are you storng these styles? If you look at the code to store the style yu should be able to reverse it back out to apply in your report?
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Hi Bob
thanks for your reply.
we are fecthing the Data from DB as
'As a representative of Preston Insulation, I would like to congratulate you on your recent construction permit and introduce myself, my Company and the products that we offer. Please take a moment to review the following information, as having the right partners in the building process will save time, effort and dollars. '
like this we are getting data.from the client we are getting that data .
we can change this column to any data type .
we have to show the report with the styles in .
Thanks
|
|
|
|
|
Sorry, I don't know what you are talking about.
ch.ramesh wrote: As a representative of Preston Insulation, I would like to congratulate you on your recent construction permit and introduce myself, my Company and the products that we offer. Please take a moment to review the following information, as having the right partners in the building process will save time, effort and dollars.
has no styles to apply.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
First off, I really don't know much about vbScript, so hopefully what I have thrown together won't look too awful.
I am trying to come up with a way to label parcels in ArcGIS, and I am about ready to throw my computer out the window. I have to use vbScript to create the labels by connecting to a database pulling the data from multiple tables, assembling a text string and assigning it to a polygon. Unfortunately the vbScript engine in ArcGIS doesn't support joins, so I came up with a way to create one query, get the data I need from one table and stuff it into an array (if necessary) for the second query. Unfortunately, I am getting bombarded with errors when I try to do the second query, and I have scoured search engines for a solution with no luck at all. Everything I found tells me how to do one query, but doesn't address the problem of running multiple queries. Any help that I can be given would be greatly appreciated. Everything works fine until I try to do the second query. I'm not sure what the procedure is to do another query, or if it's even possible.
Here is what I have written so far, including the portions that don't work (they are commented out).
Thanks a lot in advance!
Function FindLabel ([FEATURE_ID])
Dim strPrclQry, strLseQry, strinfo, LeaseNum, aLeaseList, LabelCount, FinalLabel, sLeaseList
'form query string
strPrclQry = "SELECT LEASE_NAME FROM TRACTS WHERE TRACTS.FEATURE_ID = '"&[FEATURE_ID]&"' ORDER BY LEASE_NAME ASC"
Dim ADOConn
set ADOConn = CreateObject("ADODB.Connection")
Dim rsPrcl
set rsPrcl = CreateObject("ADODB.Recordset")
ADOConn.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=.\DART_PROJECTS.mdb"
ADOConn.CursorLocation = 3
rsPrcl.Open strPrclQry, ADOConn, 3, 1, 1
'if no record is found, return empty string
Select Case rsPrcl.RecordCount
Case -1, 0
strInfo = " "
Case 1
'reading only the first record
LeaseNum = rsPrcl.Fields("LEASE_NAME").Value
strInfo = LeaseNum + strInfo
aLeaseList = strInfo
Case Else
for I = 1 to rsPrcl.RecordCount
if I <> 1 then
strInfo = " " + strInfo
End If
LeaseNum = rsPrcl.Fields("LEASE_NAME").Value
strInfo = LeaseNum + strInfo
rsPrcl.Movenext
Next
aLeaseList = Split(strInfo)
End Select
'closing connections
rsPrcl.Close
Set rsPrcl = Nothing
'put arrays into labels
If IsArray(aLeaseList) <> 0 then
LabelCount = 0
For Each sLeaseList In aLeaseList
if LabelCount <> 0 then
FinalLabel = vbNewLine + FinalLabel
End If
LabelCount = 1
FinalLabel = sLeaseList + FinalLabel
Next
Erase aLeaseList
'If there is only one lease in the parcel
Else
'form query string
' strLseQry = "SELECT L_NAME, F_NAME1, F_NAME2, ADD_INFO FROM LEASES WHERE LEASE_ID = '"&aLeaseList&"'"
' Dim rsLse
' set rsLse = CreateObject("ADODB.Recordset")
' rsLse.Open strLseQuery, ADOConn, 3, 1, 1
'building text string if there is a name
' If rsLse.Fields("L_NAME").Value <> "" then
' FinalLabel = rsLse.Fields("L_NAME").Value
' if IsNull(rsLse.Fields("F_NAME1") = "False" then
' strLabelInfo = rsLse.Fields("L_NAME")&", "&LEFT(rsLseFields("F_NAME1"), 1)
' End If
' if IsNull(rsLse.Fields("F_NAME2") = "False" then
' strLabelInfo = strLabelInfo + "/" & LEFT(rsLse.Fields("F_NAME2"), 1)
' End If
' if IsNull(rsLse.Fields("ADD_INFO") = "False" then
' strLabelInfo = strLabelInfo & " " & rsLse.Fields("ADD_INFO")
' End If
' End If
FinalLabel = aLeaseList
End If
'closing connections
'rsLse.Close
'Set rsLse = Nothing
ADOConn.Close
Set ADOConn = Nothing
FindLabel = FinalLabel
End Function
|
|
|
|
|
Try releasing your ADO.Recordset - you are using it twice in your code without a release - or create two record sets.
|
|
|
|
|
I would say that the following line looks iffy:
strLseQry = "SELECT L_NAME, F_NAME1, F_NAME2, ADD_INFO FROM LEASES WHERE LEASE_ID = '"&aLeaseList&"'"
If LeaseNum values are numbers then your aLeaseList should be comma-separated, then use:
strLseQry = "SELECT L_NAME, F_NAME1, F_NAME2, ADD_INFO FROM LEASES WHERE LEASE_ID IN ("&aLeaseList&")"
If that doesn't work then post the value of your strLseQry variable to this thread.
|
|
|
|
|
Thanks for the quick responses, I've made some real progress and have gotten the queries to work!
|
|
|
|
|
Don´t know exactely if this will help, but you do not necessarily must use "JOIN" to get data out of several tables. You can define the connection between two or more tables in the "Where" clause. In your case it would look something like this:
SELECT TRACTS.LEASE_NAME, LEASES.L_NAME, LEASES.F_NAME1, LEASES.F_NAME2 FROM TRACTS, LEASES
WHERE TRACTS.LEASE_NAME=LEASES.L_NAME AND TRACTS.FEATURE_ID = '"&[FEATURE_ID]&"'
ORDER BY LEASE_NAME ASC"
You will receive 1 recordset with all needed data.
There is one little blemish within that kind of SQL. To connect several tables by a "JOIN" statement works quicker than only using the "Where" clause because "JOIN" is an optimized procedure. But in Your case that doesn´t matter!
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
