Create a View that runs a Case Statement Containing a View

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
IEHP1Asked:
Who is Participating?
 
jfmadorCommented:
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
 
jfmadorCommented:
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
 
IEHP1Author Commented:
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
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
jfmadorCommented:
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
 
IEHP1Author Commented:
ok but can I run a procedure from a view?  
0
 
jfmadorCommented:
No, why you need it as a view?
0
 
IEHP1Author Commented:
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
 
IEHP1Author Commented:
Yes that's what I thought, thanks for your help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.