November 12, 2017 at 12:58 am
Dear DBAs
I am using a CTE to get terminal locations from one table and then joining this CTE with 2 more tables to get the time and attendance from those tables.
The results appear fine when i use 2 tables but as soon as i join the CTE to the mix the values become duplicates
The CTE is below:
WITH CTE AS(
SELECT distinct
x_term_name,
CASE WHEN x_term_name like '%OUT'
OR x_term_name like '% OUT 2'
OR x_term_name like '%STAIR %' THEN 'EXIT'
WHEN x_term_name like '%IN'
OR x_term_name like '% IN 2' THEN 'ENTRY'
END AS Direction
FROM Pegasys.dbo.xaction)
THe other query is below:
select distinct SUBSTRING(LTRIM(RTRIM(l.c_nick_name)), PATINDEX('%[^0]%', LTRIM(RTRIM(l.c_nick_name))+'.'), LEN(LTRIM(RTRIM(l.c_nick_name)))),
RTRIM(LTRIM(DATEPART(YEAR, m.x_timestamp) * 10000 + DATEPART(MONTH, m.x_timestamp) * 100 + DATEPART(DAY,m.x_timestamp))),
(CONVERT(VARCHAR(8),m.x_timestamp,108))--, n.Direction
from Pegasys.dbo.badge k
inner join Pegasys.dbo.cardholder l ON k.b_cardholder_id=l.c_id
inner JOIN Pegasys.dbo.xaction m ON k.b_number_str = m.x_badge_number
LEFT JOIN CTE_Dir n on k.b_number_str = n.x_badge_number
where
n.Direction IS NOT NULL
AND
c_dept_id is NOT NULL
AND CONVERT(VARCHAR(25), m.x_timestamp , 103) = CONVERT(VARCHAR(25), GETDate(), 103)
AND l.c_nick_name <> ' '
AND SUBSTRING(LTRIM(RTRIM(l.c_nick_name)), PATINDEX('%[^0]%', LTRIM(RTRIM(l.c_nick_name))+'.'), LEN(LTRIM(RTRIM(l.c_nick_name)))) <>''
AND l.c_nick_name NOT like '%[A-Z]%'
--AND n.Direction IS NOT NULL
--AND m.x_badge_number IS NOT NULL
ORDER BY SUBSTRING(LTRIM(RTRIM(l.c_nick_name)), PATINDEX('%[^0]%', LTRIM(RTRIM(l.c_nick_name))+'.'), LEN(LTRIM(RTRIM(l.c_nick_name)))), (CONVERT(VARCHAR(8),m.x_timestamp,108))
If the run the above query without including n.direction then its shows
10054 20171112 07:19:16
10054 20171112 08:17:34
If i do include direction then it becomes:
10054 20171112 07:19:16 ENTRY
10054 20171112 07:19:16 EXIT
Why is it picking up both exit and entry?
Does the CASE bring in all possibilities or is it in my CTE?
have a good weekend
Hurricane
November 12, 2017 at 4:16 am
Post the DDL (create table) scripts, sample data as an insert statement, what you have tried so far and the expected results please!
😎
Quick thought, seeing DISTINCT scattered all over tells me that there might be a data issue here.
November 12, 2017 at 5:11 am
Hi Eirikur
I was able to use to the same table and get the case statement to work there however the CASE statement if not satisfied yields a NULL and i have been searching online for a way to ignore the nulls from the output but with no success
Here is my updated statement
select distinct SUBSTRING(LTRIM(RTRIM(l.c_nick_name)), PATINDEX('%[^0]%', LTRIM(RTRIM(l.c_nick_name))+'.'), LEN(LTRIM(RTRIM(l.c_nick_name)))),
RTRIM(LTRIM(DATEPART(YEAR, m.x_timestamp) * 10000 + DATEPART(MONTH, m.x_timestamp) * 100 + DATEPART(DAY,m.x_timestamp))),
(CONVERT(VARCHAR(8),m.x_timestamp,108)),
CASE
WHEN RTRIM(m.x_term_name) like '%OUT'THEN 'EXIT'
WHEN RTRIM(m.x_term_name) like '% OUT 2' THEN 'EXIT'
WHEN RTRIM(m.x_term_name) like '%STAIR %' THEN 'EXIT'
WHEN RTRIM(m.x_term_name) like '%IN' THEN 'ENTRY'
WHEN RTRIM(m.x_term_name) like '% IN 2' THEN 'ENTRY'
END AS Direction
from Pegasys.dbo.badge k
LEFT OUTER JOIN Pegasys.dbo.cardholder l ON k.b_cardholder_id=l.c_id
LEFT OUTER JOIN Pegasys.dbo.xaction m ON k.b_number_str = m.x_badge_number
where
--Direction IS NOT NULL
c_dept_id is NOT NULL
AND CONVERT(VARCHAR(25), m.x_timestamp , 103) = CONVERT(VARCHAR(25), GETDate(), 103)
AND l.c_nick_name <> ' '
AND SUBSTRING(LTRIM(RTRIM(l.c_nick_name)), PATINDEX('%[^0]%', LTRIM(RTRIM(l.c_nick_name))+'.'), LEN(LTRIM(RTRIM(l.c_nick_name)))) <>''
AND l.c_nick_name NOT like '%[A-Z]%'
--AND n.Direction IS NOT NULL
--AND m.x_badge_number IS NOT NULL
ORDER BY SUBSTRING(LTRIM(RTRIM(l.c_nick_name)), PATINDEX('%[^0]%', LTRIM(RTRIM(l.c_nick_name))+'.'), LEN(LTRIM(RTRIM(l.c_nick_name)))), (CONVERT(VARCHAR(8),m.x_timestamp,108))
November 12, 2017 at 5:44 am
hurricaneDBA - Sunday, November 12, 2017 5:11 AMHi Eirikur
I was able to use to the same table and get the case statement to work there however the CASE statement if not satisfied yields a NULL and i have been searching online for a way to ignore the nulls from the output but with no success
Good that you got some of your query, working, however...
Eirikur Eiriksson - Sunday, November 12, 2017 4:16 AMPost the DDL (create table) scripts, sample data as an insert statement, what you have tried so far and the expected results please!
😎
We still can't help you if you don't help us help you. 🙂
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
November 12, 2017 at 6:00 am
Give me until tomorrow and I’ll send you the ddl for all 3 tables
Kal
November 12, 2017 at 6:02 am
A general observation: CASE expressions do not 'bring anything in', nor do they 'take anything out'.
If your query works fine without joining to a CTE but results in duplicates after the join, there is only one reason: the CTE contains duplicates. Not necessarily of the whole row, but of the column, or columns, being joined on.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
November 12, 2017 at 11:44 pm
Hi Guys
Here are the DDLs for the 3 tables
Badge
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[badge](
[b_partition] [int] NULL,
[b_public] [smallint] NULL,
[b_number_str] [varchar](100) NOT NULL,
[b_cardholder_id] [int] NULL,
[b_alpha] [char](4) NULL,
[b_issue] [smallint] NULL,
[b_description] [char](64) NULL,
[b_disabled] [smallint] NOT NULL,
[b_exp_timestamp] [datetime] NULL,
[b_start_timestamp] [datetime] NULL,
[b_pin] [int] NULL,
[b_event_priv] [smallint] NULL,
[b_exec_priv] [smallint] NULL,
[b_trace] [smallint] NULL,
[b_override] [smallint] NULL,
[b_access_grp_0] [int] NULL,
[b_access_grp_1] [int] NULL,
[b_access_grp_2] [int] NULL,
[b_access_grp_3] [int] NULL,
[b_access_grp_4] [int] NULL,
[b_access_grp_5] [int] NULL,
[b_access_grp_6] [int] NULL,
[b_access_grp_7] [int] NULL,
[b_access_tz_0] [int] NULL,
[b_access_tz_1] [int] NULL,
[b_access_tz_2] [int] NULL,
[b_access_tz_3] [int] NULL,
[b_access_tz_4] [int] NULL,
[b_access_tz_5] [int] NULL,
[b_access_tz_6] [int] NULL,
[b_access_tz_7] [int] NULL,
[b_reason] [char](32) NULL,
[b_design] [int] NULL,
[b_interlock1] [int] NULL,
[b_interlock1_value] [float] NULL,
[b_interlock2] [int] NULL,
[b_interlock2_value] [float] NULL,
[b_priority] [smallint] NULL,
[b_dnld_sti_e] [smallint] NULL,
[b_ag_start0] [datetime] NULL,
[b_ag_start1] [datetime] NULL,
[b_ag_start2] [datetime] NULL,
[b_ag_start3] [datetime] NULL,
[b_ag_start4] [datetime] NULL,
[b_ag_start5] [datetime] NULL,
[b_ag_start6] [datetime] NULL,
[b_ag_start7] [datetime] NULL,
[b_ag_end0] [datetime] NULL,
[b_ag_end1] [datetime] NULL,
[b_ag_end2] [datetime] NULL,
[b_ag_end3] [datetime] NULL,
[b_ag_end4] [datetime] NULL,
[b_ag_end5] [datetime] NULL,
[b_ag_end6] [datetime] NULL,
[b_ag_end7] [datetime] NULL,
[b_facility_code] [int] NULL,
[b_security_level] [smallint] NULL,
[b_flag_a] [smallint] NULL,
[b_flag_b] [smallint] NULL,
[b_flag_c] [smallint] NULL,
[b_block_xaction] [smallint] NULL,
[site] [char](32) NOT NULL,
[b_guid] [uniqueidentifier] ROWGUIDCOL NOT NULL,
[b_checksum] [int] NOT NULL,
[b_access_grp_8] [int] NULL,
[b_access_grp_9] [int] NULL,
[b_access_grp_10] [int] NULL,
[b_access_grp_11] [int] NULL,
[b_access_grp_12] [int] NULL,
[b_access_grp_13] [int] NULL,
[b_access_grp_14] [int] NULL,
[b_access_grp_15] [int] NULL,
[b_access_grp_16] [int] NULL,
[b_access_grp_17] [int] NULL,
[b_access_grp_18] [int] NULL,
[b_access_grp_19] [int] NULL,
[b_access_grp_20] [int] NULL,
[b_access_grp_21] [int] NULL,
[b_access_grp_22] [int] NULL,
[b_access_grp_23] [int] NULL,
[b_access_grp_24] [int] NULL,
[b_access_grp_25] [int] NULL,
[b_access_grp_26] [int] NULL,
[b_access_grp_27] [int] NULL,
[b_access_grp_28] [int] NULL,
[b_access_grp_29] [int] NULL,
[b_access_grp_30] [int] NULL,
[b_access_grp_31] [int] NULL,
[b_access_tz_8] [int] NULL,
[b_access_tz_9] [int] NULL,
[b_access_tz_10] [int] NULL,
[b_access_tz_11] [int] NULL,
[b_access_tz_12] [int] NULL,
[b_access_tz_13] [int] NULL,
[b_access_tz_14] [int] NULL,
[b_access_tz_15] [int] NULL,
[b_access_tz_16] [int] NULL,
[b_access_tz_17] [int] NULL,
[b_access_tz_18] [int] NULL,
[b_access_tz_19] [int] NULL,
[b_access_tz_20] [int] NULL,
[b_access_tz_21] [int] NULL,
[b_access_tz_22] [int] NULL,
[b_access_tz_23] [int] NULL,
[b_access_tz_24] [int] NULL,
[b_access_tz_25] [int] NULL,
[b_access_tz_26] [int] NULL,
[b_access_tz_27] [int] NULL,
[b_access_tz_28] [int] NULL,
[b_access_tz_29] [int] NULL,
[b_access_tz_30] [int] NULL,
[b_access_tz_31] [int] NULL,
[b_ag_start8] [datetime] NULL,
[b_ag_start9] [datetime] NULL,
[b_ag_start10] [datetime] NULL,
[b_ag_start11] [datetime] NULL,
[b_ag_start12] [datetime] NULL,
[b_ag_start13] [datetime] NULL,
[b_ag_start14] [datetime] NULL,
[b_ag_start15] [datetime] NULL,
[b_ag_start16] [datetime] NULL,
[b_ag_start17] [datetime] NULL,
[b_ag_start18] [datetime] NULL,
[b_ag_start19] [datetime] NULL,
[b_ag_start20] [datetime] NULL,
[b_ag_start21] [datetime] NULL,
[b_ag_start22] [datetime] NULL,
[b_ag_start23] [datetime] NULL,
[b_ag_start24] [datetime] NULL,
[b_ag_start25] [datetime] NULL,
[b_ag_start26] [datetime] NULL,
[b_ag_start27] [datetime] NULL,
[b_ag_start28] [datetime] NULL,
[b_ag_start29] [datetime] NULL,
[b_ag_start30] [datetime] NULL,
[b_ag_start31] [datetime] NULL,
[b_ag_end8] [datetime] NULL,
[b_ag_end9] [datetime] NULL,
[b_ag_end10] [datetime] NULL,
[b_ag_end11] [datetime] NULL,
[b_ag_end12] [datetime] NULL,
[b_ag_end13] [datetime] NULL,
[b_ag_end14] [datetime] NULL,
[b_ag_end15] [datetime] NULL,
[b_ag_end16] [datetime] NULL,
[b_ag_end17] [datetime] NULL,
[b_ag_end18] [datetime] NULL,
[b_ag_end19] [datetime] NULL,
[b_ag_end20] [datetime] NULL,
[b_ag_end21] [datetime] NULL,
[b_ag_end22] [datetime] NULL,
[b_ag_end23] [datetime] NULL,
[b_ag_end24] [datetime] NULL,
[b_ag_end25] [datetime] NULL,
[b_ag_end26] [datetime] NULL,
[b_ag_end27] [datetime] NULL,
[b_ag_end28] [datetime] NULL,
[b_ag_end29] [datetime] NULL,
[b_ag_end30] [datetime] NULL,
[b_ag_end31] [datetime] NULL,
[b_purpose] [uniqueidentifier] NULL,
[b_data_style] [smallint] NOT NULL,
[b_default_floor] [smallint] NULL,
[b_otis_pin_floor_mask] [int] NULL,
[b_operator] [char](32) NULL,
[b_edit_time] [datetime] NULL,
[b_technology] [int] NOT NULL,
[b_bits] [int] NOT NULL,
[b_qualifier] [int] NOT NULL,
PRIMARY KEY CLUSTERED
(
[b_number_str] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
UNIQUE NONCLUSTERED
(
[b_guid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[badge] WITH NOCHECK ADD FOREIGN KEY([b_access_grp_0])
REFERENCES [dbo].[accgroup] ([ag_id])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[badge] WITH NOCHECK ADD FOREIGN KEY([b_access_grp_1])
REFERENCES [dbo].[accgroup] ([ag_id])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[badge] WITH NOCHECK ADD FOREIGN KEY([b_access_grp_2])
REFERENCES [dbo].[accgroup] ([ag_id])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[badge] WITH NOCHECK ADD FOREIGN KEY([b_access_grp_3])
REFERENCES [dbo].[accgroup] ([ag_id])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[badge] WITH NOCHECK ADD FOREIGN KEY([b_access_grp_4])
REFERENCES [dbo].[accgroup] ([ag_id])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[badge] WITH NOCHECK ADD FOREIGN KEY([b_access_grp_5])
REFERENCES [dbo].[accgroup] ([ag_id])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[badge] WITH NOCHECK ADD FOREIGN KEY([b_access_grp_6])
REFERENCES [dbo].[accgroup] ([ag_id])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[badge] WITH NOCHECK ADD FOREIGN KEY([b_access_grp_7])
REFERENCES [dbo].[accgroup] ([ag_id])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[badge] WITH NOCHECK ADD FOREIGN KEY([b_access_tz_0])
REFERENCES [dbo].[timezone] ([tz_id])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[badge] WITH NOCHECK ADD FOREIGN KEY([b_access_tz_1])
REFERENCES [dbo].[timezone] ([tz_id])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[badge] WITH NOCHECK ADD FOREIGN KEY([b_access_tz_2])
REFERENCES [dbo].[timezone] ([tz_id])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[badge] WITH NOCHECK ADD FOREIGN KEY([b_access_tz_3])
REFERENCES [dbo].[timezone] ([tz_id])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[badge] WITH NOCHECK ADD FOREIGN KEY([b_access_tz_4])
REFERENCES [dbo].[timezone] ([tz_id])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[badge] WITH NOCHECK ADD FOREIGN KEY([b_access_tz_5])
REFERENCES [dbo].[timezone] ([tz_id])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[badge] WITH NOCHECK ADD FOREIGN KEY([b_access_tz_6])
REFERENCES [dbo].[timezone] ([tz_id])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[badge] WITH NOCHECK ADD FOREIGN KEY([b_access_tz_7])
REFERENCES [dbo].[timezone] ([tz_id])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[badge] WITH NOCHECK ADD FOREIGN KEY([b_access_grp_8])
REFERENCES [dbo].[accgroup] ([ag_id])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[badge] WITH NOCHECK ADD FOREIGN KEY([b_access_grp_9])
REFERENCES [dbo].[accgroup] ([ag_id])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[badge] WITH NOCHECK ADD FOREIGN KEY([b_access_grp_10])
REFERENCES [dbo].[accgroup] ([ag_id])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[badge] WITH NOCHECK ADD FOREIGN KEY([b_access_grp_11])
REFERENCES [dbo].[accgroup] ([ag_id])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[badge] WITH NOCHECK ADD FOREIGN KEY([b_access_grp_12])
REFERENCES [dbo].[accgroup] ([ag_id])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[badge] WITH NOCHECK ADD FOREIGN KEY([b_access_grp_13])
REFERENCES [dbo].[accgroup] ([ag_id])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[badge] WITH NOCHECK ADD FOREIGN KEY([b_access_grp_14])
REFERENCES [dbo].[accgroup] ([ag_id])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[badge] WITH NOCHECK ADD FOREIGN KEY([b_access_grp_15])
REFERENCES [dbo].[accgroup] ([ag_id])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[badge] WITH NOCHECK ADD FOREIGN KEY([b_access_grp_16])
REFERENCES [dbo].[accgroup] ([ag_id])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[badge] WITH NOCHECK ADD FOREIGN KEY([b_access_grp_17])
REFERENCES [dbo].[accgroup] ([ag_id])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[badge] WITH NOCHECK ADD FOREIGN KEY([b_access_grp_18])
REFERENCES [dbo].[accgroup] ([ag_id])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[badge] WITH NOCHECK ADD FOREIGN KEY([b_access_grp_19])
REFERENCES [dbo].[accgroup] ([ag_id])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[badge] WITH NOCHECK ADD FOREIGN KEY([b_access_grp_20])
REFERENCES [dbo].[accgroup] ([ag_id])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[badge] WITH NOCHECK ADD FOREIGN KEY([b_access_grp_21])
REFERENCES [dbo].[accgroup] ([ag_id])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[badge] WITH NOCHECK ADD FOREIGN KEY([b_access_grp_22])
REFERENCES [dbo].[accgroup] ([ag_id])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[badge] WITH NOCHECK ADD FOREIGN KEY([b_access_grp_23])
REFERENCES [dbo].[accgroup] ([ag_id])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[badge] WITH NOCHECK ADD FOREIGN KEY([b_access_grp_24])
REFERENCES [dbo].[accgroup] ([ag_id])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[badge] WITH NOCHECK ADD FOREIGN KEY([b_access_grp_25])
REFERENCES [dbo].[accgroup] ([ag_id])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[badge] WITH NOCHECK ADD FOREIGN KEY([b_access_grp_26])
REFERENCES [dbo].[accgroup] ([ag_id])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[badge] WITH NOCHECK ADD FOREIGN KEY([b_access_grp_27])
REFERENCES [dbo].[accgroup] ([ag_id])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[badge] WITH NOCHECK ADD FOREIGN KEY([b_access_grp_28])
REFERENCES [dbo].[accgroup] ([ag_id])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[badge] WITH NOCHECK ADD FOREIGN KEY([b_access_grp_29])
REFERENCES [dbo].[accgroup] ([ag_id])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[badge] WITH NOCHECK ADD FOREIGN KEY([b_access_grp_30])
REFERENCES [dbo].[accgroup] ([ag_id])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[badge] WITH NOCHECK ADD FOREIGN KEY([b_access_grp_31])
REFERENCES [dbo].[accgroup] ([ag_id])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[badge] WITH NOCHECK ADD FOREIGN KEY([b_access_tz_8])
REFERENCES [dbo].[timezone] ([tz_id])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[badge] WITH NOCHECK ADD FOREIGN KEY([b_access_tz_9])
REFERENCES [dbo].[timezone] ([tz_id])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[badge] WITH NOCHECK ADD FOREIGN KEY([b_access_tz_10])
REFERENCES [dbo].[timezone] ([tz_id])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[badge] WITH NOCHECK ADD FOREIGN KEY([b_access_tz_11])
REFERENCES [dbo].[timezone] ([tz_id])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[badge] WITH NOCHECK ADD FOREIGN KEY([b_access_tz_12])
REFERENCES [dbo].[timezone] ([tz_id])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[badge] WITH NOCHECK ADD FOREIGN KEY([b_access_tz_13])
REFERENCES [dbo].[timezone] ([tz_id])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[badge] WITH NOCHECK ADD FOREIGN KEY([b_access_tz_14])
REFERENCES [dbo].[timezone] ([tz_id])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[badge] WITH NOCHECK ADD FOREIGN KEY([b_access_tz_15])
REFERENCES [dbo].[timezone] ([tz_id])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[badge] WITH NOCHECK ADD FOREIGN KEY([b_access_tz_16])
REFERENCES [dbo].[timezone] ([tz_id])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[badge] WITH NOCHECK ADD FOREIGN KEY([b_access_tz_17])
REFERENCES [dbo].[timezone] ([tz_id])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[badge] WITH NOCHECK ADD FOREIGN KEY([b_access_tz_18])
REFERENCES [dbo].[timezone] ([tz_id])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[badge] WITH NOCHECK ADD FOREIGN KEY([b_access_tz_19])
REFERENCES [dbo].[timezone] ([tz_id])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[badge] WITH NOCHECK ADD FOREIGN KEY([b_access_tz_20])
REFERENCES [dbo].[timezone] ([tz_id])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[badge] WITH NOCHECK ADD FOREIGN KEY([b_access_tz_21])
REFERENCES [dbo].[timezone] ([tz_id])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[badge] WITH NOCHECK ADD FOREIGN KEY([b_access_tz_22])
REFERENCES [dbo].[timezone] ([tz_id])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[badge] WITH NOCHECK ADD FOREIGN KEY([b_access_tz_23])
REFERENCES [dbo].[timezone] ([tz_id])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[badge] WITH NOCHECK ADD FOREIGN KEY([b_access_tz_24])
REFERENCES [dbo].[timezone] ([tz_id])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[badge] WITH NOCHECK ADD FOREIGN KEY([b_access_tz_25])
REFERENCES [dbo].[timezone] ([tz_id])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[badge] WITH NOCHECK ADD FOREIGN KEY([b_access_tz_26])
REFERENCES [dbo].[timezone] ([tz_id])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[badge] WITH NOCHECK ADD FOREIGN KEY([b_access_tz_27])
REFERENCES [dbo].[timezone] ([tz_id])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[badge] WITH NOCHECK ADD FOREIGN KEY([b_access_tz_28])
REFERENCES [dbo].[timezone] ([tz_id])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[badge] WITH NOCHECK ADD FOREIGN KEY([b_access_tz_29])
REFERENCES [dbo].[timezone] ([tz_id])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[badge] WITH NOCHECK ADD FOREIGN KEY([b_access_tz_30])
REFERENCES [dbo].[timezone] ([tz_id])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[badge] WITH NOCHECK ADD FOREIGN KEY([b_access_tz_31])
REFERENCES [dbo].[timezone] ([tz_id])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[badge] WITH NOCHECK ADD FOREIGN KEY([b_cardholder_id])
REFERENCES [dbo].[cardholder] ([c_id])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[badge] WITH NOCHECK ADD FOREIGN KEY([b_design])
REFERENCES [dbo].[badgelayout] ([bl_id])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[badge] WITH NOCHECK ADD FOREIGN KEY([b_interlock1])
REFERENCES [dbo].[actioninterlock] ([ai_id])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[badge] WITH NOCHECK ADD FOREIGN KEY([b_interlock2])
REFERENCES [dbo].[actioninterlock] ([ai_id])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[badge] WITH NOCHECK ADD FOREIGN KEY([b_otis_pin_floor_mask])
REFERENCES [dbo].[floormask] ([fm_id])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[badge] WITH NOCHECK ADD FOREIGN KEY([b_partition])
REFERENCES [dbo].[partition] ([part_number])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[badge] WITH NOCHECK ADD FOREIGN KEY([b_purpose])
REFERENCES [dbo].[badgepurpose] ([bp_guid])
NOT FOR REPLICATION
GO
cardholder
SET
ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[cardholder](
[c_partition] [int] NOT NULL,
[c_public] [smallint] NULL,
[c_id] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[c_lname] [char](25) NOT NULL,
[c_mname] [char](25) NULL,
[c_fname] [char](25) NOT NULL,
[c_nick_name] [char](25) NULL,
[c_addr] [char](64) NULL,
[c_addr1] [char](32) NULL,
[c_addr2] [char](32) NULL,
[c_addr3] [char](32) NULL,
[c_phone] [char](16) NULL,
[c_ext] [char](6) NULL,
[c_s_timestamp] [datetime] NULL,
[c_t_timestamp] [datetime] NULL,
[c_card_type] [smallint] NULL,
[c_dept_id] [int] NULL,
[c_company_id] [int] NULL,
[c_sponsor_id] [int] NULL,
[c_guard] [smallint] NULL,
[c_suite] [char](32) NULL,
[site] [char](32) NOT NULL,
[c_guid] [uniqueidentifier] ROWGUIDCOL NOT NULL,
[c_checksum] [int] NOT NULL,
[c_email] [char](64) NULL,
[c_operator] [char](32) NULL,
[c_edit_time] [datetime] NULL,
PRIMARY KEY CLUSTERED
(
[c_partition] ASC,
[c_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
UNIQUE NONCLUSTERED
(
[c_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
UNIQUE NONCLUSTERED
(
[c_guid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[cardholder] WITH NOCHECK ADD FOREIGN KEY([c_company_id])
REFERENCES [dbo].[company] ([company_id])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[cardholder] WITH NOCHECK ADD FOREIGN KEY([c_dept_id])
REFERENCES [dbo].[dept] ([dept_id])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[cardholder] WITH NOCHECK ADD FOREIGN KEY([c_partition])
REFERENCES [dbo].[partition] ([part_number])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[cardholder] WITH NOCHECK ADD FOREIGN KEY([c_sponsor_id])
REFERENCES [dbo].[cardholder] ([c_id])
NOT FOR REPLICATION
GO
xaction
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[xaction](
[x_hist_type] [int] NULL,
[x_panel_name] [char](32) NULL,
[x_term_name] [char](32) NULL,
[x_item_name] [char](32) NULL,
[x_badge_number] [varchar](100) NULL,
[x_fname] [char](26) NULL,
[x_lname] [char](26) NULL,
[x_timed_overrd] [smallint] NULL,
[x_issue_level] [int] NULL,
[x_fac_code] [int] NULL,
[x_event_name] [char](32) NULL,
[x_timestamp] [datetime] NOT NULL,
[site] [char](32) NULL,
[x_partition] [char](32) NULL,
[x_public] [smallint] NOT NULL,
[x_guid] [uniqueidentifier] ROWGUIDCOL NOT NULL,
[x_checksum] [int] NOT NULL,
PRIMARY KEY NONCLUSTERED
(
[x_guid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[xaction] ADD DEFAULT (newid()) FOR [x_guid]
GO
November 12, 2017 at 11:50 pm
I tried getting sample data but the rows are too long
November 13, 2017 at 2:05 am
Ok, let's all turn this into something a bit more "readable".
Next, we can't actually create those tables as they are, because you have foreign key constraints for tables you haven't supplied. So, when we're running them we'll need to not sue those. Also, talking about those Foreign Keys, this table is clearly not using 3NF. What is you're reasoning for this? Your badge table, I'm afraid, is quite a mess. it has 169 columns! You should really be using a composite key table (one that basically just stored a list of IDs for the two different tables). If you used one of these you'd cut that particular table's width down to about 40. It's another discussion, but I really suggest you ask about how to convert that table to 3NF in another topic. It'll make your life a lot easier in the long term.
Now, the sample data, this is going to be really important for us to replicate the problem. Without data, how can we hope to replicate it? Simple put; we can't. You're query, however, doesn't use all of those columns in your table, so, why don't you trim down your table and then provide sample data for that. In fact, as far as I can see, you only reference about 7 or 8 columns from all of those columns in that query (remember badge has 169 columns). Don't forget that if you are providing Foreign Keys we need those tables as well though.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
November 13, 2017 at 2:24 am
Hi Thom
This is a database which was provided by a vendor of ours and this is their database
I am just creating the queries based on the fields in the database
I will look at this issue and see if i can solve it
The question here is there is no way to stop the CASE statement from generating NULLs if the condition isnt met. Is this true?
thanks for the help in advance
kal
November 13, 2017 at 2:25 am
Thom
Which tool do you use to write SQL?
Just out of curiousity for myself 🙂
Kal
November 13, 2017 at 2:54 am
hurricaneDBA - Monday, November 13, 2017 2:25 AMThom
Which tool do you use to write SQL?
Just out of curiousity for myself 🙂Kal
I use SSMS. For SSC, however, I use VS Code to copy and paste from, as it maintains the formatting. I, and a lot of other users, have a lot of problems posting to SSC (losing white space, indentations, line breaks, etc, etc); thus i find it easier to copy and paste large code from a pre-formatted text editor and edit that, than using the IFCode markup.
hurricaneDBA - Monday, November 13, 2017 2:24 AMHi Thom
This is a database which was provided by a vendor of ours and this is their database
I am just creating the queries based on the fields in the database
I will look at this issue and see if i can solve itThe question here is there is no way to stop the CASE statement from generating NULLs if the condition isnt met. Is this true?
thanks for the help in advance
kal
Ahh... Well that explains a lot. There are far too many "developers" out there that create applications and the data back end with no idea how to really set it up properly. For example, in your CREATE statement for badge, you can see that the dev has had to add more columns later on (rather than using 3NF), as columns 8 onwards are declared later in the CREATE statement.
Anyway, like I said before, that's a different discussion. I've recreated some DDL for you, but without all the extra columns. Then I've also provided your statement for you again. Can you supply some Consumable Sample Data to go in these tables (I've started the statements off for you)? YOu need to ensure the sample data you provide shows the problem you are having. if you can't replicate the problem, neither will we. I've changed the names of some of your columns and objects here (that's intentional so you know you're not working with your original data).
Thanks!
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
November 13, 2017 at 5:40 am
hurricaneDBA - Monday, November 13, 2017 2:24 AMThe question here is there is no way to stop the CASE statement from generating NULLs if the condition isnt met. Is this true?kal
CASE statements do not and cannot filter out unwanted rows.
But there is nothing to stop you filtering out those rows in the WHERE clause.
select Col1 = (case ... end)
where (case ... end) is not null
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
November 14, 2017 at 6:01 am
Thanks Phil
That solved my NULL issue and for Thom my sample data is below:
12354 20171114 07:37:53 ENTRY
Thanks again
Kal
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply