Query in View - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Query in View

Author

Topic

StephO11
Starting Member

4 Posts

Posted - 2010-10-20 : 13:01:20

So I have a view (cust_view) already made that goes similar to this more/less:

Select A.Cust_Name B.Cust_ID
From Table A Left Join Table B on A.Cust_Num = B.Cust_Num
--Where A.Buy_Mo = 'Mar 2009'
Where B.Cust_Num is not null

The "--" commented is where I run into troubles because I want that day to change, but again it only applies in Table A and I don't want it to show up in the final table when all gets selected, just the cust_name and cust_id.

I'd like this to work with this query that can have an additional where clause, basically (probably why problems arise)

select * from cust_view where a.buy_mo = 'mar 2009'

Of course, it'd be best if could write in a parameter, but views do not allow for such from what have seen. Could consider a function/procedure (would have to be in PL/SQL please) though am up for suggestions and input because my concern is that the table format with columns across will work. Again, above is bit truncated from actual version

Hello y

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-10-20 : 13:03:38

if you want date to change then ideally what you need is a parameterised procedure.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

StephO11
Starting Member

4 Posts

Posted - 2010-10-20 : 13:57:28

@visakh16: That only partially helps me starts. I understand that, my further concern is that I am a SQL and PL/SQL novice. If you know of examples for doing such with a view (implementing a function) and instructing further on how to use that solution (function, procedure, etc), then that would be greatly appreciated.

Hello y

StephO11
Starting Member

4 Posts

Posted - 2010-10-20 : 14:03:20

Also, visakh16, if you know how to parameterize the above code and show the resulting query appropriately, then help with that works out very well. Especially as I think that solution works better and will be less costly, from what I can tell.

Hello y

zhen1827
Starting Member

1 Post

Posted - 2010-10-20 : 22:46:31

The syntax highlight makes the edited SQL text more convenient for viewing and editing. The popup menu of the editor provides standard functions for working with the text as well as some advanced functions.

The Load SQL/Save SQL functions allow you to load SQL text from file on disc or save the edited text to file. The Insert Table function opens a dialog window for selecting a table to insert into the query text http://www.shopmbtshoesonline.com/ . The selected table is inserted in the following format: SELECT [COLUMNS...] FROM <TABLE_NAME>. The Insert Query function opens a dialog window for selecting one of the already existing profile queries to insert its full text into the query. This can be useful, e.g. for creating queries with sub-selects.

Take a look at the two commented lines at the end of the text. Using the same format as show above you can edit query name and description without switching to the design mode of the Query Designer.

StephO11
Starting Member

4 Posts

Posted - 2010-10-21 : 16:01:34

zhen1827 that did not make sense at all...

Hello y

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-10-23 : 01:54:05

quote:
Originally posted by StephO11

Also, visakh16, if you know how to parameterize the above code and show the resulting query appropriately, then help with that works out very well. Especially as I think that solution works better and will be less costly, from what I can tell.

Hello y

you can just make your view a procedure like


create proc yourprcname
@MonthName varchar(11)
AS
Select A.Cust_Name B.Cust_ID 
From Table A 
InnerJoin Table B on A.Cust_Num = B.Cust_Num 
Where A.Buy_Mo = @MonthName


then use it like 

EXEC yourprcname @MonthName

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources