forexguy1000
asked on
SQL 7.0 Create 4 queries that link to each other in SQL and return a recordset
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
ASKER