Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2014
    Posts
    10

    Question Unanswered: Can i create a view in Stored procedure in that view uses local variable.?

    i wanted to create a view in db2 stored procedure with use of local variable declared in that procedure.
    but i cant , its always showing an error

    as

    "LOC_END_DATE" is not valid in the context where it is used. LINE
    NUMBER=55. SQLSTATE=42703

    the pictures of my code is attached.
    Click image for larger version. 

Name:	Untitled1.jpg 
Views:	5 
Size:	121.5 KB 
ID:	16026Click image for larger version. 

Name:	Untitled.jpg 
Views:	2 
Size:	127.0 KB 
ID:	16027

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Why would you create a view within a stored proc? Do you also delete it when you are done? A view should be a fairly stagnant object that is used by multiple processes/users/etc... Looks like all you are doing is adding the WHERE clause to your 'view'. A view can exist joining many tables together and having different criteria, then in your SQL against that view you can add a where clause to further filter your view. For instance in your case:

    Code:
    create view my_view
    (these_four_columns)
    as select these_concatenated_columns
      from my_table
    Then you would have SQL like:
    Code:
    select * from my_view
    where these_condition = those_columns
    Other wise, just run your SQL statement.
    Dave

  3. #3
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Also, as an aside for your performance get away from the select from sysdummy statements. Just use the SET :hostvar = ? You are already using it for some, use it for the rest of those variables, it is >30% cheaper to do so.
    Dave

  4. #4
    Join Date
    Oct 2014
    Posts
    10
    Actually i have millions of data stored in that table. now i want only few months data that is in probably in thousands. so i am using View because there are some restrictions that i cant use global temp table. so is there anything i can do for that ??

  5. #5
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    yes, seriously do as I stated. Create a view that always exists, talk to your DBA team about it. Then query your view for the date range you want. or run the desired SQL. Either way is the same. A view is not a materialized resultset. A view is just a definition of how to get to the data that you want to access then you can put additional filtering criteria on the view when you query it.
    Dave

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •