1
vote
3answers
325 views

best practice for 'staging' relation in stored procedure: use temp table, or view?

When I write stored procedures that take relation A and output relation Z, I like to create 'staging' relations along the way, so I can inspect the state of the data after each step in the programming ...
0
votes
0answers
311 views

How can I create a view in a stored procedure on MySQL?

I am trying to dynamically create views based on an SQL query and have a trigger recreate them when the fields in the view are to change. When I call the procedure I end up with a permission denied ...
1
vote
2answers
75 views

Fixed database paths in stored procedures/views - is there a smoother way of doing this?

Our developers love to create lots of different stored procedures/views, which extracts data from lots of different databases on different servers. Every time we migrate to a new database server, ...
5
votes
2answers
1k views

Frequency distributions segmented by a field

Some background: I have a sample population data file. Each record in the data file has a frequency weight (FIELD NAME: wgt) indicating how many times the records needs to be replicated to get the ...
0
votes
0answers
60 views

Procedure after commit

I have an application which periodically will insert large amounts of data, from that data I build a few materialized views (well emulate by creating a table from a view and replacing it with the ...
0
votes
2answers
29 views

A query for a latest version of data

MySQL. Let there is a table with fields startdate and kind. Actually in the real problem column named here startdate and kind are in a UNIQUE index, but the case if only startdate (not kind) is ...
4
votes
1answer
1k views

Join To a @Table Variable is running ineficiently

Alright so I have a report sproc that was running incredibly slow. Customers were complaining that the report would not run so I started investigating exactly where in the sproc the problem was and I ...
0
votes
1answer
37 views

Two dichotomy views of a table, and means to express this as a stored procudure?

There is a table events. Some relatively complex logic determines for each event whether it is archived. Then I need two more views: All archived events and all not archived events. How to express ...
2
votes
2answers
3k views

Primary Key on a View

While answering this question How to add a Primary Key on a Oracle view? A lot of questions popped in my mind. 1. Is it advisable to have primary key in a view. 2. Why anybody be intrested in ...
2
votes
1answer
349 views

Virtual Column - Using it in view (Oracle)

I have made a virtual column in Oracle, using the following code. CREATE OR REPLACE function email_address (ID_ varchar2) return varchar2 deterministic as lname varchar2 (256); snumber varchar2 ...
0
votes
2answers
2k views

Hide column if any value is null or zero via proc sql or view [closed]

Given either of the two following datasets Kağıt Cinsi Sarı Yeşil Mavi Kalın 4 0 5 İnce null 0 3 or Kağıt Cinsi ...