Table1
ID,Description
1,"Vowels"
2,"Others"
Table2
ID,Table1ID,Letter
1,1,"A"
2,2,"L"
3,2,"K"
4,1,"U"
5,1,"E"
6,2,"G"
7,1,"I"
How can I query the two tables to come up with a resulting table of:
ID,Description,Letter
1,"Vowels","A, U, E, I"
2,"Others","L, K, G"
I have the folloiwng fields:
ProbationLengthYears AS Decimal
ProbationLengthMonths AS Decimal
ProbationLengthDays AS Decimal
Sample Data:
ProbationLengthYears = 2.00
ProbationLengthMonths 0.00
ProbationLengthDays 0.00
I have successfully concatenated and converted all three fields into one field as Follow:
SELECT
CONVERT(varchar(10),CAST(cc2.ProbationLengthYears AS INT)) + ' Year(s)' + ' ' +
CONVERT(varchar(10),CAST(cc2.ProbationLengthMonths AS INT)) + ' Month(s)' + ' ' +
CONVERT(varchar(10),CAST(cc2.ProbationLengthDays AS INT)) + ' Day(s)'
The result is:
2 Year(s) 0 Month(s) 0 Day(s)
Ideally I would like the result to be as follow because the probation months and days are 0:
2 Year(s).
I'm trying to create an email report which gives a result of multiple results from multiple databases in a table format bt I'm trying to find out if there is a simple format I can use.Here is what I've done so far but I'm having troble getting into html and also with the database column:
EXEC msdb.dbo.sp_send_dbmail
@subject
= 'Job Summary',
@profile_name =
'SQL SMTP',
[code]....
Hi!Another silly question:If a stored procedure returns multiple result sets, how do I choose the oneI want to insert into a table?For example sp_spaceused returns two result sets if object name is ommited.I only need the first result set. How do I do that?Tnx!Darko
View 2 Replies View RelatedHi,
I am aware that SSRS does not support stored procedures with multiple data sets, but what if the same stored procedure may only return one set based on a parameter?
For example, lets say if parameter @TableID = 0, the stored procedure returns 2 result sets. But if @TableID = 1, or @TableID = 2, the SP only brings back 1 result set. Is this also not supported?
When I try to do this it still only brings back the fields from the first table. Am I doing something wrong?
Thanks for any insight.
Hi,I have a table like this :TimeIDApplicationLoginState1App1login101App2login211App3login311App1login401App1login511App4login102App1login102App2login202App3login312App1login402App1login512App4login103App1login103App2login213App3login313App1login403App1login513App4login10And I want a result like thisTimeID (state=0) (state=1)1 3 32 4 23 3 3I use this code :set nocount ondeclare @timeid1 intdeclare @timeid2 intdeclare @timeid intdeclare @sessionstate intdeclare @count1 intdeclare @count2 intdeclare @count intdeclare @aux intdeclare txt_cursor cursor forselect timeid, sessionstate , count(login) from metromasterwhere sessionstate = 0group by timeid, sessionstateUNIONselect timeid, sessionstate , count(login) from metromasterwhere sessionstate = 1group by timeid, sessionstateorder by timeidopen txt_cursorselect @aux = 0fetch next from txt_cursor into @timeid, @sessionstate, @countwhile (@@fetch_status = 0)beginif @aux = 0beginselect @timeid1 = @timeidselect @count1 = @countselect @aux =1endelsebeginselect @timeid2 = @timeidselect @count2 = @countselect @aux = 2endif @aux = 2begin--select @timeid1, @count1, @sessionstate, @count2, @timeid2select @timeid1, @count1, @count2select @aux = 0endfetch next from txt_cursor into@timeid, @sessionstate, @countendclose txt_cursordeallocate txt_cursorset nocount offBut it create a lot of blank row and field header.Does anyone know an other methode ??Thanks.
View 1 Replies View RelatedHi,
do u know
has sql server 2000 support MARS?
Hello,
Is it possible to save the results of several SP calls in a script, to one file?
Here's what I mean:
I want to run these 4 sp calls--
exec EPC_SP1 'aph','live'
exec EAUI_SP2 'noble','newswire'
exec EAUI_SP3 'noble',1
exec EAUI_SP4 5507,'live'
And save the results of each one of those calls to the same file, in other words, an APPEND
Is this or something like it possible in TSQL?
Help appreciated!
Thank you,
--PhB
I have a stored procedure like the following. This returns 2 result sets, however i only want it to return 2nd (SELECT SomeField FROM SomeTable). How is this done? Note - it is not possible to change 'SomeSPThatReturnsAnIntAndAResultSet '
CREATE PROCEDURE [dbo].[SomeSP]
@SomeParam int
AS
BEGIN
SET NOCOUNT ON;
declare @SomeScalar int
exec @SomeScalar = SomeSPThatReturnsAnIntAndAResultSet @SomeParam
if @SomeScalar = 0
BEGIN
SELECT SomeField FROM SomeTable
END
END
I worked with someone else to create a query that gives us the age of a stock. How long it has been in the warehouse since the Purchase order date (without completely selling out). It does exactly what I want, the problem is that it only accepts 1 row as a result.
The error message I get is:
quote:Msg 512, Level 16, State 1, Line 4
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
So my question is; can this code be modified to pass it multiple SKUS and run a report on every item currently in stock?
SELECT TOP 1
@skuVar, CAST(GETDATE()-ReceivedOn AS INT) AS 'Age'
FROM
(SELECT pir.id AS id,aggregateQty AS aggregateQty,-qtyreceived as qtyreceived, (aggregateQty - qtyreceived) AS Diff, ReceivedOn AS ReceivedOn
,(
SELECT SUM (PurchaseItemReceive.qtyreceived)
FROM bvc_product pp
[code].....
I use Microsoft SQL 2008
hi
i read that stored procedures can return multiple result sets?how is that?
thanks in advance.
I have a criteria where i want to join table 1 with table 2 , table 1 consists of products which were given to salesman to sell and table 2 has the sales data which salesman has sold out. Now i want to know left over products of each sales with join .Below is my data, here is what i am trying to do, but it return only salesman 1 data.
CREATE TABLE Salesman_Product
(
SalesManID int,
ProductID int
)
INSERT INTO Salesman_Product (SalesManID,ProductID) Values (1,1),(1,2),(1,3),(1,4)
INSERT INTO Salesman_Product (SalesManID,ProductID) Values (2,1),(2,2),(2,3),(2,4)
[code]....
Hi guys,
I've been doing some LOCAL reports on my current application until recently there's has been a case that I really need to do SERVER reports.
Usually when I design my local reports, I create a XSD file, so I usually have one dataset with multiple tables in it. I just pass the dataset to report with a single procedure call that returns multiple result sets or data table.
From what I understood server reports are binded to database objects only, like stored procedures. Now I used the same stored procedure that I used in my local report to my server report. But the thing is only the first result set in the stored procedure is recognized. Are there anyway that I can bind the server report to a single stored procedure that return multiple result sets?
Thanks.
Seasons greetings to everyone,A simple question. Could someone show me the syntax to produce multiple (2 or 3) result sets in a stored proc and how you access those sets from a c# program (ASP.NET)..Couldn't find a reference on Google, maybe I was asking the wrong question! Thanks for any help regardsDavej
View 3 Replies View Relatedname age weightaaa 23 50bbb 23 60ccc 22 70ddd 24 20 eee 22 30i need the output that calculate the sum of weight group by name input : age limit ex: 22 - 23 output : age total weight 23 11022 100 this output must stored in a sql declared variable for some other further process .
View 7 Replies View RelatedHi all,
I want to know how to handle multiple result sets return from Stored Procedure? I know one way is to insert the result sets into the table, but the limitation is the result sets must have the same data structure. If the result sets have different data structure, how can I handle it.
Thanks,
I have 3 tables:
TABLE [dbo].[Tbl_Products](
[Product_ID] [int] IDENTITY(1,1) NOT NULL,
[Product_Name] [nvarchar](50) NOT NULL,
[Catagory_ID] [int] NOT NULL,
[Entry_Date] [date] NOT NULL,
[Code] ....
I am using this query to get ( Product name from tbl_products , Buy Price - Total Price- Total Quantity from Tbl_Details )
But am getting a multiple result if the order purchase has more than 1 item :
SELECT DISTINCT B.Product_Name,A.AllPieceBoxes,
A.BuyPrice,A.TotalPrice,A.BuyPrice
FROM
Tbl_Products B INNER JOIN Tbl_PurchaseHeader C
ON C.ProductId=B.Product_ID INNER JOIN Tbl_PurchaseDetails A
ON A.PurchaseOrder=C.purchaseOrder
WHERE A.PurchaseOrder=3
Hi All,
I created one report having more than two datasets. How can I display the "No result found Message" If some of the dataset having no data.
Thanks,
Aneesh
I have two tables .. in one (containing user data, lets call it u).The important fields are:u.userName, u.userID (uniqueidentifier) and u.workgroupID (uniqueidentifier)The second table (w) has fieldsw.delegateID (uniqueidentifier), w.workgroupID (uniqueidentifier) The SP takes the delegateID and I want to gather all the people from table u where any of the workgroupID's for that delegate match in w. one delegateID may be tied to multiple workgroupID's. I know I can create a temporary table (@wgs) and do a: INSERT INTO @wgs SELECT workgroupID from w WHERE delegateID = @delegateIDthat creates a result set with all the workgroupID's .. this may be one, none or multipleI then want to get all u.userName, u.userID FROM u WHERE u.workgroupIDThis query works on an individual workgroupID (using another temp table, @users to aggregate the results was my thought, so that's included) INSERT INTO @users SELECT u.userName,u.userID FROM tableU u LEFT JOIN tableW w ON w.workgroupID = u.workgroupID WHERE u.workgroupID = @workGroupIDI'm trying to avoid looping or using a CURSOR for the performance hit (had to kick the development server after one of the cursor attempts yesterday)Essentially what I'm after is: SELECT u.userName,u.userID
FROM tableU u
LEFT JOIN tableW w ON w.workgroupID = u.workgroupID
WHERE u.workgroupID = (SELECT workgroupID from w WHERE delegateID = @delegateID) ... but that syntax does not work and I haven't found another work around yet.TIA!
I know I can do a JOIN(parameter, "some seperator") and it will build me a list/string of all the values in the multiselect parameter.
However, I want to do the same thing with all the occurances of a field in my result set (each row being an occurance).
For example say I have a form that is being printed which will pull in all the medications a patient is currently listed as having perscriptions for. I want to return all those values (say 8) and display them on a single line (or wrap onto additional lines as needed).
Something like:
List of current perscriptions: Allegra, Allegra-D, Clariton, Nasalcort, Sudafed, Zantac
How can I accomplish this?
I was playing with the list box, but that only lets me repeat on a new line, I couldn't find any way to get it to repeate side by side (repeat left to right instead of top to bottom). I played with the orientation options, but that really just lets me adjust how multiple columns are displayed as best I can tell.
Could a custom function of some sort be written to take all the values and spit them out one by one into a comma seperated string?
I need to send the result of a procedure to an update statement.Basically updating the column of one table with the result of aquery in a stored procedure. It only returns one value, if it didnt Icould see why it would not work, but it only returns a count.Lets say I have a sproc like so:create proc sp_countclients@datecreated datetimeasset nocount onselect count(clientid) as countfrom clientstablewhere datecreated > @datecreatedThen, I want to update another table with that value:Declare @dc datetimeset @dc = '2003-09-30'update anothertableset ClientCount = (exec sp_countclients @dc) -- this line errorswhere id_ = @@identityOR, I could try this, but still gives me error:declare @c intset @c = exec sp_countclients @dcWhat should I do?Thanks in advance!Greg
View 4 Replies View Related
I have an Execute SQL Task that executes "select count(*) as Row_Count from xyztable" from an Oracle Server. I'm trying to assign the result to a variable. However when I try to execute I get an error:
[Execute SQL Task] Error: An error occurred while assigning a value to variable "RowCount": "Unsupported data type on result set binding Row_Count.".
Which data type should I use for the variable, RowCount? I've tried Int16, Int32, Int64.
Thanks!
Hi all,
I copied the following code from Microsoft SQL Server 2005 Online (September 2007):
UDF_table.sql:
USE AdventureWorks;
GO
IF OBJECT_ID(N'dbo.ufnGetContactInformation', N'TF') IS NOT NULL
DROP FUNCTION dbo.ufnGetContactInformation;
GO
CREATE FUNCTION dbo.ufnGetContactInformation(@ContactID int)
RETURNS @retContactInformation TABLE
(
-- Columns returned by the function
ContactID int PRIMARY KEY NOT NULL,
FirstName nvarchar(50) NULL,
LastName nvarchar(50) NULL,
JobTitle nvarchar(50) NULL,
ContactType nvarchar(50) NULL
)
AS
-- Returns the first name, last name, job title, and contact type for the specified contact.
BEGIN
DECLARE
@FirstName nvarchar(50),
@LastName nvarchar(50),
@JobTitle nvarchar(50),
@ContactType nvarchar(50);
-- Get common contact information
SELECT
@ContactID = ContactID,
@FirstName = FirstName,
@LastName = LastName
FROM Person.Contact
WHERE ContactID = @ContactID;
SELECT @JobTitle =
CASE
-- Check for employee
WHEN EXISTS(SELECT * FROM HumanResources.Employee e
WHERE e.ContactID = @ContactID)
THEN (SELECT Title
FROM HumanResources.Employee
WHERE ContactID = @ContactID)
-- Check for vendor
WHEN EXISTS(SELECT * FROM Purchasing.VendorContact vc
INNER JOIN Person.ContactType ct
ON vc.ContactTypeID = ct.ContactTypeID
WHERE vc.ContactID = @ContactID)
THEN (SELECT ct.Name
FROM Purchasing.VendorContact vc
INNER JOIN Person.ContactType ct
ON vc.ContactTypeID = ct.ContactTypeID
WHERE vc.ContactID = @ContactID)
-- Check for store
WHEN EXISTS(SELECT * FROM Sales.StoreContact sc
INNER JOIN Person.ContactType ct
ON sc.ContactTypeID = ct.ContactTypeID
WHERE sc.ContactID = @ContactID)
THEN (SELECT ct.Name
FROM Sales.StoreContact sc
INNER JOIN Person.ContactType ct
ON sc.ContactTypeID = ct.ContactTypeID
WHERE ContactID = @ContactID)
ELSE NULL
END;
SET @ContactType =
CASE
-- Check for employee
WHEN EXISTS(SELECT * FROM HumanResources.Employee e
WHERE e.ContactID = @ContactID)
THEN 'Employee'
-- Check for vendor
WHEN EXISTS(SELECT * FROM Purchasing.VendorContact vc
INNER JOIN Person.ContactType ct
ON vc.ContactTypeID = ct.ContactTypeID
WHERE vc.ContactID = @ContactID)
THEN 'Vendor Contact'
-- Check for store
WHEN EXISTS(SELECT * FROM Sales.StoreContact sc
INNER JOIN Person.ContactType ct
ON sc.ContactTypeID = ct.ContactTypeID
WHERE sc.ContactID = @ContactID)
THEN 'Store Contact'
-- Check for individual consumer
WHEN EXISTS(SELECT * FROM Sales.Individual i
WHERE i.ContactID = @ContactID)
THEN 'Consumer'
END;
-- Return the information to the caller
IF @ContactID IS NOT NULL
BEGIN
INSERT @retContactInformation
SELECT @ContactID, @FirstName, @LastName, @JobTitle, @ContactType;
END;
RETURN;
END;
GO
----------------------------------------------------------------------
I executed it in my SQL Server Management Studio Express and I got: Commands completed successfully. I do not know where the result is and how to get the result viewed. Please help and advise.
Thanks in advance,
Scott Chang
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
I am trying to code a WHERE xxxx IN ('aaa','bbb','ccc') requirement but it the return values for the IN keyword changes according to another column, thus the need for a CASE function.
WHERE GROUP.GROUP_ID = 2 AND DEPT.DEPT_ID = 'D' AND WORK_TYPE_ID IN ( CASE DEPT_ID WHEN 'D' THEN 'A','B','C' <---- ERROR WHEN 'F' THEN 'C','D ELSE 'A','B','C','D' END )
I kept on getting errors, like
Msg 156, Level 15, State 1, Line 44Incorrect syntax near the keyword 'WHERE'.
which leads me to assume that the CASE ... WHEN ... THEN statement does not allow mutiple values for result expression. Is there a way to get the SQL above to work or code the same logic in a different manner in just one simple SQL, and not a procedure or T-SQL script.
I need to concatenate two date fields so they appear in a drop-down list like this: 8:00 AM - 10:00 AM
I'm using MS SQL 2005 and my query looks like this: SELECT ClinicTimesID, ClinicID, (CTStartTime + ' - ' + CTEndTime) AS TimeSlot FROM Clinics_Times WHERE (ClinicID = 1) and I get this error: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
Can I strip out the date so that only the time appears or is it even possible to concatenate these fields? If so, how?
Thanks!
Hello all, I'm trying to combine two columns of data into a third column using a formula on the thrid column. Each of the columns could contain nulls and each of the columns could contain padding after or before the data. I'm trying to use the following formula yet SQL is throwing an error. Can someone provide another set of eyes to check this out? ISNULL(LTRIM(RTRIM([user_Define_4a])),’’) + ISNULL(LTRIM(RTRIM([user_Define_1])),’’) Thanks
View 2 Replies View RelatedI am importing via DTS a .csv file. I have 2 issues:
-One field from the source needs to be inserted into two existing fields.
-Two fields from the source, First_Name and Last_Name need to be
concatenated to a destination field, Full_Name.
I do not know VB but I do know SQL.
Can anyone help?
Hi. I want to make a query that concatenate the address of the person with a comma inbetween and neglecting the null value.
table - pspersonaldata
dcno name streetbldg1 streetbldg2 streetbldg3
1 jon santol1 null santol3
2 mike mangga1 mangga2 null
3 jay langka1 langka2 langka3
4 joy null buko2 buko3
5 jean null null null
expected output is:
name address
jon santol1, santol3
mike mangga1, mangga2
jay langka1, langka2, langka3
joy buko2, buko3
jean null
thanks.
-Ron-
I am completely new to SQL and I have some syntax questions. I am trying to concatenate 4 fields and some padded constants to form a new key field to perform joins. The result should be a twelve character field without spaces. My problem is that the current fields use spaces as place holders and I need to replace the spaces with ‘0’.
[RD_ID] nvarchar length 5
[RDWY_ID] nvarchar length 1
[MLGE_TYPE] nvarchar length 1
[OVLAP_MLGE_CD] nvarchar length 1
Concatenate 12 characters
1st ‘0’ (constant)
2nd, 3rd, and 4th, from [RD_ID] (without the suffix)
5th and 6th from [RD_ID] suffix or replace spaces with ‘00’
7th 1 or 2 from [RDWY_ID]
8th Z from [MLGE_TYPE] or replace space with ‘0’
9th 1 – 9 from [OVLAP_MLGE_CD] or replace space with ‘0’
10th ‘S’ (constant)
11th ‘0’ (constant)
12th ‘0’ (constant)
Results should resemble
0001CQ100S00 or 000100100S00
This is the query I used in Access.
LRS: "0" & IIf((Len(LTrim(RTrim([HITIS2_TVCLEAR2]![RD_ID])))=3),LTrim(RTrim([HITIS2_TVCLEAR2]![RD_ID])) & "00",LTrim(RTrim([HITIS2_TVCLEAR2]![RD_ID]))) & [HITIS2_TVCLEAR2]![DIR_RDWY_ID] & IIf([HITIS2_TVCLEAR2]![MLGE_TYPE]=" ",0,[HITIS2_TVCLEAR2]![MLGE_TYPE]) & IIf([HITIS2_TVCLEAR2]![OVLAP_MLGE_CD]=" ",0,[HITIS2_TVCLEAR2]![OVLAP_MLGE_CD]) & "S00"
Thanks for any help.
My file is in this format
Col1 Col2 Col3
---------------------------------------------------------------------------
Andrews S 93845877712 P
Sylvia 9999876669 J
Bill K 7657654677 L
I need the output like this
Col1 Col2 Col3
--------------------------------------------------------------------------
AndrewsS 93845877712 P
Sylvia 99999876669 J
BillK 76576546677 L
The character on the left of Col2 has to be joined to Col1 and character on the right of col2 has to be joined to Col3. Can anybody suggest a query for this.
Thanks,
I need to concatenate the strings of the grouped result set in my report. Lets say if I have
ID Text
-----------------
1 text1
1 text2
1 text3
and if I am grouping by ID, the resulted text should be "text, text2, text3". Any ideas on this? Its kind of emergency
Thanks
Dinesh