Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Create a View that runs a Case Statement Containing a View

Posted on 2008-10-15
8
Medium Priority
?
197 Views
Last Modified: 2010-04-21
Hi Experts:  I am currently using SQL 2000.  I have created two views and a table.  What I need now is to create a third view that states:  If CodeType (field on the table)  = 1 then run view #1 else run view #2.  Hope this makes sense.
Thanks
IEHP1
0
Comment
Question by:IEHP1
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
8 Comments
 
LVL 5

Expert Comment

by:jfmador
ID: 22727193
Hello, you cannot use a view that use a condition evaluation to select a table or another view. You may be able to do it using a stored procedure that will return a resultset using the view depending of your condition.

There is maybe a way to do it assuming that your two view have the same structure (columns) and that your table only have a row

SELECT * FROM
(
SELECT 1 as viewtype, * FROM VIEW1
UNION ALL
SELECT 2 as viewtype, * FROM VIEW2
) t inner join yourtable on t.ViewType = yourtable.ViewType

Then if yourtable.viewtype is equal to 1 you will get only information from view1 and if viewtype = 2 you will get the data from view2

It is not an effective way to do it, maybe if you provide more information we will be able to give you a more complete answer
0
 

Author Comment

by:IEHP1
ID: 22732084
This is the what I put together in SQL analyzer and it works but I can't run it in Enterprise but of the if statement.  The object of the entire process is users enter data in table dbo.Claims_CPTDX then I run the select statements to check if a certain criteria shows to give the correct results.  It's like building a parameter query.

if exists(select * from dbo.Claims_CPTDX where CodeType = 1)
    SELECT     dbo.CLAIM_HEADER.CACLAIM, dbo.CLAIM_HEADER.CASUBNO + dbo.CLAIM_HEADER.CASUBNO AS IEHPID, dbo.CLAIM_HEADER.CASUBNO,
                      dbo.CLAIM_HEADER.CAPERSNO, dbo.CLAIM_HEADER.CAPRIMDATE, dbo.CLAIM_DETAIL.CBSERVDATE, CONVERT(datetime,
                      dbo.CLAIM_DETAIL.CBSERVDATE, 101) AS ServiceDate, dbo.CLAIM_DETAIL.CBTHRUDATE, dbo.CLAIM_HEADER.CAPROVIDER,
                      dbo.CLAIM_HEADER.CAVENDOR, dbo.CLAIM_HEADER.CADX1, dbo.CLAIM_HEADER.CADX2, dbo.CLAIM_HEADER.CADX3,
                      dbo.CLAIM_DETAIL.CBPROCCODE, dbo.CLAIM_DETAIL.CBQUANT, dbo.CLAIM_DETAIL.CBBILLED, dbo.CLAIM_DETAIL.CBNET,
                      dbo.CLAIM_HEADER.CAPLACE, dbo.CLAIM_DETAIL.CBCLAIMSTAT, dbo.CLAIM_DETAIL.CBPOSTDT
     FROM         dbo.CLAIM_DETAIL INNER JOIN
                      dbo.CLAIM_HEADER ON dbo.CLAIM_DETAIL.CBCLAIM = dbo.CLAIM_HEADER.CACLAIM AND
                      dbo.CLAIM_DETAIL.SCREEN = dbo.CLAIM_HEADER.SCREEN INNER JOIN
                      dbo.Claims_CPTDX ON dbo.CLAIM_DETAIL.CBPROCCODE = dbo.Claims_CPTDX.Code
      WHERE     (dbo.CLAIM_DETAIL.CBCLAIMSTAT IN ('A', 'P')) AND (CONVERT(datetime, dbo.CLAIM_DETAIL.CBSERVDATE, 101) BETWEEN
                      dbo.Claims_CPTDX.BEG_DATE AND dbo.Claims_CPTDX.END_DATE)

ELSE

     

SELECT     dbo.Claims_CPTDX.CODE, dbo.CLAIM_HEADER.CADX1, dbo.CLAIM_HEADER.CADX2, dbo.CLAIM_HEADER.CADX3, CONVERT(datetime,
                      dbo.CLAIM_DETAIL.CBSERVDATE, 101) AS ServiceDate
FROM         dbo.CLAIM_DETAIL INNER JOIN
                      dbo.CLAIM_HEADER ON dbo.CLAIM_DETAIL.CBDATE = dbo.CLAIM_HEADER.CAPRIMDATE AND
                      dbo.CLAIM_DETAIL.CBCLAIM = dbo.CLAIM_HEADER.CACLAIM AND dbo.CLAIM_DETAIL.SCREEN = dbo.CLAIM_HEADER.SCREEN INNER JOIN
                      dbo.Claims_CPTDX ON dbo.CLAIM_HEADER.CADX1 = dbo.Claims_CPTDX.CODE
WHERE     (dbo.CLAIM_DETAIL.CBCLAIMSTAT IN ('A', 'P')) AND (CONVERT(datetime, dbo.CLAIM_DETAIL.CBSERVDATE, 101) BETWEEN dbo.Claims_CPTDX.BEG_DATE AND dbo.Claims_CPTDX.END_DATE)


UNION ALL

SELECT     dbo.Claims_CPTDX.CODE, dbo.CLAIM_HEADER.CADX1, dbo.CLAIM_HEADER.CADX2, dbo.CLAIM_HEADER.CADX3, CONVERT(datetime,
                      dbo.CLAIM_DETAIL.CBSERVDATE, 101) AS ServiceDate
FROM         dbo.CLAIM_DETAIL INNER JOIN
                      dbo.CLAIM_HEADER ON dbo.CLAIM_DETAIL.CBDATE = dbo.CLAIM_HEADER.CAPRIMDATE AND
                      dbo.CLAIM_DETAIL.CBCLAIM = dbo.CLAIM_HEADER.CACLAIM AND dbo.CLAIM_DETAIL.SCREEN = dbo.CLAIM_HEADER.SCREEN INNER JOIN
                      dbo.Claims_CPTDX ON dbo.CLAIM_HEADER.CADX2 = dbo.Claims_CPTDX.CODE
WHERE        (dbo.CLAIM_DETAIL.CBCLAIMSTAT IN ('A', 'P')) AND (CONVERT(datetime, dbo.CLAIM_DETAIL.CBSERVDATE, 101) BETWEEN dbo.Claims_CPTDX.BEG_DATE AND dbo.Claims_CPTDX.END_DATE)


UNION ALL
SELECT     dbo.Claims_CPTDX.CODE, dbo.CLAIM_HEADER.CADX1, dbo.CLAIM_HEADER.CADX2, dbo.CLAIM_HEADER.CADX3, CONVERT(datetime,
                      dbo.CLAIM_DETAIL.CBSERVDATE, 101) AS ServiceDate
FROM         dbo.CLAIM_DETAIL INNER JOIN
                      dbo.CLAIM_HEADER ON dbo.CLAIM_DETAIL.CBDATE = dbo.CLAIM_HEADER.CAPRIMDATE AND
                      dbo.CLAIM_DETAIL.CBCLAIM = dbo.CLAIM_HEADER.CACLAIM AND dbo.CLAIM_DETAIL.SCREEN = dbo.CLAIM_HEADER.SCREEN INNER JOIN
                      dbo.Claims_CPTDX ON dbo.CLAIM_HEADER.CADX3 = dbo.Claims_CPTDX.CODE
WHERE       (dbo.CLAIM_DETAIL.CBCLAIMSTAT IN ('A', 'P')) AND (CONVERT(datetime, dbo.CLAIM_DETAIL.CBSERVDATE, 101) BETWEEN dbo.Claims_CPTDX.BEG_DATE AND dbo.Claims_CPTDX.END_DATE)

0
 
LVL 5

Expert Comment

by:jfmador
ID: 22732337
Hello using the if exists(select * from dbo.Claims_CPTDX where CodeType = 1) is kind of dangerous, if you have multiple row in this table if only one contain a codetype equal to 1 the condition will always be true, if there is multiple user adding data to claims_cptdx it can give to a user bad informations

I suggest you to create a stored procedure
CREATE PROCEDURE dbo.GetClaim (@ClaimType int)
AS

if (@ClaimType = 1)
begin
     -- your first select statement
end
else
begin
     -- your second select statement
end
GO

After that use EXEC GetClaim 1 or EXEC GetClaim 0
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:IEHP1
ID: 22732704
ok but can I run a procedure from a view?  
0
 
LVL 5

Expert Comment

by:jfmador
ID: 22733144
No, why you need it as a view?
0
 

Author Comment

by:IEHP1
ID: 22733165
Yes.  Like I said users enter the info in the table and then run the view to see the results.  Sorry forgot to mention this is being done in Access 2000.  The view and table are ODBC'd into Access.
0
 
LVL 5

Accepted Solution

by:
jfmador earned 2000 total points
ID: 22733235
Ok then forget about my stored procedure I don't think that it exists in Access, in your first post you mentionned SQL 2000.

Since your two select statement doesn't contains the same information (same fields) it is impossible to build a view that can return different structure using condition.

You maybe take a look to a macro that could build a temporary table with your data depending of your condition.
0
 

Author Closing Comment

by:IEHP1
ID: 31506539
Yes that's what I thought, thanks for your help.
0

Featured Post

CHALLENGE LAB: Troubleshooting Connectivity Issues

Goal: Fix the connectivity issue in the lab's AWS environment so that you can SSH into the provided EC2 instance.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

688 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