April 26, 2012 at 12:59 pm
Two part problem:
(1) For validation purposes need to compare returned int values of result set with a list of int values typed into where filter.
select system_id, name
from Validate
where system_id not in (23,24,25,26);
This returns nothing, but obviously should return system_id 26.
(2)
The list of values is brought in from another document and the numbers aren't delimited with commas. Can someone advise a way to automate adding commas after each two numbers making up a system_id...so that I am not doing this 325 for a list of 325 system ids that I need to compare with the result set?
Perhaps you resort to Excel?
Thank you!!
--DDL
create table Validate
(system_id int
, name varchar(50))
insert into validate
values
(23, 'High Tide at At Zero Moon'),
(24, 'Weightfulness in Space'),
(25, 'Office Supply Largesse');
--Quote me
April 26, 2012 at 1:07 pm
polkadot (4/26/2012)
Two part problem:(1) For validation purposes need to compare returned int values of result set with a list of int values typed into where filter.
select system_id, name
from Validate
where system_id not in (23,24,25,26);
This returns nothing, but obviously should return system_id 26.
(2)
The list of values is brought in from another document and the numbers aren't delimited with commas. Can someone advise a way to automate adding commas after each two numbers making up a system_id...so that I am not doing this 325 for a list of 325 system ids that I need to compare with the result set?
Perhaps you resort to Excel?
Thank you!!
--DDL
create table Validate
(system_id int
, name varchar(50))
insert into validate
values
(23, 'High Tide at At Zero Moon'),
(24, 'Weightfulness in Space'),
(25, 'Office Supply Largesse');
LOST.
select system_id, name
from Validate
where system_id not in (23,24,25,26);
This returns nothing, but obviously should return system_id 26.
This query is asking for all rows (system_id, name) from the table Validate where system_ID <> 23 and system_ID <> 24 system_ID <> 25 system_ID <> 26. If all your entries are 23,24,25, or 26 then getting nothing in return is correct.
April 26, 2012 at 1:14 pm
Ok I understand what you are saying and why my query isn't working.
I need to isolate what isn't found in the result set. What comparison operator is available to return all system_ids from result set which are not found in the list of supplied system_ids?
--Quote me
April 26, 2012 at 1:16 pm
HUH????
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 26, 2012 at 1:17 pm
More information, still not sure what it is you are trying to do. A concrete example of what you would expect would help.
April 26, 2012 at 1:20 pm
If your correct result set is found by running:
select system_id, name
from Validate
where system_id not in (23,24,25,26);
Then to find all the ones from the table that are not in that it the opposite or:
select system_id, name
from Validate
where system_id not in (23,24,25,26);
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 26, 2012 at 1:24 pm
polkadot (4/26/2012)
Ok I understand what you are saying and why my query isn't working.I need to isolate what isn't found in the result set. What comparison operator is available to return all system_ids from result set which are not found in the list of supplied system_ids?
If you are given a list of system_id's (23, 24, 25), you want all other system_id's (1,2,3,4,5, etc), correct?
April 26, 2012 at 1:27 pm
polkadot (4/26/2012)
Ok I understand what you are saying and why my query isn't working.I need to isolate what isn't found in the result set. What comparison operator is available to return all system_ids from result set which are not found in the list of supplied system_ids?
"Where Not In" is the operator to do what you are asking. You're writing it correctly per your description. If the results aren't what you want, then the description is incorrect.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 26, 2012 at 1:30 pm
GSquared (4/26/2012)
polkadot (4/26/2012)
Ok I understand what you are saying and why my query isn't working.I need to isolate what isn't found in the result set. What comparison operator is available to return all system_ids from result set which are not found in the list of supplied system_ids?
"Where Not In" is the operator to do what you are asking. You're writing it correctly per your description. If the results aren't what you want, then the description is incorrect.
Or, you don't have enough data in the table (ie everything in there is currently excluded by the criteria of the query).
April 26, 2012 at 2:22 pm
I have two seperate lists of system_ids. One from the table validate, a second from a flat file (excel).
I need to compart the list of system_ids.
I need to find any system_id that isn't in the table, that IS in the list of system IDs from the flat file.
How to do this?
I've thought of creating a second table and them taking the cartesian product of two selects, but this still leaves me with the problem of having to format the numbers from flat file to be comma delimited, as they are not when I copy past them in from Excel, and there are too many to do this manuall.
Does this make sense?
--Quote me
April 26, 2012 at 2:25 pm
If you need help with the sql for this you are going to have to provide ddl, sample data and desired output. Take a look at the first link in my signature.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 26, 2012 at 2:25 pm
Polka, as others have already noted, you query is performing exactly what u r asking it to
"Get me the list of values from Validate tables that do not have 23 or 24 or 25 or 26 as the system_ids"
Insert a row with system_id 22 and run the same query, it will bring back that new row.
You query should instead be like this
; with listofSystemIDs (system_id) AS
(
select 23
union all select 24
union all select 25
union all select 26
)
select *
from listofSystemIDs
where system_id not in (select system_id from Validate)
April 26, 2012 at 2:25 pm
Celko, THANKS!
you're query is the charm.
It wouldn't be Celko if you didn't jump on the table naming/column naming conventions. This is sample data ONLY;-)
Thanks again.
Now will someone advise on how to format flat file data, so that system_ids are comma delimited, since there will be a lot of them? Shall I do this in Excel ( I guess I should look).
--Quote me
April 26, 2012 at 7:24 pm
Yes, there is an answer about creating a comma delimited row of values from a column of values in Excel and it is here.
http://www.ozgrid.com/forum/showthread.php?t=59234
Thank you everyone.
--Quote me
April 27, 2012 at 9:10 am
Is this what you're looking for?
Most of this I stole from Jeff's 0 based splitter...
DECLARE @pstring VARCHAR(8000) = '23,24,25,26'
DECLARE @pdelimiter VARCHAR(1)=','
;WITH E1(N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
), --10E+1 or 10 rows
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
cteTally(N) AS (--==== This provides the "zero base" and limits the number of rows right up front
-- for both a performance gain and prevention of accidental "overruns"
SELECT 0 UNION ALL
SELECT TOP (DATALENGTH(ISNULL(@pString,1))) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
),
cteStart(N1) AS (--==== This returns N+1 (starting position of each "element" just once for each delimiter)
SELECT t.N+1
FROM cteTally t
WHERE (SUBSTRING(@pString,t.N,1) = @pDelimiter OR t.N = 0)
),
cte_split AS (
SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY s.N1),
Item = SUBSTRING(@pString,s.N1,ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0)-s.N1,8000))
FROM cteStart s )
SELECT
CAST(Item AS INT)
FROM
cte_split
EXCEPT
SELECT
system_id
FROM
dbo.Validate a
WHERE
EXISTS (SELECT 1 FROM cte_split b WHERE a.system_id = CAST(b.item AS INT))
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply