why this is not selecting the job manger?-CodePudding

This is the about my error, which you can see in the screenshot:

enter image description here

CodePudding user response：

You start by using ALTER TABLE employee MODIFY job VARCHAR(20); which implies that you converted the value from one data type to VARCHAR(20), which is a variable-length string. I am assuming that it was previously a CHAR(20), which is a fixed-width string and would have padded the string to the maximum size with space characters.

You can see if there are characters after the visible ones that are not being displayed:

SELECT ename
FROM   employee
WHERE  JOB LIKE 'Manager%'

If there are then you can use:

SELECT DUMP(job)
FROM   employee
WHERE  JOB LIKE 'Manager%'

To see what characters they are.

If it is just trailing spaces then you can use:

UPDATE employee
SET Job = RTRIM(Job)
-- WHERE job LIKE 'Manager%'
;

To remove the trailing spaces and then any of your queries should work.

CodePudding user response：

The alter table statement at the top of your image suggests that the column was initially char(20), which means the values were - and still are - padded with spaces.

So your query would work if you also padded the literal with spaces to the same length:

select ename from employee where job = 'Manager             '

Or if you used like with a wildcard character (which is missing from your attempt), though this could match any job title that just start with 'Manager':

select ename from employee where job like 'Manager%'

Or if you trimmed the column value before comparing:

select ename from employee where rtrim(job) = 'Manager'

But you probably want to remove the trailing spaces as a one-off operation after converting:

alter table employee modify job varchar2(20);
update employee set job = rtrim(job);
select ename from employee where job = 'Manager';

db<>fiddle

I've used varchar2 rather than varchar, as Oracle recommend.

You can read more about blank-padded and nonpadded comparison semantics in the documentation.

CodePudding user response：

The problem is that you did not select the JOB column.

Try this code:

SELECT ENAME,JOB FROM employee WHERE JOB ='Manager';