June 12, 2017 at 11:13 am
Greetings,
I have a table that has 2 columns, TransTableDetail_Trans_To and TransTable_Description. the TransTableDetail_Trans_To column lists store numbers that can repeated in different rows. The TransTable_Description column lists a description for each store. A store may be listed more than once with a different description. (See attached StoreData.xlsx file for sample data)
What I need to do is query the table to return a list of distinct store numbers (TransTableDetail_Trans_To) where the TransTable_Description equals LFAllCSCW AND LFTEST. (see attached OutputExample.xlsx file) I am guessing that I need to create a virtual table to go through the list of store numbers and add a column for each description found. Then I could run a simply query with a WHERE TransTable_Description = LFAllCSCW AND TransTable_Description = LFTEST. Would that be the preferred route? If so, how would go about creating such a table? Thank you for your help in advance.
June 12, 2017 at 11:17 am
https://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
June 12, 2017 at 11:40 am
J Livingston SQL - Monday, June 12, 2017 11:17 AM
I apologize J for not giving that information. I have updated the post to include the example data and also the example outcome I would like.
June 12, 2017 at 11:40 am
Please post DDL (create table) for tables or declaration of table variables and inserts with sample data.
June 12, 2017 at 2:21 pm
Based on your description (I didn't look at your excel files), what you are asking for is not possible.
You state that TransTable_Description is a column which is no problem. The problem is your proposed WHERE clause of WHERE TransTable_Description = LFAllCSCW AND TransTable_Description = LFTEST. There is no way that that query could work.
That'd be like saying "WHERE X = 1 AND X = 2". It can't happen; X cannot equal both 1 and 2. You could do that with an OR or an IN, but not an AND.
I think what you likely want is:SELECT DISTINCT TransTableDetail_Trans_To
FROM <table name>
WHERE TransTable_Description IN ('LFAIICSCW','LFTEST')
which would give you a distinct list of values in the TransTableDetail_Trans_To column where TransTableDescription is either LFAIICSCW or LFTEST. But there will never be a column in any table that has a single row containing 2 values.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
June 12, 2017 at 3:18 pm
You could use this query:SELECT TransTableDetail_Trans_To
FROM TransTableDetail
WHERE TransTable_Description IN ('LFAllCSCW', 'LFTEST')
GROUP BY TransTableDetail_Trans_To
HAVING COUNT(DISTINCT TransTable_Description) = 2
-- If table has a unique index on (Trans_To, Description), that could just be COUNT(*) = 2
If you're asking for a table structure that can handle a generalization of this query for any combination of descriptions, I'd say that's a bad design unless you have ironclad control over the list of possible descriptions. You'd probably be better off generating a version of the above query dynamically for a given set of descriptions.
I can see a query with a WHERE .. IN (...) clause with a thousand values, but not a table with possibly a thousand additional columns.
How about putting all the distinct definitions in another table? It could be used to populate a drop-down list in an app, rather than letting users make new ones simply by misspelling something. Your main table then could have an INT DescriptionId column instead of repeating all that text. And it would make the above query much more efficient if the WHERE IN () clause had a list of ints rather than strings.
June 14, 2017 at 8:38 am
bmg002 - Monday, June 12, 2017 2:21 PMBased on your description (I didn't look at your excel files), what you are asking for is not possible.You state that TransTable_Description is a column which is no problem. The problem is your proposed WHERE clause of WHERE TransTable_Description = LFAllCSCW AND TransTable_Description = LFTEST. There is no way that that query could work.
That'd be like saying "WHERE X = 1 AND X = 2". It can't happen; X cannot equal both 1 and 2. You could do that with an OR or an IN, but not an AND.
I think what you likely want is:SELECT DISTINCT TransTableDetail_Trans_To
FROM <table name>
WHERE TransTable_Description IN ('LFAIICSCW','LFTEST')which would give you a distinct list of values in the TransTableDetail_Trans_To column where TransTableDescription is either LFAIICSCW or LFTEST. But there will never be a column in any table that has a single row containing 2 values.
Thank you for this. I'm not sure why I didn't think of the IN operator before, but that suffices the requirements.
June 14, 2017 at 8:45 am
No problem.
The other option would have been to use "OR" and have 2 entries in your where clause.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
June 20, 2017 at 12:31 pm
Based on your description (I didn't look at your excel files), what you are asking for is not possible.You state that TransTable_Description is a column which is no problem. The problem is your proposed WHERE clause of WHERE TransTable_Description = LFAllCSCW AND TransTable_Description = LFTEST. There is no way that that query could work.
That'd be like saying "WHERE X = 1 AND X = 2". It can't happen; X cannot equal both 1 and 2. You could do that with an OR or an IN, but not an AND.
I think what you likely want is:SELECT DISTINCT TransTableDetail_Trans_To
FROM <table name>
WHERE TransTable_Description IN ('LFAIICSCW','LFTEST')which would give you a distinct list of values in the TransTableDetail_Trans_To column where TransTableDescription is either LFAIICSCW or LFTEST. But there will never be a column in any table that has a single row containing 2 values.
That is not exactly true. Bitwise AND checks can actually check a field value for 2 or more values existence at the same time.
NOTE*** The code below ONLY satisfies this targeted case and this code does not work when a string value to find is combined with an unwanted string value, in this case ("LFTESTsomeothervalue") Does NOT work. (that would take a bit more work) but this should give you an idea of how it is possible.
DECLARE @X INT=1, @Y INT=2, @Z INT
IF (@X&@Y) = 0
PRINT 'TRUE'
ELSE
PRINT 'FALSE'
DECLARE @TestTable TABLE(TextDescription VARCHAR(MAX))
INSERT INTO @TestTable(TextDescription) VALUES ('LFAIICSCW')
INSERT INTO @TestTable(TextDescription) VALUES ('LFTEST')
INSERT INTO @TestTable(TextDescription) VALUES ('LFTESTLFAIICSCW')
INSERT INTO @TestTable(TextDescription) VALUES ('TEST')
INSERT INTO @TestTable(TextDescription) VALUES ('AnotherNonValue')
SELECT 'BitWiseCheck'= CASE 2 & REPLACE(REPLACE(REPLACE(TextDescription,'LFAIICSCW',3),'LFTEST',2),TextDescription,1) WHEN 0 THEN 'NO' ELSE 'YES' END,
REPLACE(REPLACE(REPLACE(TextDescription,'LFAIICSCW',3),'LFTEST',2),TextDescription,1) as ConversionCheck, TextDescription
from @TestTable
RESULTS
BitWiseCheck ConversionCheck TextDescription
YES 3 LFAIICSCW
YES 2 LFTEST
YES 23 LFTESTLFAIICSCW
NO 1 TEST
NO 1 AnotherNonValue
**Edited to add results
June 20, 2017 at 12:46 pm
Smendle - Tuesday, June 20, 2017 12:31 PM+xBased on your description (I didn't look at your excel files), what you are asking for is not possible.You state that TransTable_Description is a column which is no problem. The problem is your proposed WHERE clause of WHERE TransTable_Description = LFAllCSCW AND TransTable_Description = LFTEST. There is no way that that query could work.
That'd be like saying "WHERE X = 1 AND X = 2". It can't happen; X cannot equal both 1 and 2. You could do that with an OR or an IN, but not an AND.
I think what you likely want is:SELECT DISTINCT TransTableDetail_Trans_To
FROM <table name>
WHERE TransTable_Description IN ('LFAIICSCW','LFTEST')which would give you a distinct list of values in the TransTableDetail_Trans_To column where TransTableDescription is either LFAIICSCW or LFTEST. But there will never be a column in any table that has a single row containing 2 values.
That is not exactly true. Bitwise AND checks can actually check a field value for 2 or more values existence at the same time.
NOTE*** The code below ONLY satisfies this targeted case and this code does not work when a string value to find is combined with an unwanted string value, in this case ("LFTESTsomeothervalue") Does NOT work. (that would take a bit more work) but this should give you an idea of how it is possible.
DECLARE @X INT=1, @Y INT=2, @Z INT
IF (@X&@Y) = 0
PRINT 'TRUE'
ELSE
PRINT 'FALSE'
DECLARE @TestTable TABLE(TextDescription VARCHAR(MAX))
INSERT INTO @TestTable(TextDescription) VALUES ('LFAIICSCW')
INSERT INTO @TestTable(TextDescription) VALUES ('LFTEST')
INSERT INTO @TestTable(TextDescription) VALUES ('LFTESTLFAIICSCW')
INSERT INTO @TestTable(TextDescription) VALUES ('TEST')
INSERT INTO @TestTable(TextDescription) VALUES ('AnotherNonValue')
SELECT 'BitWiseCheck'= CASE 2 & REPLACE(REPLACE(REPLACE(TextDescription,'LFAIICSCW',3),'LFTEST',2),TextDescription,1) WHEN 0 THEN 'NO' ELSE 'YES' END,
REPLACE(REPLACE(REPLACE(TextDescription,'LFAIICSCW',3),'LFTEST',2),TextDescription,1) as ConversionCheck, TextDescription
from @TestTableRESULTS
BitWiseCheck ConversionCheck TextDescription
YES 3 LFAIICSCW
YES 2 LFTEST
YES 23 LFTESTLFAIICSCW
NO 1 TEST
NO 1 AnotherNonValue**Edited to add results
That is only true if you are not using exact comparison though.
You are using fuzzy matching. In my example (and my understanding of the original request), your 3rd result should be false as LFTEST != LFTESTLFAIICSCW. LFTEST% = LFTESTLFAIICSCW, but if you are using exact matching, then they do not match up.
The OP said "where the TransTable_Description equals LFAllCSCW AND LFTEST" which I interpreted to be exact values, not fuzzy matching.
I didn't open the attached documents mostly because I am paranoid about viruses. I know Excel files would need to have macros enabled to cause any issues, but I'm still not willing to risk it on my work machine.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply