August 20, 2014 at 3:55 pm
I am creating a view with below script from 3 tables. I am having problems when a HCPCS is found in MUES_OUT_2010_07 and MUES_DME_2010_07 and not in MUES_PHY_2010_07
CREATE VIEW MUES AS
SELECT COALESCE(TPHY.HCPCS, TOUT.HCPCS, TDME.HCPCS) AS HCPCS
, COALESCE(UNITS_PHY, -1) AS UNITS_PHY
, COALESCE(UNITS_OUT, -1) AS UNITS_OUT
, COALESCE(UNITS_DME, -1) AS UNITS_DME
FROM MUES_PHY_2010_07 TPHY
FULL OUTER JOIN MUES_OUT_2010_07 TOUT
ON TPHY.HCPCS = TOUT.HCPCS
FULL OUTER JOIN MUES_DME_2010_07 TDME
ON TPHY.HCPCS = TDME.HCPCS
GO
My tables are created like below
CREATE TABLE [dbo].[MUES_OUT_2010_07](
[HCPCS] [char](5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[UNITS_OUT] [smallint] NULL,
CONSTRAINT [PK_MUES_OUT_2010_07] PRIMARY KEY CLUSTERED
(
[HCPCS] ASC
)WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[MUES_DME_2010_07](
[HCPCS] [char](5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[UNITS_DME] [smallint] NULL,
CONSTRAINT [PK_MUES_DME_2010_07] PRIMARY KEY CLUSTERED
(
[HCPCS] ASC
)WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[MUES_PHY_2010_07](
[HCPCS] [char](5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[UNITS_PHY] [smallint] NULL,
CONSTRAINT [PK_MUES_DME_2010_07] PRIMARY KEY CLUSTERED
(
[HCPCS] ASC
)WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]
GO
insert into MUES_OUT_2010_07 select 'E1036', 1
insert into MUES_DME_2010_07 select 'E1036', 1
i expect the view to create a one record like below since HCPCS 'E1036' is not in the MUES_PHY_2010_07 table
HCPCS UNITS_PHY UNITS_OUT UNITS_DME
E1036 -1 1 1
but it creates two records like below
E1036 -1 -1 1
E1036 -1 1 -1
what am i doing wrong in the view
August 20, 2014 at 4:43 pm
Your problem is the double FULL OUTER JOIN as you can see if you use SELECT * to show all rows and columns.
Here's an alternative, but you should reevaluate the need of the full joins.
SELECT COALESCE(TPHY.HCPCS, TOUT.HCPCS, TDME.HCPCS) AS HCPCS
, COALESCE(MAX(UNITS_PHY), -1) AS UNITS_PHY
, COALESCE(MAX(UNITS_OUT), -1) AS UNITS_OUT
, COALESCE(MAX(UNITS_DME), -1) AS UNITS_DME
FROM MUES_PHY_2010_07 TPHY
FULL JOIN MUES_OUT_2010_07 TOUT ON TPHY.HCPCS = TOUT.HCPCS
FULL JOIN MUES_DME_2010_07 TDME ON TPHY.HCPCS = TDME.HCPCS
GROUP BY COALESCE(TPHY.HCPCS, TOUT.HCPCS, TDME.HCPCS)
August 21, 2014 at 8:01 am
Thanks. your reply fixed my issue
August 21, 2014 at 8:13 am
But why the MAX function?
August 21, 2014 at 8:41 am
I used the MAX function because I'm not sure what kind of data you need, so it will aggregate the data to get a single result. You might need a SUM() or AVG(), but that's your decision as I don't know your exact requirements.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply