The Above is my database, I need help in retrieving the X_Co and the Y_Co using values of rcv_A, rcv_B and rcv_C to compare with the Mean_A, Mean_B, Mean_C. The values of rcv_A, rcv_B and rcv_C are instances of values that are not exact of the mean columns , and we want is to compare it against our database and retrieve the row that is the closest to the rcv_A, rcv_B and rcv_C.
Here is an example of what i need. Let's say my rcv_A = 71, rcv_B = 73 and rcv_C = 70.8, so the row with mean value closest would be row 1, followed by row 2, then row 3.
So the result i hope to retrieve is in order of the closest value and i only need the X_Co and Y_Co.
This is what i want
I receive the input file with some 100 columns and some 20k+ rows and I want to check the incoming input row is existed in the database or not based on 2 key columns. If the row is existed then I need to check all the columns (nearly 100 columns) values in input and the database are equal or not. If both are equal I need to treat them seperately if not there is a seperate logic. How Can I do that check for each row and for each column?
Basically the algorithm is like this, if the input file row is not existed in the database then treat that as new row else if the input row is existed in the database then check all the columns are equal or not. If all the columns are equal then treat that as existing row and do nothing else if some columns are not equal then treat this row seperately.
I found some thing to achieve the above thing. 1. Take the input row and check in the database. 2. If the row is not found in the database then treat it as new row. 3. If row is found in the database then a) Take the source row and prepare a concatenated string for all the columns b) Take the database row and prepare a concatenated string for all the columns c) Find out the hash code for the 2 strings and then compare hash codes for equal.
The disadvantage of this is running a loop 2*m*n times where m is the number of rows and n is the number of columns. It should be done 2 times for input file row and database row.
Can anybody suggest a good method to do this?
What does the function "GetHashCode" for InputBuffer in method "Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)" will do? Will it generates hash code based on all the columns values?
Hi!Can anybody help me please? I don't know what is wrong with the underline row... Dim UtilizadorID As String = HttpContext.Current.User.Identity.Name.ToString Dim ProducaoSource As New SqlDataSource() ProducaoSource.ConnectionString = ConfigurationManager.ConnectionStrings("seaempiresdbConnectionString1").ToString() ProducaoSource.SelectCommandType = SqlDataSourceCommandType.Text ProducaoSource.SelectCommand = "SELECT * FROM Producao WHERE ProdUserID = UtilizadorID" TotalLinhas = ProducaoSource.Rows.Count() If TotalLinhas = 0 Then What I want is to determine if the user is already in a particular DataBase.Thanks in advance.
Hello, I have this Access 2K query that I need to re-create in MS SQLServer 2000, so I'm using the Query Analyzer to test it.One of the Access fields stores the home phone number. In the Accessquery, if the phone number is null, it fills it up with zeroes"000000000." If the phone has an input mask, it only gets the 9 numbers(area code included) and if the phone number's good (all numbers) thenit leaves it alone. That Access query is using immediate ifs toaccomplish that task.Does anyone have any idea how to copy this behavior into SQL Server2000? I've using the CASE statement but so far my code is not correct.I get stuck in the input mask. This is the Access code:HomePhone:IIf(IsNull([HomePhone]),"0000000000",IIf(Left([HomePhone],1)="(",Right(Left([Homephone],4),3)& Right(Left([Homephone],9),3) & Right([HomePhone],4),[HomePhone]))Thanks for all your help.JR.
How to count the number of values that exist in a row based on the values from an array of numbers. Basically the the array of numbers I want to look for are in row 1 of table [test 1] and I want to search for them and count the "out of" in table [test 2]. Excuse me for not using the easiest way to convey my question below. I guess in short I have 10 numbers and like to find how many of those numbers exist in each row. short example:
SELECT DISTINCT patient.patientID, patientFirstName, patientLastName, patientDOB, patientGender, completed_date FROM patient LEFT JOIN patient_record ON patient_record.patientID = patient.patientID WHERE (sub_categoryID = 4 OR patient_record.allocated = 4) AND (patient_status = 1 OR patient_status = 2 OR patient_status = 5) GROUP BY patient.patientID, patientFirstName, patientLastName, patientDOB, patientGender, completed_date
This brings up duplicate records, my aim is to bring distinct records, now if I take out the other returned fields after patientID and using the following sql:
SELECT DISTINCT patient.patientID FROM patient LEFT JOIN patient_record ON patient_record.patientID = patient.patientID WHERE (sub_categoryID = 4 OR patient_record.allocated = 4) AND (patient_status = 1 OR patient_status = 2 OR patient_status = 5) GROUP BY patient.patientID
This bring up distinct results, but I need to retrieve the other fields from the database i.e. patientFirstName and patientLastName
Hello,Currently we are in the process of implementing a sql server database where couple tables will have millions of rows ( about 98 millions and will grow) and a web site that will retrieve and sort the data ( read only). How asp.net gridview and sqldatareader act situation like that? Will it be a very slow response? Is there any alternative? Is there any example on the net? Assuming tables are well tuned and well indexed. Thank you in advance.
I have a simple setup: 2 tables and a joining table, and want to retrieve a data set showing every possible combination of table A and table B together with whether that combination actually exists in the joining table or not.
My tables:
channels ====== channel_id channel_name
items ==== item_id item_name
channels_items (joining table) =========== channel_id item_id created
An example of the dataset I want (assuming 2 items and 2 channels, with itemA not being in channelB):
"select * from sample order by newid()" im getting a set of rows. On refreshing this query i need the same set of rows to validate.(provided sufficient data in the table).
Question in review today is Creating a Report showing the FName, LName of all Employees not Specified in a region; I would assume "No Value to be Null" Correct?
Or is there another way for me to do this?
This is what I have so far...What am I missing that this is not showing me results?
Select Firstname, Lastname, Region From Employees WHERE Region LIKE 'null%';
Hi - I'm new to SSRS/SQL and have a situation I can't figure out. I've tried a number of things, but I'm not sure the best way to return a calculated column based on the structure of my tables.
I have a primary Detail table that tracks product testing results. The Detail records need to be calculated against a "Year" table that contains a set of values by year. This table contains a single record for each year. There isn't a direct key that joins these two tables.
The Detail table looks like: Record ID: 9999 PO Number: 12345 Date: 12/1/2007 Test Result: 1.52
The Year table looks like: Record ID: 1 Start Date: 10/6/2007 End Date: 6/14/2008 LowVal1: 1.0 HighVal1: 1.49 Incentive1: .05 LowVal2: 1.50 HighVal2: 1.59 Incentive2: .06 and so on...
The Detail records need to find the correct Year record based on the Detail date, find the correct value within the LowVal - HighVal range based on the Test Result value, and then multiply the Test Result by the correct Incentive value and return that value.
I was able to find the correct LowVal-HighVal range using an Expression in the report column. Now that I need multiple years, I can't figure out the best way to configure the query.
Any suggestions would be appreciated. Thanks in advance!
I created one stored proc, then copied it to create another similar stored proc with just some filtering changes in the second. Now I want to obtain the results from both like this:
ProjFee ProjGross DailyRunRate Var1 InHouse1 InHouse2 GrossGoal Group Name PostedAmount --------------------------------------------------------------------------------------------------------------------------- Row# 1 from current stored proc Row # 2 from called stored proc
so something like this I should get in the end for example when both results are combined:
The first stored proc (CurrentMonthCollections_AZ) attempts to include the results of the second stored proc at the end (CurrentMonthCollections_IL) by calling it and inserting the fields into a temp table.
I was told by Angel to instead convert the second stored proc into a UDF...but having trouble with this.
What is the best approach to get the desired 2 rows back that I need in that fasion?
Here is the code for the 2 stored procs (very long so I will post as as links):
Look at the end of CurrentMonthCollections_AZ.txt to see where I'm stuck in trying to select the results (ProjFee ProjGross DailyRunRate Var1 InHouse1 InHouse2 GrossGoal Var1 PostedAmount ) from both stored procs.
I don't think UNION is what I want because it will combine stuff...I just want 2 separated rows
Hi, can someone plz give me an idea of how to proceed with this... I've got a server side cursor that retrieves a list of customers from a sql server table which match a specific criteria (those who have had orders in the yr 2000) For each customer, I need to retrieve a list of 5 top items purchased along with dollars spent on each item by that customer. I've tried to do this in a loop that uses a counter but probably my syntax was off and I'm not sure that this is better than a correlated join? Can someone show me a temlate of the appropriate syntax to use for this operation I appreciate suggestions, thanks again.
OK here's my question. I want to retrieve from my database employee table all those employees with the name eg. Smith and display them in a list. Can anyone give me any pointers please. I'm using VB 2005 Express Edition. So far this is what I have but it only seems to return 1 row when I know there are more than one entries with the name I am inputting
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim searchString As String
searchString = Me.SearchStringBox.Text
Try
Dim filter As String
filter = "LastName LIKE '" & searchString & "'"
Dim search() As System.Data.DataRow
search = myCDDataSEt.ClientData.Select(filter)
If search.Length > 0 Then
'no code as yet
Else
MessageBox.Show("The client " & searchString & "is not in the database")
I am trying to build an Sql page hit provider. I am having trouble getting a count back from the database. If I use ExecuteScalar it doesn't see any value in the returned R1C1. If I use ExecuteNonQuery with a @ReturnValue, the return value parameter value is always zero. Ideally I would like to use a dynamic stored proceudre if there are any suggestions for using them with C#. My table has rvPathName, userName and a date. I have the AddWebPageHit method working so I know data connection and sql support code in provider is working. I think the problem is either in how I am writing the stored procedures or how I am trying to retrieve the data in C#. Any help with this will be greatly appreciated.
Please excuse me if this question has been asked before; I couldn’t find it. Perhaps someone could point me to the solution. A few years ago I wrote an order-entry application in classic ASP that I am now re-writing in ASP.NET 2.0. I am having very good success however I can’t figure out how to retrieve data from a stored procedure. I am using the FormView & SqlDataSource controls with a stored procedure to insert items into an order. Every time an item is inserted into the order the stored procedure does all kinds of business logic to figure out if the order qualifies for pricing promotions, free shipping, etc. If something happens that the user needs to know about the stored procedure returns a message with this line (last line in the SP) SELECT @MessageCode AS MessageCode, @MessageText AS MessageText I need to retrieve both the @MessageCode and the @MessageText values in my application. In classic ASP I retrieved these values by executing the SP with a MyRecordset.Open() and accessing them as normal fields in the recordset. In ASP.NET I have specified the SP as the InsertCommand for the SqlDataSource control. I have supplied all the parameters required by my SP and told the SqlDataSource that the insert command is a “StoredProcedure�. Everything actuly works just fine. The items are added to the order and the business logic in the SP works fine. I just have no way of telling the user that something important has happened. Does anyone know how I can pickup these values so that I can show them to my users? Bassicly if @MessageCode <> 0 I want to show @MessageText on the screen.
I'm generating a list of parameters needed by stored procedures, and I'd like to know which ones have default values assigned to them. To retrieve the parameter information I use:
sp_sproc_columns @Procedure_Name='InsertUser''
However, the column that is supposed to give the default value, 'COLUMN_DEF' always returns as NULL, even when that column has a default value assigned to it. i.e. CREATE PROCEDURE InsertUser@UserID INT = 10,.....
And then if I do a sp_sproc_columns @Procedure_Name='InsertUser'', the COLUMN_DEF value for the @UserID column is still NULL.
Does anyone know what I'm doing wrong and how I can retrieve the default value?
( select min(cast(f.valuestr as float)) from bvfindings f where f.ObjectName = 'Hematocrit' and f.sessionid = s.sessionid and f.ValueTime > s.open_time)
the above returns me the 10.9
i modified the query
select min(cast(f.valuestr as float)), f.valuetime from bvfindings f where f.ObjectName = 'Hb'
Is it possible to retrieve the value of a subreport's field or control from the parent report? I'm doing some grouping in the subreport and need to retrieve the group by's data value from the subreport.
Also, is there a way to repeat the main page's body when subreport has a page break? ie you page break on some thing in the subreport and need the body and head of the parent report to repeat on subsequent pages.
I get 100 rows of result, i want to write a sql query similar to the one available in MySql database where in i can specify the starting row and number of rows of records i want,
something like,
select * from emp LIMIT 10,20
I want all the records from the 10th row to the 20th row.
This would be helpful for me to do paging in the front end , where is i can navigate to the next previous buttons and fetch the corresponding records.
I want to do something like in google, previous next screens.
So I am trying to limit the number of rows fetched from a query.
somethin like,
select * from emp where startRowNum=10 and NoOfRecords = 20
Hi allI hv made a stored procedure which printsvarious messages using Print statement(shown in bold)------------------------------------------------------------------------------ .....if (@current_date<@ed) and (@current_date>@sd) begin print 'Date Lies Between Boundary Limits' select * from membership where uid=@uid end else begin if(@pipe=1) begin if(@plan_id=1) begin print 'Monthly Plan Activated' update membership set start_date=@opt_sd,end_date=DateAdd(M,1,@opt_sd),status=@opt,pipeline=0,user_option='',plan_id=null,download_limit=20 where uid=@uid select * from membership where uid=@uid end else begin print 'Weekly Plan Activated' update membership set start_date=@opt_sd,end_date=DateAdd(D,7,@opt_sd),status=@opt,pipeline=0,user_option='',plan_id=null,download_limit=10 where uid=@uid select * from membership where uid=@uid end end end --------------Now I want to retrieve the messages disp by these Print statements in my asp.net page where i m calling this stored proc.Pls suggest RegardsMunish
Not sure if this is the correct forum, but I 'm having problems retrieving a sqldatasource's asp:control parameter values from a selected row (during edit) in a gridview to update a record thru a stored procedure. The stored procedure is pretty intense, so I'd like to keep it in SQL if possible instead of creating the generic "update table set ..." that I see in most examples. It seems as if I can't get the propertyname right or something because it keeps giving me a "Procedure or function XX has too many arguments specified error". Maybe the DataKeyNames is not right?? I've tried just passing one parameter (ProductID-same as DataKeyNames) using "SelectedValue" as propertyname and still get the same. It's got to be something very simple, but I'm at a loss. All parameters are spelled the same in the sp (with an added "@" at start) as in the asp:controlparameters. Here's the gridview (asp.net 2.0 connecting to SQL Server 2005): <asp:GridView ID="gvLoadEditProductPrices" runat="server" AutoGenerateColumns="False" AllowSorting="True" DataSourceID="SqlDataSource1" DataKeyNames="ProductID"> <Columns> <asp:CommandField ShowEditButton="True" /> <asp:BoundField DataField="ProductID" HeaderText="ProductID" HeaderStyle-BackColor="white" InsertVisible="False" ReadOnly="True" SortExpression="ProductID" /> <asp:BoundField DataField="Product" HeaderText="Product" SortExpression="Product" ReadOnly="True" /> <asp:BoundField DataField="ProductCat" HeaderText="ProductCat" SortExpression="ProductCat" ReadOnly="True" /> <asp:BoundField DataField="VarRate" HeaderText="VarRate" SortExpression="VarRate" /> <asp:BoundField DataField="loadid" HeaderText="loadid" InsertVisible="False" ReadOnly="True" SortExpression="loadid" /> <asp:BoundField DataField="loadamount" HeaderText="loadamount" SortExpression="loadamount" ReadOnly="True" /> <asp:BoundField DataField="ProductCol" HeaderText="ProductCol" SortExpression="ProductCol" ReadOnly="True" /> <asp:BoundField DataField="PageID" HeaderText="PageID" SortExpression="PageID" ReadOnly="True" /> </Columns> </asp:GridView> and the sqldatasource's info: <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:MARSProductEditor %>" ProviderName="System.Data.SqlClient" SelectCommand="spGetLoadEditProductPrices" SelectCommandType="StoredProcedure" UpdateCommand="spUpdateProductPrices" UpdateCommandType="StoredProcedure" > <UpdateParameters> <asp:ControlParameter Name="ProductID" Type="Int32" ControlID="gvLoadEditProductPrices" PropertyName=SelectedDataKey.Values("ProductID")></asp:ControlParameter> <asp:ControlParameter Name="LoadID" Type="Int32" ControlID="gvLoadEditProductPrices" PropertyName=SelectedDataKey.Values("LoadID")></asp:ControlParameter> <asp:ControlParameter Name="PageID" Type="Int32" ControlID="gvLoadEditProductPrices" PropertyName=SelectedDataKey.Values("PageID")></asp:ControlParameter> <asp:ControlParameter Name="ProductCol" Type="Int32" ControlID="gvLoadEditProductPrices" PropertyName=SelectedDataKey.Values("ProductCol")></asp:ControlParameter> <asp:ControlParameter Name="NewRate" Type="Double" ControlID="gvLoadEditProductPrices" PropertyName=SelectedDataKey.Values("NewRate")></asp:ControlParameter> </UpdateParameters> <SelectParameters> <asp:ControlParameter ControlID="ddlEstLoadsPerAcre" Name="LoadID" PropertyName="SelectedValue" Type="Int32" /> <asp:ControlParameter ControlID="txtEditType" Name="PageName" PropertyName="Text" Type="String" /> </SelectParameters> </asp:SqlDataSource>
I'm trying to write a transformation script to write multiple rows from 1 input row. In SQL Server 2000 DTS I could do this by using DTSTransformStat_SkipFetch and DTSTransformStat_SkipRow. In SQL server 2005 it looks like I need to use EndOfRowset but I do not know how to use it for sure.
If this correct can someone show me a code example and if I am incorrect can someone tell me how to do this?
