TOAD, Teradata, Pulling Records With More Than One Value Returned by a Subquery

I have two datasets both with multiple records by Mem_id but distinct in terms of the whole record.

One file (RadResFO1) has 26K records and is the original from which the second file (RadResPOFO1) was created which contains a subset of 21K records matched on a third file (irrelevant) by Mem_id and SvcDate.

I want to pull from the original 26K, those records that did not match, ~ 2,500 records.

I tried this below, however, I am getting an error that "more than one value is being returned by a subquery" and the output fails.

Your table(sandbox.RadResFO1) must be having duplicate Mem_Ids. That should explain why you are getting more number of rows than expected.

With regards to MINUS set operator issue.. the column types of these two table rows are not compatible. If they are the same, please try with an explicit column list of both the tables. Again you may have the same duplicates issue here.

Thanks

Vijay Guduru

Help the community by fixing grammatical or spelling errors, summarizing or clarifying the solution, and adding supporting information or resources. Always respect the original author.

Your table(sandbox.RadResFO1) must be having duplicate Mem_Ids. That should explain why you are getting more number of rows than expected.

With regards to MINUS set operator issue.. the column types of these two table rows are not compatible.
If they are the same, please try with an explicit column list of both the tables.
Again you may have the same duplicates issue here.

Based on your most recent response, it sounds like you have not specified enough criteria in order to achieve the one-to-one matching that would be needed. In other words, if a patient can have multiple rows with the same serice data, then you need to not only specify the Patient_ID and Service_Date but also some other column (or columns) so that you can uniquely identify the rows. Failing to specify enough columns to achieve that unique identification will result in multiple rows matching the columns you _do_ specify.

In addition, if you are matching data from the 2 tables, on, say, Patient_ID and Service_Date without including, for example, NDC, then each row from TableA will match each of the rows from TableB. So, if you have, say, 5 rows in TableA and 3 rows in TaleB, you will wind up with a total of _15_ matches.