Solved

select view to view in sql

Posted on 2014-10-07
8
148 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
8 Comments
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
No
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
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
Comment Utility
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
 

Author Comment

by:VBdotnet2005
Comment Utility
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
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
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 45

Accepted Solution

by:
Vitor Montalvão earned 500 total points
Comment Utility
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 69

Expert Comment

by:Éric Moreau
Comment Utility
instead of a View, you can create a User Defined Function that accepts parameters
0
 
LVL 48

Expert Comment

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

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

In this article I will describe the Backup & Restore 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.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

743 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now