Link to home
Start Free TrialLog in
Avatar of IEHP1
IEHP1Flag for United States of America

asked on

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
Avatar of jfmador
jfmador
Flag of Canada image

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
Avatar of IEHP1

ASKER

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)

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
Avatar of IEHP1

ASKER

ok but can I run a procedure from a view?  
No, why you need it as a view?
Avatar of IEHP1

ASKER

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.
ASKER CERTIFIED SOLUTION
Avatar of jfmador
jfmador
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of IEHP1

ASKER

Yes that's what I thought, thanks for your help.