We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

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

forexguy1000
forexguy1000 asked
on
Medium Priority
156 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.
Comment
Watch Question

Commented:
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

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

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

Author

Commented:
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?
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.