Return One Table Instead Of 3??
Apr 16, 2007
Hello!
How does one make this statement return one table instead of three??
SELECT (Select Count(*) from qaTestSuite with(NOLOCK) INNER JOIN
qaTests with(NOLOCK) ON qaTestSuite.TestSuiteID = qaTests.TestSuiteID where qaTests.TestResult='P' and qaTestSuite.TestMode='r' and TestIDInternal='emailbasic') as P
select (Select Count(*) from qaTestSuite with(NOLOCK) INNER JOIN
qaTests with(NOLOCK) ON qaTestSuite.TestSuiteID = qaTests.TestSuiteID where qaTests.TestResult='W' and qaTestSuite.TestMode='r' and TestIDInternal='emailbasic') as W
select (Select Count(*) from qaTestSuite with(NOLOCK) INNER JOIN
qaTests with(NOLOCK) ON qaTestSuite.TestSuiteID = qaTests.TestSuiteID where qaTests.TestResult='F' and qaTestSuite.TestMode='r' and TestIDInternal='emailbasic') as F
--PhB
View 20 Replies
ADVERTISEMENT
Feb 13, 2008
I have two tables that share a common identity row. I need to build a query where data that exists in one table does not contain data in the other table. For example, table 1 has columns of Owner_ID, LastName, FirstName and table 2 has columns Auto_ID, Owner_ID, AutoMake. Both tables are joined by the Owner_ID column. I need a query that provides all owners from table 1 who do not have an entry in table 2.
Thanks in advance,
Mark
View 5 Replies
View Related
Apr 18, 2007
Here is the scenario,
I have 2 stored procedures, SP1 and SP2
SP1 has the following code:
declare @tmp as varchar(300)
set @tmp = 'SELECT * FROM
OPENROWSET ( ''SQLOLEDB'', ''SERVER=.;Trusted_Connection=yes'',
''SET FMTONLY OFF EXEC ' + db_name() + '..StoredProcedure'' )'
EXEC (@tmp)
SP2 has the following code:
SELECT *
FROM SP1 (which won't work because SP1 is a stored procedure. A view, a table valued function, or a temporary table must be used for this)
Views - can't use a view because they don't allow dynamic sql and the db_name() in the OPENROWSET function must be used.
Temp Tables - can't use these because it would cause a large hit on system performance due to the frequency SP2 and others like it will be used.
Functions - My last resort is to use a table valued function as shown:
FUNCTION MyFunction
( )
RETURNS @retTable
(
@Field1 int,
@Field2 varchar(50)
)
AS
BEGIN
-- the problem here is that I need to call SP1 and assign it's resulting data into the
-- @retTable variable
-- this statement is incorrect, but it's meaning is my goal
INSERT @retTableSELECT *FROM SP1
RETURN
END
View 2 Replies
View Related
Sep 14, 2015
I have a function that returns a table from a comma-delimited string.
I want to take this a step further and create a function that will return a set of tablenames in a table based on a 'group' parameter which is a simple integer...1->9, etc.Obviously, what I am doing is not working out.
CREATE FUNCTION dbo.fnReturnTablesForGroup
(
@whichgroup int
)
RETURNS @RETTAB TABLE (
TABLENAME VARCHAR(50)
[code]....
View 9 Replies
View Related
Jan 5, 2006
I have looked everywhere but cannot find the solutions. I have two tables.
Table1 = tblvacation (10 records)
fields vacation_id, land, destination
Table2 = tblimages (1000 records)
fields image_id, vacation_id, folder, name
Both are related through vacation_id. Every vacation however has many pictures. I need a query where the result would give me back all records (vacations) from Table 1 with only 1 record from Table 2(one image per vacation).
View 5 Replies
View Related
Nov 1, 2007
I have table "Clients" who have associated records in table "Mailings"
I want to populate a gridview using a single query that grabs all the info I need so that I may utilize the gridview's built in sorting.
I'm trying to return records containing the next upcoming mailing for each client.
The closest I can get is below:
I'm using GROUP BY because it allows me to return a single record for each client and the MIN part allows me to return the associated record in the mailings table for each client that contains the next upcoming 'send_date'
SELECT MIN(dbo.tbl_clients.client_last_name) AS exp_last_name, MIN(dbo.tbl_mailings.send_date) AS exp_send_date, MIN(dbo.tbl_mailings.user_id) AS exp_user_id, dbo.tbl_clients.client_id, MIN(dbo.tbl_mailings.mailing_id) AS exp_mailing_idFROM dbo.tbl_clients INNER JOIN
dbo.tbl_mailings ON dbo.tbl_clients.client_id = dbo.tbl_mailings.client_idWHERE (dbo.tbl_mailings.user_id = 1000)GROUP BY dbo.tbl_clients.client_id
The user_id set at 1000 part is what makes it rightly pull in all clients for a particular user. Problem is, by using the GROUP BY statement I'm just getting the lowest 'mailing_id' number and NOT the actual entry associated with mailing item I want to return. Same goes for the last_name field. Perhaps I need to have a subquery within my WHERE clause?Or am I barking up the wrong tree entirely..
View 7 Replies
View Related
Jun 14, 2004
Has anyone been able to use SQL to find a tables associated ID number?
For example, when I select * from syscolumns I get a list of columns in the DB and an ID number that refers to the table it belongs to. I would like a way to query the DB by the table name and return that ID.
View 4 Replies
View Related
Feb 3, 2004
In a stored procedure returning several tables how can I assign a name to each table.
I wish to select the table in my app using DataSet.Tables("MyTable") rather than DataSet.Tables(0)
Create Procedure myProc As
Select * from table1
Select * from table2
Select * from table3
Go
View 4 Replies
View Related
Feb 14, 2008
Dear all,
can i write a table return function like this?
create function my_function(
returns @MYTABLE table(NAME VARCHAR(20),SES VARCHAR(20),CNT DECIMAL(10),MYDATE DATATIME)
insert into @mytable
select col1,col2,col3,col4 from tab1
go
select col1,col2,col3,col4 from tab2
go
select col1,col2,col3,col4 from tab3
go
select col1,col2,col3,col4 from tab4
go
return
end
am i doing correct?
what i'm expecting from this function is, i need all the data from select statements should be inserted into one table.
please guide me in this regard
Vinod
Even you learn 1%, Learn it with 100% confidence.
View 3 Replies
View Related
May 17, 2006
Hi people,
i Have a small issue. I need to be able to retrive an id number of a new row to a table using the the insert into command. I was able to do this in sql 2000 but the same sql does not work now in 2005. here is the code
"Set NoCount On; select user_id from users insert into users (username) values('" & CurrentUser & "')"
This used to work in sql2000,
I am woundering if anyone could help me or point me in the right direction for doing this with SQL 2005
Best regards
RBowden
View 5 Replies
View Related
Oct 25, 2006
Hi all,
I am trying to create to a store procedure that manages to return the last 10 dates from a table i know there is a TOP keyword that return the first rows but is there a keyword that returns the last rows instead?
Matt
View 3 Replies
View Related
Apr 24, 2007
I have an incident reporting management application. People are supposed to report incidents by this application and every time some one reports an incident, they also select thier employee#(reqiured field). so how can write an sql statement that returns only the Top 5 incident reporters i.e going by employee number. Iam thinking of applying a COUNT function on the incident_id and grouping by Employee# but then how do i make sure that only the top 5 incident reporters are returned.
View 1 Replies
View Related
Jun 20, 2007
i have a procedure like below but it dosen't return any tableSELECT Hardwares.HWID, Hardwares.Title
FROM Hardwares INNER JOIN
Category ON Hardwares.CategoryID = Category.CategoryID
WHERE (Category.Type LIKE '%Hardware%') AND (Category.Title = @Title)
View 3 Replies
View Related
Dec 9, 2007
Im am trying to return the number of rows in a table and i only can get a response of true thanks for any help
View 3 Replies
View Related
May 14, 2008
I have a temporary table in a stored procedure and the table contains a single value I need to retuen from the stored procedure
How do I get the value?
View 3 Replies
View Related
Dec 30, 2003
This is a portion of my stored procedure in T-SQL
CREATE PROCEDURE GetPoll
(
@PollID INT,
@PollQuestion VARCHAR(1000) Output,
@PollOptions VARCHAR(1000) Output,
@PollPercentages DECIMAL Output
)
AS
SELECT @PollQuestion = (SELECT PollQuestion FROM Polls WHERE PollID = @PollID)
SELECT @PollOptions = (SELECT [Option] FROM PollOPtions WHERE PollID = @PollID)
*SELECT @PollPercentages = (SELECT [Option], COUNT([Option]) As Num_Votes FROM Votes WHERE PollID = 1 GROUP BY [Option])
The final part(*) of this SQL will return more than one value, so is it possible for me to return all that information in one varaible with one SELECT query?? Or would I need to use a loop and query a value at a time, and store it into a delimited string?
thx in advance...
View 3 Replies
View Related
Nov 10, 2005
For each blog entry viewed in my web app, a (IPNumber, Date, BlogID) row is inserted into table "BlogViews"
How can I fetch the BlogID which has been most often visited, that is has the most number of rows?
View 1 Replies
View Related
Jul 2, 2001
Hi, I am trying to find a way to capture all the status (Start time, execution time, Status messages etc) from executing a DTS package in to a table I will create in a database, does anyone know, where those information being kept?
When I excute the DTS package manually, a window will come up and show the status of each step within the DTS package. I am hoping to capture these information and load it to my log table.
Thanks in advance.
View 1 Replies
View Related
Dec 10, 2002
I'm getting syntax errors that just aren't helping me at all, so I thought maybe what I'm trying to do can't be done. I'm creating a UDF with 4 parameters, and I want it to return a result set (i.e. a table). But I want a different result set depending upon the value of one of the parameters. This works totally fine as a SP, but I can't tell where to put the RETURN clause(s) on the UDF.
I've got:
CREATE FUNCTION myFunction(@param1,...,@param4)
RETURNS TABLE
AS
BEGIN
IF @param1='x'
BEGIN
RETURN(SELECT columns FROM TableX)
END
ELSE
BEGIN
RETURN(SELECT columns FROM TableY)
END
END
I get an "Incorrect syntax near the keyword 'IF'" error. I also tried using just one RETURN() wrapped around the outside of the IF construction (right after the very first BEGIN and before the last END), but to no avail. I get no errors when I run this logic as an SP. Is this type of construct not allowed in a UDF? Is there an alternative? I can't just leave this as a proc because I'm going to have to call these results from several views. Help!
Thanks,
-Ed H.
View 3 Replies
View Related
Nov 25, 2004
how do i query a table and if it doesn't have a value for a field return something anyway?
e.g.
(table 1)
id title
--------
1 a
2 b
3 c
4 (null)
e.g. if null return 'not assigned'
?
View 2 Replies
View Related
Jul 24, 2012
I'm looking to pull back some results but not include those that have a value in a column that matches a value in a temp table column.
The below code returns all the values rather than excluding those that are in the temp table.
Code:
------Folder Differences-----
--Create Temp Table--
CREATE TABLE #fdiff
(foldername VARCHAR(255))
SELECT
replace(
replace(foldername,'[Template] ','')
[Code] .....
View 2 Replies
View Related
Apr 16, 2013
I have a table that lists states that I need to see order info from. I am attempting to create a SQL query that displays the production data for those states. So if the state is in my saleState table I want it to show in my query result even if there were 0 sales to that state. Below is the syntax I am trying, but if the count is 0, it is not returning the sales state it is omitting it.
Code:
Select a.state, COUNT(b.recordID)
From saleState a left outer join tblSales b
On a.state = b.state
And b.orderDate between '01/01/2012' AND '12/31/2012'
Need to point out the error in my query syntax?
View 14 Replies
View Related
Oct 1, 2015
I have a small problem with a join clause, because i need to return all values from my table BL:
my code is:
SELECT cast(0 as bit) as 'Escolha',data, contado , ollocal ,origem, ousrdata,ousrhora
FROM
(
SELECT noconta,banco, u_area
FROM BL
[code]....
In fact, i need to return 2 accounts (16,35) - x.NOCONTA IN (16,35), but I know that the problem is on the WHERE clause.How can do that, because i need all the condition on WHERE clause regarding my table OL, but also, i need to return my two accounts (16,35).
View 2 Replies
View Related
Aug 18, 2005
I have an unusual problem. I am using VB.Net 2003 and sqlexpress using .NET dataset to insert records into an timecards table. After inserting several records I tried a 'Select * from timecards' and the inserted records where not selected. if I 'select * from timecards order by employee' ( or any other field) the inserted records are selected! The table was created by an Access Upsize command.
I used Express Manager ( XM ) to try the select statements. That is how I isolated the problem. Even using a "Select * from timecards where employee = 'test' " returns the inserted test records. I found that if I use a WHERE or ORDER BY clause in the SELECT statement to .fill the .net dataset, all records are returned.
I am familiar with DB2 but I am a newbie at VB.NET and MSSQL
Any suggestions?
Thanks!
GordonG
View 1 Replies
View Related
Aug 16, 2006
Hello All,I have a scenario in which my stored procedure has to return fewvariables with their value and also the collection. Now in SQL their isno such as array, so the best is to return the table in place of.I am execting the stored procedure by having sql command in place.and created the various parameters(variables) those i need the valuesof and secondly wondering how should i be creating the parameter as atable returntype.Any help on this would be a million worth useful.I can excerpt the code if required.RegardsSandesh Kadam
View 1 Replies
View Related
Aug 31, 2006
Hi,Am new to Stored Procedures and am lost how to achieve the following. I havethis table:-CREATE TABLE [dbo].[docs] ([ID] [int] IDENTITY (1, 1) NOT NULL ,[ParentID] [int] NULL ,[Name] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,[Link] [varchar] (100) COLLATE Latin1_General_CI_AS NULL) ON [PRIMARY]GOI want to write a stored procedure to return a single column table. Thefirst field should contain the result of:SELECT ParentID WHERE ID = @id@id being the procedure input parameter.The procedure should then iterate through the table returning subsequent(single column) rows containing the result of:SELECT ParentID WHERE ID = @PreviousRowParentIDUntil NULL is returnedCan anyone help?Thanks,Jack
View 3 Replies
View Related
Jul 20, 2005
I need to access a table and return 3 values from it in the samerecordset - ie one iteration of the recordset will have 3 values fromthe same database, I have looked at sub queries but they dont seem tobe able to do what i want.I would be grateful for any guidanceS
View 1 Replies
View Related
Jul 20, 2005
Is there a way to change the Open table - Return Top... -1000 defaultto something like 10. It should return only 10 by default? Any registrykeys?
View 1 Replies
View Related
Nov 2, 2007
Is it possible in SQL 2000 to return a table variable from dynamic sql? We need to have some code that looks kind of like this:
declare @qry varchar(8000)
declare @sourcetable varchar(100) -- name of source table
declare @mytable table (ID_Num int identity(1,1), Child_Key varchar(100))
set @sourcetable = (select tablename from tblConfig where app = 1)
set @qry = 'insert into @mytable
select * from @sourcetable'
execute (@qry)
if (select count(*) from @mytable) > 0
begin
'insert into myFinalTable
select * from @mytable where blah blah blah
I can get the first part to work by declaring the table variable (mytable) in the dynamic sql, but I can't figure out how to return the table object.
View 7 Replies
View Related
Aug 9, 2007
Hello Reporting specialists ... (GregSQL, Jarret, Lisa .....)
How can i get the max value of a single specific table column ??
Is this possible ? Or am i asking the impossible ??
Hopelessly, desperatelly remaining ...
Vinnie
see also http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1979307&SiteID=1
View 1 Replies
View Related
Sep 3, 2007
Given the Patients and PatientVisits tables as per below, how do I obtain the most recent (latest) Weight and Height for each patient as per http://www.hazzsoftwaresolutions.net/selectStatement.htm
The result of the query should only return 3 rows/records,not 5. Thank you. Greg
Code Snippet
select p.ID, p.FirstName,p.LastName,DATEDIFF(year, p.DOB, getdate()) AS age
,pv.WeightPounds, pv.HeightInches
from Patients as p
inner join PatientVisits as pv
ON p.ID = PV.PatientID
order by pv.VisitDate desc
INSERT INTO Patients (ID, FirstName,LastName,DOB)
select '1234-12', 'Joe','Smith','3/1/1960'
union
select '5432-30','Bob','Jones','3/1/1960'
union
select '3232-22','Paul','White','5/12/1982'
INSERT INTO PatientVisits (PatientID, VisitDate,WeightPounds,HeightInches)
select '1234-12', '10/11/2001','180','68.5'
union
select '1234-12', '2/1/2003','185','68.7'
union
select '5432-30','11/6/2000','155','63.0'
union
select '5432-30','5/12/2001','165','63.0'
union
select '5432-30','4/5/2000','164','63.5'
union
select '3232-22','1/17/2002','220','75.0'
CREATE TABLE [dbo].[Patients](
[PID] [int] IDENTITY(1,1) NOT NULL,
[ID] [varchar](50) NULL,
[FirstName] [nvarchar](50) NULL,
[LastName] [nvarchar](50) NULL,
[DOB] [datetime] NULL,
CONSTRAINT [PK_Patients] PRIMARY KEY CLUSTERED
CREATE TABLE [dbo].[PatientVisits](
[ID] [int] IDENTITY(1,1) NOT NULL,
[PatientID] [nvarchar](50) NULL,
[VisitDate] [datetime] NULL,
[WeightPounds] [numeric](18, 0) NULL,
[HeightInches] [decimal](18, 0) NULL
) ON [PRIMARY]
View 11 Replies
View Related
Sep 20, 2006
I have a sp that will do two select from two table. now, can datareader read both table or only dataset can? if datareader can? how to handle it?
View 1 Replies
View Related
Jul 30, 2000
Help, is something wrong with my SL Server? I am unable to return any rows from all tables in all databases (user and system)on My SQL 7.0 SP2 machine. Whne i right click on the table in E.M and select design or open table i get no results. Does anyone know why this is happening? It did not always happen either.
Thanks
View 1 Replies
View Related
