November 1, 2017 at 4:23 am
Dear DBAs
I have a database which includes values such as:
Main entrance In
Main exit out
Main entrance out
etc
So i need to run a query to classify which terminal is ENTRY and which one is EXIT
I thought of using a CASE statement in addition to OR statements and my result was as follows:
Terminal Direction
CANADA-ADMIN GRND SERV CR IN ENTRY
L22-EMC 2 DOOR 43 NULL
MCR MAIN DOOR CR OUT EXIT
My only issue is that while using the CASE statement the END section defaults to NULL for statements not satisfying any of the conditions so how do i not display the null rows? In the example above i dont want any rows where Direction is NULL to display
Hurricane
November 1, 2017 at 4:40 am
Hi Thom
I already tried and here is my query but its not working
SELECT distinct x_term_name, Direction=
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'
--ELSE 'N/A'
END
FROM Table x
where Direction <> NULL
But the term Direction isnt recognized
Hurricane
November 1, 2017 at 4:45 am
WHERE Direction IS NOT NULL
John
November 1, 2017 at 4:46 am
Yes i updated the query but the issue isnt there its with this
Msg 207, Level 16, State 1, Line 12
Invalid column name 'Terminal'.
November 1, 2017 at 4:47 am
You can place your logic in a CTE and then use your WHERE clause when selecting from the CTE.
Probably not the best solution but still an option
November 1, 2017 at 4:48 am
hurricaneDBA - Wednesday, November 1, 2017 4:46 AMYes i updated the query but the issue isnt there its with this
Msg 207, Level 16, State 1, Line 12
Invalid column name 'Terminal'.
Can you please post the full new query and your DDL and DLM please? The query you posted above posted even contain the column terminal, so we're missing something here.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
November 1, 2017 at 4:51 am
The full query is
SELECT distinct x_term_name,Direction =
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'
--ELSE 'N/A'
END
FROM Table x
where Direction IS NOT NULL
I cant post the DDL as its confidential, you know how it is 🙂
November 1, 2017 at 4:55 am
hurricaneDBA - Wednesday, November 1, 2017 4:51 AMThe full query isSELECT distinct x_term_name,Direction =
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'
--ELSE 'N/A'
END
FROM Table x
where Direction IS NOT NULL
I cant post the DDL as its confidential, you know how it is 🙂
Terminal isn't even mentioned in this statement, so the error "Invalid column name 'Terminal'." is not relevant to that query.
And, no, DDL isn't confidential. I'm more than happy to share the DDL of our Customer Table, or even financial. If you need to rename a couple of columns, then do so, but DDL doesn't contain any data. It's DLM that does and, again, you can always obscure that.
Table x actually some kind of View?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
November 1, 2017 at 5:00 am
Dear Thom
Terminal is the column x_term_name in my table and the DDL for my table which is called xaction :
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 1, 2017 at 5:08 am
OK, now I see your problem. Your initial post was misleading, as it made it seem that the name of the columns in your table were terminal and Direction; they are not it's x_term_name.
In your WHERE you can't reference Direction, as it doesn't exist. Your WHERE is processed prior to your SELECT, so there is no column Direction (have a look here: https://docs.microsoft.com/en-us/sql/t-sql/queries/select-transact-sql).
Paulo, therefore, has the right idea, and use a CTE:
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
November 1, 2017 at 5:23 am
Dear Thom
My apologies for the incomplete information and yes i tried using CTEs and it works great
Thank you Paulo and Thom
Have a lovely remainder of the week
Hurricane
November 1, 2017 at 5:35 am
hurricaneDBA - Wednesday, November 1, 2017 5:23 AMDear Thom
My apologies for the incomplete information and yes i tried using CTEs and it works great
Thank you Paulo and ThomHave a lovely remainder of the week
Hurricane
Glad to hear you got there in the end. 🙂
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
November 1, 2017 at 5:43 am
A possibility?SELECT DISTINCT
x.term_name
, dir.Direction
FROM
tbl x
CROSS APPLY
(
SELECT Direction = 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
) dir
WHERE dir.Direction IS NOT NULL;
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply