Closed Recordsets From Stored Procedures
Jun 18, 2001
Hello all,
I'm writing some stored procedures that first do an Insert or an Update, and then also do a Select to return a Recordset back to an ADO client. However an Insert or Update causes a closed recordset to be produced in ADO. I can skip over the closed recordset with the NextRecordset method.
So the question is, is there any way of stopping the closed recordset being returned? I don't want to have to call the NextRecordset method from ADO as this would mean that the client would need to know about the implementation of the stored procedure.
Has anyone got any ideas on how to get round this?
Thanks
Dave Sykes
View 2 Replies
ADVERTISEMENT
Jul 20, 2005
I've read that stored procedures should use output parameters instead ofrecordsets where possible for best efficiency. Unfortunately I need toquantify this with some hard data and I'm not sure which counters touse. Should I be looking at the SQL Server memory counters or somethingelse.*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 1 Replies
View Related
Oct 10, 2000
Hi,
I was wondering, is there any way to have a stored procedure write to a file its resultant recordset?
i.e. I have this SP which fires a query, this in turn returns a single row recordset that I need to have written to a file. I realise this could easily be facilitated using an ADO client, however, can it be done by the backend i.e. the SQL7 & SP itself, directly?
I hope you can help me here! thanks for your time!
Cheers
Gaurav
View 2 Replies
View Related
May 7, 2008
I am working on a C# stored procedure for SQL 2005, and i've uncovered a couple questions.
First a description of the procedure:
I have a series of equations taking place to calculate a score based on activities in which the user participated in, that will give them an over all grade or rating. The calculations currently take place in the database, and I am moving this from T-SQL to C# CRL.
1. In order to connect the stored procedure to the database I use a SqlConnection and a SqlCommand to execute either dynamic sql or a stored procedure to return data to a data reader. Is there an easier way to connect to the database? In SSMS if i open up the query it knows what database i am connected to. Do I have to make a sql connection in C# stored Procedures?
2. I have multiple functions within the main C# Stored Procedure that I'm working on. This ends up requiring Multiple Active Recordsets. I must set this withing the connection string. Seeing as I'm using a named instance of SQL 2005, I now must put the userid, password, and server name into the code. Is there a more secure way to connect to SQL Server in a C# Stored procedure that allows MARS?
3. I encryped the connection string, and put it into the assembly, I wrote a decryption class, and in the procedure itself everytime I need to refrence the connection string, I call it, decrypt it, and pass it along. But my code to decrypt the connection string is in the compiled DLL, if the server was ever compromised the encrypted connection string and the key to decrypt it are sitting in the DLL. Is there a config file that I can use for C# Stored Procedures?
4. If I have to keep the connection string in the file, then I need to change that per environment. Example I have 3 test environments before production. So I would need to change the connection string for each file. That may be fine for one procedure, but what if I have 20, that will quickly get of hand?
5. Along the security lines, can the assembly for a C# Stored Procedure be called from outside the assembly? From a command prompt, or by a maliceous program? Or could it be called directly by a .NET application instead of going through a T-SQL Stored Procedure that is using
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [PROJECTNAME].[CLASSNAME].[METHODNAME]
Thanks
View 1 Replies
View Related
Jul 23, 2005
I have written a stored proceedure for MSSQL that needs to run for hours ata time. I need to execute it from C++ code. The current code does:nRet = SQLDIRECTEXEC(hstmt, "exec stored_proc", SQL_NTS)followed shortly after by aFree_Stmt_Handle(hstmt) //roughlyThe stored proc currently dies with the statement handle, not fullypopulating the table I need it to.I need to either know when the proc finishes so I can close the handle afterthat, or allow the proc to run independently on the server no matter whatthe program is doing (is exited, etc), either of these is fine.Please Help! Thanks in advance!Joseph
View 2 Replies
View Related
Jul 23, 2005
I want to know the differences between SQL Server 2000 storedprocedures and oracle stored procedures? Do they have differentsyntax? The concept should be the same that the stored proceduresexecute in the database server with better performance?Please advise good references for Oracle stored procedures also.thanks!!
View 11 Replies
View Related
Sep 30, 2006
Hi,
This Might be a really simple thing, however we have just installed SQL server 2005 on a new server, and are having difficulties with the set up of the Store Procedures. Every time we try to modify an existing stored procedure it attempts to save it as an SQL file, unlike in 2000 where it saved it as part of the database itself.
Thank you in advance for any help on this matter
View 1 Replies
View Related
Nov 6, 2007
Using SQL 2005, SP2. All of a sudden, whenever I create any stored procedures in the master database, they get created as system stored procedures. Doesn't matter what I name them, and what they do.
For example, even this simple little guy:
CREATE PROCEDURE BOB
AS
PRINT 'BOB'
GO
Gets created as a system stored procedure.
Any ideas what would cause that and/or how to fix it?
Thanks,
Jason
View 16 Replies
View Related
Apr 29, 2008
How do I search for and print all stored procedure names in a particular database? I can use the following query to search and print out all table names in a database. I just need to figure out how to modify the code below to search for stored procedure names. Can anyone help me out?
SELECT TABLE_SCHEMA + '.' + TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
View 1 Replies
View Related
Jun 13, 2007
Seems like I'm stealing all the threads here, : But I need to learn :) I have a StoredProcedure that needs to return values that other StoredProcedures return.Rather than have my DataAccess layer access the DB multiple times, I would like to call One stored Procedure, and have that stored procedure call the others to get the information I need. I think this way would be more efficient than accessing the DB multiple times. One of my SP is:SELECT I.ItemDetailID, I.ItemDetailStatusID, I.ItemDetailTypeID, I.Archived, I.Expired, I.ExpireDate, I.Deleted, S.Name AS 'StatusName', S.ItemDetailStatusID, S.InProgress as 'StatusInProgress', S.Color AS 'StatusColor',T.[Name] AS 'TypeName', T.Prefix, T.Name AS 'ItemDetailTypeName', T.ItemDetailTypeID FROM [Item].ItemDetails I INNER JOIN Item.ItemDetailStatus S ON I.ItemDetailStatusID = S.ItemDetailStatusID INNER JOIN [Item].ItemDetailTypes T ON I.ItemDetailTypeID = T.ItemDetailTypeID However, I already have StoredProcedures that return the exact same data from the ItemDetailStatus table and ItemDetailTypes table.Would it be better to do it above, and have more code to change when a new column/field is added, or more checks, or do something like:(This is not propper SQL) SELECT I.ItemDetailID, I.ItemDetailStatusID, I.ItemDetailTypeID, I.Archived, I.Expired, I.ExpireDate, I.Deleted, EXEC [Item].ItemDetailStatusInfo I.ItemDetailStatusID, EXEC [Item].ItemDetailTypeInfo I.ItemDetailTypeID FROM [Item].ItemDetails IOr something like that... Any thoughts?
View 3 Replies
View Related
May 13, 2008
Greetings:
I have MSSQL 2005. On earlier versions of MSSQL saving a stored procedure wasn't a confusing action. However, every time I try to save my completed stored procedure (parsed successfully ) I'm prompted to save it as a query on the hard drive.
How do I cause the 'Save' action to add the new stored procedure to my database's list of stored procedures?
Thanks!
View 5 Replies
View Related
Apr 7, 2006
We recently upgraded to SQL Server 2005. We had several stored procedures in the master database and, rather than completely rewriting a lot of code, we just recreated these stored procedures in the new master database.
For some reason, some of these stored procedures are getting stored as "System Stored Procedures" rather than just as "Stored Procedures". Queries to sys.Objects and sys.Procedures shows that these procs are being saved with the is_ms_shipped field set to 1, even though they obviously were not shipped with the product.
I can't update the sys.Objects or sys.Procedures views in 2005.
What effect will this flag (is_ms_shipped = 1) have on my stored procedures?
Can I move these out of "System Stored Procedures" and into "Stored Procedures"?
Thanks!
View 24 Replies
View Related
Apr 23, 2008
Hello friends......How are you ? I want to ask you all that how can I do the following ?
I want to now that how many ways are there to do this ?
How can I call one or more stored procedures into perticular one Stored Proc ? in MS SQL Server 2000/05.
View 1 Replies
View Related
Mar 26, 2008
Hello
I'm start to work with SSIS.
We have a lot (many hundreds) of old (SQL Server2000) procedures on SQL 2005.
Most of the Stored Procedures ends with the following commands:
SET @SQLSTRING = 'SELECT * INTO ' + @OutputTableName + ' FROM #RESULTTABLE'
EXEC @RETVAL = sp_executeSQL @SQLSTRING
How can I use SSIS to move the complete #RESULTTABLE to Excel or to a Flat File? (e.g. as a *.csv -File)
I found a way but I think i'ts only a workaround:
1. Write the #Resulttable to DB (changed Prozedure)
2. create data flow task (ole DB Source - Data Conversion - Excel Destination)
Does anyone know a better way to transfer the #RESULTTABLE to Excel or Flat file?
Thanks for an early Answer
Chaepp
View 9 Replies
View Related
Jun 16, 2007
Hi,
Do you know how to write stored procedures inside another stored procedure in MS SQL.
Create procedure spMyProc inputData varchar(50)
AS
----- some logical
procedure spMyProc inputInsideData varchar(10)
AS
--- some logical
--- go
-------
View 5 Replies
View Related
May 8, 2008
I am writing a set of store procedures (around 30), most of them require the same basic logic to get an ID, I was thinking to add this logic into an stored procedure.
The question is: Would calling an stored procedure from within an stored procedure affect performance? I mean, would it need to create a separate db connection? am I better off copying and pasting the logic into all the store procedures (in terms of performance)?
Thanks in advance
John
View 5 Replies
View Related
Nov 1, 2007
Hi all - I'm trying to optimized my stored procedures to be a bit easier to maintain, and am sure this is possible, not am very unclear on the syntax to doing this correctly. For example, I have a simple stored procedure that takes a string as a parameter, and returns its resolved index that corresponds to a record in my database. ie
exec dbo.DeriveStatusID 'Created'
returns an int value as 1
(performed by "SELECT statusID FROM statusList WHERE statusName= 'Created')
but I also have a second stored procedure that needs to make reference to this procedure first, in order to resolve an id - ie:
exec dbo.AddProduct_Insert 'widget1'
which currently performs:SET @statusID = (SELECT statusID FROM statusList WHERE statusName='Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID)
I want to simply the insert to perform (in one sproc):
SET @statusID = EXEC deriveStatusID ('Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID)
This works fine if I call this stored procedure in code first, then pass it to the second stored procedure, but NOT if it is reference in the second stored procedure directly (I end up with an empty value for @statusID in this example).
My actual "Insert" stored procedures are far more complicated, but I am working towards lightening the business logic in my application ( it shouldn't have to pre-vet the data prior to executing a valid insert).
Hopefully this makes some sense - it doesn't seem right to me that this is impossible, and am fairly sure I'm just missing some simple syntax - can anyone assist?
View 1 Replies
View Related
Aug 8, 2005
why should one use Recordsets and not SQL itself in database work. Isn't the latter muvch faster, more portable, and needs to be learnt only once?
View 1 Replies
View Related
Aug 31, 2007
is it possible to concatenate 2 recordsets ? i have 2 select, and i need then to be displayed one after other.using union all the results are gathered, ordered like only one select.thanks for all
View 5 Replies
View Related
Mar 16, 2001
Is it possible to return multiple recordsets from SQL to ASP in a single query?
TIA
Pilot
View 5 Replies
View Related
Jun 21, 2007
Hi all,
I've written a query which needs to return different records depending on its argument.
I've used an IF statment to test which of the three SELECT statments should be returned. However, when I try to access the data from VBA in Access I find that the recordset is empty, whereas the query returns thr correct data if I run it in the SQL environment. WHy is this? More importantly, how do I make it return records?
Code:
ALTER PROC [dbo].[spFilteredContractListByUser]
@UserID varchar(7) --The user's 'T' number
AS
PRINT 'User ID is' + @UserID
--Get this user's filter type from tbl
DECLARE @FilterType int
SELECT @FilterType = FilterID
FROM tblFindContractUserFilter
WHERE UserID = @UserID
-- Standard filter
-- Returning all queries matching the username
IF @FilterType = 1
BEGIN
SELECT DISTINCT [Acc No], Business, Type, [Log Date], [Logged By], [Ack. Date], [Ack. By], [Late?]
FROM qryQueriesWorkList
WHERE (suser_sname = @UserID)
END
ELSE IF @FilterType = 2
-- Return only queries acknowledged by @UserID
BEGIN
SELECT DISTINCT q.[Acc No], q.Business, q.Type, q.[Log Date], q.[Logged By], q.[Ack. Date], q.[Ack. By], q.[Late?]
FROM qryQueriesWorkList q
WHERE (q.[Ack. By] = dbo.getEmployeeName(@UserID))
END
ELSE IF @FilterType = 3
-- Return all the queries which have not been acknowledged
BEGIN
SELECT DISTINCT q.[Acc No], q.Business, q.Type, q.[Log Date], q.[Logged By], q.[Ack. Date], q.[Ack. By], q.[Late?]
FROM qryQueriesWorkList q
WHERE (q.[Ack. By] IS NULL)
END
The VBA code is:
Code:
rst.Open ("EXEC spFilteredContractListByUser '" & Me.cmbUserFilter.Value & "'")
Me.cmbUserFilter.Value contains the argument.
Thanks all
Dave
View 2 Replies
View Related
Nov 19, 2004
I need to decrease the amount of time it takes to return a set of data of the following format:
State[Provided vai user intput]->All Counties in State->All Cities in County->All Zones in City[usually < 50 or 75 per city]
current behavior:
1. currently user selects state all counties are returned from db
2. foreach county cities are returned
3. foreach city streets are returned
4. objects hydrated with data & display drawn
As you can see this gets expensive. I know there has to be a better way to do this. Any suggestions? We use sql server/c#.net.
View 8 Replies
View Related
Aug 31, 2007
is it possible to concatenate 2 recordsets ?
i have 2 selects and i want to display all results from the first
select and then the results from the second select query
with paging.
if i use union, all the results will be mixed.
thanks for the help!
View 7 Replies
View Related
Sep 26, 2007
Greetings,
I'm having SSIS block right now and thought I'd see if anyone had a simple solution to this.
What I have is SSN data coming in from several different tables, which needs to be inserted into a SurrogateSSN table which just uses an Identity field for the surrogate.
I normally build a seperate package for each detination table, so in each package that has SSN data in the source, I need to insert into the SurrogateSSN table (then I can use the surrogate values later in my package).
What I'm looking for is a simple solution if possible (preferrable using existing non-script tasks, or very short script), where I can pass in the SSN. Is there way to create a package that will accept a recordset (of SSN's) for this insert, that I can call from several different packages, and pass in the recordset of SSN's?
Thanks for your ideas
View 7 Replies
View Related
Mar 9, 2005
Hello,
I have a SP1 that is calling another SP2,
SP2 is using select and returning records
So in SP1 I get 3 different record sets
(as I can see it on Query Analyzer - one after the other)
When I'm trying using the ASP page I get this error:
"Item cannot be found in the collection corresponding to the requested name or ordinal."
How can I access the last recordset ?
View 3 Replies
View Related
Mar 26, 2000
I think it whould be simpler to explain what I am trying to accomplish.
I wish to pull a query to return multiple records from a SQL 7.0 database.
The query is a customers standing order.
I wish to make edits to multiple records then return those records for update.
The HTML pages that I have seen all seem to display tables on a singular record format.
Is it possible to display records in a 'Table' format and allow it to be updated.
Can any one answer this or direct me to a site with the relevent info.
Thanks Stuart
Indtec@eircom.net
View 2 Replies
View Related
Nov 8, 2001
Hello Folks,
Using just SQL (within SQL Server 2000), I'm looking to output 2 (or more) recordsets from a one-to-many join between table all on one row. Help! Can someone please show me how to do this if it can be done.
Below is an example of the situation.
Thanks a lot folks, - Jerry
************* Tables *******************
** ITEMS **
ItemID
=======
10
11
12
** ITEMSTOPARTS **
ItemID ---- PartID ---- Funktion
=================================
10 -------- 100 ------- headlight
10 -------- 120 ------- taillight
** PARTS **
PARTID ---- Manf ---- ProductName
=================================
100 ------- M1 ------ Halogens
120 ------- M2 ------ Red Lights
************* Queries *******************
-- QUERY 1 --
==============
(dashes preserve indentation)
SELECT
- i.itemID AS ItemID,
- p1.partID AS HLPartID,
- p1.manf AS HLManf,
- p1.name AS HLName,
- p2.partID AS TLPartID,
- p2.manf AS TLManf,
- p2.name AS TLName
FROM - ZItems i
- - - - INNER JOIN ZItemsToParts ip
- - - - - - ON i.itemID = ip.itemID AND
- - - - - - (ip.funktion = 'headlight' OR
- - - - - - ip.funktion = 'taillight') AND
- - - - - - i.itemID = 10
- - - - LEFT OUTER JOIN ZParts p1
- - - - - - ON ip.partID = p1.partID AND
- - - - - - ip.funktion = 'headlight'
- - - - LEFT OUTER JOIN ZParts p2
- - - - - - ON ip.partID = p2.partID AND
- - - - - - ip.funktion = 'taillight'
************* Output *******************
Using "Query 1" I get this:
(Note: HL = Headlight; TL = Taillight)
ItemID - HLPartID - HLManf - HLName --- TLPartID - TL Manf - TL Name
================================================== ==============
10 ----- 100 ------ M1 ----- Halogens - NULL ----- NULL ---- NULL
10 ----- NULL ----- NULL --- NULL ----- 120 ------ M2 ------ Red Lights
I wish to output the proceeding two record set in one row
Any ideas how this can be done with SQL Server 2000
(with JOINS in the FROM clause)?
- DESIRED OUTPUT:
ItemID - HLPartID - HLManf - HLName --- TLPartID - TL Manf - TL Name
================================================== =============
10 ----- 100 ------ M1 ----- Halogens - 120 ------ M2 ------ Red Lights
-= End-O-Message =-
View 2 Replies
View Related
Oct 21, 2007
Here's a question you'll never quit hearing: is there a convenient way to page through large recordsets in SQL Server 2000?
I'm writing some software which, for all intents and purposes, works like a messageboard: users can create threads, leave replies, and so on.
I have about a half million records in a few of my tables, and some of my queries return 1000s of results. I'd prefer not to return 1000s of records all at once, so I don't want to page my records in code; I'd rather page them in SQL Server. Naturally, I want to page replies. However, I don't know of a convenient way to page records in SQL Server 2000.
View 1 Replies
View Related
Jul 20, 2005
I have a single .asp page that opens a connection and then sequentiallyopens and closes 14 recordsets from stored procedures to obtain variousproduct information before closing the connection.Is it common practice to do something like this? Or is opening 14recordsets going to become a real problem when the page goes live and startsgetting high web traffic?Thank you in advance for any information you might provide.Dave
View 2 Replies
View Related
Jul 20, 2005
Hey,I've got an Classic ASP app pulling some records out of a DB, whichworked fine when it was Access. When we upped to SQL Server, all of asudden, when the Recordsets came back, all the dates were empty, eventhough the data was showing up in the DB.About a year ago (only code asp apps once and a while), I rememberseeing this and finding something that just had something to do with theorder in which something was being referenced, but I just can't for thelife of me remember what it was.The Dates are set as datetime, length of 8.Anyone know why this happens and a solution?many thanks,rob
View 1 Replies
View Related
Apr 24, 2008
Can anyone help with this problem.
I am doing some mods to an existing db web app in asp. I am providing a facility to copy a subset of related records in a few tables. I am using one recordset to read in the selected records and then copying the data into another recordset creating a new record in the same table. I have to then pick up the @@IDENTITY of the new record so that I can update linked records in other tables.
The @@IDENTITY value returned is null unless I close the selection recordset.
I have created a simplified version of the code to demonstrate this.
The underlying table is called 'test' and it has 2 fields, 'test_id', integer, identity and 'test_desc' varchar(50)
ASP Code
<%
dim objConn, objRS1, objRS2, objRS3
sub OpenDatabase()
'open database object here instead of in page
Set objConn = Server.CreateObject("ADODB.Connection")
'Connect object to database through global.asa application variable
objConn.Open = Application("test_ConnectionString")
'Create recordset object to use
Set objRS1 = Server.CreateObject("ADODB.Recordset")
Set objRS2 = Server.CreateObject("ADODB.Recordset")
end sub
sub CloseDatabase
Set objRS1 = Nothing
Set objRS2 = Nothing
objConn.Close
Set objConn = Nothing
end sub
function UnNull(numval)
if isnull(numval) then
UnNull="NULL"
else
UnNull=cstr(numval)
end if
end function
OpenDatabase
strSQLQuery = "select * from test where test_id in (1,2) order by test_id"
objRS1.Open strSQLQuery, objConn, 1, 3
do while not objRS1.eof
objRS2.Open "test", objConn, 1, 3
objRS2.AddNew
old_test_id = objRS1("test_id")
objRS2("test_desc") = objRS1("test_desc")
objRS2.Update
objRS2.Close
' *1 objRS1.Close
strSQLQuery = "select @@IDENTITY as new_id"
objRS2.Open strSQLQuery, objConn
new_test_id=objRS2("new_id")
objRS2.close
response.write("Old ID = " & UnNull(old_test_id) & ", New ID = " & UnNull(new_test_id))
response.write("<p>")
' *2 response.end
objRS1.movenext
loop
objRS1.close
CloseDatabase
%>
Output
Old ID = 1, New ID = NULL
Old ID = 2, New ID = NULL
If I unrem the lines *1 and *2 then the output is
Old ID = 1, New ID = 11
I realise that there are other ways of doing this but this is only a simplified example whereas the actual application is far more complicated.
Thanks is advance
Kennedy
View 3 Replies
View Related
Jul 23, 2005
Hi All!I need help with a Statement!I am working with an Access2000 DB.I have the following Problem.ChNrLinieDatum Code 39 Stückzahl BHL1 BHL2 BMRH582-064L2.1008.03.2005 02:30:00FCAA14821701582-064L2.1008.03.2005 02:30:00FCAA14871701582-114L2.1208.03.2005 01:00:00FAC827501240582-114L2.1208.03.2005 01:00:00FAC827441240582-114L2.1208.03.2005 01:00:00FAC827501240582-094L2.707.03.2005 19:45:00FAE74323481582-094L2.707.03.2005 19:45:00FAE74489481582-094L2.707.03.2005 19:45:00FAE74489481581-294L2.807.03.2005 18:20:00FA8V2658221581-294L2.807.03.2005 18:20:00FA8V2652221581-294L2.807.03.2005 18:20:00FA8V2658221582-114L2.1207.03.2005 17:45:00FAAR20721236As you can see I have a few recordsets that are double. The Thing is, thereis an ID that makes them different.I need a Statement that deletes the surplus records where 'Datum' and'Linie' are identical to another record. 1 record has to remain of course.I thought of something like this.DELETE FROM tbAuswertWHERE EXISTS(SELECT *FROM tbAuswertWHERE (Linie AND Datum)IN (SELECT Linie AND Datum AS SuchkritFROM tbAuswertGROUP BY SuchkritHAVING ((Count(Suchkrit)>1)))But I get an error:You wanted to execute a Query that did not have the following expression'Not Linie = 0 And Not Datum = 0' in its AggregatefunctionPerhaps you ccan help me.ThanksJulia--Message posted via http://www.sqlmonster.com
View 3 Replies
View Related
Jul 5, 2006
I'm currently running the following statement that is used in a CrystalReport. Basically, a record is returned when the T_PAYMENT.amounthas a record in the database based on the value of the T_MULTILIST.codefield. Currently, if there is no record returned, there is no listingin the report for the given T_MULTILIST.code.The user now wants a record to be displayed on the report when there isno record in the database - she wants it to display a value of '$0'for the given T_MULTILIST.code record. I tried to explain the fact thatis not possible the way things stand at the moment. Basically I needsome typeof case statement that says 'if there is no record returned, create asingle record and set T_PAYMENT.amount = 0' AFTER each query has beenexecuted.Anyone have any idea how to accomplish this?SELECT DISTINCT'English Language Arts, Grade 1' as Rec_Type, 'English Language Arts(Consumable)' as Super_Type, '' as Other_Type, 'Continuing Contracts'as Proc_Type,T_MULTILIST_GRADE.grade, T_MULTILIST.description, T_MULTILIST.code,T_PAYMENT.amountFROM (T_MULTILIST T_MULTILIST INNER JOIN (T_PAYMENT T_PAYMENTINNER JOIN T_SHIPPING_DETAILT_SHIPPING_DETAIL ONT_PAYMENT.transaction_id=T_SHIPPING_DETAIL.transac tion_id)ON T_MULTILIST.code=T_SHIPPING_DETAIL.multilist_code) INNER JOINT_MULTILIST_GRADE T_MULTILIST_GRADE ONT_MULTILIST.code=T_MULTILIST_GRADE.multilist_code, T_ORDER, T_REQUISITION, T_REQUISITION_DETAILWHERET_ORDER.id = T_SHIPPING_DETAIL.order_id ANDT_REQUISITION.id = T_ORDER.requisition_id ANDT_REQUISITION_DETAIL.requisition_id = T_REQUISITION.id ANDT_REQUISITION_DETAIL.latest_record_flag = 1 ANDT_REQUISITION.latest_record_flag = 1 ANDT_ORDER.latest_record_flag = 1AND (T_MULTILIST.code='1040')AND (T_MULTILIST.expiration_year >= '2006' )AND (T_REQUISITION.requested_shipment_date >= '2006' + '0601'AND T_REQUISITION.requested_shipment_date < dateadd(YY, 1,'2006' + '0601' ) )UNIONSELECT DISTINCT'English Language Arts, Kindergarten' as Rec_Type, 'EnglishLanguage Arts (Consumable)' as Super_Type,'' as Other_Type, 'Continuing Contracts' as Proc_Type,T_MULTILIST_GRADE.grade, T_MULTILIST.description,T_MULTILIST.code, T_PAYMENT.amountFROM (T_MULTILIST T_MULTILIST INNER JOIN (T_PAYMENT T_PAYMENTINNER JOIN T_SHIPPING_DETAIL T_SHIPPING_DETAILON T_PAYMENT.transaction_id=T_SHIPPING_DETAIL.transac tion_id)ON T_MULTILIST.code=T_SHIPPING_DETAIL.multilist_code) INNER JOINT_MULTILIST_GRADE T_MULTILIST_GRADE ONT_MULTILIST.code=T_MULTILIST_GRADE.multilist_code, T_ORDER, T_REQUISITION, T_REQUISITION_DETAILWHERET_ORDER.id = T_SHIPPING_DETAIL.order_id ANDT_REQUISITION.id = T_ORDER.requisition_id ANDT_REQUISITION_DETAIL.requisition_id = T_REQUISITION.id ANDT_REQUISITION_DETAIL.latest_record_flag = 1 ANDT_REQUISITION.latest_record_flag = 1 ANDT_ORDER.latest_record_flag = 1AND (T_MULTILIST.code='0040')and (T_MULTILIST.expiration_year >= '2006' )AND (T_REQUISITION.requested_shipment_date >= '2006' + '0601'AND T_REQUISITION.requested_shipment_date < dateadd(YY, 1,'2006' + '0601' ) )Up to 40 more UNION statements follow the above 2.
View 3 Replies
View Related
