May 19, 2008 at 9:58 am
Hi Everyone,
I am writing a very simple query but when the results are returned, I am sure if I have written the right query but I can not figure out what is wrong.
The problem is:
I have a table A where the GUID (UNIQUE) are randomly set while entering records from the form.
Now I have 4123 rows in this table when I run the following query
SELECT *
FROM IMPORT_OCR_ADMIN_2000_1
WHERE DataSource_ID = '24'
Now the records from this table get distributed to different tables after processing, so say it goes a table B where the GUID is formed using combination of columns in table A
SELECT *
FROM IMPORT_CD_D
WHERE DataSource_ID = '24'
It gives me a total of 4063 records.
Now I want to find out which are the 60 records in table A that are not in table B, so that I can verify if they have processed and eliminated them as the procedure was supposed to do.
Now I want result set = Table A - Table B :
SELECT ocr.*
FROM IMPORT_OCR_ADMIN_2000_1 AS ocr
WHERE ('2OCR_ADMIN_'+REVIEW_DATE+PAT_ID+Convert(varchar(9),Form_Id))
NOT IN (Select GUID from IMPORT_CD_D where DataSource_ID = '24')
AND ocr.DataSource_ID = '24'
I am not sure if this is right. If someone can help, it will be great.
Thanks in advance,
Sree
May 19, 2008 at 11:44 am
what you have should work but using a join will be faster and cleaner
SELECT ocr.*
FROM IMPORT_OCR_ADMIN_2000_1 AS ocr
left join IMPORT_CD_D as cd
on '2OCR_ADMIN_'+REVIEW_DATE+PAT_ID+Convert(varchar(9),ocr.Form_Id) = cd.GUID and cd.DataSource_ID = '24'
where cd.GUID is null and ocr.DataSource_ID = '24'
May 19, 2008 at 12:01 pm
Just an observation here, but why not just leave the GUID value alone when you move the row to another table? Wouldn't this help you with relating the data back to TableA? Finding rows that don't exist in TableB would then just be a straight outer join on the column without having to include all of the string manipulation garbage.
May 20, 2008 at 6:36 am
Hi mrpolecat,
SELECT ocr.*
FROM IMPORT_OCR_ADMIN_2000_1 AS ocr
left join IMPORT_CD_D as cd
on '2OCR_ADMIN_'+REVIEW_DATE+PAT_ID+Convert(varchar(9),ocr.Form_Id) = cd.GUID and cd.DataSource_ID = '24'
where ocr.DataSource_ID = '24'
This is the query I think I need. But I am not getting the expected results.
As I mentioned -
If Table A Gives me 4123 records and Table B 4063 , I need 60 records in Table A that are not there in table B. But this query returns me 4122 records. I am puzzled. ANy help.
Hi John,
The GUID in table A comes from the form directly but it has data which needs to go to Table B, Table c etc and when the data is being import, new GUID are formed such that the GUID can tell us the source and type of data in the destination import table.
For example: If Table B is a fruits table, Table C is a Vegetables table: Than GUID for TABLE A will be formed saying : FruitsName + Fruits Source + Fruits Store+ DateOf Packing - i.e. AppleSpain1TESCO3101/01/1900. Hope that makes sense, its a legacy DB but its not too bad as well.
Thanks and regards,
Sree
May 20, 2008 at 7:24 am
May 20, 2008 at 7:31 am
Hi mrpolecat (Any short name? Though its quite funny, very long to type each time :D)
But cd.GUID can not be NULL at all.
Basically I want then records that are not imported from Table A to table B that has the datasource 24.
Please do let me know if I not clear, I will try to be more explanatory.
Thanks
Sree
May 20, 2008 at 7:40 am
May 20, 2008 at 7:44 am
IMPORT_OCR_ADMIN_2000_1 is Table A
May 20, 2008 at 7:48 am
May 20, 2008 at 7:52 am
IMPORT_OCR_ADMIN_2000_1 has a field called GUID but its not same as GUID of table B.
GUID in Table A - Unique Identifier automatically generated when the fields are entered
GUID in TAble B - Generated from columsn combined from Table A i.e. '2OCR_ADMIN_'+REVIEW_DATE+PAT_ID+Convert(varchar(9),ocr.Form_Id)
May 20, 2008 at 8:05 am
OK then what we have should work. By saying tableB.guid is null it will exclude all records where there is a match between table A and Table B giving you the results of records in table A where there is no match in table B. This may be easier to read.
SELECT tableA.*
FROM IMPORT_OCR_ADMIN_2000_1 AS tableA
left join IMPORT_CD_D as tableB
on '2OCR_ADMIN_'+REVIEW_DATE+PAT_ID+Convert(varchar(9),tableB.Form_Id) = tableB.GUID and tableB.DataSource_ID = '24'
where tableB.GUID is null and tableA.DataSource_ID = '24'
May 20, 2008 at 8:07 am
Sreemati,
The query posted by mrpolecat is right and it should produce the required results.
SELECT ocr.*
FROM IMPORT_OCR_ADMIN_2000_1 AS ocr
left join IMPORT_CD_D as cd
on '2OCR_ADMIN_'+REVIEW_DATE+PAT_ID+Convert(varchar(9),ocr.Form_Id) = cd.GUID and cd.DataSource_ID = ocr.DataSource_ID
where cd.GUID is null
and ocr.DataSource_ID = '24'
The above query uses left join it means it gets all the tables in ocr table which are not in cd table, in order to get the (ocr-cd) results we use the clause cd.GUID is null to accomplish that, which doesn't mean it should be null.
Hope I made sense!
Try running the above query and let me know how many records it produces.
May 20, 2008 at 8:58 am
Hi John,
The GUID in table A comes from the form directly but it has data which needs to go to Table B, Table c etc and when the data is being import, new GUID are formed such that the GUID can tell us the source and type of data in the destination import table.
For example: If Table B is a fruits table, Table C is a Vegetables table: Than GUID for TABLE A will be formed saying : FruitsName + Fruits Source + Fruits Store+ DateOf Packing - i.e. AppleSpain1TESCO3101/01/1900. Hope that makes sense, its a legacy DB but its not too bad as well.
Thanks and regards,
Sree
Thanks for the reply Sree. I guess that's where I was confused. That is not actually a GUID. A GUID is a hexidecimal value formatted as such: {3F2504E0-4F89-11D3-9A0C-0305E82C3301}. What you have is just a unique identifier created by concatenating values together to create the value.
This is scary. You should find the original designer and knock him over the head with a book on relational database design. Oh well, gotta love those legacy apps!! If you are in the mood to fix the table structure to make querying it not a nightmare, feel free to post the table DDL with some sample data and some of us will look it over.
May 20, 2008 at 9:03 am
Hi John,
Yes, I know its hard. Half of our time is spent in decoding the table structure, datatypes and the store procedure. I would say its a perfect example of how things are not to be done but surprisingly its been working for past 10 years and hence management is quite scared of even thinking of re-vamping it.
I would definitely want to learn how to improve it for my personal development 🙂
Thanks for you help,
Sree
May 21, 2008 at 3:14 am
HI Everyone,
Well, thanks for the query, all the time, the query had been right. I went through the SP that processed it and realized that all Values of DS= 24 in Table A does not have to go to Table B but it could also be going to Table C also 🙂
Thanks for you help.
Sree
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply