My SQL is limited and I have inherited the schema so please go easy!
The upper tables are populated, the lower section is to be completed.
The customer wants a simple reference, C_SCAFFOLD_DOMAIN_COMBO, for every C_SEQUENCE_RESULT of the same TYPE having the same C_PROTEIN_REGIONs (only ever two) of the same REGION_NAME.
My intention is to read each C_SEQUENCE_RESULT getting the (2) C_PROTEIN_REGION.SEQUENCEs with their REGION_NAME using a cursor.
I can then check if they are referenced by a C_SCAFFOLD_DOMAIN_COMBO already.
If so, then simply add the correct C_SCAFFOLD_DOMAIN_NAME FK to C_SEQUENCE_RESULT.
If not, create a new C_SCAFFOLD_DOMAIN_NAME entry with the appropriate C_PROTEIN_REGION refs and add the FK to C_SEQUENCE_RESULT.
My SQL query returns two results for each C_SEQUENCE_RESULT e.g.
Loop 1 ABCDEFG
Loop 2 HIJKLMN
Running as a simple query and can INSERT INTO a temp table and get the values. As a cursor I don’t seem to be able. How can I read the data returned above using a cursor?