December 2, 2016 at 8:39 am
We have two different sources form one source1 data is picking and putting in another source2
This is source1
I have a column called NOMB1 where in the select list we are selecting like below.
The data from other view called Emp for this column NOMB1 is getting values like ('00000000' , '23567345')
SELECT SUBSTRING(Emp.NOMB1 , PATINDEX('%[^0]%', Emp.NOMB1 +'.'), LEN(Emp.NOMB1 )) as EmployeeNOM
In source 2 we are calling SP
In the stored procedure basically we are checking the condition
select * from Emp
where NOMB1 IS NOT NULL and NOMB1 NOT IN ('00000000')
As in source 1 we are using patindex may be that's the reason I am not getting the same value '00000000' in emp table.
the column is just showing as empty .when I select records form emp its picking all the records with empty too. its not satisfying this condition NOMB1 NOT IN ('00000000')
it should exclude the records but its not.
Any suggestion plz.
December 2, 2016 at 8:52 am
You'll need to provide some DDL for a table and DML to help here. This is a little vague and likely due to some data issue with your query.
December 2, 2016 at 9:24 am
As Steve said, we don't have a lot to work with here.
The field your testing is clearly a INTEGER, so I'm wondering why you're wrapping it with single quotations. Is it actually a VARCHAR? Perhaps Preceding spaces? Sample data with the problem will really help here.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
December 2, 2016 at 9:46 am
yes NOMB1 column is nvarchar(8), null.
December 2, 2016 at 9:53 am
select NOMB1 from view1where TNum = 0011008183
Data looks like this
02020790
00000000
select SUBSTRING(Emp.NOMB1, PATINDEX('%[^0]%', Emp.NOMB1+'.'), LEN(Emp.NOMB1)) as EmpNUM
from view1 Emp where TNum = 0011008183
Data looks like
2020790
Note : In the second select statement if u compare the data form top and next one first digit is missing and the 00000000 is not showing in second query data its just empty.
December 2, 2016 at 10:01 am
Using your example:
USE DevTestDB;
GO
CREATE TABLE #Emp (ID INT IDENTITY(1,1),
TNum VARCHAR(12),
NOMB1 VARCHAR(8));
INSERT INTO #EMP (Tnum, NOMB1)
VALUES ('0011008183', '02020790'),
('0011008183', '00000000');
--Your Statement
select SUBSTRING(Emp.NOMB1, PATINDEX('%[^0]%', Emp.NOMB1+'.'), LEN(Emp.NOMB1)) as EmpNUM
from #Emp Emp where TNum = 0011008183 --Why is this now an INT? It has preceding 0's.
DROP TABLE #Emp;
Your statement isn't correct, the statement bring back TWO rows, '2020790' & ''. No row is very different to a row containing a blank value.
We still need that DDL and sample data though.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply