Solved

Create a View that runs a Case Statement Containing a View

Posted on 2008-10-15
8
192 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
  • 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
How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

 

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 500 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

How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

Question has a verified solution.

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

Suggested Solutions

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …

830 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