Page 1 of 3 123 LastLast
Results 1 to 15 of 37

Thread: View Question

  1. #1
    Join Date
    Feb 2012
    Posts
    188

    Unanswered: View Question

    I am currently running a maketable statement, and then pulling data from my make table. However, I was thinking it would be more efficient to just create a view. However, the snare that I am hitting is that my SELECT statement pulls from 3 tables, 1 of which is a temp table.
    Here is my current SELECT statement structure:
    Code:
    SELECT ID, FirstName, LastName, position, StoreName, StoreNumber 
    FROM (MasterStoreInfo INNER JOIN #MasterStoreInfo_Helper ON MasterStoreInfo.ID = #MasterStoreInfo_Helper.ID)
    LEFTJOIN MasterStoreList ON #MasterStoreInfo_helper.StoreName = MasterStoreList.StoreName
    Which with my make table statement, the temp tables are created, the Select statements are run, the results are displayed and the temp tables are dropped. So the temp tables need to be re-created at the beginning of each make-table run. Is it possible to do something like this with a view?

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You can do that with a table-valued function, but not with a simple view.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Feb 2012
    Posts
    188
    Thank you for the link, I am reading up on it now. It looks like in place of a temp table I would use the Table-Valued UDF.

    EDIT ---
    Let me also show the structure of my temp table and see if a better set-up is a viable option.
    Code:
    SELECT ID,
    Case
    WHEN MasterStoreInfo.StoreName = MasterStoreList.StoreName
    THEN MasterStoreInfo.StoreName
    Else CASE
    WHEN MasterStoreInfo.StoreName LIKE 'RU%' THEN MasterStoreInfo.StoreName
    Else
    'Non American Store'
    End
    End As [StoreName]
    INTO #MasterStoreInfo_Helper
    FROM MasterStoreList RIGHT JOIN MasterStoreInfo
    ON MasterStoreList.StoreName = MasterStoreInfo.StoreName
    Last edited by jo15765; 07-16-13 at 11:18. Reason: added syntax for TempTable

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Use the table valued function to replace the whole kit and caboodle. Inside of the UDF you can create your temp tables, populate them, join to the other tables, and return the result set back to the UDF caller.

    Note that this doesn't perform or scale really well. Like your current model, it requires rebuilding all of the temp tables and inhibits caching. It is just an easy way to "bottle" what you've got, which is often enough for a small or "one shot" job. If you need to call this function frequently, I'd refactor it so that it will perform and scale better.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Feb 2012
    Posts
    188
    Quote Originally Posted by Pat Phelan View Post
    Use the table valued function to replace the whole kit and caboodle. Inside of the UDF you can create your temp tables, populate them, join to the other tables, and return the result set back to the UDF caller.

    Note that this doesn't perform or scale really well. Like your current model, it requires rebuilding all of the temp tables and inhibits caching. It is just an easy way to "bottle" what you've got, which is often enough for a small or "one shot" job. If you need to call this function frequently, I'd refactor it so that it will perform and scale better.

    -PatP
    Ah, this is called once a week after hours, so there is no one else (well maybe 1 or 2 users) on the system.

  6. #6
    Join Date
    Feb 2012
    Posts
    188
    With the SQL for my temp tale being relatively simple, could I rewrite my Select statement to include the temp table within the From statement? Then use a view to display the results?

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    That would be a FAR better way to do things. Moving the temp tables into the FROM clause is portable to most other SQL dialects, it allows the plan to be cached, and it allows the Plan optimizer much more leeway to improve the performance of the query.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  8. #8
    Join Date
    Feb 2012
    Posts
    188
    Quote Originally Posted by Pat Phelan View Post
    That would be a FAR better way to do things. Moving the temp tables into the FROM clause is portable to most other SQL dialects, it allows the plan to be cached, and it allows the Plan optimizer much more leeway to improve the performance of the query.

    -PatP
    Do you have any good examples for me to follow?

  9. #9
    Join Date
    Feb 2012
    Posts
    188
    Quote Originally Posted by jo15765 View Post
    Do you have any good examples for me to follow?
    This is what I tried, but it produces an error of Incorrect syntax near the keyword Left
    Code:
    SELECT ID, FirstName, LastName, position, StoreName, StoreNumber
    FROM MasterStoreInfo INNER JOIN
    (
    SELECT ID,
    Case
    WHEN MasterStoreInfo.StoreName = MasterStoreList.StoreName THEN MasterStoreInfo.StoreName
    ELSE Case
    WHEN MasterStoreInfo.StoreName LIKE 'RU%' THEN MasterStoreInfo.StoreName
    Else 'Non American Store'
    End
    End As [Store.Name]
    FROM FROM MasterStoreList RIGHT JOIN MasterStoreInfo ON MasterStoreList.StoreName = MasterStoreInfo.StoreName
    )
    LEFT JOIN MasterStoreList ON
    (
    SELECT ID,
    Case
    WHEN MasterStoreInfo.StoreName = MasterStoreList.StoreName THEN MasterStoreInfo.StoreName
    ELSE Case
    WHEN MasterStoreInfo.StoreName LIKE 'RU%' THEN MasterStoreInfo.StoreName
    Else 'Non American Store'
    End
    End As [Store.Name]
    FROM
    FROM FROM MasterStoreList RIGHT JOIN MasterStoreInfo ON MasterStoreList.StoreName = MasterStoreInfo.StoreName
    FROM MasterStoreList RIGHT JOIN MasterStoreInfo ON MasterStoreList.StoreName = MasterStoreInfo.StoreName
    )

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I tried to parse this to figure out what you wanted, but things went woefully wrong. I've added some white space to show what the parser sees as it looks at this code, which may help you to determine what you really want.
    Code:
    SELECT ID, FirstName, LastName
    ,  position, StoreName, StoreNumber
       FROM MasterStoreInfo 
       INNER JOIN
          (SELECT ID,
                Case
                   WHEN MasterStoreInfo.StoreName = MasterStoreList.StoreName THEN MasterStoreInfo.StoreName
                   ELSE Case
                      WHEN MasterStoreInfo.StoreName LIKE 'RU%' THEN MasterStoreInfo.StoreName
                      Else 'Non American Store'
                   End
                End As [Store.Name]
                FROM FROM MasterStoreList 
    			RIGHT JOIN MasterStoreInfo 
    			   ON MasterStoreList.StoreName = MasterStoreInfo.StoreName
          )
       LEFT JOIN MasterStoreList
          ON
             (SELECT ID,
                Case
                   WHEN MasterStoreInfo.StoreName = MasterStoreList.StoreName THEN MasterStoreInfo.StoreName
                   ELSE Case
                      WHEN MasterStoreInfo.StoreName LIKE 'RU%' THEN MasterStoreInfo.StoreName
                      Else 'Non American Store'
                   End
                End As [Store.Name]
             FROM
                FROM FROM MasterStoreList 
    	        RIGHT JOIN MasterStoreInfo
    	           ON MasterStoreList.StoreName = MasterStoreInfo.StoreName
                      FROM MasterStoreList 
    		      	RIGHT JOIN MasterStoreInfo 
    			         ON MasterStoreList.StoreName = MasterStoreInfo.StoreName
             )
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  11. #11
    Join Date
    Feb 2012
    Posts
    188
    What I was going for, was taking my original query (post 1) and everywhere the temp table was used (post 3) just hard code the syntax for the temp table in an effort to remove the temp table.

    My feeble attempt may have been awfully incorrect. Thank you for cleaning up the code a good bit, I will toy with that and see if I can make it work.

  12. #12
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    The subselect in the from clause will need a table alias name. No idea what is going on in the ON clause that immediately follows that, though.

  13. #13
    Join Date
    Feb 2012
    Posts
    188
    Quote Originally Posted by MCrowley View Post
    The subselect in the from clause will need a table alias name. No idea what is going on in the ON clause that immediately follows that, though.
    Code:
    LEFTJOIN MasterStoreList ON #MasterStoreInfo_helper.StoreName = MasterStoreList.StoreName
    It links the table to the temp table on StoreName. Post 3 may help explain a little further (essentially I just tried to replace each instance of my temp table, with the actual SQL syntax of my temp table).

    I will try to add an alias to the table in the FROM clause and see if that catches it.

  14. #14
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    No guarantee (this is "air code"), but try:
    Code:
    WITH MasterStoreInfo_Helper (ID, StoreName)
       AS (
          SELECT ID,
             Case
                WHEN MasterStoreInfo.StoreName = MasterStoreList.StoreName
                   THEN MasterStoreInfo.StoreName
                WHEN MasterStoreInfo.StoreName LIKE 'RU%'
                   THEN MasterStoreInfo.StoreName
                ELSE 'Non American Store'
             End As [StoreName]
             FROM MasterStoreList 
             RIGHT JOIN MasterStoreInfo
                ON MasterStoreList.StoreName = MasterStoreInfo.StoreName
       )
    SELECT ID, FirstName, LastName
    ,  position, StoreName, StoreNumber 
       FROM (MasterStoreInfo
       INNER JOIN MasterStoreInfo_Helper
          ON (MasterStoreInfo.ID = #MasterStoreInfo_Helper.ID)
       LEFT JOIN MasterStoreList
    	  ON (MasterStoreInfo_helper.StoreName = MasterStoreList.StoreName)
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  15. #15
    Join Date
    Feb 2012
    Posts
    188
    Quote Originally Posted by Pat Phelan View Post
    No guarantee (this is "air code"), but try:
    Code:
    WITH MasterStoreInfo_Helper (ID, StoreName)
       AS (
          SELECT ID,
             Case
                WHEN MasterStoreInfo.StoreName = MasterStoreList.StoreName
                   THEN MasterStoreInfo.StoreName
                WHEN MasterStoreInfo.StoreName LIKE 'RU%'
                   THEN MasterStoreInfo.StoreName
                ELSE 'Non American Store'
             End As [StoreName]
             FROM MasterStoreList 
             RIGHT JOIN MasterStoreInfo
                ON MasterStoreList.StoreName = MasterStoreInfo.StoreName
       )
    SELECT ID, FirstName, LastName
    ,  position, StoreName, StoreNumber 
       FROM (MasterStoreInfo
       INNER JOIN MasterStoreInfo_Helper
          ON (MasterStoreInfo.ID = #MasterStoreInfo_Helper.ID)
       LEFT JOIN MasterStoreList
    	  ON (MasterStoreInfo_helper.StoreName = MasterStoreList.StoreName)
    -PatP
    Just to double check, should the # still be in front of the table name?

    Also, i am getting an error of Incorrect syntax near the keyword 'With'

Posting Permissions

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