Solved

Create a View that runs a Case Statement Containing a View

Posted on 2008-10-15
8
189 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
 

Author Comment

by:IEHP1
ID: 22732704
ok but can I run a procedure from a view?  
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

708 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now