Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

select view to view in sql

Posted on 2014-10-07
8
Medium Priority
?
158 Views
Last Modified: 2014-10-13
I have a select statement which was written awhile back. From VB code it calls ike this :
select *from vw_sample_1

 Using cn As New OleDb.OleDbConnection(sConnString)
        cn.Open()
        Dim cmd As New OleDb.OleDbCommand("select *from vw_sample_1", cn)
        Dim r As OleDb.OleDbDataReader = cmd.ExecuteReader()

       etc...

        cn.Close


inside vw_sample_1  is

select *from vw_sample_1 s1
     inner join vw_sample_2 s2
      on s1.id   = s2.id


inside vw_sample_2

select  * from vw_sample_2
where startdate between DATEADD(day, -90, GETDATE()) and getdate()

Instead of setting  a fixed of last 90 days, I want use variable instead.  User can pass 30, 90, 60, etc.

Is there a way to pass a variable from vw_sample_1 to vw_sample_2 ?
0
Comment
Question by:VBdotnet2005
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40367573
No
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40367577
Views do not have parameters, you use where clauses instead.

I suspect you have substantially reduced the real sql involved, but if we take your example as

vw_sample_1  calls: vw_sample_2  and this has a hardcoded -90

Then remove that hardcoded date range from vw_sample_2

then include the wanted date range through your where clause

But I cannot be more specific unless I saw more real code (and even then I might have to see data as well).
0
 

Author Comment

by:VBdotnet2005
ID: 40367581
How about create a small table, Startdate_table.  Update Startdate field from vb code first, sample set to 30 and from vw_sample_2

select  * from vw_sample_2
where startdate between (select startdate from Startdate_table) and getdate()
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

Author Comment

by:VBdotnet2005
ID: 40367584
PortletPaul,

It was written awhile back by a previous developer. It is a long...long select statement which call view after view after view. It is very confusing. I have never seen view calls views, and views.
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40367682
view of views of views: then it's almost invariably going to be less than optimal.

You could use a table - but!!! what about concurrent use?

What isn't well understood is you don't need a parameter to make things efficient in SQL. Parameters have there place in the world and they can be used with functions and stored procedures - but not with views. (and views should not need them either).

Without more details I can only give you generic answers.
0
 
LVL 52

Accepted Solution

by:
Vitor Montalvão earned 2000 total points
ID: 40367757
Why don't you change the code?
(...)
Dim cmd As New OleDb.OleDbCommand("select * from vw_sample_1 where startdate between DATEADD(day, -30, GETDATE()) and getdate() ", cn)
(...)

Open in new window

I posted an example with 30 days but you can easily transform it in a variable and use the variable instead of a constant.
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 40368166
instead of a View, you can create a User Defined Function that accepts parameters
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40376436
"Views do not have parameters, you use where clauses instead."
0

Featured Post

Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

Question has a verified solution.

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

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
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…

715 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