Returning Top1 Row From Related Table
Dec 29, 2007
I have two tables. In tblUsers is all userdata. In table tblMoreInfo is some info on a user (0-15 records).
Now I want to select tblUsers.username and tblUsers.usercode and the matching top 1 row tblMoreInfo.schoolname (top 1 based on tblMoreInfo.createdate) IF IT EVENT EXISTS from tblMoreInfo. If no matching record exists I want the value NULL to be returned for the schoolname...
In all cases tblUsers.username must be unique (and I cant use the distinct keyword as im already using the row_number() function.
Any help?
Thanks!
View 1 Replies
ADVERTISEMENT
Mar 20, 2008
Hi thanks for looking at my question
Using sqlServer management studio 2005
My Tables are something like this:
--Table 1 "Employee"
CREATE TABLE [MyCompany].[Employee](
[EmployeeGID] [int] IDENTITY(1,1) NOT NULL,
[BranchFID] [int] NOT NULL,
[FirstName] [varchar](50) NOT NULL,
[MiddleName] [varchar](50) NOT NULL,
[LastName] [varchar](50) NOT NULL,
CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED
(
[EmployeeGID]
)
GO
ALTER TABLE [MyCompany].[Employee]
WITH CHECK ADD CONSTRAINT [FK_Employee_BranchFID]
FOREIGN KEY([BranchFID])
REFERENCES [myCompany].[Branch] ([BranchGID])
GO
ALTER TABLE [MyCompany].[Employee] CHECK CONSTRAINT [FK_Employee_BranchFID]
-- Table 2 "Branch"
CREATE TABLE [Mycompany].[Branch](
[BranchGID] [int] IDENTITY(1,1) NOT NULL,
[BranchName] [varchar](50) NOT NULL,
[City] [varchar](50) NOT NULL,
[ManagerFID] [int] NOT NULL,
CONSTRAINT [PK_Branch] PRIMARY KEY CLUSTERED
(
[BranchGID]
)
GO
ALTER TABLE [MyCompany].[Branch]
WITH CHECK ADD CONSTRAINT [FK_Branch_ManagerFID]
FOREIGN KEY([ManagerFID])
REFERENCES [MyCompany].[Employee] ([EmployeeGID])
GO
ALTER TABLE [MyCompany].[Branch]
CHECK CONSTRAINT [FK_Branch_ManagerFID]
--Foreign IDs = FID
--generated IDs = GID
Then I try a simple single row DELETE
DELETE FROM MyCompany.Employee
WHERE EmployeeGID= 39
Well this might look like a very basic error:
I get this Error after trying to delete something from Table €śEmployee€?
The DELETE statement conflicted with the
REFERENCE constraint "FK_Branch_ManagerFID".
The conflict occurred in database "MyDatabase",
table "myCompany.Branch", column 'ManagerFID'.
Yes what I€™ve been doing is to deactivate the foreign key constraint, in both tables when performing these kinds of operations, same thing if I try to delete a €śBranch€? entry, basically each entry in €śbranch€? and €śEmployee€? is child of each other which makes things more complicated.
My question is, is there a simple way to overcome this obstacle without having to deactivate the foreign key constraints every time or a good way to prevent this from happening in the first place? Is this when I have to use €śON DELETE CASCADE€? or something?
Thanks
View 8 Replies
View Related
Aug 10, 2015
I am doing a distinct count on a related table's column, but get an out of memory error if I run it for the entire table (works great for just a few rows when filtered down).The error I get is: "We couldn't get data from the external source.The operation has been cancelled because there is not enough memory available for the application. If using 32-bit version of the product consider upgrading.
I know I can add a related column and that works fine...but that seems to me like I've defeated the purpose, I have a good and proper lookup table, and should be able to run my query against its relationship.Here is the query and details below *Note I supplied a scaled down sample, on my actual model I receive these errors, not in the sample as it has only a few rows
List Workers Distinct Project Customers:=CALCULATE(DISTINCTCOUNT(Projects[CustomerID]),'WorkersToProjects')
Other measure which returns no errors, but included for completeness:
List Workers Projects:=CALCULATE(DISTINCTCOUNT([ProjectID]),ISBLANK(WorkersToProjects[ProjectID])=FALSE())
My goal here is to allow the user to view the workers assigned to a project, but also get counts of the workers assigned to the CUSTOMER of a project. For example, suppose we lose a customer, we want to see how many workers would be impacted by that, so a count of projects per worker is not useful there, we need to see a count of workers per project's customer (owner of project whom project work is being done for)The question being: How can I accomplish this:
1. WITHOUT adding a calculated column to WorkersToProjects (of Projects.CustomerID)
2. WITH better performance?
There must be a better way to write this DAX to still get the correct answer?*Pic of pivot table, again, the numbers are accurate but the formula used to List Workers Distinct Project Customers measure does NOT scale :( 3 count for red , the number of Projects John has and 2 count for blue, the unique customers/owners of those projects "Veridian Dynamics" and "Massive Dynamic". URL....
View 3 Replies
View Related
Jun 7, 2007
I am sending out an SOS.
Here is the situation:
We recently upgrade to 2005(sp). We have one report that ran fine in 2000 but leaves out data from certain columns (date related) in the results, so we chalked it up to being a non compatiable issue. So, I decided to try and switch the DB back to 2000 compatibility (in our test env) and then back to 2005. After that the report started returning the proper data. We can€™t really explain why it worked but it did. So we thought we would try it in prod (we knew it was a long shot) and it didn€™t work. So the business needs this report so we thought we would refresh the test system from prod, but now we are back to square one. I was wondering if anyone else has heard or seen anything like this. I am open to any idea€™s, no matter how crazy. J The systems are configured identically. Let me know if you need more information.
Thank you. Scott
View 4 Replies
View Related
Mar 29, 2007
I am trying to use report builder to generate a report that has multiple transaction. All I need is the most recent. normally I would use a select top1 in SQL but can not find anything in report builder that can accompish the same thing. Any ideas would be greatly appreciated
View 1 Replies
View Related
Jun 29, 2015
I need to delete records from a table (Table1) which has a foreign key column in a related table (Table2).
Table1 columns are: table1Id; Name. Table2 columns include Table2.table1Id which is the foreign key to Table1.
What is the syntax to delete records from Table1 using Table1.Name='some name' and remove any records in Table2 that have Table2.table1Id equal to Table1.table1Id?
View 11 Replies
View Related
Nov 23, 2007
Hi All,
I have 2 tables People & PeopleCosts.
PeopleID in People Table is the primarykey and foreign Key in PeopleCosts Table. PeopleID is an autonumber
The major fields in People Table are PeopleID | MajorVersion | SubVersion. I want to create a new copy of data for existing subversion (say from sub version 1 to 2) in the same table. when the new data is copied my PeopleID is getting incremented and how to copy the related data in the other table (PeopleCosts Table) with the new set of PeopleIDs..
Kindly help. thanks in advance.
Myl
View 3 Replies
View Related
Jan 5, 2008
I have the following tablestblUserdatausercode username firstname lastname5 peter peter smith11 john433 john doe15 simonsays Simon SmithtblEventsID postedbycode title eventtext createdate1 5 woodstock 'oldies' 12/12/20082 11 love parade 'dance all night 1/1/20083 11 spring break 'great party' 2/2/2006tblEventVisitorsusercode eventid5 15 311 111 211 3As you can see User John433 is going to 3 events.But I only want to select the one that has the first upcoming startdate bigger than now: getdate()Desired output would be:username firstname lastname eventid title eventtext eventdatepeter Peter Smith 1 woodstock 'oldies' 12/12/2008john433 john doe 2 love parade 'dance all night 1/1/2008simonsays Simon Smith NULL NULL NULL NULLHow can I make such a selection? (perhaps see this thread for similar info: http://forums.asp.net/t/1201266.aspx)Thanks!
View 16 Replies
View Related
Jan 4, 2008
I have table 1 from which I select some values belonging to users.In table 2 I store tips a user might give: tipid, tiptitle, tiptext,tipcreatedateA user may give more than one tip.But now I want a query that selects the info of a SINGLE user and the LATEST tip he created, so resultset might look like:username lastname sex tiptitle tiptext tipcreatedateSo even though a user might have given more tips, only the latest will be retreived...and thus 1 row for a particular user is returned...How would I construct such a query? The problem is that I want to get exactly 1 row but more than 1 column from the table 2, so I think I cannot use the SQL Server "TOP" command...
View 6 Replies
View Related
Dec 9, 2006
Hi all, What I'm trying to do and having a lot of trouble with is pulling how one user is related to another user from my database. I'll explain...
I set up a table called relationships that looks like this:
ID
type: int
RELID1
type: int, is the user id that initiated the relationship request.
RELID2
type: int, is the user id of the second person in the relationship.
Story
type: varchar(255), quick blerb on how they are related.
Type
type: int, a number 1-20 based on the relationship they have
Status
type: int, 1 = confirmed by second person, 0 = not confirmed
What i'd like to have pulled is a list of the people that person (for example: 70) is related to. I am having the two following problems:
1. unqid of "70" could be in RELID1 or RELID2 as they could have initiated the request or been the second person.
2. I don't want to display them selves in their own relationship listing
Example Data:
ID
1
2
3
4
RELID1
25
15
70
12
RELID2
54
70
13
8
Story
Met on the east coast
Met at walmart
Met walking
Met outside
Type
14
11
3
8
Status
1
1
1
1
Example Output:
Again assuming the current user is "70", the sql should pull: 15, 13.. but i'd like to pull their names from another table called "Users" where "15" and "13" are the UnqID's in a column called "ID".
So:
15 = Bob Smith
13 = Jane Doe
The following code works great that I got with the help on another form:
SELECT Relationship.RELID1
, Relationship.RELID2
, ReMembers.FirstName
, ReMembers.LastName
FROM Relationship
INNER
JOIN ReMembers
ON ReMembers.AccountID = Relationship.RELID1
WHERE Relationship.RELID2 = 70
UNION ALL
SELECT Relationship.RELID1
, Relationship.RELID2
, ReMembers.FirstName
, ReMembers.LastName
FROM Relationship
INNER
JOIN ReMembers
ON ReMembers.AccountID = Relationship.RELID2
WHERE Relationship.RELID2 = 70
My question is: What is the best way to set this table up? I'm not committed to any any design as of right now, but want to be sure I set it up in the most efficient manner.
Any feedback / opinions are welcome! :)
View 5 Replies
View Related
Nov 1, 2007
Hi,
If I have 2 tables:
TableA
aID
aCount
TableB
bID
bCount
And I need to create a 3rd table that will look like:
aID
bID
What should I call this table?
I've seen:
relAB
AXB
Any other naming conventions?
View 7 Replies
View Related
Sep 8, 2006
how can i find all the names of tables that a specific table is related to in tsql?
View 3 Replies
View Related
Jul 20, 2006
I have two tables. When my user completes an insert of data in table (1), I would like the second "related" table (2) to be automatically populated with defaults. Is this possible?
My logical approach to this is:
1. Build a handler for the OnInsert event of the first table
2. In the handler, call the Insert Command on the SQLDataSource for the second table with the defaults specified in the DataSource.
What I'm not sure how to do is Step 2 or whats the best way. How do I call the Insertcommand programmatically for a DataSource? Or, is there a better way such as some kind of traditional hardwired SQL insert statement like in classical ASP? Or is there a way to programmatically call a stored procedure and if so is the 3rd approach the best way?
How exactly would someone do this best? It seems this would be a rather common thing someone might need to do.
View 4 Replies
View Related
Aug 23, 2007
The problem of mine is, I have a datagrid, Which displays data from a Employee(parent) table.
Now I want to delete some records based on the user selected checkbox,only those records which has no related records in the EmployeeProject(child) can be deleted.I want to know which are all the record that cannot be deleted?
How can I achieve this?
View 3 Replies
View Related
Nov 18, 2005
Hi,I've got VS 05 web dev express installed and i'm trying the walkthroughs for login admin. I've succeeded and noticed the tables VS05 produces in the database ASPNETDB.MDF. I've created a new table "Customers" also with a UserID and also configured it as "UniqueIdentifyer" as VS05 has done in the table aspnet_users. The Customers table has two other fields: CustID (autoint) and CustomerName. Now i'm setting up a detailsview control that should insert a CustomerName but I also want it to insert the current logged in userID to the Customers.UserID field so that the aspnet_users and customers tables can be related. My question is how would my Sql insert statement look like to incorporate the parameter of the current logged in UserID and insert it into the Customers.UserID field?thanks.
View 5 Replies
View Related
Jan 11, 2006
Hey guys,
I have created an asp.net page where users can select multiple items and then submit the form. I would like to return related items back. The catch is, I want to only return items that are related to all of the selected items.
I've created a SQL Procedure that puts each of the inputted item's ItemId in to a temp table, I have a second table called RelatedItems which I use as my junction table that has ItemId, and ReleatedItemId, I then have my Item table that has the data I want to get to (I've excluded this because I have no trouble pulling out data once I have an ItemId)
I can pull out all related ItemIds with a simple join, however I don't know where to start when it comes to pulling out only items related to all ItemIds in the @TempTable.
Any help or suggestions would be great.
Thanks,
Matt
View 1 Replies
View Related
Jan 22, 2008
I have the following:
TicketID_1 AuditRec1
TicketID_1 AuditRec2
TicketID_1 AuditRec3
TicketID_1 AuditRec4
TicketID_1 AuditRec5
TicketID_2 AuditRec1
TicketID_2 AuditRec2
TicketID_2 AuditRec3
TicketID_2 AuditRec4
TicketID_2 AuditRec5
I need to insert a dummy row as the first row in this AuditRecord table for every occurrance of a given TicketID so that I get the following:
Dummy_Tick1 Dummy_AuditRec
TicketID_1 AuditRec1
TicketID_1 AuditRec2
TicketID_1 AuditRec3
TicketID_1 AuditRec4
TicketID_1 AuditRec5
Dummy_Tick2 Dummy_AuditRec
TicketID_2 AuditRec1
TicketID_2 AuditRec2
TicketID_2 AuditRec3
TicketID_2 AuditRec4
TicketID_2 AuditRec5
The AuditRec table is huge. How can I accomplish this using SQL?
View 1 Replies
View Related
Dec 28, 2007
Hi All,
Can anyone help me find out which system table/s are out there that hold SQL Server Job/Agent information?
Thanks!
View 3 Replies
View Related
Dec 27, 2007
I have a query/report that I need to create that needs to look at the size of a company and based on that size apply different rules. I am sure that this is not the only query/report I'll need to do using this and I'm also not so sure that the size ranges won't be changed in the future. Given this, I'd like to store the size ranges in a lookup(global) table. That way, if the ranges ever change I can just alter them in that table and not in all of the queries/reports that use them. What I need to figure out is how to join the live table with the look up table.
Specifically, here is what I have. The look up table would be:
Code Block
CREATE TABLE #gl_sizerange (
glsid int IDENTITY(1,1) NOT NULL,
lowsize int,
highsize int,
sizecat varchar(10),
milestone varchar(25),
days int
) ON [PRIMARY]
INSERT into #gl_sizerange VALUES(1, 24, 'Small', 'Approach', 14)
INSERT into #gl_sizerange VALUES(1, 24, 'Small', 'Interview', 14)
INSERT into #gl_sizerange VALUES(1, 24, 'Small', 'Demonstrate', 21)
INSERT into #gl_sizerange VALUES(1, 24, 'Small', 'Negotiate', 14)
INSERT into #gl_sizerange VALUES(1, 24, 'Small', 'Close', 7)
INSERT into #gl_sizerange VALUES(25, 99, 'Medium', 'Approach', 14)
INSERT into #gl_sizerange VALUES(25, 99, 'Medium', 'Interview', 21)
INSERT into #gl_sizerange VALUES(25, 99, 'Medium', 'Demonstrate', 21)
INSERT into #gl_sizerange VALUES(25, 99, 'Medium', 'Negotiate', 14)
INSERT into #gl_sizerange VALUES(25, 99, 'Medium', 'Close', 7)
INSERT into #gl_sizerange VALUES(100, 499, 'Large', 'Approach', 14)
INSERT into #gl_sizerange VALUES(100, 499, 'Large', 'Interview', 21)
INSERT into #gl_sizerange VALUES(100, 499, 'Large', 'Demonstrate', 21)
INSERT into #gl_sizerange VALUES(100, 499, 'Large', 'Negotiate', 14)
INSERT into #gl_sizerange VALUES(100, 499, 'Large', 'Close', 7)
INSERT into #gl_sizerange VALUES(500, 300000000, 'Super', 'Approach', 28)
INSERT into #gl_sizerange VALUES(500, 300000000, 'Super', 'Interview', 35)
INSERT into #gl_sizerange VALUES(500, 300000000, 'Super', 'Demonstrate', 28)
INSERT into #gl_sizerange VALUES(500, 300000000, 'Super', 'Negotiate', 35)
INSERT into #gl_sizerange VALUES(500, 300000000, 'Super', 'Close', 35)
Then what I have is two more tables, one that indicates the size of the company and another that has the milestone contained within it and I will also calculate how long that milestone has been open and if it is longer than what is in the lookup table for that milestone and size range I need it returned in the report. Here are some quick sample table to represent that data (I've condensed the size and number of tables for the example):
Code Block
CREATE TABLE #en_entity (
enid int NOT NULL,
orgsize int,
) ON [PRIMARY]
INSERT into #en_entity VALUES(1, 5)
INSERT into #en_entity VALUES(2, 18)
INSERT into #en_entity VALUES(3, 24)
INSERT into #en_entity VALUES(4, 25)
INSERT into #en_entity VALUES(5, 47)
INSERT into #en_entity VALUES(6, 101)
INSERT into #en_entity VALUES(7, 499)
INSERT into #en_entity VALUES(8, 500)
INSERT into #en_entity VALUES(9, 10000)
INSERT into #en_entity VALUES(10, 567890)
CREATE TABLE #op_opportunity (
opid int NOT NULL,
enid int NOT NULL,
milestone varchar(25),
daysopen int
) ON [PRIMARY]
INSERT into #op_opportunity VALUES(1, 1, 'Approach', 5)
INSERT into #op_opportunity VALUES(2, 2, 'Interview', 18)
INSERT into #op_opportunity VALUES(3, 4, 'Negotiate', 24)
INSERT into #op_opportunity VALUES(4, 7, 'Demonstrate', 25)
INSERT into #op_opportunity VALUES(5, 7, 'Approach', 7)
INSERT into #op_opportunity VALUES(6, 9, 'Close', 35)
INSERT into #op_opportunity VALUES(7, 8, 'Close', 36)
So, given the sample data, I would expect the results to return me the following opids from the #op_opportunity table because they don't comply with what is in the look up table based on milestone, size and days open: 2,3,4,7
View 4 Replies
View Related
Dec 4, 2007
Hi everyone!
I'm on my way to learn SSIS by myself and it's a little complicated!
I'd like to ask you one thing:
I have two tables at my data source, one is "Clients" and the other one is ClientsAddress. That is, a client can have more than one address. Both tables are related by a one to many relationship and the tables description is:
CLIENTS CLIENTSADDRESS
#PK_Client #PK_Client
other fields.. #ID_address
... other fields
What i intend to do is to obtain one table with approximately 3 fields, each one for a possible client address; something like this:
CLIENTS
PK_Client
ID_Address1
ID_Address2
ID_Address3
My question is what transformation can i use? an how ?
Thanks very much in advance!!
Emilio Leyes
Salta, Argentina
View 1 Replies
View Related
Nov 19, 2014
I'm using SS 2012.
I started with an inline table returning function with a hard coded input table name. This works fine, but my boss wants me to generalize the function, to give it in input table parameter. That's where I'm running into problems.
In one forum, someone suggested that an input parameter for a table is possible in 2012, and the example I saw used "sysname" as the parameter type. It didn't like that. I tried "table" for the parameter type. It didn't like that.
The other suggestion was to use dynamic sql, which I assume means I can no longer use an inline function.
This means switching to the multi-line function, which I will if I have to, but those are more tedious.
Any syntax for using the inline function to accomplish this, or am I stuck with multi-line?
A simple example of what I'm trying to do is below:
Create FUNCTION [CSH388102].[fnTest]
(
-- Add the parameters for the function here
@Source_Tbl sysname
)
RETURNS TABLE
AS
RETURN
(
select @Source_Tbl.yr from @Source_Tbl
)
Error I get is:
Msg 1087, Level 16, State 1, Procedure fnTest, Line 12
Must declare the table variable "@Source_Tbl".
If I use "table" as the parameter type, it gives me:
Msg 156, Level 15, State 1, Procedure fnTest, Line 4
Incorrect syntax near the keyword 'table'.
Msg 137, Level 15, State 2, Procedure fnTest, Line 12
Must declare the scalar variable "@Source_Tbl".
The input table can have several thousand rows.
View 9 Replies
View Related
Apr 2, 2014
I am working on a query that seems very simple, but I just cannot seem to get it correct.
I want to be able to select Only 1 MemberAddress with the MIN(MoveDate) for each of the 3 Members. The results would be this:
Joe Smith, 2000-03-10, 1034 Sturgis Road, 115, Portland, Or, 77665
Sally Jones, 2001-01-02, 8970 Pierce Road, 25, Clear Bay, Washington, 96547
Beth Moore, 2006-05-30, 456 W. Blane Ave, NULL, Charleston, West Virgina, 56897
DECLARE @Members TABLE
(
MemberRowID INT IDENTITY(1,1) NOT NULL
,FirstName VARCHAR(20)
, LastName VARCHAR(20)
[Code]....
I am not opposed to using CTE, I really like them, but I cannot figure this one out. But I will try most anything.
View 4 Replies
View Related
May 12, 2015
Here is the data:
ProjectId
Stage
StateStatus
ProjectId
ProjectName
StartDate
P1
S1
Completed
P1
P1Name
31/12/2015
[Code] ...
I want with PowerPivot to tell what stage is in progress for project. I looked at RElatedTable, LOOKUPVALUES, but I can't find a way to associate this to get working. I got however the one telling me which projects are Completed.
Result shall be:
ProjectName StageinProgess
P1Name S2
P2Name S3
P3Name None
View 4 Replies
View Related
May 14, 2008
Hi,
Not too long ago I was looking to change a primary key in a table from one column to another, the standard accepted procedure to so procedure to do so (I was told) was:
1) drop primary key constraint from old_key_column
2)add primary key constraint to new_key_column
3) drop old_key_column (optional).
In order to carry out the first step, I needed the name of the primary key constraint, at which point I asked around for a command that would return that name (and would quite probably take as a parameter the name of the column that key is related to). The answer was :
Try this:
select name 'constraintName' from sys.indexes where object_id=object_id('<tableName>') and is_primary_key=1
Unfortunately this command never worked for me. Probably because I don't have a table sys.indexes in my DB, but I can see a table sysindexes in the system tables folder, then again that table doesn't have an object_id column, but it has an id column, finally this column only contain obscur numbers.
So I am asking you all (again) for help, do you know about a command that returns every single constraint related to a column (or a table, or both).
One more thing, I am running SQL SERVER 2000, I do know that there are graphic ways to do those very operations (in the enterprise manager or the query analyzer) and I do know those ways (that's how i got past the problem mentioned earlier), I just think that knowing how to do it programmatically would be a plus.
PS: I am a total newbie to T-SQL (and a still a beginner in SQL, so please take it easy with me )
View 6 Replies
View Related
Mar 4, 2005
OK heres the situation, I have a Categories table and a Products table, each Category can have one or many Products, but a product can only belong to one Category hence one-to-many relationship.
Now I want to do a SELECT query that outputs all of the Categories onto an ASP page, but also displays how many Products are in each category eg.
CatID | Name | Description | No. Products
0001 | Cars | Blah blah blah | 5
etc etc
At the moment I'm doing nesting in my application logic so that for each category that is displayed, another query is run that returns the number of products for that particular category. It works ok!
However, is there a way to write a SQL Statement that returns all the Categories AND number products from just the one SELECT statement, rather than with the method I'm using outlined above? The reason I'm asking is that I want to be able to order by the number of products for each category and my method doesn't allow me to do this.
Many thanks!
View 3 Replies
View Related
Sep 17, 2007
In a table-valued UDF, does the UDF use a table variable or a temp table to form the resultset returned?
View 1 Replies
View Related
Apr 14, 2015
I have around 3 tables having around 20 to 30gb of data. My table A related to table B by a FK and same way table B related to table C by FK. I would like to delete all rows satisfying certain condition from table A and all corresponding related records from table B and C. I have created a query to delete the grandchild first, followed by child table and finally parent. I have used inner join in my delete query. As you all know, inner join delete operations, are going to be extremely resource Intensive especially on bigger tables.
What is the best approach to delete all these rows? There are many constraints, triggers on these tables. Also, there might be some FK relations to other tables as well.
View 3 Replies
View Related
Jun 9, 2014
I am having a problem in creating query for this exciting scenario.
Table A
ID ItemQtyCreatedDatetime
W001 CB112014-06-03 20:30:48.000
W002 CB112014-06-04 01:30:48.000
Table B
IDItemQtyCreatedDatetime
A001 CB112014-06-03 19:05:48.000
A002 CB112014-06-03 20:05:48.000
A003 CB112014-06-03 21:05:48.000
A004 CB112014-06-04 01:05:48.000
A005 CB112014-06-04 02:05:48.000
I would like to return the nearest date of Table B in my table like for
ID W001 in table B should return ID A002 CreatedDatetime: 2014-06-03 20:05:48.000
ID W002 in table B should return ID A004 CreatedDatetime: 2014-06-04 01:05:48.000
View 3 Replies
View Related
Jul 20, 2005
There's an SP that in effect returns a table -- it loops and executesstatements like SELECT @field1, @Field2, @Field2Can I capture its results in a table? I know that if it were a FUNCTIONthat returned a table, that would be simple:INSERT ResultsTable SELECT * FROM dbo.Function (@Param1, @Param2)But how to do that if the code is a procedure rather than a function?
View 1 Replies
View Related
Feb 19, 2008
Hi, I'm new to the forums, so hopefully I'm at the right place with my question...
Suppose I have a user-defined type in C#, which stands for simple point in 2D (let's call it Point). The type consists of two integer fields that represent the X and Y coordinate of the point. To make things a little more complicated, I want to store my values compressed using an algorithm, so I can't directly access X and Y without using a decompression algorithm.
I also have a table (PointTable), it's only column is called Data and is of type Point.
Now, writing and executing a simple query like
SELECT Data.ToString() FROM PointTable
is no problem at all.
What I want to do, is to be able to retrieve a table filled with the X and Y coordinates, so something like this:
SELECT PointX, PointY FROM GetTable()
where GetTable creates a table with columns PointX and PointY.
The function GetTable() should be implemented in C#, and work something like this: iterate through all Point values, decompress them, and create a new row from each Point.
I hope my intentions are clear and thanks for your help in advance!
View 8 Replies
View Related
Jul 31, 2006
I have a multi-level folders table, named folders with attributes of folder_id, parent_id and user_id, and have another table, users, contains all the user_id. I need to list all the users for each folder which has parent_id = 0 and its all sub-folders' users into one table. I have created a function to return a table with folder_id and user_id for one single folder. However, I don't know how to use this function to get the sub-folder's users and merge them together as one single table.
Here is my function:
CREATE FUNCTION [dbo].[FolderUsers] (@fid int) RETURNS Table AS Return (select folder_id, f.user_id from folders f, users u where f.user_id = u.user_id and folder_id = @fid)Go
where @fid is the top folder with parent_id = 0 at here, the next level sub-folder's parent_id would be = @fid.
I am thinking to have recursive call from the parent_id = @fid that returns another table and have to concatenate to the called table. I have been thinking of store procedure, "insert into" and so on, but don't know how to implement it.
Do you have any good inspiration for me? Thank you in advance!
View 9 Replies
View Related
Nov 16, 2005
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
create FUNCTION [dbo].[search](@art varchar,@cd varchar,@tra varchar,@gen varchar,@cdate datetime,@label varchar)
RETURNS @result TABLE(Artist varchar(100),CDTitle varchar(100),Track varchar(100),CDtype
varchar(100),CDDate datetime, Label varchar(100))
AS
BEGIN
IF @art <>'/'
INSERT INTO @result SELECT dbo.CD.CDCoverURL AS ' ', dbo.CD.CDTitle AS 'CD Title',cd.cdtype as
'Section', convert(varchar,cd.cddate,106) as 'Release Date', dbo.Label.Label,
dbo.Shelf.Shelf
FROM artist,cd,label,shelf,cdtrack,artisttrack,track
WHERE artist.artistid=artisttrack.artistid and cd.cdid=cdtrack.cdid and
track.trackid=cdtrack.trackid and label.labelid=cd.labelid and
shelf.shelfid=cd.shelfid and artisttrack.trackid=track.trackid
and artist.artist=@art
Group by dbo.CD.CDCoverURL, dbo.CD.CDTitle, dbo.CD.CDType, dbo.CD.CDDate,
dbo.Label.Label, dbo.Shelf.Shelf
if @cd <>'/'
insert into @result SELECT dbo.CD.CDCoverURL AS ' ', dbo.CD.CDTitle AS 'CD Title',cd.cdtype as 'Section', convert(varchar,cd.cddate,106) as 'Release Date', dbo.Label.Label, dbo.Shelf.Shelf
FROM artist,cd,label,shelf,cdtrack,artisttrack,track
where artist.artistid=artisttrack.artistid
and cd.cdid=cdtrack.cdid and track.trackid=cdtrack.trackid and label.labelid=cd.labelid
and shelf.shelfid=cd.shelfid and artisttrack.trackid=track.trackid and cd.cdtitle=@cd
Group by dbo.CD.CDCoverURL, dbo.CD.CDTitle, dbo.CD.CDType, dbo.CD.CDDate, dbo.Label.Label, dbo.Shelf.Shelf
if @tra <> '/'
insert into @result SELECT dbo.CD.CDCoverURL AS ' ', dbo.CD.CDTitle AS 'CD Title',cd.cdtype as 'Section', convert(varchar,cd.cddate,106) as 'Release Date', dbo.Label.Label, dbo.Shelf.Shelf
FROM artist,cd,label,shelf,cdtrack,artisttrack,track
where artist.artistid=artisttrack.artistid
and cd.cdid=cdtrack.cdid and track.trackid=cdtrack.trackid and label.labelid=cd.labelid
and shelf.shelfid=cd.shelfid and artisttrack.trackid=track.trackid and track.track=@tra
Group by dbo.CD.CDCoverURL, dbo.CD.CDTitle, dbo.CD.CDType, dbo.CD.CDDate, dbo.Label.Label, dbo.Shelf.Shelf
if @gen <>'/'
insert into @result SELECT dbo.CD.CDCoverURL AS ' ', dbo.CD.CDTitle AS 'CD Title',cd.cdtype as 'Section', convert(varchar,cd.cddate,106) as 'Release Date', dbo.Label.Label, dbo.Shelf.Shelf
FROM artist,cd,label,shelf,cdtrack,artisttrack,track
where artist.artistid=artisttrack.artistid
and cd.cdid=cdtrack.cdid and track.trackid=cdtrack.trackid and label.labelid=cd.labelid
and shelf.shelfid=cd.shelfid and artisttrack.trackid=track.trackid and cd.cdtype=@gen
Group by dbo.CD.CDCoverURL, dbo.CD.CDTitle, dbo.CD.CDType, dbo.CD.CDDate, dbo.Label.Label, dbo.Shelf.Shelf
if @cdate<>'01/01/1900'
insert into @result SELECT dbo.CD.CDCoverURL, dbo.CD.CDTitle, dbo.CD.CDType, dbo.CD.CDDate, dbo.Label.Label, dbo.Shelf.Shelf
FROM artist,cd,label,shelf,cdtrack,artisttrack,track
where artist.artistid=artisttrack.artistid
and cd.cdid=cdtrack.cdid and track.trackid=cdtrack.trackid and label.labelid=cd.labelid
and shelf.shelfid=cd.shelfid and artisttrack.trackid=track.trackid and cd.cddate=@cdate
Group by dbo.CD.CDCoverURL, dbo.CD.CDTitle, dbo.CD.CDType, dbo.CD.CDDate, dbo.Label.Label, dbo.Shelf.Shelf
if @label<>'/'
insert into @result SELECT dbo.CD.CDCoverURL, dbo.CD.CDTitle, dbo.CD.CDType, dbo.CD.CDDate, dbo.Label.Label, dbo.Shelf.Shelf
FROM artist,cd,label,shelf,cdtrack,artisttrack,track
where artist.artistid=artisttrack.artistid
and cd.cdid=cdtrack.cdid and track.trackid=cdtrack.trackid and label.labelid=cd.labelid
and shelf.shelfid=cd.shelfid and artisttrack.trackid=track.trackid and label.label=@label
Group by dbo.CD.CDCoverURL, dbo.CD.CDTitle, dbo.CD.CDType, dbo.CD.CDDate, dbo.Label.Label, dbo.Shelf.Shelf
return
end
---------------------------------------------------------------------
upon running executing this function with valid values i am not getting any results.
anything is wrong?
thank you,
View 13 Replies
View Related
May 7, 2008
Hi guys,
I have to work with some configuration data that is stored in rows as a comma separated values. Something like this:
Key1
A,1,Z,0;B,2,Y,9;C,,8,X;
Key2
Alpha,101;Beta,102;
Each group of data is separated by a semicolon and each value by a comma. The quantity of values may vary from one key to the other. Over this values sometimes I need to run some selects, so I went with the idea to get it as a table using CLR.
There I find the first problem: I didn't find a way to return a "variable" column with a CLR function, I had to create a SP. Ok, now I execute spGetConfigurationAsTable 'Key1' and I can obtain something like this:
A
1
Z
0
B
2
Y
9
C
3
X
8
But I'm faced with a second problem: How can I run a query over this? I didn't find a way to run a query over an output of a SP. And I can't INSERT the result into a temporary table because I cannot CREATE the table static (remember the columns may differ from one configuration to the other).
So, it seemed a pretty simple task and a neat solution, but I'm kinda stuck. Is there a way to run a query over the SP output? Or is there a way to have a variable table output from a CLR UDF?
Here is the code of the CLR SP I use to obtain the data, and also the (non-working) CLR user defined function.
THANKS!
Code Snippet
public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void spGetConfigurationAsTable(string Key)
{
SqlConnection conn = new SqlConnection("Context Connection=true");
string SqlCmd = string.Format("SELECT Value FROM Configuracion WHERE [Key] = '{0}' ", Key);
SqlCommand cmd = new SqlCommand(SqlCmd, conn);
conn.Open();
string Value = Convert.ToString(cmd.ExecuteScalar());
if (Value.Length > 0)
{
char SeparatorRow = ';';
char SeparatorColumn = ',';
if (Value.Split(SeparatorRow)[0].Split(SeparatorColumn).Length > 35)
return;
StringBuilder SqlCreate = new StringBuilder("DECLARE @Output TABLE (");
for (int i = 0; i < Value.Split(SeparatorRow)[0].Split(SeparatorColumn).Length; i++)
{
SqlCreate.AppendFormat("[{0}] varchar(50),", Convert.ToChar(65 + i));
}
SqlCreate.Remove(SqlCreate.Length - 1, 1);
SqlCreate.AppendLine(");");
StringBuilder SqlInsert = new StringBuilder();
foreach (string row in Value.Split(SeparatorRow))
{
if (row.Length > 0)
{
SqlInsert.Append("INSERT INTO @Output VALUES (");
// busca las diferentes "columns" ~ Charly
foreach (string column in row.Split(SeparatorColumn))
{
SqlInsert.AppendFormat("'{0}',", column);
}
SqlInsert.Remove(SqlInsert.Length - 1, 1);
SqlInsert.AppendLine(");");
}
}
string SqlSelect = "SELECT * FROM @Output;";
cmd.CommandText = SqlCreate.ToString() + SqlInsert.ToString() + SqlSelect;
SqlDataReader reader = cmd.ExecuteReader();
SqlContext.Pipe.Send(reader);
reader.Close();
reader.Dispose();
}
conn.Close();
conn.Dispose();
cmd.Dispose();
}
};
Code Snippet
public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction]
public static IEnumerable fGetConfigurationAsTable(string Key)
{
SqlConnection conn = new SqlConnection("Context Connection=true");
string SqlCmd = string.Format("SELECT Value FROM Configuracion WHERE [Key] = '{0}' ", Key);
SqlCommand cmd = new SqlCommand(SqlCmd, conn);
conn.Open();
string Value = Convert.ToString(cmd.ExecuteScalar());
conn.Close();
conn.Dispose();
cmd.Dispose();
DataTable dt = new DataTable();
if (Value.Length > 0)
{
char SeparatorRow = ';';
char SeparatorColumn = ',';
if (Value.Split(SeparatorRow)[0].Split(SeparatorColumn).Length > 35)
{
// throw exception
}
string ColumnName;
for (int i = 0; i < Value.Split(SeparatorRow)[0].Split(SeparatorColumn).Length; i++)
{
ColumnName = string.Format("[{0}] varchar(50),", Convert.ToChar(65 + i));
dt.Columns.Add(ColumnName, Type.GetType("System.String"));
}
foreach (string row in Value.Split(SeparatorRow))
{
if (row.Length > 0)
{
dt.Rows.Add(row.Split(SeparatorColumn));
}
}
}
return dt.Rows;
}
};
View 5 Replies
View Related
