IEHP1
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
Thanks
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.CAPRIMDAT E, dbo.CLAIM_DETAIL.CBSERVDAT E, CONVERT(datetime,
dbo.CLAIM_DETAIL.CBSERVDAT E, 101) AS ServiceDate, dbo.CLAIM_DETAIL.CBTHRUDAT E, dbo.CLAIM_HEADER.CAPROVIDE R,
dbo.CLAIM_HEADER.CAVENDOR, dbo.CLAIM_HEADER.CADX1, dbo.CLAIM_HEADER.CADX2, dbo.CLAIM_HEADER.CADX3,
dbo.CLAIM_DETAIL.CBPROCCOD E, dbo.CLAIM_DETAIL.CBQUANT, dbo.CLAIM_DETAIL.CBBILLED, dbo.CLAIM_DETAIL.CBNET,
dbo.CLAIM_HEADER.CAPLACE, dbo.CLAIM_DETAIL.CBCLAIMST AT, 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.CBPROCCOD E = dbo.Claims_CPTDX.Code
WHERE (dbo.CLAIM_DETAIL.CBCLAIMS TAT IN ('A', 'P')) AND (CONVERT(datetime, dbo.CLAIM_DETAIL.CBSERVDAT E, 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.CBSERVDAT E, 101) AS ServiceDate
FROM dbo.CLAIM_DETAIL INNER JOIN
dbo.CLAIM_HEADER ON dbo.CLAIM_DETAIL.CBDATE = dbo.CLAIM_HEADER.CAPRIMDAT E 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.CBCLAIMS TAT IN ('A', 'P')) AND (CONVERT(datetime, dbo.CLAIM_DETAIL.CBSERVDAT E, 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.CBSERVDAT E, 101) AS ServiceDate
FROM dbo.CLAIM_DETAIL INNER JOIN
dbo.CLAIM_HEADER ON dbo.CLAIM_DETAIL.CBDATE = dbo.CLAIM_HEADER.CAPRIMDAT E 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.CBCLAIMS TAT IN ('A', 'P')) AND (CONVERT(datetime, dbo.CLAIM_DETAIL.CBSERVDAT E, 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.CBSERVDAT E, 101) AS ServiceDate
FROM dbo.CLAIM_DETAIL INNER JOIN
dbo.CLAIM_HEADER ON dbo.CLAIM_DETAIL.CBDATE = dbo.CLAIM_HEADER.CAPRIMDAT E 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.CBCLAIMS TAT IN ('A', 'P')) AND (CONVERT(datetime, dbo.CLAIM_DETAIL.CBSERVDAT E, 101) BETWEEN dbo.Claims_CPTDX.BEG_DATE AND dbo.Claims_CPTDX.END_DATE)
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_DETAIL.CBSERVDAT
dbo.CLAIM_HEADER.CAVENDOR,
dbo.CLAIM_DETAIL.CBPROCCOD
dbo.CLAIM_HEADER.CAPLACE, dbo.CLAIM_DETAIL.CBCLAIMST
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.CBPROCCOD
WHERE (dbo.CLAIM_DETAIL.CBCLAIMS
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.CBSERVDAT
FROM dbo.CLAIM_DETAIL INNER JOIN
dbo.CLAIM_HEADER ON dbo.CLAIM_DETAIL.CBDATE = dbo.CLAIM_HEADER.CAPRIMDAT
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.CBCLAIMS
UNION ALL
SELECT dbo.Claims_CPTDX.CODE, dbo.CLAIM_HEADER.CADX1, dbo.CLAIM_HEADER.CADX2, dbo.CLAIM_HEADER.CADX3, CONVERT(datetime,
dbo.CLAIM_DETAIL.CBSERVDAT
FROM dbo.CLAIM_DETAIL INNER JOIN
dbo.CLAIM_HEADER ON dbo.CLAIM_DETAIL.CBDATE = dbo.CLAIM_HEADER.CAPRIMDAT
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.CBCLAIMS
UNION ALL
SELECT dbo.Claims_CPTDX.CODE, dbo.CLAIM_HEADER.CADX1, dbo.CLAIM_HEADER.CADX2, dbo.CLAIM_HEADER.CADX3, CONVERT(datetime,
dbo.CLAIM_DETAIL.CBSERVDAT
FROM dbo.CLAIM_DETAIL INNER JOIN
dbo.CLAIM_HEADER ON dbo.CLAIM_DETAIL.CBDATE = dbo.CLAIM_HEADER.CAPRIMDAT
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.CBCLAIMS
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
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
ASKER
ok but can I run a procedure from a view?
No, why you need it as a view?
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Yes that's what I thought, thanks for your help.
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