Cursor returns Multiple Rows & Columns

Question

Cursor returns Multiple Rows & Columns

andy_fulstow

Valued Member

Points: 68
More actions
June 5, 2019 at 8:56 am

#3650311

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 simple 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.
REGION_NAME                SEQUENCE
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?
Thanks

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

jcelko212 32090 SSCrazy Eights Points: 9294 More actions · Answer 1

Let's start with the basics. For over 35 years, Netiquette on SQL forums is to post DDL --not pictures-- when asking for help. Now we have to print out your posting, and transcribe the tables into DDL. Then we have to type the sample data into INSERT INTO statements. OH! there is no sample data! Why would anyone want to help a person is this rude and soooo much trouble?

I have bad eyes, so being your personal servant is a real pain. Do you know how to print the DDL from your schema?

This is not your fault, but the schema is a mess. SEQUENCE is a reserved word in SQL (should this be protein_sequence?), it mixes data and meta-data, generic useless column names (name, type, etc) in violation of ISO-11179, etc. Everything in the world is NOT a fifty character string. This schema uses a magic Kabalah number called "id" as the key on all kinds of tables. This is where we got the term "ID-iot" to describe this non-relational design. The non-tables have no relational keys at all. It is base on pointer chains from pre-RBMS database

We want to avoid cursors; SQL is a set-oriented language. If this were a cooking class, you would be asking about deep frying a baby 🙂

I do not know about genetics, but if you will post DDL, then we can normalize this thing and assure some data integrity.

Please post DDL and follow ANSI/ISO standards when asking for help.

sgmunson SSC Guru Points: 110639 More actions · Answer 2

Well,

No surprises from Mr. Celko... he's as rude and insensitive as ever. Maybe even a new low with accusing you of the equivalent of a Hansel and Gretel Witch crime. Anyway, there is one thing he's right about, and it's that you haven't posted any kind of actual DDL describing the tables and their columns, nor have you posted any sample data. Cursors are also generally a good idea to avoid, and chances are they can actually be avoided. If you can post the table DDL, and some INSERT statements that would place some sample data in those tables, we can likely work out a query that will directly insert the necessary data. However... I question whether or not such a thing is even necessary, as once you have that query, it could be placed in a stored procedure and called upon whenever needed by an application, as opposed to all the work of setting up new tables just to hold information you already have, as well as make the crappy schema you are stuck with even worse. Just remember that data duplication is what database normalization exists to prevent. Doing this in table form is a really BAD idea... just not quite at the baby murder level.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

andy_fulstow Valued Member Points: 68 More actions · Answer 3

Thanks for all the responses (honest).

Unfortunately my data is confidential, my company's IP in fact! Mocking something up would be a painful task.

I have swerved the problem by 'splitting' the SQL cursor query over the two (and only two) REGION_NAMEs in the data. I run the cursor using one value then query using the second value.

I am sure that someone with suitable experience could solve this 'properly' using datasets- it's just beyond my ken!