I'm using AVG in a simple query but the results are being returned as INTEGERs and not the correct DOUBLE form.
AVG is being used on integer fields in a .mdf database eg: "SELECT AVG(intField) AS Results FROM myDB"
Thanks,
Geoff
the talbe row like this:
ID Name Scoe 11 Tome 20 12 Jack 30 11 Tome 40 12 Jack 10 13 John 10
My query command like this:
Select T1.Id,T1.Name,T2.mathfrom st T1right join(Select Id as Id2,Sum(Math) as Math from St group by id) T2on T1.id=t2.id2where t1.id = t2.id2
While the reuslt is :
Id Name Score
11 Tom 60
11 Tom 60
12 Jake 40
12 Jack 40
13 John 10
I am wonder :the T1 gives a table with six rows, the T2 gives a table with three rows, and I use RIGHT JOIN to connect the two table,the result should be a table with only three rows.I tried INNER JOIN, the result is same.
but why ? please help me !
Hello all:
How can I return the result of a SELECT statement from a stored procedure (function)?
CREATE FUNCTION returnAllAuthors ()
RETURNS (what do i put here??)
BEGIN
// Is this right??
RETURN SELECT * FROM authors
END
Thanks!
I'm having a bit of a trouble explaining what I'm trying to do here.
I have 3 "source" tables and a "connecting" table that I'm going to use
tblContacts - with contactID, ContactName etc
tblGroups - with GroupID, GroupName
tblSubGroups - with SubGroupID, GroupID and SubGroupName (groupID is the ID for the parent Group from tblGroups)
They are related in a table called
tblContactsGroupConnection - with ContactID, GroupID and SubGroupID
One contact can be related to many subgroups.
What I want is a list of all contacts, with their IDs, names and what groups they are related to:
ContactID, ContactName, [SubGroupName1, SubGroupName2, SubGroupName3]
ContactID, ContactName, [SubGroupName1, SubGroupName3]
ContactID, ContactName, [SubGroupName3]
I'm sure there's a simple solution to this, but I can't find it. Any help appreciated. :)
Kirikiri
Hi, I need a sql that returns the query result as comma seperated list of values, instead of several rows. Below is the scenario... Table Name - Customer Columns - CustomerID, Join DateSay below is the data of Customer table ...CustomerID JoinDate1 04/01/20052 01/03/20033 06/02/20044 01/05/20025 09/07/2005Now i want to retrieve all the customerid's who have joined this year. Below is the query that i use for this case.Select CustomerID from Customer where JoinDate between '01/01/2005' and GetDate()This gives the below result as two rows.CustomerID15But i need to get the result as '1,5' (comma seperated list of resulting values).Any help is highly appreciatedThanks in AdvanceRamesh
View 4 Replies View RelatedSELECT * FROM
( SELECT TOP 15 * FROM
(SELECT TOP 15 CMDS.STOCKCODE AS CODE,CMDS.STOCKNAME AS NAME,CMDS.Sector AS SEC, CMD7.REFERENCE AS REF,T1.HIGHP AS HIGH,
T1.LOW,T1.B1_CUM AS 'B/QTY', T1.B1_PRICE AS BUY,T1.S1_PRICE AS SELL,
T1.S1_CUM AS 'S/QTY', T1.D_PRICE AS LAST,T1.L_CUM AS LVOL,T1.Chg AS CHG,T1.Chgp AS CHGP, T1.D_CUM AS VOLUME,substring(T1.ST,7,6) AS TIME,
CMDS.SERIAL as SERIAL FROM CMD7,CMDS,CMD4 AS T1 WHERE T1.ST IN
(SELECT max(T2.ST) FROM CMD4 AS T2 ,CMDS WHERE
T1.SERIAL=T2.SERIAL
AND CMDS.SERIAL=T2.SERIAL
AND T2.sd='20060821'
AND CMDS.sd='20060821'
AND T2.L_CUM < '1900'
AND CMDS.sector >='1'
AND CMDS.sector <='47')
AND CMDS.SERIAL=T1.SERIAL AND
CMDS.SERIAL=CMD7.SERIAL AND
CMDS.sd='20060821' AND
CMD7.sd='20060821' AND
T1.sd='20060821' AND
T1.L_CUM < '1900' AND
CMDS.sector >='1' AND
CMDS.sector <='47' ORDER BY T1.D_CUM desc)
AS TBL1 ORDER BY VOLUME asc) AS TBL1 ORDER BY VOLUME desc;
I've put a SelectCommand with an aggregate function calculation and AS into a SqlDataSource and was able to display the result of the calculation in an asp:BoundField in a GridView; there was an expression after the AS (not sure what to call it) and that expression apparently took the calculation to the GridView (so far so good).
If I write the same SELECT statement in a C# code behind file, is there a way to take the aggregate function calculation and put it into a double variable? Possibly, is the expression after an AS something
that I can manipulate into a double variable? My end goal is to insert the result of the calculation into a database.
What I have so far with the SelectCommand, the SqlDataSource and the GridView is shown below in case this helps:
<asp:GridView class="gridview" ID="GridView2" runat="server" AutoGenerateColumns="False" DataSourceID="lbsgalDataSource">
<Columns>
<asp:BoundField DataField="Formulation" HeaderText="Formulation" SortExpression="Formulation" />
<asp:BoundField DataField="lbs" HeaderText="lbs" SortExpression="lbs" />
<asp:BoundField DataField="gal" HeaderText="gallons" SortExpression="gal" />
<asp:BoundField DataField="density" HeaderText="density" SortExpression="density" />
</Columns>
</asp:GridView>
<asp:SqlDataSource ID="lbsgalDataSource" runat="server"
ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
SelectCommand="SELECT
a.Formulation,
SUM (a.lbs) AS lbs,
SUM ((a.lbs)/(b.density)) AS gal,
( ( SUM (a.lbs) ) / ( SUM ((a.lbs)/(b.density)) ) ) AS density
FROM Formulations a INNER JOIN Materials b
ON a.Material=b.Material WHERE Formulation=@Formulation GROUP BY Formulation">
<selectparameters>
<asp:controlparameter controlid="DropDownList1" name="Formulation" propertyname="SelectedValue" type="String" />
</selectparameters>
</asp:SqlDataSource>
Hi,
I have now this (1 record) result:
;Admins;Sales;SalesAdmin;Other;Sales;Admins;All users;
Now, as you can see, there are some double rolenames.
I see admins and Sales 2 times between ";".
Is there a way to filter that out?
So that this will be the result:
;SalesAdmin;Other;Sales;Admins;All users;
Thanks!
How do I automatically assign a new cardcode-number? (according to the following formula: highest existing number + 1)
Scenario:
-There are two types of business partners: Customers and Suppliers.
-Customers have the value 'C' in the colomn CardType.
-Suppliers have the value 'S' in the colomn CardType.
-Customers have the following syntax 'C123456' in the colomn CardCode.
-Suppliers have the following syntax 'S123456' in the colomn CardCode.
-Existing CardCode-values in the DB for the Customers: C000001 - C100599.
-Existing CardCode-values in the DB for the Suppliers: S000001 - S200199.
The idea is that when a user creates a new business partner, the CardCode should be automatically filled when a new assigned number (highest existing number + 1), according to the value that is selected in CardType (either the letter 'C' or 'S').
What's been done so far:
SELECT top 1
(CASE
WHEN CardType='C' THEN (SELECT top 1 'C' + '' + cast((substring(T0.CardCode, 2, 7) + 1) as varchar) as [nummer]
FROM OCRD T0
WHERE T0. CardCode like 'C%' AND T0. CardType='C'
order BY T0.CardCode desc FOR BROWSE)
WHEN CardType='S' THEN (SELECT top 1 'S' + '' + cast((substring(T0.CardCode, 2, 7) + 1) as varchar) as [nummer]
FROM OCRD T0
WHERE T0. CardCode like 'S%' AND T0. CardType='S'
order BY T0.CardCode desc FOR BROWSE)
END)
FROM OCRD T0
The current result:
The result that it gives is 'C100600'.
The problem however is that it always gives this result and does not take into account what has been selected in CardType.
When I add the following: "order BY T0.CardCode desc FOR BROWSE" it gives the result 'S200200'.
So, what does work is that it takes the highest existing value and adds 1. But what doesn't work is the taking account what value is selected in CardType.
Does anyone know how I can make this work?
I am doing search through Date Range but didn't get correct result.
For Example if i created a Task on 5/8/2014 and when i have take Date Range from 1/8/2014 to 6/8/2014 then didn't get 5/8/2014 data in result but when i taken range from 1/8/2014 to 5/8/2014 then got the 5th Date data.
I am taking Range Inclusive Operator(:).
SELECT ( STRTOMEMBER(@FromDate) : STRTOMEMBER(@ToDate) )
I am using VS 2006, asp.net and C# to call a stored procedure. I want to return a value from the stored procedure into a variable in my C# code. Currently this is not working for me, and I can not figure out whatthe problem is? Can someone please help me out?
I really don't think the problem is in my stored procedure. I can right click on the stored proc and run it withsuccess. If I trace into the C# code though only a negative one (-1) is returned.
On line 5 I have tried the alternate lines of code but this has not worked for me.
mySqlCommand.Parameters["@TotalRecords"].Direction = ParameterDirection.Output; mySqlCommand.Parameters["@TotalRecords"].Direction = ParameterDirection.ReturnValue;
Can someone please help me out. I have spent to much time trying to figure this one out.
// C# code to call stored proc.1 try2 {3 SqlCommand mySqlCommand = new SqlCommand("[GetRecordsAssociatedWithRealtor]", mySqlConnection);4 mySqlCommand.Parameters.Add("@RealtorId", SqlDbType.Decimal, 10).Value = RealtorId;5 mySqlCommand.Parameters["@TotalRecords"].Direction = ParameterDirection.InputOutput;6 mySqlCommand.CommandType = CommandType.StoredProcedure;7 RecordsAssociatedWithRealtor = mySqlCommand.ExecuteNonQuery();8 }
// Stored procedure below.USE [REALTOR]GO/****** Object: StoredProcedure [dbo].[GetRecordAssociatedWithRealtor] Script Date: 10/18/2007 13:15:18 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE PROCEDURE [dbo].[GetRecordAssociatedWithRealtor]( @RealtorId int, @TotalRecords int output)AS BEGIN DECLARE @HouseDetailRecords int DECLARE @RealtorRecords int SELECT @HouseDetailRecords= RealtorId from Realtor where RealtorId=@RealtorId SELECT @RealtorRecords = RealtorId from ConstructionDetail where RealtorId=@RealtorId SET @TotalRecords=SUM(@HouseDetailRecords+@RealtorRecords) RETURN @TotalRecordsEND
I have two table one call Employee and the other table call Target Ratio. They are related via FK EmployeeID
tblEmployee
EmployeeID FirstName LastName
1 John Doe
tblTargetRatio
TargetRatioID EmployeeID EffectiveDate Ratio
1 1 1/1/2012 8
2 1 6/1/2012 5
3 1 9/1/2012 7
My question is how can I query tblTargetRatio table to return correct record for the following cases:
1 EmployeeID = 1 and Date = 03/12/2012 (Expecting Ratio = 8)
2 EmployeeID = 1 and Date = 10/10/2012 (Expecting Ratio = 7)
3 EmployeeID = 1 and Date = 08/12/2012 (Expecting Ratio = 5)
I'm using a subquery to return a delivery charge line as a column in the result set. I want to see this delivery charge only on the first line of the results for each contract. Code and results are below.
declare @start smalldatetime
declare @end smalldatetime
set @start = '2015-03-22 00:00' -- this should be a Sunday
set @end = '2015-03-28 23:59' -- this should be the following Saturday
select di.dticket [Contract], di.ddate [Delivered], di.item [Fleet_No], di.descr [Description], dd.min_chg [Delivery_Chg], dd.last_invc_date [Delivery_Invoiced],
[code]....
In this example, I only want to see the delivery charge of 125.00 for the first line of contract HU004377. For simplicity I have only shown the lines for 1 contract here, but there would normally be many different contracts with varying numbers of lines, and I only want to see the delivery charge once for each contract.
I have sql code that returns the correct number of record when run without an aggregate function like count(myfield) and group by myfield. It always returns 86 row which is correct when Select DISTINCT is used. As, expected when DISTINCT is not used I get double the number if rows or 172. But when I count(myfield) and group by myfield the count is 172 and not 86. The strangest thing about this is that when I am grouping a set of items
Group 1
Group 2
Group 3
The other group sum up correctly while others don't. What can explain this? Here is the code.
Select DISTINCT ws.p4Districtnumber, ws.cycle, ws.worksetid, count(msi.MeterSessionInputKey) as ASND
from fcs.dbo.WorkSet as ws
left outer join fcs.dbo.WorkAssignment as wa
on ws.WorkSetID = wa.WorkSetID
left outer join fcs.dbo.MeterSessionInput as msi
on wa.worksetkey = msi.worksetkey
[code]....
I am performing a series of calculations where accuracy is very important, so have a quick question about single vs double precision variables in SQL 2008.
I'm assuming that there is an easy way to cast a variable that is currently stored as a FLOAT as a DOUBLE prior to these calculations for reduced rounding errors, but I can't seem to find it.
I've tried CAST and CONVERT, but get errors when I try to convert to DOUBLE.
For example...
SELECT CAST(1.0/7.0 AS FLOAT)
SELECT CONVERT(FLOAT, 1.0/7.0)
both give the same 6 decimal place approximation, and the 6 decimals make me think this is single precision.
But I get errors if I try to change the word FLOAT to DOUBLE in either one of those commands...
SELECT CAST(1.0/7.0 AS DOUBLE)
gives "Incorrect syntax near )"
SELECT CONVERT(DOUBLE, 1.0/7.0)
gives "Incorrect syntax near ,"
Every night we connect to a remote server using Linked Server and copy details from that database to a loading table, then load it into the 'real' table in our own environment. The remove database we load it from has indexes/primary keys that match the 'real', however the 'loading' table itself does not have any indexes or primary keys, both are SQL Server 2005 machines.
In the loading table we first of all truncate it then do a select insert statement from the remote server, then we then truncate the 'real' table and load iit from the 'loading' table.
The issue is when we attempted to load it into our 'real' table from our loading table there was a duplicate row, and our process failed with a Primary Key violation.
I checked the source with does have the same primary key's in, it did not contain a duplicate row and I checked the loading table and that did contain a duplicate row.
My question this is in what circumstances this could happen ?
HI,
I ran a select * from customers where state ='va', this is the result...
(29 row(s) affected)
The following file has been saved successfully:
C:outputcustomers.rpt 10826 bytes
I choose Query select to a file
then when I tried to open the customer.rpt from the c drive I got this error message. I am not sure why this happend
invalid TLV record
Thanks for your help
Ali
As the topic suggests I need the end results to show a list of shows and their dates ordered by date DESC.
Tables I have are structured as follows:
SHOWS
showID
showTitle
SHOWACCESS
showID
remoteID
VIDEOS
videoDate
showID
SQL is as follows:
SELECT shows.showID AS showID, shows.showTitle AS showTitle,
(SELECT MAX(videos.videoFilmDate) AS vidDate FROM videos WHERE videos.showID = shows.showID)
FROM shows, showAccess
WHERE shows.showID = showAccess.showID
AND showAccess.remoteID=21
ORDER BY vidDate DESC;
I had it ordering by showTitle and it worked fine, but I need it to order by vidDate.
Can anyone shed some light on where I am going wrong?
thanks
the following is the code,....When i run this code I get "Invalid operation exception", Trying to read when no data is present, but the query returns result in query analyser...can anyone suggest what the problem is ...
Connection is open and the query is simple select statement
SqlCommand myCommand = new SqlCommand(query,myConnection);
myConnection.Open();
SqlDataReader myReader = myCommand.ExecuteReader();
Thanks
Niranch
What is the easiest way to return rows 200 through to 300 of a 500 row result set using SQL? Is there a simple way of doing this or do I need to write some Transact SQL? Any ideas would be appreciated.
Thanks
Rod
Dear all,
We are doing a query to SQL database through ODBC, to return a field named 'description'. The field has a maximum length of 1024 characters, ie, it is defined as varchar(1024). However, ODBC only return the first 255 characters. To rub more salt to the wound, when we are trying to insert a row of data into the field, ODBC actually truncated all the data after the first 255 characters, resulted in a little disaster.
I don't think we have a problem in the C programs, since the whole thing works in a little test database. (also in SQL7) I suspect there is a setting somewhere, only that the documentation doesn't give me much chance to find it. Any hints is desperately needed!!
Thanks in advance!
I am using MSSQL 2000, SQL Server Developer Edition and product version is : 8.00.760 (SP3)
Application Server: JBoss v 4.0.4
Driver : sql 2005 driver.
Hi,
I am running my application in JBOSS and hibernate to do all the query to the DB. While we are testing our application, it is working fine for the 1st day of stress testing, however after running for one days, it starts throwing the following exception. This can occur in a number of method call with no fixed pattern. Anyone has any idea what is going on?
used by: org.hibernate.exception.GenericJDBCException: could not execute query
at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:103)
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:91)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.loader.Loader.doList(Loader.java:2148)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2029)
at org.hibernate.loader.Loader.list(Loader.java:2024)
at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:392)
at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:333)
at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:172)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1114)
at org.hibernate.impl.QueryImpl.list(QueryImpl.java:79)
at org.hibernate.impl.AbstractQueryImpl.uniqueResult(AbstractQueryImpl.java:756)
at org.hibernate.ejb.QueryImpl.getSingleResult(QueryImpl.java:63)
... 88 more
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The statement did not return a result set.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PreparedStatementExecutionRequest.executeStatement(Unknown Source)
at com.microsoft.sqlserver.jdbc.CancelableRequest.execute(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeRequest(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(Unknown Source)
at org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:236)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:186)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1669)
at org.hibernate.loader.Loader.doQuery(Loader.java:662)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:224)
at org.hibernate.loader.Loader.doList(Loader.java:2145)
... 97 more
Hi, I'm trying to return 2 different result sets using the below query mapped to 2 different variables in my execute sql task. I've tried this with one task and 2 seperate tasks but can't get it to work. Is this possible using only one task? It keeps giving me a result set error.
SELECT COUNT(*) AS DeceasedCount
FROM AMGR_User_Fields_Tbl
WHERE (Client_Id = '' or Client_Id is NULL) and Type_Id = 53
SELECT COUNT(*) AS LostCount
FROM AMGR_User_Fields_Tbl
WHERE (Client_Id = '' or Client_Id is NULL) and Type_Id = 469
My desired output is:
abc - 2
def - 2
ghi - 2
jkl - 2
As you can see my query returns all the values from both tables instead of combining them. This is SQL Server 2008
Create Table #1 (blah varchar(100), cnt int)
Insert Into #1 Values
('abc', 1)
,('def', 1)
,('ghi', 1)
,('jkl', 1)
[Code] ....
I know how to write a stored procedure that does various things to a database. I know that a stored procedure returns a value of 0 by default if it executes successfully, and a non-zero value otherwise. I know you can use output variables to return other values from a stored procedure. I am moderately familiar with these things.
But... how do I fashion it if I'm calling the stored procedure from VB.NET in a web application, and I don't just want a couple of variable values, I want the whole result set?
I know there's #temporaryTables that ...exist within the scope of the stored procedures... ##Globaltemptables... regular_tables... and @tableVariables. I'm reading furiously to figure out what these things...all...do... and I'm leaning in the direction of ... a variable table as an output variable, but I just don't know... how and what I can stuff that into in the front end so I can shove it into the nice and neat grid view thing.
(fyi, I'm trying to return a consolidated table of available rooms fitting the user's specified reservation dates and amenity preferences - that part, I've gotten done like a boss. It's...getting it back to the front end I'm struggling with.)
Hello guys,
MS SQL server 2000 behavies strange with big queries that involves relatively large number of tables:
If I just enumerate columns I want to receive, the result set is empty. Adding * to the column list without making any change to where clause seems to solve the problem but I guess it's not the best practice.
The most amazing is that this behavior is not stable, so I suppose there's something to deal with server itself, not the application.
Has anybody suffered this problem and what solution was adopted?
Thanks for any information you can provide.
I have a form that has many checkboxes (more than 40) that provide information about companies.
For example. Company ABC
Checkbox 1 (Windows XP)
Checkbox 2 (Windows Vista)
Checkbox 3 (Windows NT4)
etc.
I save these in a SQL table like the following
Row 1 - Col 1 (CompanyID), col2 (checkbox value)
Row 2 - Col 1 (CompanyID), col2 (checkbox value)
etc.. so a comapany can have multiple checkbox selected..
I am currently developing a report where the end user can select one or many of the checkbox to see if the company exist.
This is where I get stuck - I only want to return the results of the companies that meet the report selections. So if I want to see all the companies that current have Windows NT and Windows Vista...how would I build that querry based on the data model above.
I have tried using the IN clause - however that use the OR connector and doesnt show ONLY the compaines that meet. I have tried using a UNION and INTERSECT, and running the select statement many times.. (SELECT CompanyID from xx WHERE CheckboxVal = xx)
INTERSECT
(SELECT CompanyID from xx WHERE CheckboxVal = xx)
etc.. howevert this creates a very large quesry that is unable to be handled by SQL 2005. I get a error message asking to minimize the query.
Any suggestions on this please...this is my 4th day working on this..
I excute sp_columns in my Stored Procedure script to get the data type of a table column.
EXEC sp_columns @table_name = 'XXX', @column_name='YYY'
How do i store the column 'TYPE_NAME' in the return row into a variable so that i can use it later in my stored procedure?
Thanks
Hannah
Hi all, I have a strange situation.
I use VBA to call a MSSQL server 2000 to get data from one of the DB inside, using ADODB.
My problem is, the DB is around 20GB, and if we get data from it for few days, say 7 days, is fine. When I come to trying get more data like 1 month, the excel returns no data after waiting for about 30 sec.
The query is actually calling a stored procedure on the server side to return data.
I have add connectiontimeout=10000 to my connection string but still not working...
I run the same execute statement in query analyzer on the same machine using the Excel and it is fine. Around 2 minute waiting I can see my data. But in Excel, never.
Anyone can offer some helps to me? Thanks alot.
Hi,
I am trying to find a way to return the result of an EXEC(*sqlstring*) from a function. I can return the tsql but not the result of an execute.
This is my function:
ALTER FUNCTION [dbo].[ReturnPickItemValue]
(
-- Add the parameters for the function here
@TypeID int,
@CaseID int
)
RETURNS varchar(max)
AS
BEGIN
-- Declare the return variable here
DECLARE @RTN varchar(max)
IF(SELECT IncludeDates FROM TBL_LU_PICK WHERE PickTypeID = @TypeID) = 1
BEGIN
SET @RTN = 'SELECT PickItem I +
CASE D.IsStartDateEstimated
WHEN 0 THEN CAST(StartDate as varchar)
ELSE CAST(dbo.ReturnEstimatedDate(D.IsStartDateEstimated, 0) as varchar)
END +
CASE D.IsEndDateEstimated
WHEN 0 THEN CAST(EndDate as varchar)
ELSE CAST(dbo.ReturnEstimatedDate(D.IsEndDateEstimated, 1) as varchar)
END
FROM TBL_LU_PICK L
INNER JOIN TBL_Pick_Items I ON I.PickTypeID = L.PickTypeID
INNER JOIN TBL_PICK P ON P.PickItemID = I.PickItemID
LEFT JOIN TBL_PickDates D ON D.PickID = P.PickID
WHERE L.PickTypeID = ' + CAST(@TypeID as varchar) + '
AND P.CaseID = ' + CAST(@CaseID as varchar)
END
ELSE
BEGIN
SET @RTN=
'SELECT I.PickItem
FROM TBL_LU_PICK L
INNER JOIN TBL_Pick_Items I ON I.PickTypeID = L.PickTypeID
INNER JOIN TBL_Pick P ON P.PickItemID = I.PickItemID
WHERE L.PickTypeID = ' + CAST(@TypeID as varchar) + '
AND CaseID = ' + CAST(@CaseID as varchar)
END
RETURN @RTN
END
Each time I try " RETURN EXEC(@RTN) " or something similar I get an error.
I have tried executing the tsql and assigning the result to a varchar and returning that varchar but i get an error.
Anyone with any ideas?
I have a table with records like that.
Group | Value
Team 1 | 0
Team 1 | 0
Team 1 | 1
Team 1 | 1
Team 2 | 0
Team 2 | 0
Team 2 | 0
I want a script that return 0 if all the values of the group are 0 and return 1 if the records of the value is mixed with 0 and 1.
I'm trying to migrate to the 2005 JDBC driver against SQL Server 2000, but I'm getting the following exception: com.microsoft.sqlserver.jdbc.SQLServerException: The statement did not return a result set. This is similar problem to a past thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=413019&SiteID=1
The difference is that my stored procedure uses cursors. This same stored procedure works fine with the 2000 JDBC driver.
Does anybody have any ideas how to resolve this problem?
ListingLink
ID1 ID2 Active Date
9212007-10-22 17:47:31.230
9712007-10-22 17:47:32.137
9612007-10-22 17:47:32.540
9812007-10-22 17:47:33.010
3912007-10-22 18:31:15.980
I have this table of Data which will Link to topics together, Problem is I need a Query that will produce 5 Rows if the Input is ID 9. Or One Row if the ID was 2.
Not Conditional just example inputs and outputs.
This will only give one side of the Results
SELECT Listing.sTitle,Listing.iListingID
FROM Listing
INNER JOIN ListingLink
ON listingLink.ID2 = listing.ilistingID
WHERE ListingLink.bIsActive = 1 AND ListingLink.ID1 =9 OR ListingLink.ID2 = 9