|
Sorry, didn't see the s_no column I'll see if I can scribble down some SQL
|
|
|
|
|
How about this:
declare @iCount int
select @iCount = count(1) from unit where d_no='D1'
select
s_no,
count(1) UnitCount
from
Assessment
where
u_no in (select u_no from unit where d_no='D1')
group by s_no
having count(1)<@iCount
I didn't test it...but it should be close....
|
|
|
|
|
So the output is 3 records.
How can I display as:
No. Students
------------
3
How's the SQL?
|
|
|
|
|
Hi, I want to know how can i add 2 CRecordset Derived class to my application.. I tried to 2 CRecordset class to my application, but when i make pointers to these classes. It gives me runtime error.. Anyone can help me with that??
I just want to open 2 CRecordset classes and each one point to only 1 table. So i can control them sepeartely... Thanks!
|
|
|
|
|
if you use VC++6,
you can try this way:
use appwizard to derive 2 classes, and use CRecordset as the base class.
and just follow the wizard.
good luck.
|
|
|
|
|
Hey guys, got a question for you all! I've got a DataSet/DataTable, created by using a DataAdapter.Fill call. I'm writing a C# class that handles all the ADO.NET work I need, and a different class will call the public methods of this class.
So; I've created the two public properties I need- Public String _custId and Public DateTime _orderDate. But, I can't get the values out of the DataSet/DataTable/DataColumn without doing (in my mind) useless casting! For instance, this doesn't work:
_custID = dsOrders.Tables["tblOrd"].Rows[iCurrentRow][0];
I get the message "Cannot implicitly convert type 'object' to 'string' ". To make it work, I have to do:
_custID = (String)dsOrders.Tables["tblOrd"].Rows[iCurrentRow][0];
....or....
_custID = dsOrders.Tables["tblOrd"].Rows[iCurrentRow][0].ToString();
I'm confused as to why? The following line displays "System.String":
Console.WriteLine(dsOrders.Tables["tblOrd"].Rows[iCurrentRow].Table.Columns[0].DataType);
So, shouldn't that mean that I don't have to do anything else? Why do I need to use (String) or .ToString() ?
And I also can't get the DateTime into a variable. Trying
_orderDate = dsHousing.Tables["tblOrd"].Rows[iCurrentRow][1];
gives me "Cannot implicitly convert type 'object' to 'System.DateTime' ". And, again, if I display the value for Columns[1].DataType, it shows me "System.DateTime". Worse, there's no ".ToDateTime()" method of the Column object, so the only way I can get the DateTime field out is to cast it to a string, and then convert it back to a DateTime. Compared to my problem with the String column, I'm doing twice the un-necessary work just to get the DateTime column!
Is this really what's supposed to happen? If the DataType shows string, why do I need to perform an explicit conversion from object to string? It seems like I'm doing un-necessary work; if the value is stored as a certain datatype, I should be able to pull it out of the DataTable and put it into a variable of the same type.
I even toyed with the idea of .NET assuming I wanted the DataColumn being put into a String- but I can't find a property of DataColumn that just gives me the value. In classic ADO, there was a Field object, which had a Value property. I can understand "DataColumn" being an object, but is there a specific property for pulling the value out of DataColumn? For instance, if those code samples I just gave would be in classic ADO, I would be trying to say _CustID = rs.Fields(x).Value.
Oh I'm using VS 2003.NET EA, with WinXP, .NET 1.1, and have "Using System.Data" and "Using System.Data.OracleClient" at the top of my C# class definition. At first I thought this might be Oracle related, but DataSet/DataTable/DataColumn is part of the System.Data namespace.
Thanks!
-Thomas
|
|
|
|
|
Hello all:
I have an XML file I am parsing. I will need to have some sort of "signal" or event that will notify the databaseautomatically start the table insert process.
Thought A: I could pull data from the XML file and store this data in an ASP.NET data storage object. Some of the data is associated with look up tables, so I need to do some queries, therefore I am using the term processing to mean: getting data id's associated with the string values parsed from the XML file. So processed data are now id's (that are associated with the string values parsed from the XML file). After the data has been processed I want to insert the id's into the appropriate tables.
Thought B: Use some sort of DTS package or another method for the parsing and inserting of data.
Additionally: I will need to have some sort of "signal" or event that will notify the SQL Server that the file has been saved to the proper location and is ready for parsing. If I use Thought A then I can do the aforementioned programatically. I don't know how to do the aforementioned using Thought B. Any suggestions?
Here are my questions:
Thought A:
What type of ASP.NET data storage object would you use (a datatable, dataset etc.)?
How would you insert data in the appropriate database tables with ASP.NET (e.g. some type of bulk load or call a SPROC and load the dataset info)
Thought B:
Or would you skip the ASP.NET stuff, and use some sort of DTS package or another way of inserting the data? If so, any pointers on how would that would be done?
TYIA,
lonelobo
|
|
|
|
|
hello forum,
i want to store c# objects in a MS SQL database. I think the best way is to serialize the object to a byte array but i have problems to insert a c# byte array into the database...
First, the equivalent type of datum in MS SQL is the binary or varbinary type, isn´t it?
well, my code:
<br />
<br />
MyObject mo = new MyObject();<br />
MemoryStream ms= new MemoryStream();<br />
BinaryFormatter bf = new BinaryFormatter();<br />
b.Serialize(ms,mo);<br />
<br />
SqlConnection sc = new SqlConnection("server=localhost;uid=sa;pwd=;database=myDB");<br />
sc.Open();<br />
<br />
SqlDataAdapter sda = new SqlDataAdapter("select * from myTable",sc);<br />
DataSet ds = new DataSet();<br />
ad.Fill(ds,"tableFile");<br />
DataTable dt = ds.Tables["tableFile"];<br />
DataRow dr = dt.NewRow();<br />
<br />
dr["object"] = ms.GetBuffer();
dt.Rows.Add(dbRow);<br />
<br />
SqlCommandBuilder scb = new SqlCommandBuilder(sda);<br />
sda.Update(ds, "tableFile");<br />
<br />
The instruction:
dr["object"] = ms.GetBuffer();
throws the message exception "String or binary data would be truncated"
could you help me, please?
thanks in advance.
|
|
|
|
|
If the error says the string will be truncated, the size of the field you want to store it in, is to small.
|
|
|
|
|
ok. thank you. that was the problem! but ... another question:
if the maxium size of a varbinary is 8000, how could i store an object with a greater lenght (for example, a file)?
thank you. bye.
|
|
|
|
|
By using "blob" as the field's type:
http://www.winnetmag.com/SQLServer/Article/ArticleID/20461/SQLServer_20461.html
|
|
|
|
|
By using "blob" as the field's type:
http://www.winnetmag.com/SQLServer/Article/ArticleID/20461/SQLServer_20461.html
Even better, just save a reference to the file i.e. save it's path as a string in the database. If you have a file server to store your files on, just have your frontend app copy the images there and then save the path (//FILESERVER/SQLSERVERFILES/file1.jpg) in the database. Although SQL server has better performance serving up images (streamed), if a lot of people start using the app at once, the server might get stuck at just streaming files all the time. By just saving the reference, you place the task of getting the image at the client.
|
|
|
|
|
I create a frmCustomer that contains of txtCustID, txtCustFName,txtCustLName,txtCustDob and txtCustIC.... and the Add button, Search button and Exit button. When the user click on the Search button, the frmSearch will show. The user can search for the customer by select the alphabet in a combobox. The details of the customer whose last name start with that alphabet will display in the datagrid.
The datagrid in ths frmSearch contains of CustID, CustName, CustDoB. When the user double click on the specific row of the datagrid. The data in the selected row will bind to all the textbox in frmCustomers. Then, the user can do the edit or delete of the record by pressing the button in frmCustomers.
But,i am having a problem that it always get the original datasource instead of the filtered datasource when i double click on the selected row in the datagrid. Please help if you know how to solve this problem.
part of the coding is as follows:
Private Sub btnSearchName_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSearchName.Click
With Me.DataSet11.Tables("Customers")
'carrier()
With DataGrid1
.CaptionText = "Search by Last Name"
End With
If cboLastName.Text = "ALL" Then
.DefaultView.RowFilter = "CustLName like '%'"
Else
.DefaultView.RowFilter = "CustLName like '" & cboLastName.Text & "%'"
End If
If .DefaultView.Count = 0 Then
MessageBox.Show("Data not found !.", "VideoMate", MessageBoxButtons.OK, MessageBoxIcon.Information)
'Else
'MessageBox.Show("Number of data : " & .DefaultView.Count.ToString(), "VideoMate", MessageBoxButtons.OK, MessageBoxIcon.Information)
End If
If BindingContext(Me.DataSet11.Customers).Position < 0 Then
With DataGrid1
.CaptionText = "Database is empty !"
End With
End If
Me.DataGrid1.DataSource = .DefaultView
End With
End Sub
Private Sub DataGrid1_DoubleClick(ByVal sender As Object, ByVal e As System.EventArgs) Handles DataGrid1.DoubleClick
Dim CM1 As CurrencyManager = CType(Me.BindingContext(Me.DataGrid1.DataSource, Me.DataGrid1.DataMember), CurrencyManager)
If CM1.Position > -1 Then
Me._CM.Position = CM1.Position
Me.Close()
End If
End Sub
Thank you
|
|
|
|
|
It's the same output as
select *
from employee_table
-Thomas
|
|
|
|
|
First, it might be good idea to get a basic SQL book... The query you asked about will return all fields for the specified employee.
|
|
|
|
|
Actually I want to intstall multi instances of the dame database from the DTS package but with different name to each instance. How can this be ahieved for SQL server 2000?
Harmeet
|
|
|
|
|
I have a query that is as follows:
select * from contact c, contact_emails ce where ce.email = 'EMAIL' and c.password = 'PASSWORD' and ce.contact_id = c.id.
The query works but for what I need to do, I need the table names to be displayed in the header information.
Right now the header looks like this:
| id | first | last | password | id | email |
What I need is so that is looks like this:
| c.id | c.first | c.last | c.password | ce.id | ce.email |
I need to accomplish this by using the wildcard * (In other words the query cannot change).
Any ideas???
|
|
|
|
|
Can't be done, AFAIK.
If you can say a little more about:
1) what you are using the query for
2) what you are trying to accomplish
someone might be able to suggest a work-around.
Bill
|
|
|
|
|
I think the title says it all...oh except in VB.NET
Thanks in advance
David
|
|
|
|
|
Hi
I am a .net programmer writing database codes.
my qestion is:
Every time I do a project I should go to customer's office and install SQL Server on his computer , because my codes uses "sql server" as DBMS.
now...
CAN You help me to do something to avoid this?
for example a way to packaging sql server engine in my program install package?
regards
|
|
|
|
|
You can package MSDE, but I'll bet it's not legal to do it with SQL-Server as your clients needs licenses for it.
You can do it with MSDE, and just make it install in silent mode, using it's commandline switches.
- Anders
Money talks, but all mine ever says is "Goodbye!"
ShotKeeper, my Photo Album / Organizer Application[^]
My Photos[^]
New developersite: RealDevs.Net
|
|
|
|
|
Well, I'd still consider myself a newbie here. I was hoping someone would be
able to rewrite the following monster to be more efficient.
Thank You and Good Luck! 
CREATE PROCEDURE usp_Calls
@SexID char,
@RaceID char,
@LBirth datetime,
@UBirth datetime,
@SiteID varchar(5),
@DD datetime,
@CallActivity datetime
AS
declare @CAct table (CallStamp datetime, TesterID char(7), StudyNo char(15), QueStatusID varchar(2))
declare @CallFT table (CallFrom datetime, CallTo datetime, TesterID char(7))
insert @CAct
select distinct tResultQue.CallStamp, tResultQue.TesterID, tResultQue.StudyNo, tResultQue.QueStatusID
from tResultQue
where tResultQue.CallStamp >= @CallActivity
insert @CallFT
select distinct tTesterContactInfo.ContactBestFrom as CallFrom, tTesterContactInfo.ContactBestTo as CallTo, tTesterContactInfo.TesterID
from tTesterContactInfo
IF @SexID = '*' AND @RaceID = '*' AND @SiteID <> '90000'
BEGIN SELECT distinct datediff(dd, getdate(), tGroup.ScheduleStamp) * -1 as DaysPast, tResultQue.TesterID, tTester.LastName, tTester.FirstName, tTester.WorkNo, tTester.WorkExtension as WorkExt, tTester.HomeNo, tTester.SexID as Sex, tTester.Birth, tTester.RaceID as Race, Poof.CallStamp as CallActivity, Poof.StudyNo as StudyActivity, Poof.QueStatusID as Status,TCall.CallFrom, TCall.CallTo
FROM tGroup INNER JOIN tResultQue ON (tGroup.GroupID = tResultQue.GroupID) INNER JOIN tTester ON (tResultQue.TesterID = tTester.TesterID) left join @CAct as Poof on (Poof.TesterID = tTester.TesterID) left join @CallFT as TCall on (TCall.TesterID = tTester.TesterID)
WHERE tTester.Birth >= @LBirth AND tTester.Birth <= @UBirth AND tTester.SiteID = @SiteID AND tGroup.ScheduleStamp <= @DD AND tTester.TesterID NOT IN
(SELECT distinct tTester.TesterID
FROM tTester INNER JOIN tResultQue ON (tTester.TesterID = tResultQue.TesterID) INNER JOIN tGroup ON (tResultQue.GroupID = tGroup.GroupID)
WHERE tGroup.ScheduleStamp > @DD
GROUP BY tTester.TesterID) AND
tGroup.ScheduleStamp = (SELECT MAX(cor_tGroup.ScheduleStamp)
FROM tTester cor_tTester INNER JOIN tResultQue cor_tResultQue ON (cor_tTester.TesterID = cor_tResultQue.TesterID) INNER JOIN tGroup cor_tGroup ON (cor_tResultQue.GroupID = cor_tGroup.GroupID)
WHERE cor_tTester.TesterID = tTester.TesterID)
and
tResultQue.CallStamp = (SELECT MAX(cor_tResultQue.CallStamp)
FROM tTester cor_tTester INNER JOIN tResultQue cor_tResultQue ON (cor_tTester.TesterID = cor_tResultQue.TesterID) INNER JOIN tGroup cor_tGroup ON (cor_tResultQue.GroupID = cor_tGroup.GroupID)
WHERE cor_tTester.TesterID = tTester.TesterID)
order by tTester.LastName
END
IF @SexID = '*' AND @RaceID = '*' AND @SiteID = '90000'
BEGIN SELECT distinct datediff(dd, getdate(), tGroup.ScheduleStamp) * -1 as DaysPast, tResultQue.TesterID, tTester.LastName, tTester.FirstName, tTester.WorkNo, tTester.WorkExtension as WorkExt, tTester.HomeNo, tTester.SexID as Sex, tTester.Birth, tTester.RaceID as Race, Poof.CallStamp as CallActivity, Poof.StudyNo as StudyActivity, Poof.QueStatusID as Status, TCall.CallFrom, TCall.CallTo
FROM tGroup INNER JOIN tResultQue ON (tGroup.GroupID = tResultQue.GroupID) INNER JOIN tTester ON (tResultQue.TesterID = tTester.TesterID) left join @CAct as Poof on (Poof.TesterID = tTester.TesterID) left join @CallFT as TCall on (TCall.TesterID = tTester.TesterID)
WHERE tTester.Birth >= @LBirth AND tTester.Birth <= @UBirth AND tGroup.ScheduleStamp <= @DD AND tTester.TesterID NOT IN
(SELECT distinct tTester.TesterID
FROM tTester INNER JOIN tResultQue ON (tTester.TesterID = tResultQue.TesterID) INNER JOIN tGroup ON (tResultQue.GroupID = tGroup.GroupID)
WHERE tGroup.ScheduleStamp > @DD
GROUP BY tTester.TesterID)
AND
tGroup.ScheduleStamp = (SELECT MAX(cor_tGroup.ScheduleStamp)
FROM tTester cor_tTester INNER JOIN tResultQue cor_tResultQue ON (cor_tTester.TesterID = cor_tResultQue.TesterID) INNER JOIN tGroup cor_tGroup ON (cor_tResultQue.GroupID = cor_tGroup.GroupID)
WHERE cor_tTester.TesterID = tTester.TesterID)
and
tResultQue.CallStamp = (SELECT MAX(cor_tResultQue.CallStamp)
FROM tTester cor_tTester INNER JOIN tResultQue cor_tResultQue ON (cor_tTester.TesterID = cor_tResultQue.TesterID) INNER JOIN tGroup cor_tGroup ON (cor_tResultQue.GroupID = cor_tGroup.GroupID)
WHERE cor_tTester.TesterID = tTester.TesterID)
order by tTester.LastName
END
IF @SexID <> '*' AND @RaceID = '*' AND @SiteID <> '90000'
BEGIN SELECT distinct datediff(dd, getdate(), tGroup.ScheduleStamp) * -1 as DaysPast, tResultQue.TesterID, tTester.LastName, tTester.FirstName, tTester.WorkNo, tTester.WorkExtension as WorkExt, tTester.HomeNo, tTester.SexID as Sex, tTester.Birth, tTester.RaceID as Race, Poof.CallStamp as CallActivity, Poof.StudyNo as StudyActivity, Poof.QueStatusID as Status, TCall.CallFrom, TCall.CallTo
FROM tGroup INNER JOIN tResultQue ON (tGroup.GroupID = tResultQue.GroupID) INNER JOIN tTester ON (tResultQue.TesterID = tTester.TesterID) left join @CAct as Poof on (Poof.TesterID = tTester.TesterID) left join @CallFT as TCall on (TCall.TesterID = tTester.TesterID)
WHERE tTester.SexID = @SexID AND tTester.Birth >= @LBirth AND tTester.Birth <= @UBirth AND tTester.SiteID = @SiteID AND tGroup.ScheduleStamp <= @DD AND tTester.TesterID NOT IN
(SELECT distinct tTester.TesterID
FROM tTester INNER JOIN tResultQue ON (tTester.TesterID = tResultQue.TesterID) INNER JOIN tGroup ON (tResultQue.GroupID = tGroup.GroupID)
WHERE tGroup.ScheduleStamp > @DD
GROUP BY tTester.TesterID)
AND
tGroup.ScheduleStamp = (SELECT MAX(cor_tGroup.ScheduleStamp)
FROM tTester cor_tTester INNER JOIN tResultQue cor_tResultQue ON (cor_tTester.TesterID = cor_tResultQue.TesterID) INNER JOIN tGroup cor_tGroup ON (cor_tResultQue.GroupID = cor_tGroup.GroupID)
WHERE cor_tTester.TesterID = tTester.TesterID)
and
tResultQue.CallStamp = (SELECT MAX(cor_tResultQue.CallStamp)
FROM tTester cor_tTester INNER JOIN tResultQue cor_tResultQue ON (cor_tTester.TesterID = cor_tResultQue.TesterID) INNER JOIN tGroup cor_tGroup ON (cor_tResultQue.GroupID = cor_tGroup.GroupID)
WHERE cor_tTester.TesterID = tTester.TesterID)
order by tTester.LastName
END
IF @SexID <> '*' AND @RaceID = '*' AND @SiteID = '90000'
BEGIN SELECT distinct datediff(dd, getdate(), tGroup.ScheduleStamp) * -1 as DaysPast, tResultQue.TesterID, tTester.LastName, tTester.FirstName, tTester.WorkNo, tTester.WorkExtension as WorkExt, tTester.HomeNo, tTester.SexID as Sex, tTester.Birth, tTester.RaceID as Race, Poof.CallStamp as CallActivity, Poof.StudyNo as StudyActivity, Poof.QueStatusID as Status , TCall.CallFrom, TCall.CallTo
FROM tGroup INNER JOIN tResultQue ON (tGroup.GroupID = tResultQue.GroupID) INNER JOIN tTester ON (tResultQue.TesterID = tTester.TesterID) left join @CAct as Poof on (Poof.TesterID = tTester.TesterID) left join @CallFT as TCall on (TCall.TesterID = tTester.TesterID)
WHERE tTester.SexID = @SexID AND tTester.Birth >= @LBirth AND tTester.Birth <= @UBirth AND tGroup.ScheduleStamp <= @DD AND tTester.TesterID NOT IN
(SELECT distinct tTester.TesterID
FROM tTester INNER JOIN tResultQue ON (tTester.TesterID = tResultQue.TesterID) INNER JOIN tGroup ON (tResultQue.GroupID = tGroup.GroupID)
WHERE tGroup.ScheduleStamp > @DD
GROUP BY tTester.TesterID)
AND
tGroup.ScheduleStamp = (SELECT MAX(cor_tGroup.ScheduleStamp)
FROM tTester cor_tTester INNER JOIN tResultQue cor_tResultQue ON (cor_tTester.TesterID = cor_tResultQue.TesterID) INNER JOIN tGroup cor_tGroup ON (cor_tResultQue.GroupID = cor_tGroup.GroupID)
WHERE cor_tTester.TesterID = tTester.TesterID)
and
tResultQue.CallStamp = (SELECT MAX(cor_tResultQue.CallStamp)
FROM tTester cor_tTester INNER JOIN tResultQue cor_tResultQue ON (cor_tTester.TesterID = cor_tResultQue.TesterID) INNER JOIN tGroup cor_tGroup ON (cor_tResultQue.GroupID = cor_tGroup.GroupID)
WHERE cor_tTester.TesterID = tTester.TesterID)
order by tTester.LastName
END
IF @SexID <> '*' AND @RaceID <> '*' AND @SiteID <> '90000'
BEGIN SELECT distinct datediff(dd, getdate(), tGroup.ScheduleStamp) * -1 as DaysPast, tResultQue.TesterID, tTester.LastName, tTester.FirstName, tTester.WorkNo, tTester.WorkExtension as WorkExt, tTester.HomeNo, tTester.SexID as Sex, tTester.Birth, tTester.RaceID as Race, Poof.CallStamp as CallActivity, Poof.StudyNo as StudyActivity, Poof.QueStatusID as Status, TCall.CallFrom, TCall.CallTo
FROM tGroup INNER JOIN tResultQue ON (tGroup.GroupID = tResultQue.GroupID) INNER JOIN tTester ON (tResultQue.TesterID = tTester.TesterID) left join @CAct as Poof on (Poof.TesterID = tTester.TesterID) left join @CallFT as TCall on (TCall.TesterID = tTester.TesterID)
WHERE tTester.SexID = @SexID AND tTester.RaceID = @RaceID AND tTester.Birth >= @LBirth AND tTester.Birth <= @UBirth AND tTester.SiteID = @SiteID AND tGroup.ScheduleStamp <= @DD AND tTester.TesterID NOT IN
(SELECT distinct tTester.TesterID
FROM tTester INNER JOIN tResultQue ON (tTester.TesterID = tResultQue.TesterID) INNER JOIN tGroup ON (tResultQue.GroupID = tGroup.GroupID)
WHERE tGroup.ScheduleStamp > @DD
GROUP BY tTester.TesterID)
AND
tGroup.ScheduleStamp = (SELECT MAX(cor_tGroup.ScheduleStamp)
FROM tTester cor_tTester INNER JOIN tResultQue cor_tResultQue ON (cor_tTester.TesterID = cor_tResultQue.TesterID) INNER JOIN tGroup cor_tGroup ON (cor_tResultQue.GroupID = cor_tGroup.GroupID)
WHERE cor_tTester.TesterID = tTester.TesterID)
and
tResultQue.CallStamp = (SELECT MAX(cor_tResultQue.CallStamp)
FROM tTester cor_tTester INNER JOIN tResultQue cor_tResultQue ON (cor_tTester.TesterID = cor_tResultQue.TesterID) INNER JOIN tGroup cor_tGroup ON (cor_tResultQue.GroupID = cor_tGroup.GroupID)
WHERE cor_tTester.TesterID = tTester.TesterID)
order by tTester.LastName
END
IF @SexID <> '*' AND @RaceID <> '*' AND @SiteID = '90000'
BEGIN SELECT distinct datediff(dd, getdate(), tGroup.ScheduleStamp) * -1 as DaysPast, tResultQue.TesterID, tTester.LastName, tTester.FirstName, tTester.WorkNo, tTester.WorkExtension as WorkExt, tTester.HomeNo, tTester.SexID as Sex, tTester.Birth, tTester.RaceID as Race, Poof.CallStamp as CallActivity, Poof.StudyNo as StudyActivity, Poof.QueStatusID as Status, TCall.CallFrom, TCall.CallTo
FROM tGroup INNER JOIN tResultQue ON (tGroup.GroupID = tResultQue.GroupID) INNER JOIN tTester ON (tResultQue.TesterID = tTester.TesterID) left join @CAct as Poof on (Poof.TesterID = tTester.TesterID) left join @CallFT as TCall on (TCall.TesterID = tTester.TesterID)
WHERE tTester.SexID = @SexID AND tTester.RaceID = @RaceID AND tTester.Birth >= @LBirth AND tTester.Birth <= @UBirth AND tGroup.ScheduleStamp <= @DD AND tTester.TesterID NOT IN
(SELECT distinct tTester.TesterID
FROM tTester INNER JOIN tResultQue ON (tTester.TesterID = tResultQue.TesterID) INNER JOIN tGroup ON (tResultQue.GroupID = tGroup.GroupID)
WHERE tGroup.ScheduleStamp > @DD
GROUP BY tTester.TesterID)
AND
tGroup.ScheduleStamp = (SELECT MAX(cor_tGroup.ScheduleStamp)
FROM tTester cor_tTester INNER JOIN tResultQue cor_tResultQue ON (cor_tTester.TesterID = cor_tResultQue.TesterID) INNER JOIN tGroup cor_tGroup ON (cor_tResultQue.GroupID = cor_tGroup.GroupID)
WHERE cor_tTester.TesterID = tTester.TesterID)
and
tResultQue.CallStamp = (SELECT MAX(cor_tResultQue.CallStamp)
FROM tTester cor_tTester INNER JOIN tResultQue cor_tResultQue ON (cor_tTester.TesterID = cor_tResultQue.TesterID) INNER JOIN tGroup cor_tGroup ON (cor_tResultQue.GroupID = cor_tGroup.GroupID)
WHERE cor_tTester.TesterID = tTester.TesterID)
order by tTester.LastName
END
IF @SexID = '*' AND @RaceID <> '*' AND @SiteID <> '90000'
BEGIN SELECT distinct datediff(dd, getdate(), tGroup.ScheduleStamp) * -1 as DaysPast, tResultQue.TesterID, tTester.LastName, tTester.FirstName, tTester.WorkNo, tTester.WorkExtension as WorkExt, tTester.HomeNo, tTester.SexID as Sex, tTester.Birth, tTester.RaceID as Race, Poof.CallStamp as CallActivity, Poof.StudyNo as StudyActivity, Poof.QueStatusID as Status, TCall.CallFrom, TCall.CallTo
FROM tGroup INNER JOIN tResultQue ON (tGroup.GroupID = tResultQue.GroupID) INNER JOIN tTester ON (tResultQue.TesterID = tTester.TesterID) left join @CAct as Poof on (Poof.TesterID = tTester.TesterID) left join @CallFT as TCall on (TCall.TesterID = tTester.TesterID)
WHERE tTester.RaceID = @RaceID AND tTester.Birth >= @LBirth AND tTester.Birth <= @UBirth AND tTester.SiteID = @SiteID AND tGroup.ScheduleStamp <= @DD AND tTester.TesterID NOT IN
(SELECT distinct tTester.TesterID
FROM tTester INNER JOIN tResultQue ON (tTester.TesterID = tResultQue.TesterID) INNER JOIN tGroup ON (tResultQue.GroupID = tGroup.GroupID)
WHERE tGroup.ScheduleStamp > @DD
GROUP BY tTester.TesterID)
AND
tGroup.ScheduleStamp = (SELECT MAX(cor_tGroup.ScheduleStamp)
FROM tTester cor_tTester INNER JOIN tResultQue cor_tResultQue ON (cor_tTester.TesterID = cor_tResultQue.TesterID) INNER JOIN tGroup cor_tGroup ON (cor_tResultQue.GroupID = cor_tGroup.GroupID)
WHERE cor_tTester.TesterID = tTester.TesterID)
and
tResultQue.CallStamp = (SELECT MAX(cor_tResultQue.CallStamp)
FROM tTester cor_tTester INNER JOIN tResultQue cor_tResultQue ON (cor_tTester.TesterID = cor_tResultQue.TesterID) INNER JOIN tGroup cor_tGroup ON (cor_tResultQue.GroupID = cor_tGroup.GroupID)
WHERE cor_tTester.TesterID = tTester.TesterID)
order by tTester.LastName
END
IF @SexID = '*' AND @RaceID <> '*' AND @SiteID = '90000'
BEGIN SELECT distinct datediff(dd, getdate(), tGroup.ScheduleStamp) * -1 as DaysPast, tResultQue.TesterID, tTester.LastName, tTester.FirstName, tTester.WorkNo, tTester.WorkExtension as WorkExt, tTester.HomeNo, tTester.SexID as Sex, tTester.Birth, tTester.RaceID as Race, Poof.CallStamp as CallActivity, Poof.StudyNo as StudyActivity, Poof.QueStatusID as Status, TCall.CallFrom, TCall.CallTo
FROM tGroup INNER JOIN tResultQue ON (tGroup.GroupID = tResultQue.GroupID) INNER JOIN tTester ON (tResultQue.TesterID = tTester.TesterID) left join @CAct as Poof on (Poof.TesterID = tTester.TesterID) left join @CallFT as TCall on (TCall.TesterID = tTester.TesterID)
WHERE tTester.RaceID = @RaceID AND tTester.Birth >= @LBirth AND tTester.Birth <= @UBirth AND tGroup.ScheduleStamp <= @DD AND tTester.TesterID NOT IN
(SELECT distinct tTester.TesterID
FROM tTester INNER JOIN tResultQue ON (tTester.TesterID = tResultQue.TesterID) INNER JOIN tGroup ON (tResultQue.GroupID = tGroup.GroupID)
WHERE tGroup.ScheduleStamp > @DD
GROUP BY tTester.TesterID)
AND
tGroup.ScheduleStamp = (SELECT MAX(cor_tGroup.ScheduleStamp)
FROM tTester cor_tTester INNER JOIN tResultQue cor_tResultQue ON (cor_tTester.TesterID = cor_tResultQue.TesterID) INNER JOIN tGroup cor_tGroup ON (cor_tResultQue.GroupID = cor_tGroup.GroupID)
WHERE cor_tTester.TesterID = tTester.TesterID)
and
tResultQue.CallStamp = (SELECT MAX(cor_tResultQue.CallStamp)
FROM tTester cor_tTester INNER JOIN tResultQue cor_tResultQue ON (cor_tTester.TesterID = cor_tResultQue.TesterID) INNER JOIN tGroup cor_tGroup ON (cor_tResultQue.GroupID = cor_tGroup.GroupID)
WHERE cor_tTester.TesterID = tTester.TesterID)
order by tTester.LastName
END
GO
|
|
|
|
|
This looks like some sort of dynamic search procedure. Stored procedures are a particularly poor fit for this sort of thing. It is far easier to write dynamic SQL on the client.
If you absolutely have to write it in a stored procedure, then you should consider using the exec command inside the stored proc. That will let you execute dynamically constructed SQL, which would be much simpler.
By this I mean that you can dynamically build the SQL string (based on the parameters), much as you could in a normal programming language.
my blog
|
|
|
|
|
Just my 2 cents....
Sql S 2000?
I would put this part into a table valued function:
For sex I would use and in (i.e WHERE ttester.sexid in(@sexid):
IF @sexid = '*' SET @sexid = 'm,f'
That would narrow it down a bit
BEGIN SELECT distinct datediff(dd, getdate(), tGroup.ScheduleStamp) * -1 as DaysPast, tResultQue.TesterID, tTester.LastName, tTester.FirstName, tTester.WorkNo, tTester.WorkExtension as WorkExt, tTester.HomeNo, tTester.SexID as Sex, tTester.Birth, tTester.RaceID as Race, Poof.CallStamp as CallActivity, Poof.StudyNo as StudyActivity, Poof.QueStatusID as Status, TCall.CallFrom, TCall.CallTo
FROM tGroup INNER JOIN tResultQue ON (tGroup.GroupID = tResultQue.GroupID) INNER JOIN tTester ON (tResultQue.TesterID = tTester.TesterID) left join @CAct as Poof on (Poof.TesterID = tTester.TesterID) left join @CallFT as TCall on (TCall.TesterID = tTester.TesterID)
WHERE tTester.Birth >= @LBirth AND tTester.Birth <= @UBirth AND tGroup.ScheduleStamp <= @DD AND tTester.TesterID
Then I would call the function:
SELECT Distinct dayspast,etc*
From dbo.myfunction(@sexid,@vars...etc) f
WHERE not exists (
SELECT tTester.TesterID (don't need distinct or group.)
FROM tTester INNER JOIN tResultQue ON (tTester.TesterID = tResultQue.TesterID) INNER JOIN tGroup ON (tResultQue.GroupID = tGroup.GroupID)
WHERE tGroup.ScheduleStamp > @DD
and f.testerid = tTester.TesterID
)
AND
f.ScheduleStamp = (SELECT MAX(cor_tGroup.ScheduleStamp)
FROM tTester cor_tTester INNER JOIN tResultQue cor_tResultQue ON (cor_tTester.TesterID = cor_tResultQue.TesterID) INNER JOIN tGroup cor_tGroup ON (cor_tResultQue.GroupID = cor_tGroup.GroupID)
WHERE cor_tTester.TesterID = f.TesterID)
and
tResultQue.CallStamp = (SELECT MAX(cor_tResultQue.CallStamp)
FROM tTester cor_tTester INNER JOIN tResultQue cor_tResultQue ON (cor_tTester.TesterID = cor_tResultQue.TesterID) INNER JOIN tGroup cor_tGroup ON (cor_tResultQue.GroupID = cor_tGroup.GroupID)
WHERE cor_tTester.TesterID = f.TesterID)
order by f.LastName
|
|
|
|
|
To All,
I have written a database application using MS Visual C++ 6.0 and MFC. I am using the CDao classes (and using the Jet 3.5 engine/MS Access 97).
The application seems to work very well and I have many installations of the application and satisfied customers.
Now I am switching to Visual Studio .NET. I’d like to continue to use MFC and write my code using Visual C++, however, I have read that DAO is becoming obsolete and when I compile using .NET I get the ‘deprecated’ warning whenever the compiler sees a reference to DAO.
So I need to switch to a new DB technology. I liked DAO because I could directly read and write to an Access database without having to register the DB as an ODBC.
Is there any recommendation on the correct technology to use? The database/application needs to allow for multiple users from multiple computers. It is a client/server type of application and must initially read and write to an Access database. The long term plan is to go to an SQL Server so this should be taken into consideration.
Thank You
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
