?
Solved

SQL 7.0 Create 4 queries that link to each other in SQL and return a recordset

Posted on 2009-02-14
3
Medium Priority
?
149 Views
Last Modified: 2012-05-06
So I've been searching like mad and spending way too much time reading help files, hoping the expert community can help me:  I have a query structure that works perfectly in access but I need to execute it and return a result set in SQL, I can do it with VIEWS in Sql but have discovered that you cannot pass parameters to view objects. Here is the SQL view solution:

CREATE VIEW view1 AS
SELECT m1,d1 FROM T1
WHERE (((T1.L)="V1") AND ((T1.G)="V2") AND ((T1.C)="V3")
AND ((T1.A) Between V4 And V5) AND ((T1.O)="V6") AND ((T1.CT="V7")
AND ((T1.VA)="Y"))

CREATE VIEW view2 as
SELECT m1 FROM T2 INNER JOIN T1 ON T2.BE = T1.M1
WHERE (((T2.BR)="V8") AND ((T1.G)="V2") AND ((T1.C)="V3")
AND ((T1.A) Between V4 And V5) AND ((T1.O)="V6") AND ((T1.CT)="V7")
AND ((T1.VA)="Y"))

CREATE VIEW view3 as
SELECT m1 FROM T1 INNER JOIN T2 ON T1.M1=T2.BR
WHERE (((T2.BE)="V8") AND ((T1.G)="V2") AND ((T1.C)="V3")
AND ((T1.A) Between V4 And V5) AND ((T1.O)="V6") AND ((T1.CT)="V7")
AND ((T1.VA)="Y"))

create view finalview as
SELECT view1.m1, view1.d1
FROM VIEW2 RIGHT JOIN (VIEW1 LEFT JOIN VIEW3 ON VIEW1.M1 = VIEW3.M1) ON VIEW2.M = VIEW1.M
WHERE (((VIEW3.M) Is Null) AND ((VIEW2.M) Is Null)) order by d1 desc

As you can see "finalview" needs the returns from all 3 views above.
Ideally I want to send variables: V1,V2... thru to V8 to some stored procedure or query and have it return a recordset exactly as FINALVIEW does.  The problem with the above solution is that I cannot change the values of V1,V2,V3 etc... unless I create a new view each time.  I am using SQL Server 7.0.  You'll notice that i use the same variables/parameters for all 3 views so if there is a way of doing this all in one SQL statment please let me know.  Also the solution needs to allow for result returns local to the python process that is calling it, with the view solution even if I could change the parameters then there would be a small chance that another process of my client code would call a view while it is being fed parameters from yet another process. Thanks in advance for any assistance.
0
Comment
Question by:forexguy1000
  • 2
3 Comments
 
LVL 25

Accepted Solution

by:
reb73 earned 2000 total points
ID: 23640501
Here you go -
CREATE PROCEDURE dbo.NewProc
	 @param1 char(2) = 'V1'
	,@param2 char(2) = 'V2'
	,@param3 char(2) = 'V3'
	,@param4 char(2) = 'V4'
	,@param5 char(2) = 'V5'
	,@param6 char(2) = 'V6'
	,@param7 char(2) = 'V7'
	,@param8 char(2) = 'V8'
AS
 
SELECT DISTINCT T1.M1,T1.D1 
FROM T1
LEFT JOIN T2 T2A ON T2A.BE = T1.M1 AND T2A.BR = @param8
LEFT JOIN T2 T2B ON T2B.BR = T1.M1 AND T2B.BE = @param8
WHERE T2A.BE IS NULL AND T2B.BR IS NULL
AND	T1.L=@param1 AND T1.G=@param2 AND T1.C=@param3 AND T1.O=@param6
AND	T1.CT=@param7 AND T1.A BETWEEN @param4 And @param5 AND T1.VA='Y'
 
GO

Open in new window

0
 

Author Closing Comment

by:forexguy1000
ID: 31546884
Wow, wow and wow! Thank you so much! Worked like a charm! very happy with the prompt response and your re-design/optimization!
0
 

Author Comment

by:forexguy1000
ID: 23640619
One modification I made was to add the order by d1 desc to the end of that procedure.  Now that I have the recordset how do you recommend I count the records? I could just use other cursor methods to get total records but how would I do it with another stored procedure? or the inclusion of some kind of count() command?
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question