September 28, 2016 at 11:05 am
Hello,
I am trying to make over a column which depends on 3 columns. Like combination of emp1,emp2,emp3 as ID)
select * form employee where(emp1,emp2,emp3) IN (select emp1,emp2,emp3 from employee)
I saw this type od syntax is sql server but in 2012 its not working.
Its giving error like Expression of non Boolean type specified in a context where a condition is expected ,near ','
September 28, 2016 at 11:23 am
What you are doing does not make much sense. Perhaps you could post some sample data and your desired results and we can point you in the right direction.
a couple things to note:
First, having multiple columns based on the same entity is a bad design.
Next, for better performance - if you have to combine columns like you are, consider using a hash key.
-- Itzik Ben-Gan 2001
September 28, 2016 at 11:42 am
There's no way logically that can work because you would be joining all three columns on all three columns. The syntax doesn't support it and the language doesn't either.
Can you try expanding on what it is that you're attempting to do?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 28, 2016 at 12:17 pm
Okay.
Here is the thing.
The data looks like from emp1,emp2 and emp3 as
DOM1000108806-00110
Here DOM is constant - this will not change
before hifen the data belongs to emp1
next 3 digits hifen the data comes form emp2
last 2 digits comes from emp3.
So I am planning to make over these 3 columns into that select statement.
September 28, 2016 at 1:06 pm
mcfarlandparkway (9/28/2016)
Okay.Here is the thing.
The data looks like from emp1,emp2 and emp3 as
DOM1000108806-00110
Here DOM is constant - this will not change
before hifen the data belongs to emp1
next 3 digits hifen the data comes form emp2
last 2 digits comes from emp3.
So I am planning to make over these 3 columns into that select statement.
Do you mean you just want to build the string from 3 different columns?
DECLARE @myTable TABLE (emp1 INT, emp2 INT, emp3 INT)
INSERT INTO @myTable
VALUES
(1000108806, 001, 10), (1000108905, 001, 11)
SELECT 'DOM' + RIGHT('0000000000' + CAST(emp1 AS VARCHAR(10)),10) + '-' + RIGHT('000' + CAST(emp2 AS VARCHAR(3)),3) + RIGHT('00' + CAST(emp3 AS VARCHAR(2)),2)
FROM @myTable
That code assumes a couple of things.
1. That the emp columns are int...if they are already strings you don't need to cast or pad them.
2. The length for the different components will always be the same.
At the very least you get some good examples.
September 28, 2016 at 1:28 pm
TY for the response, In the recent code we are selecting data form temp table where DOC NOT IN (select DOC from table where code<> -1)
This is what the same we are trying to implement here. DOC will replace with all these 3 columns(emp1,emp2 and emp3)
That's why I tried to separate with comma and again select those columns in the query.
Structure should be same..
that's why I tried select * from Employee where (emp1,emp2,emp3) IN (select emp1,emp2,emp3 from employee)
In the where clause I can use the code which you provided to CAST in the select statement looks good to me.
September 28, 2016 at 2:09 pm
mcfarlandparkway (9/28/2016)
TY for the response, In the recent code we are selecting data form temp table where DOC NOT IN (select DOC from table where code<> -1)This is what the same we are trying to implement here. DOC will replace with all these 3 columns(emp1,emp2 and emp3)
That's why I tried to separate with comma and again select those columns in the query.
Structure should be same..
that's why I tried select * from Employee where (emp1,emp2,emp3) IN (select emp1,emp2,emp3 from employee)
In the where clause I can use the code which you provided to CAST in the select statement looks good to me.
No, they should not be the same. One is a single column, the other is a list of columns. Those are not the same structures any more than an integer is the same structure as an array of integers.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
September 28, 2016 at 3:42 pm
drew.allen (9/28/2016)
mcfarlandparkway (9/28/2016)
TY for the response, In the recent code we are selecting data form temp table where DOC NOT IN (select DOC from table where code<> -1)This is what the same we are trying to implement here. DOC will replace with all these 3 columns(emp1,emp2 and emp3)
That's why I tried to separate with comma and again select those columns in the query.
Structure should be same..
that's why I tried select * from Employee where (emp1,emp2,emp3) IN (select emp1,emp2,emp3 from employee)
In the where clause I can use the code which you provided to CAST in the select statement looks good to me.
No, they should not be the same. One is a single column, the other is a list of columns. Those are not the same structures any more than an integer is the same structure as an array of integers.
Drew
Very much this. You can't simply refer to the columns and they'll magically get combined. You have to combine them. Minimum would be to use the plus (+) sign between them. Strings should naturally concatenate that way. However, that is going to lead to a tuning nightmare to filter on combined columns means no index use or statistics use.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 29, 2016 at 7:19 am
Yes, Those 3 columns are nvarchar and char
they are not int.
September 29, 2016 at 7:52 am
I am trying to make over a column which depends on 3 columns. Like combination of emp1,emp2,emp3 as ID)
SELECT * FROM Personnel WHERE (emp1,emp2,emp3) IN (SELECT emp1,emp2,emp3 FROM Personnel);
This is a little weird. Your list of "emp-??" Columns look like a repeated group, which would be a violation of first normal form. We never write "SELECT *" in production code. The table represents a set; do you really have only one employee as you said?
I think what you are trying to do is a feature that SQL Server does not have yet. That is the ability to compare constructed rows instead of having to write it out column by column. You want to do this, you will need to switch over to DB2.
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
September 29, 2016 at 12:14 pm
If you absolutely are identifying a row here base on the values of emp1, emp2 and emp3, then you can create a calculated field that persists in the table. This will make inserts a bit slower but the new field can be the subject of an index thus making selects perform reasonably well.
----------------------------------------------------
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply