Troubles Returning Values With SQL Statement -
Mar 28, 2006
Hi, I just want to know the following.
Is it posible to write a SQL statement that searches all values in a table where the
e.g:
"Jobid = 1" and sort the result set according to the "CategoryID"?
then if you want to display the values, you can populate them according to the "CategoryID"?
and then also write the result set to a "text file", and catagorize everything according to the "categoryID" in the following format???
Category Number: 8 Description: Site
Date Labour Hours Rate Subtotal
10/05/2005 Artisan 20 95.00 1,900.00
10/06/2005 Labourer 7 45 315.00
Category Total 2,215.00
And then after that the next "categoryid"
Thank You, I really hope you understand what I mean.
your help will be greatly appreaciated.
View 4 Replies
ADVERTISEMENT
Nov 26, 2015
I am writing a query and have the bulk of it already written.
I am looking at a table that contains customer orders. There is a column named Customer_Order.Status Available values for this column is R, F, H, and C.
I'd like for my query to return all lines that have the value R, F, H.
My where clause is written like this
WHERE CUSTOMER_ORDER.SITE_ID = 'XXX' AND CUSTOMER_ORDER.STATUS = ('R','H','F')
I know I'm missing something....
View 3 Replies
View Related
Feb 28, 2008
I have just about figured everything out except for 2 things.
First sometimes there will not be a ‘177’ record. How can I structure my IF statement so I can bypass the code. Other wise it creates a hdr and a blank detail record? Or is there another conditional verb I can use that might be better?
Second I can’t get my cnt to show up in my hdr records. The print statement is not even showing them. What am I doing wrong?
Any ideas?
Thanx in advance,
Here's my code:
DECLARE @Cnt int
DECLARE @Sys varchar (4);
DECLARE @vwSys varchar (4), @vwPrin varchar (4)
/* Define Cursor */
/*Works like an ARRAY*//*holds current record */
Declare vwCursor CURSOR FOR
SELECT Sys, Prin FROM dbo.vwPrin
ORder by Sys, Prin
open vwCursor
FETCH NEXT FROM vwCursor INTO @vwSys, @vwPrin
print '1st FETCH '
Print @vwSys
Print @vwPrin
SET @Sys = @vwSys
WHILE @@Fetch_Status = 0--34
BEGIN
INSERT INTO tblOutput (Batch_Type, Batch_Num, Sys, Prin, Terminal_id, Op_Code)
SELECT DISTINCT TOP(1) '9', @Cnt, Sys, Prin, Term_Id, Op_Code
FROM dbo.tblTrans
WHERE Sys = @vwSys AND Prin = @vwPrin
INSERT INTO tblOutput (Account_Num, Tran_Code, SubTrans, Terminal_Id, Op_Code)--45
SELECT Account_Number, Transaction_Code, SubTrans, Term_id, Op_Code
FROM dbo.tblTrans
WHERE dbo.tblTrans.Transaction_Code = '020' AND Sys = @vwSys AND Prin = @vwPrin
SET @Cnt = @Cnt + 1
Print @cnt
INSERT INTO tblOutput (Batch_Type, Batch_Num, Sys, Prin, Terminal_id, Op_Code)
SELECT DISTINCT TOP (1) '9', @cnt, Sys, Prin, Term_Id, Op_Code
FROM dbo.tblTrans
WHERE Sys = @vwSys AND Prin = @vwPrin
INSERT INTO tblOutput (Account_Num, Tran_Code, Clerk_Code, Memo_Text)--45
SELECT Account_Number, Transaction_Code, Clerk_Code, Memo_Text
FROM dbo.tblTrans
WHERE dbo.tblTrans.Transaction_Code = '115' AND Sys = @vwSys AND Prin = @vwPrin
SET @Cnt = @Cnt + 1
[b]IF (SELECT Transaction_Code = '177' AND Sys = @vwSys AND Prin = @vwPrin FROM dbo.tblTrans)[/b]
BEGIN
INSERT INTO tblOutput (Batch_Type, Batch_Num, Sys, Prin, Terminal_id, Op_Code)
SELECT DISTINCT TOP (1) '9', @cnt, Sys, Prin, Term_Id, Op_Code
FROM dbo.tblTrans
WHERE Sys = @vwSys AND Prin = @vwPrin
INSERT INTO tblOutput (Account_Num, Tran_Code, SubTrans, SubTrans_2, Terminal_Id, Op_Code)--45
SELECT Account_Number, Transaction_Code, SubTrans, SubTrans2, Term_Id, Op_Code
FROM dbo.tblTrans--55
WHERE dbo.tblTrans.Transaction_Code = '177' AND Sys = @vwSys AND Prin = @vwPrin
SET @Cnt = @Cnt + 1
/*Continue FETCH Command until EOF */
FETCH NEXT FROM vwCursor INTO @vwSys, @vwPrin
print ' 2nd FETCH '
Print @cnt
END
--Print @vwSys
--Print @vwPrin
END
CLOSE vwCursor
DEALLOCATE vwCursor
END
View 8 Replies
View Related
Jan 12, 2007
Im having a slight problem with my Like statement, I'm just building a simple search/match routine. I have the following SQL statement SELECT TOP(4) tags FROM post WHERE atags LIKE '%test%'that statement is ok, it will return many results with test in it but if I add more then one search word like LIKE '%+test test2+%' or LIKE '%+test%test2+%' All I seem to get is results that only have the exact two tags not any others that may have one of them.How can I get it to display all records that have an instance of any of those words. Any help would be much appreciated.
View 14 Replies
View Related
Aug 16, 2006
Hello everyone!
Here's the deal. I have a table that contains data for contracts each time they are brought to our attention for a review. The first column in the table contains the contract number, the second column contains which review number the data is associated with (the first time it was reviewed, the second time, third, etc.). I need to make a subtable that contains the records from the last review of each contract. The problem is that, of course, each contract can and will have different numbers of reviews than most other contracts. I know how to use the max funtion to return the largest review number of the table, but this doesn't help because not all contracts have that many reviews. Is there any way I can accomplish this using a single SQL statement?
Thanks for the help!
Ryan
View 5 Replies
View Related
Jul 30, 2007
I have a stored procedure that does all the dirty work for me. I'm just having one issue. I need it to run the total number of RECORDS, and I need it to return the total number of new records for TODAY. Here is part of the code:SELECT COUNT(ID) AS TotalCount FROM CounterSELECT COUNT(*) AS TodayCount FROM Counter WHERE DATEPART(YEAR, visitdate) = Year(GetDate()) AND DATEPART(MONTH, visitdate) = Month(GetDate()) AND DATEPART(DAY, visitdate) = Day(GetDate())The statement works great, I just need to return TotalCount and TodayCount in one query. Is this possible?
View 6 Replies
View Related
Feb 18, 2008
Hi All
I have a stored procedure that returns a numeric value. I wish to call this procedure from within another one and have the returned value stored against a declared variable. I'm struggling with the syntax!!
Any help out there?
Thanks
Xo
View 9 Replies
View Related
Sep 13, 2006
hi
can i return value(bool) from a sp based on the result and receive in my vb code.
suji
View 4 Replies
View Related
Nov 20, 2007
Hello,im having a problem integrating an SQL statement with my program,it works fine when theres a result but throws an error when there isno result,is there anyway in SQL to make up a row if there isnt one that matchesthe query for example in this i could put indicator values in thereturned fields then a simple check to see if its a proper field of myindicator fieldI know ISNULL works with single values but that only works if there isa row returnedDoes anyone know of any SQL syntax for MS SQL that when no row isreturned it makes on up and parses it backThank you in advance for your help!!Ben
View 4 Replies
View Related
Jun 4, 2007
I have a simple table on my webpage which shows data from my database.
It all worked fine apart from one part. I wanted the select statement to select only NULL values from a column, as these are classed as open queries. Once I have closed the query a 0 or 1 will automatically be posted and will no longer have a NULL value.
I have a simple select statement (which I will post on here when I log in with my work computer) and at the end I have a WHERE Column = NULL. I have also tried WHERE column <> 0.0 AND column <>1.0 and that did not work.
If I only did WHERE column <> 1.0, i only get the 0.0 results and none of the NULL results, but if I have no WHERE statement I get all the results including the NULL values.
Oliver
View 6 Replies
View Related
Aug 31, 2000
I have a table that contains an identity column. This column is updated in a stored procedure that has a linked trigger that updates another 'backup' table. How can I ensure that the value of the new row added in the table with the identity is passed back to calling client. ?
The value of @@identity is incorrect as it reflects the value in the 'backup table' not the original table. and use select max() on the original table within the stored procedure could be violated by other users attempting the same operation
View 2 Replies
View Related
Feb 19, 2006
I have an accounting database which contains data from various years.The frontend is a VB.Net program. At the year end, the program createsnew voucher and transaction tables and creates new stored procedures forthem.I just append the 'new year' at the end and create themie, Vouchers2001, Vouchers2002, Vouchers2003Similarly Transactions2001, Transactions2002.The data for all the years is in the same database.Also, I maintain a table called 'Books' which contains the Years forwhich data is present in the Database. The Structure of the Books tableisBookID BookYear1 20012 20023 20034 2004My Problem is that i need to know the current balance of any ledger forany year. The method to calculate the balance for any year is to startfrom the Minimum year in the Books table and continue upto the requiredyear. The SQL is as follows.DECLARE @iLedgerID AS INT --will be passed as parameterDECLARE @iYear as INT --will be passed as parameterDECLARE @CurrentBalance as MONEYSET @iLedgerID =1DECLARE @MinBook as INTEGERDECLARE @String nVarchar(4000)SELECT @MinBook = Min(BookYear)FROM BooksWHILE @MinBook <= @iYearBEGINSET @String = ' DECLARE @TT Money ' + char(13) +' SELECT @TT = ISNULL( SUM( ISNULL(Debit,0) - ISNULL(Credit,0) ),0 )'+ ' FROM transactions' + CAST(@MinBook AS CHAR(4)) + ' LEFT OUTER JOINdbo.Vouchers' + CAST(@MinBook AS CHAR(4)) + ' ON dbo.Transactions' +CAST(@MinBook AS CHAR(4)) + '.VoucherID' + ' = dbo.Vouchers' +CAST(@MinBook AS CHAR(4)) + '.VoucherID ' +'WHERE (LedgerID = @iLedgerID)'EXEC sp_executesql @String, N'@iLedgerID Int', @iLedgerID */SET @MinBook = @MinBook + 1ENDNow this is just a sample code. It may have a few glitches. My questionisa) Do I have to create a dynamic sql if the name of the database is notknown ahead of time. If No thenb) I need to add the balance of each year to the grand total. How do ireturn a value from a dynamic sql.TIA*** Sent via Developersdex http://www.developersdex.com ***
View 6 Replies
View Related
Aug 16, 2004
Hi,
How to return values from stored procedures?? I have a value whose variable would be set thru this sp and it should return this value. How to do this?
Thanks,
View 1 Replies
View Related
Mar 7, 2007
I have this assignment where i have a table full of two digit exam scores and I have to write a function that eliminate x number of top values and x number of bottom values and return all the middle values. When the function is called, obviously a number is entered such as 3 and the top 3 and bottom 3 scores are not returned.
i.e. SELECT * FROM GetMiddleValues (3);
If anyone has any ideas on how to accomplish this, that would be great.
Thanks
View 1 Replies
View Related
Mar 1, 2012
I have a table called tableA and i am fetching 10 rows from table.
select dept_id from tableA where branch = 'Chennai';
I got 10 records.
dept_id
-------
001
002
003
004
005
so n.....
Now i want to pass these dept_ids dynamically to a function parameter.
ie. exec function_name (@dept_id).
How do i write a function?
View 3 Replies
View Related
Sep 7, 2012
My table has a column called Period i want to get a list of different periods example:
Period
1
2
31
1
2
4
12
31
2
then run an sql statement and should return you the following
Period
1
2
4
12
31
View 3 Replies
View Related
Apr 2, 2008
I am just starting to learn about stored procs and how great they can make my world. I have been able to create a pro that does some data checking and inserts based on the results but what I have not been able to figure out is how would I pass a message from the proc to my Perl script that calls it? If anyone could point me in the right direction on what commands I should be looking into I would appreciate it.
--
If I get used to envying others...
Those things about my self I pride will slowly fade away.
-Stellvia
View 1 Replies
View Related
Sep 30, 2014
I have a query set that returns values as part of a data set, I need a new one to return values from two queries to a single row.
select '1' as thekey, 'Total Picks' as Tot,sum(prod_qty) as picks from exceed.aseld, exceed.csymh
where luis_id in ('I','E')
and aseld.whse_id = 1
and (
(aseld.batch_id between goal_beg_batch and goal_end_batch
and monitor_group = 'YK')
[code]....
is it possible to get the numbers from keys 1 & 2 on the same row in a new query?
Or if it is easier a query that with give me (completed picks/total picks) = a decimal I can feed to the display as a percentage.
View 3 Replies
View Related
Mar 6, 2008
I have data that looks like below (columns are Timestamp, Offered, Answered and Delay). I'm looking to exclude returning records that have a value for Delay that are within the top 10% of values of that column. Are there any 2005 tricks where this can be accomplished in a simple statement?
2008-02-18 08:30:002322173
2008-02-18 08:45:002120174
2008-02-18 09:00:002425230
2008-02-18 09:15:002828277
2008-02-18 09:30:002522159
View 3 Replies
View Related
Jul 20, 2005
I have a table of product orders. It contains a row for "platform" and Ineed to return how many times each platform is listed in the DBExample data for platform could be:XBOXXBOXXBOXPLAYSTATIONPLAYSTATIONGAMECUBEPLAYSTATIONI'd like the data to be returned asXBOX - 3PLAYSTATION - 3GAMECUBE - 1How would I go about doing this please?
View 1 Replies
View Related
Jan 21, 2008
I have some Sp that return real values, I''m using function like SELECT CONVERT(REAL,field) FROM .... to ensure that conversion and rounding is done.
When I got the values in my application (Datagrid) I receive values having even 9 decimals
How can efectively return only 2 decimals from my query?
Thanks
View 3 Replies
View Related
Jun 9, 2006
Hi Using Following Stored Procedure,
Which always returns Null,
What s the error,
CREATE PROCEDURE prLoginAuth
(
@pStrUserName varchar(50),
@pStrPassword varchar(50),
@pOutput Varchar(20) Output
)
AS
Declare @V_Facilities Varchar(50)
SELECT Facilities=@V_Facilities From UserLoginFacilities where LoginID=(Select LoginID From UserLogin where LoginName=@pStrUserName and Password=@pStrPassword)
If(@V_Facilities=null)
Set @pOutput = @V_Facilities
Return @pOutput;
Else
Set @pOutput = @V_Facilities
Return @pOutput;
GO
Anyone correct this query , I want return the output from this procedure
Thanx in advance
Selva.R
View 3 Replies
View Related
Aug 11, 2013
I have an SQL statement to find attractions between certain longitude and latitude values. The attraction i have already added to the database to test has values 51.502899 and 0.003552 for latitude and longitude. As you can see these values fall within the limits of the statement below but nothing is returned.
SELECT * FROM Attractions WHERE (Lat BETWEEN 51.51998 AND 51.49999) AND (Long BETWEEN 0.014 AND -0.006);
View 2 Replies
View Related
Jan 29, 2004
Hi, i am trying to return two values from SQL 2000 using a single stored procedure. The stored working fine in Query Analyser and returns the two values and two grids in the results window.
My problem is that when i execute the stored procedure using ADO.Net the dataset only has one of the values. e.g TId : 2, where it should read 'TId' : 2, 'ConfigPath': 'C:lah'
Please could anyone shed ligth on this problem?
here the code for the stored procedure:
CREATE PROCEDURE dbo.GetTillInfo
(
@TillIdR varchar(50),
@Password varchar(50)
)
AS
declare @TillId int
declare @configpath varchar(150)
IF Exists (SELECT Id FROM Tills WHERE TillRef=@TillId and TillPassword=@Password)
BEGIN
set @TillIdR = (SELECT Id FROM Tills WHERE TillRef=@TillId and TillPassword=@Password)
select @TillIdR as 'TId'
set @configpath = (SELECT configpath from customer,tills where
tills.customerid = customer.id and tills.id = @login)
select @configpath as 'ConfigPath'
END
ELSE
BEGIN
set @TillIdR = 0
select @TillIdR as 'TId'
set @configpath =''
select @configpath as 'ConfigPath'
END
GO
View 1 Replies
View Related
Feb 21, 2002
Sp which inserts inforamtion into a table works fine. The trigger on that table which then inserts information into another table works fine. Only problem is that the SP will not return anything to Visual Basic.. Anyone know how to fix it?
SPbob
INsert into b values(1,1,2,2)
select 0
(this is a cut up version of the sp just to show about the way it is formated)
Please help.
View 1 Replies
View Related
Oct 16, 2001
I am having trouble using Output parameters. I have set up an Execute SQL Task to call a Stored Procedure. I am passing an input parameter and indicating 1 global variable for output to retrieve a unique value from the stored procedure call. When I execute the step, it completes successfully but nothing is returned in my output parameter for the unique value??? Below is the call that I use:
EXEC SYS_GENERATE_ID ?
-in the Execute SQL Task I click the Parameters button to set up the input:
Status = Parameter1
and the Output Variable Type:
Rowset = GUID.
the stored procedure:
CREATE PROCEDURE dbo.SYS_GENERATE_ID
(@Statusvarchar(20),
@GUID uniqueidentifier = NULL OUTPUT)
AS
BEGIN
SET @GUID = NEWID()
INSERT INTO dbo.ACTIVITY (GUID, STATUS)
VALUES (@GUID, @Status)
SET @GUID = convert(varchar(50), @GUID)
RETURN @@ERROR
END
A table is correctly populated but the GUID value does not make it back to the calling task??? The Global Variable Type under Package Properties has been changed to "Dispatch" and the value is "Not Displayable"???
Any help that is offered will be appreciated GREATLY!!
View 2 Replies
View Related
Jan 16, 2013
I've got this sql statement that keeps returning the wrong data. (it's related to a previous post, but is different)
Code:
SELECT C.NAME, OL.PART_ID, SL.SHIPPED_QTY
FROM CUSTOMER C
INNER JOIN USERS U ON C.ID = U.ID
INNER JOIN ORDERS O ON C.ID = O.ID
INNER JOIN ORDER_LINE OL ON O.ID = OL.ORDER_ID
[Code] .....
The qry pulls like this: (wrong way)
HTML Code:
company partID Qty
Acme Inc ABC123 3
Acme Inc ABC123 4
Acme Inc ABC123 100
Acme Inc KLM444 3
Acme Inc KLM444 4
Acme Inc KLM444 100
Acme Inc QRP456 3
Acme Inc QRP456 4
Acme Inc QRP456 100
It should be:
HTML Code:
Co. part Qty
Acme Inc ABC123 3
Acme Inc KLM444 4
Acme Inc QRP456 100
The Qty field has duplicates. I've tried various ways, but I cannot make this work.
View 14 Replies
View Related
Jan 29, 2013
I have a table:
Code:
CREATE TABLE [dbo].[XmlTable](
[XmlId] [int] IDENTITY(1,1) NOT NULL,
[XmlDocument] [xml] NOT NULL,
CONSTRAINT [PK_XmlTable] PRIMARY KEY CLUSTERED
[Code] .....
With a schema structure:
Code:
<dev:Doc xmlns:dev="http://www.w3.org/2001/XMLSchema" SchemaVersion="0.1" Settings="Testing" Title="Ordering">
<dev:Base RevisionNumber="0" Baseid="34433" />
<dev:Rev Time="2013-01-21T15:08:00">
<dev:Person Name="Me" Systemid="54654" />
</dev:Rev>
[code]....
I am trying to return the id, number, name, and location of the visitors
Something like:
Code:
RevNumber Function Id Number Visitor Location Sender
========= =========== ======== ======= ======== ======
0 A1 1 Dev01 STLRF FGY(14A)
0 A1 1 Dev02 STLRF FGY(14A)
0 A1 1 Dev03 FGRTY FGY(14A)
0 A2 1 GHFF NULL W33R
0 A2 2 UDT NULL RJ4
Here is the table insert
Code:
INSERT INTO XmlTable(XMLDocument)
SELECT * FROM OPENROWSET(
BULK 'C:Users123DesktopPractice.xml',
SINGLE_BLOB) AS x;
I Have gotten a little further, but the number is not showing for A2 and the Sender, visitor is showing null.
Code:
;WITH XMLNAMESPACES ( 'http://www.w3.org/2001/XMLSchema' as dev )
SELECT
Document.value('@Title' , 'NVARCHAR(MAX)') Title,
Functions.value('@Id', 'NVARCHAR(MAX)') Functions,
A1.value('@Number', 'INT') Number,
[code]....
Results I am getting that are not all correct:
Code:
Title Functions Number VisitorName Location Sender
======== ========= ====== =========== ======== ======
Ordering A1 1 Dev01 STLRF NULL
Ordering A1 1 Dev02 STLRF NULL
Ordering A1 1 Dev03 FGRTY NULL
Ordering A2 NULL NULL NULL NULL
Not I changed Rev to Title
View 1 Replies
View Related
Apr 3, 2008
i have a stored procedure that has parameters. this dataset is used in a crystal report. the parameters are in the sp and will return records if a value is selected. i would like to return records if one parameter or all parameters are selected.
there are 3 parameters, date range, receipt, po #
if i select a distinct value and leave the others null, or 2 values 1 null etc, i would like to return the records, i am having trouble with the syntax. thank you
View 1 Replies
View Related
Feb 7, 2007
my stored procedure performs actions of deletion and insertion. Both the inserted and deleted items are output in temp tables with single column.
Is there a way to return the content of these two tables?
Is there a way to return a table from the stored procedure?
Thanks in advance
waamax
View 1 Replies
View Related
Sep 13, 2007
I need to return row counts for a list of all our users. The problem with the first query is that it doesn’t search for names within the column, it considers a list of email addresses a unique entry. I need to be able to see how many times each email address appears in the database. The second query obviously does that but I don’t want to have to copy and paste 500 usernames. the to_addr_head is a text column if that matters. Thanks!!
SELECT
Table1.to_addr_head,
COUNT(*) AS "COUNT(*)"
FROM Table1
group by Table1.to_addr_head
ORDER BY "COUNT(*)" DESC;
select count(*)
where Table1.to_addr_head like '%username%'
View 15 Replies
View Related
Jan 24, 2007
I'm trying to query an LDAP server from a stored procedure written for the CLR but not getting the expected results.
The code is as follows:
<Microsoft.SqlServer.Server.SqlProcedure()> _
Public Shared Sub LDAP_UserExists(<Out()> ByRef exists As Boolean, ByVal username As SqlString)
Dim adspath As New StringBuilder()
adspath.Append(LDAP://[.......]/ou=Members/cn=)
adspath.Append(username)
If username.ToString().Length > 0 Then
Dim uobject As New DirectoryEntry(adspath.ToString(), "", "", System.DirectoryServices.AuthenticationTypes.Anonymous)
If Not (uobject Is Nothing) Then
exists = True
Else
exists = False
End If
End If
End Sub
The same code works fine from an ASP.NET. If I deploy the code and execute it with
exec LDAP_UserExists 'username'
I receive the error
Error converting data type varchar to bit.
And if I right-click and select "Execute Stored Procedure..." I receive @exists = 1 and Return Value = 0, regardless of the value I pass in as the username parameter.
Given that the same code works correctly on the ASP.NET page I suspect that this error has something to do with the <out()> parameter in the stored procedure declaration.
Can anyone suggest the correct method of performing this query?
View 1 Replies
View Related
Aug 8, 2006
Hi all,
I am passing in a variable into a CASE statement. Based on the value, I want to return a set of values in an IN clause. Here is an example:
(The where clause will use a field called 'Location')
DECLARE @strTest as Varchar(50)
SET @strTest = 'HI'
SELECT * FROM [SomeTable]
WHERE
CASE @strTest
WHEN 'HI' THEN Location IN('1', '2', '3')
END
ORDER BY Location
Is this possible to do?
Sanctos
View 1 Replies
View Related
