Exact Grouping Functionality?
Nov 26, 2007
In one of my packages, I am trying to create functionality that is similar to the fuzzy grouping transformation in that it takes a set of records and returns a subset of unique records. However, rather than fuzzy-matching columns which creates significant overhead, I just want to compare columns exactly, similar to how the lookup transformation works. At this point, data has already been fuzzy unique/duplicate seperated, this step is to only keep one record for each exactly similar dupe for an alias table.
At the moment, I am doing this (with lack of scalability foresight) with a fuzzy grouping transformation with all columns set to exact except for one which is set to fuzzy with a minimum similarity of 0.99. While this worked during development when I was testing with a small subset of data, it is unreasonable for the ~5mil records of production data it needs to operate on.
I am sure there is an efficient way to do this. I have thought about using a lookup transformation or combinations thereof, but I have not come to a definite solution yet.
Does anyone have any experience doing something similar to what I am trying or have any suggestions/ideas/questions/etc? Any help will be greatly appreciated.
View 9 Replies
ADVERTISEMENT
Oct 22, 2007
Hi i have always used views in my code to group common functionality in my sql expressions and then i can simply call these views in my data access layer by saing:
SqlCommand cmd = new SqlCommand("SELECT * FROM vw_Documents WHERE CategoryID = @CategoryID", cn);
However my view has become so complicated that i had to convert it to a stored procedure called sp_Documents. The problem now though is that is that i wish to do queries against the data returned but i can't simply say:
SqlCommand cmd = new SqlCommand("SELECT * FROM sp_Documents WHERE CategoryID = @CategoryID", cn);
The only way i can see to do it is to create a stored procedure for every single senario i have passing in the appropriate values as parameters. This seems a pretty messy solution to me because i would have repeated logic in all my stored procedures. Therefore i was wondering if there's a simpler way for me to do this or am i just being lazy :).
Appreciate if someone could help,
View 2 Replies
View Related
Nov 26, 2007
I'm really stumped on this one. I'm a self taught SQL guy, so there is probobly something I'm overlooking.
I'm trying to get information like this in to a report:
WO#
-WO Line #
--(Details)
--Work Order Line Detail #1
--Work Order Line Detail #2
--Work Order Line Detail #3
--Work Order Line Detail #etc
--(Parts)
--Work Order Line Parts #1
--Work Order Line Parts #2
--Work Order Line Detail #etc
WO#
-WO Line #
--(Details)
--Work Order Line Detail #1
--Work Order Line Detail #2
--Work Order Line Detail #3
--Work Order Line Detail #etc
--(Parts)
--Work Order Line Parts #1
--Work Order Line Parts #2
--Work Order Line Parts #etc
I'm unable to get the grouping right on this. Since the line details and line parts both are children of the line #, how do you do "parallel groups"?
There are 4 tables:
Work Order Header
Work Order Line
Work Order Line Details
Work Order Line Requisitions
The Header has a unique PK.
The Line uses the Header and a Line # as foreign keys that together are unique.
The Detail and requisition tables use the header and line #'s in addition to their own line number foreign keys. My queries ends up looking like this:
WO WOL WOLR WOLD
226952 10000 10000 10000
226952 10000 10000 20000
226952 10000 10000 30000
226952 10000 10000 40000
226952 10000 20000 10000
226952 10000 20000 20000
226952 10000 20000 30000
226952 10000 20000 40000
399999 10000 NULL 10000
375654 10000 10000 NULL
etc
Hierarchy:
WO > WOL > WOLD
WO > WOL > WOLR
It probobly isn't best practice, but I'm kinda new so I need some guidance. I'd really appreciate any help! Here's my query:
SELECT [Work Order Header].No_ AS WO_No, [Work Order Line].[Line No_] AS WOL_No,
[Work Order Requisition].[Line No_] AS WOLR_No, [Work Order Line Detail].[Line No_] AS WOLD_No
FROM [Work Order Header] LEFT OUTER JOIN
[Work Order Line] ON [Work Order Header].No_ = [Work Order Line].[Work Order No_] LEFT OUTER JOIN
[Work Order Line Detail] ON [Work Order Line].[Work Order No_] = [Work Order Line Detail].[Work Order No_] AND
[Work Order Line].[Line No_] = [Work Order Line Detail].[Work Order Line No_] LEFT OUTER JOIN
[Work Order Requisition] ON [Work Order Line].[Work Order No_] = [Work Order Requisition].[Work Order No_] AND
[Work Order Line].[Line No_] = [Work Order Requisition].[Work Order Line No_]
View 1 Replies
View Related
May 8, 2008
I think this simple. First I would like to know how to get the exact date (today's date)?
View 20 Replies
View Related
Feb 9, 2006
can any help me why the same exact code works in vb.net but not in C# . case is very simple
I have a table called MenuItems which has menu items and a table sizeandprice which has price for each menuitem based on the size. simple case of 1 to many relationship between menuitems table to sizeandprice table. I am trying to display in a gridview control couple of fields from menuitems table and have another template field in which i am display price and size field from the child table which is sizeand price. so basically this is how my code looks like
Page in vb works fine
<%@ Page Language="VB" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server">
Protected Sub GridView1_RowDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs)
If e.Row.RowType = DataControlRowType.DataRow Then
Dim ds As SqlDataSource = CType(e.Row.FindControl("SqlDataSource2"), SqlDataSource)
ds.SelectParameters("fkMenuItemID").DefaultValue = GridView1.DataKeys(e.Row.RowIndex).Value
End If
End Sub
</script>
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:PPQ_DataConnectionString1 %>" SelectCommand="SELECT [MenuItemID], [MenuItemType], [ItemName] FROM [MenuItems]"></asp:SqlDataSource>
<br />
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="MenuItemID"
DataSourceID="SqlDataSource1" OnRowDataBound="GridView1_RowDataBound">
<Columns>
<asp:BoundField DataField="MenuItemID" HeaderText="MenuItemID" InsertVisible="False"
ReadOnly="True" SortExpression="MenuItemID" />
<asp:BoundField DataField="MenuItemType" HeaderText="MenuItemType" SortExpression="MenuItemType" />
<asp:BoundField DataField="ItemName" HeaderText="ItemName" SortExpression="ItemName" />
<asp:TemplateField HeaderText="Size And Price">
<ItemTemplate>
<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:PPQ_DataConnectionString1 %>"
SelectCommand="SELECT [ItemSize], [ItemPrice] FROM [SizeAndPrice] WHERE ([fkMenuItemID] = @fkMenuItemID)">
<SelectParameters>
<asp:Parameter Name="fkMenuItemID" Type="Int32" />
</SelectParameters>
</asp:SqlDataSource>
<asp:Repeater ID="Repeater1" runat="server" DataSourceID="SqlDataSource2">
<ItemTemplate>
<%#Eval("ItemSize")%>: <%#Eval("ItemPrice", "$ {0:F2}")%><br />
</ItemTemplate>
</asp:Repeater>
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
</div>
</form>
</body>
</html>
if you would notice that in order populate my repeater with childrows i need to know the menuitemid from the parent row which i do in rowdatabound event however same code in C# does not render any values for my item price and size defined inside the repeater control. here is how the c# page looks like
<%@ Page Language="C#" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server">
protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.DataRow)
{
SqlDataSource source2 = e.Row.FindControl("SqlDataSource2") as SqlDataSource;
source2.SelectParameters["fkMenuItemID"].DefaultValue = GridView1.DataKeys[e.Row.RowIndex].Value as string;
}
}
</script>
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:PPQ_DataConnectionString1 %>"
SelectCommand="SELECT [MenuItemID], [MenuItemType], [ItemName] FROM [MenuItems]">
</asp:SqlDataSource>
</div>
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="MenuItemID"
DataSourceID="SqlDataSource1" OnRowDataBound="GridView1_RowDataBound" >
<Columns>
<asp:BoundField DataField="MenuItemID" HeaderText="MenuItemID" InsertVisible="False"
ReadOnly="True" SortExpression="MenuItemID" />
<asp:BoundField DataField="MenuItemType" HeaderText="MenuItemType" SortExpression="MenuItemType" />
<asp:BoundField DataField="ItemName" HeaderText="ItemName" SortExpression="ItemName" />
<asp:TemplateField HeaderText="Size And Price">
<ItemTemplate>
<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:PPQ_DataConnectionString1 %>"
SelectCommand="SELECT [ItemSize], [ItemPrice] FROM [SizeAndPrice] WHERE ([fkMenuItemID] = @fkMenuItemID)">
<SelectParameters>
<asp:Parameter Name="fkMenuItemID" Type="Int32" />
</SelectParameters>
</asp:SqlDataSource>
<asp:Repeater ID="Repeater1" runat="server" DataSourceID="SqlDataSource2">
<ItemTemplate>
<%#Eval("ItemSize")%>: <%#Eval("ItemPrice", "$ {0:F2}")%><br />
</ItemTemplate>
</asp:Repeater>
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
</form>
</body>
</html>
can anyone tell me what i am doing wrong?
View 1 Replies
View Related
Oct 18, 2005
I'm currently working on a match function to compare two char based columns in differnet tables to create a join.
In this particular case, I'm using a few different approaches to create a higher match ratio, as typos do happen.
For instance I use a join function using convert(char(10), tbla.field) = convert(char(10), tblb.field) to match only using the first 10 characters, as a lot of records have different endings, but are in fact the same.
Are there any other ways I could attempt to make matches? I was wondering if there was a dedicated string comparison operation giving me a percentage feedback. Debut joining dbut would give an 80% match, and thus I would leave it up to the user to decide to minimum match requirements.
Thanks in advance
View 1 Replies
View Related
Jan 20, 2004
OK, this is a simple question for you, but i am a newbie to SQL2000, and i need to find out which Service Pack we are currently running. Could anybody please give me a short reply :)
Thanks
Chris
View 2 Replies
View Related
Apr 8, 2015
I've a table with values that have the '-' char, ex:LO-AS-1.I'm trying to search the table for that value but it returns more records since it breaks the search term into several others:
SELECT * FROM sys.dm_fts_parser ('"LO-AS-1"', 2070, 0, 0) returns:
lo-as-1
lo
as - noise word
1 - noise word
nn1 - noise word
Is there anyway of searching for the exact word? With LIKE it works fine but it's very slow....
View 2 Replies
View Related
Mar 24, 2008
Hi.
I have some contracts and i want to find for every specific contract (every contract can be repeated 2-3 or more times (same policynumber) the contracts that have the same date. That's the contracts INSIDE the contract that have the same data.
I was trying something like
select distinct m.masterpolicyno from multicontract m
where m.category=3
and m.issuedate = m.issuedate
but it think i must use "having" and "in" but i can't figure it out.
Any help?
Thanks.
View 19 Replies
View Related
Aug 27, 2007
For a long running stored procedure, how can I determine the SQL statement within the stored procedure that is currently running?
The Activity Monitor only shows the name of the stored procedure and whether it is a SELECT/INSERT/UPDATE, never the complete statement.
Thank you.
View 5 Replies
View Related
Oct 17, 2007
Hello All,
I'm looking for a TSQL function or technique that when provided a date time as string and also a format, it must return a valid tsql datetime object/variable.
for example somethig like
GetDateTime('20071010', 'YYYYMMDD') or GetDateTime('10/10/2008', 'MM/DD/YYYY')
Essentially I want to get away from Implicit conversion from string to datetime. I want better control over this to ensure we dont run into any issues when our code runs on systems configured to be on various cultures.
I can even live with a few fixed formats and not being able to support arbitrary formats.
Any help/ideas/advise will be appreciated.
Regards,Avinash
View 3 Replies
View Related
Jan 6, 2006
Hi all,
i have an insert function using stored procedure with parameters. At some point while assigning parameters i make a casting problem but all i get is:
String was not recognized as a valid Boolean.
how can i find the exact line causing the error?
thank you
-shane
View 6 Replies
View Related
Aug 23, 2012
I am not sure how to go about doing this. I have a record that has a start time of 1 am and a stop time of 9pm (same day for simplicity) and I want to know how many hours during a peek time and how many were not.
For example, the application starts and inserts into the data base the start time of 1am and then the user stops the app at 9pm. Lets say the peek hours are 1pm to 7pm. I know i can do a date diff function to get how long the app ran for but how can I get the amount of hours it ran during the peek time? I know there has to be some mathematical solution to this but it is escaping me at the moment. I want to do this over many records so a a cte or pivot table is the end solution for performance.
View 11 Replies
View Related
Apr 15, 2008
How to search a string from the given values.
i want to search a string "Session" from the given column of results..
it is separator by comma.
i want only 2 results from the given value...
if i'm writing as like keyword it will return 4 but i need only the exact match of string..
_______________________
The Result should be
Session,Study
Patterns, session, asp.net
_______________________
But the Result is coming as
Session study, usercontrol
Session, study
Technical Session, Asp.net
Patterns, session, asp.net
________________________
anyone tell the solution
books catalog, education, best books
Birthday, Party Gopi
Session study, usercontrol
Session, study
Holiday
Technical Session, Asp.net
Patterns, session, asp.net
day, party
events for Lords, daily thing
events manager
events things
meeting, administrator
marriage
project ,event, demo
madurai ,event
demo, event calendar
rangoli, event
Demo Project
event project
View 2 Replies
View Related
Oct 9, 2007
I want to create an exact copy of a system running SQL 2000.
I want to copy MDF and LDF, but I cannot bring server down cause it works 24x7. What can I do?
View 4 Replies
View Related
Mar 15, 2008
Dear All
can any one help me to determine the exact size of existing row contain data type : varchar ?
thanks
View 5 Replies
View Related
Apr 6, 2007
Hi can anybody give me a simple example to check exact match word using sql sever
Ex: my word is like this : welcome to sql sever2005
Now i want to find server how it is possible?
I tried like this
select * from test where content like %server% . but is gives me possible result. But i dont want like this if i have an exact match with that key word then only i want possitive result.
Actually there is no word with server, i'm having only server2005 . So how can I get it?
Thank you
View 3 Replies
View Related
Apr 28, 2006
I need to know the exact upper limit of the Integer Data Type in SQL Server 2005Thanks
View 3 Replies
View Related
Aug 13, 2004
Hi,
This is strange....
I am getting my source data from another system am storing the SaleAmount of each product in a field the data type of which is [decimal](12, 2).
For some products I am getting an exact match (upto 2 decimal places) as compared with my source data BUT for some other products the value before the decimal places is correct but the 2 digits after the decimal place does not match with the source data :confused:
Even if this sounds stupid, can you please guide me. Am i missing some very basic and common sense thing?
Many TIA.
View 3 Replies
View Related
Oct 22, 2004
I want to be sure that the SP3a ghas been applied
View 2 Replies
View Related
Feb 20, 2012
I have to find the number of orders sent to two different postal codes but to get all of the right columns, I have to use 2 different tables.
So, I used WHERE to connect the two tables with the primary keys, but where do I use the specific criteria for the exact postal codes?
View 1 Replies
View Related
Jun 7, 2007
I have a report that displays many duplicate rows where all fields in several rows are exact duplicates. I would like to know how to make it so that the entire row does not show up if it is an exact duplicate of another row. Any ideas? Thanks.
View 8 Replies
View Related
Jan 23, 2008
In my SSIS package I have a text file source that I am mapping to a destination table. I have an error component that logs any row level errors and have noticed that it is not logging the correct field. I know this because I have a few different sources that submit the same files and have looked at the source of both. THE ONLY DIFFERENCE in the one that works versus the one that does not is that 2 of the 25+ columns are switched. I would not think this would matter because field A in the text file is mapped to field A in the database.
Does the order in which the fields come into the SSIS package matter?
Thanks
View 5 Replies
View Related
Nov 7, 2007
I have a very basic Lookup in my SSIS package that looks up against two columns and outputs a row to a table. Now currently if there is no exact match, it writes a null in my destination table. How do I simply drop all those rows that dont produce an exact match? I tried using the 'Ignore' error output, but with that it writes NULLS into my destination table. With the 'Redirect' it is looking for a place to redirect the error (NULL) rows, and I dont want to deal with the hassle or writing these NULL values to a file or table just to delete them afterwards. I just simply want to forget about all those rows that dont produce an exact hit and only fill in the destination table with those that do produce a hit. How can I drop these lookup rows that dont produce an exact match?
View 5 Replies
View Related
Oct 3, 2007
Hi,
I have the fields like this.
Session1 Session2 Session3 Session4----------------------------------------------------------------------------------------- SQL Server-Part1 SQL Server-Part2 ASP.Net CSS
C# SQL Server-Part3 ASP.Net Javascript
I have set the Full-Text to all the columns. For searching i wrote the below query
SELECT * FROM <TABLE NAME> WHERE CONTAINS(*,'"SQL Server-Part1"')
My Result expectation is: The First Record should come. But the result of the query bring the two records. It see the "SQL Server" string also. I need to find the exact word. How to do it? Please answer me as soon as possible. Ganesh.
View 6 Replies
View Related
Aug 1, 2007
Could anyone help of how to match the exact characters in a data field in SQL 2005 Developer.
For example, if one has a password "GooD", then when he or she enters "GOOD", "good", etc, the database will not match the password. And he or she must enter the exact characters, which is "GooD".
Thanks.
View 2 Replies
View Related
Aug 1, 2007
Could anyone help of how to match the exact characters in a data field in SQL 2005 Developer.
For example, if one has a password "GooD", then when he or she enters "GOOD", "good", etc, the database will not match the password. And he or she must enter the exact characters, which is "GooD".
Thanks.
View 3 Replies
View Related
Aug 6, 2015
I am required to find or check that a specific word exists in string or not.
Suppose I have to find the word 'st' than I need the result true if and only if the following occurrences are there.
1. 'St is valid;' -> true
2. 'DOB is valid;ST is invalid;' -> true
3. 'DOB is valid; ST is invalid;' -> true
4. 'DOB is valid;invalid ST;' -> true
5. 'DOB is valid; invalid ST;' -> true
6. 'DOB is valid; invalid STate;' -> false
Means the exact ST should be search. It is not free text search.
T-SQL is needed to be used in select statement with case using PATINDEX, RegEx or any suitable t-sql command.
View 10 Replies
View Related
Nov 1, 2007
I need to set a variable to datetime and time to exact milliseconds in SQL server in stored procedure.
Example:
set MyUniqueNumber = 20071101190708733
ie. MyUniqueNumber contains yyyymmddhhminsecms
Please help, i tried the following:
1. SELECT CURRENT_TIMESTAMP; ////// shows up with - & : , I want single string as in above example.2.
select cast(datepart(YYYY,getdate()) as varchar(4))+cast(datepart(mm,getdate()) as char(2))+convert(varchar(2),datepart(dd,getdate()),101 )+cast(datepart(hh,getdate()) as char(2))+cast(datepart(mi,getdate()) as char(2))+cast(datepart(ss,getdate()) as char(2))+cast(datepart(ms,getdate()) as char(4))
This one doesnot display day correctly, it should show 01 but shows 1
View 2 Replies
View Related
Apr 15, 2004
Does anyone know what is the Equivalent of Top in Oracle.
SELECT TOP 2 from TableNAME --> SQL SERVER.
What is the equivalent of this in Oracle
View 2 Replies
View Related
Nov 5, 2006
nevermind. please ignore this. theres too much extra data that id have to provide, but it would confuse the question so much that it couldnt be answered properly.
View 4 Replies
View Related
Nov 28, 2007
Hi All,
We are evaluating DB mirroring for our production box. But we have some queries with the operating mode available for mirroring.
1) I set the database miroring operation mode to full safety mode which is with synchronous. And Then executed a command to insert data in bulk in principal, the changes were commited to principal even when they are still getting mirrored to mirror server.
But as per the mirroring documentation from microsoft says this
"If transaction safety (or just 'safety') is set to FULL, the principal and mirror servers operate in a synchronous transfer mode. As the principal server hardens its principal database log records to disk, it also sends them to the mirror. The principal then waits for a response from the mirror server. The mirror responds when it has hardened those same log records to the mirror's log disk."
Is this a bug or I am doing anything wrong.
2) Mirroring documentation also says that when the transaction is on full safety mode, and if mirror and witness goes down then principal goes to read only mode. But again I saw deviation from the documentatin because when I turned mirror and witness off, principal went to "In recovery" mode.
Please help incase anybdy have faced this before or can help me if I am interpreting it wrong.
Thanks
Sachin
View 4 Replies
View Related
Jun 2, 2008
HI,
I am working on a Family tree portal which need tree functionality to display family members in tree structure. on click on any node the adding option should be displayed
for this i need a table and procedure to complete family tree
Thanks
@mbi
View 5 Replies
View Related
