Data Structure Definitions for "Select Into"

[FaStOnE]

I need to define a Qualified Data Structure for a "Select Into". But, the Table is not defined in the F-Specs. Do I have to *re-define* each field name? Or, is there a way to utilize something similar to the "Like()" keyword for an external Table name?

The only other option I know of is to define the table in F-Specs and designate it as "USROPN" and just never open it.

Suggestions?

-R

[tomholden]

Re: Data Structure Definitions for "Select Into"

have you tried using an externally described DS? no need for the F spec...

[FaStOnE]

Re: Data Structure Definitions for "Select Into"

I might as well use the F-Specs with USROPN ... I would still have to define an external DS object that has just the fields I want it in.

Yes? No?

At least the USROPN doesn't utilize the File Opening handlers until I want it. Correct? So, no wasted effort there...

[FaStOnE]

Re: Data Structure Definitions for "Select Into"

Another question that just popped into my head...

If I do define the F-Specs for the Table with a Prefix does that affect the "Select" statements in the SQL code? I don't think it would... I just haven't gotten there yet.

Hmmmmmm.....

[B.Hauser]

Re: Data Structure Definitions for "Select Into"

Hi,

Files defined in the F-Specs have nothing common with the files used in SQL-Statements, i.e. are not considered by SQL, that means a data structure is still needed.

If you won't use native I/O, I'd not define F-Specs, but an external datastructure instead.

For Performance issues, SELECT * should be avoided, instead specify only the columns you need. You may also define an internal data structure, where the selected fields are listed, and fetch the result into this data structure.

Birgitta

[gcraill]

Re: Data Structure Definitions for "Select Into"

For Performance issues, SELECT * should be avoided, instead specify only the columns you need.

Birgitta

Thanks for the tip B !!!

[BenThurley]

Re: Data Structure Definitions for "Select Into"

I always have a procedure that builds the "SELECT ..." part of the SQL and a global DS with fields that match. It is then the responsibility of the developer to make sure they match.

These fields often come from different tables so there's no tidy way to do it with F-specs or similar.

[FaStOnE]

Re: Data Structure Definitions for "Select Into"

B.

OK, I can understand these scenarios and agree. But, the question is just that. In defining an internal Data Structure to do the Select Into, how can I get around having to do a re-definition of each element of the Data Structure internally? Currently, the only option I can see is the externally defined DS, but there's another maintainable point in the process if the table changes.

I guess the jist of this question is more of making it "easier to maintain" than anything. In this case, I would have to make changes to the Table, the External Data Structure and possibly my code inside the program. Just seems like a "gotcha" waiting to happen.

Argh! Why do I get myself into these situations!!

[BenThurley]

Re: Data Structure Definitions for "Select Into"

The only thing you could do there I guess is use the Like keyword on each field. Even with the like keyword the program needs to be identified and re-compiled. In most cases fields tend to be added to tables rather than change type. If you name the fields you're asking for in the SELECT statement rather than use a * then new fields added to the table won't make any difference.

[thosmore]

Re: Data Structure Definitions for "Select Into"

Pardon me if I'm not understanding this, but if you are willing to code the individual field names in your Select statement, adding an FSpec with UsrOpn (and a dummy Open) will get you the field defs with no sweat. If maintainability is the main issue however, coding a DS with ExtName(YourFileName) will allow you to Select directly into the DS, thereby making maintenance after file changes a simple matter of recompilation. The downside of this latter approach, in my experience, is that it can occasionally require new vars for numeric parm passing.

[FaStOnE]

Re: Data Structure Definitions for "Select Into"

@Thosmore:

You're restating my original issue. I don't *personally* want to use the F-Specs (with a dummy open) just so I can use the "Like" keyword and define my Data Structure. There's just only one other option that I know of and that's "Select *" into the Data Structure... but, like Birgitta says, it's very inefficient.

My interest was in defining the Data Structure without having to use the Faux F-Spec. The "Like" keyword can't be used unless it can *see* the file specifications. Using this in a SQL based program doesn't allow me that option since I'm not utilizing the F-Specs for the desired table.

In a nutshell, I just wanted to find a way to define (a subset) of the desire table inside a Data structure and allow SQL to "select f1, f2,...fx" into that data structure.

From what I'm gathering.. that can't be done, unless I define an external data structure.

[thosmore]

Re: Data Structure Definitions for "Select Into"

My practice has been that the FSpec option and the DS option are either/or but never both. Either method serves only to bring the field defs into your code. I've never needed to use Like with either option, b/c I just read into either the DS or the original field names directly.

[Andrew K]

Re: Data Structure Definitions for "Select Into"

Also be aware that if your SQL select can return NULL values at all, you have to code for that too (for each field)


d @@GRWE s 9p 3
d nul_GRWE s 5i 0

if nul_grwe=0; // value is not null
// do stuff
else;
// do other stuff
endif;


I got burnt badly by that once !

[thosmore]

Re: Data Structure Definitions for "Select Into"

Shop practice here is to use IfNull() where that is a possibility to avoid the need to test downstream. Obviously, this isn't possible where the choice is to Select into a DS rather than individual fields.

[FaStOnE]

Re: Data Structure Definitions for "Select Into"

Table was defined as "Not Null" with a Default Value to avoid that situation. So, I think I'm OK with that.

I guess this could be an exercise in laziness maybe?!?

I just don't see having to re-define each and every field you need either as Stand Alone or within a DS for use in a SQL environment when the field definitions are available. There should be some sort of *sub-definition* option for grabbing particular fields from a large table to streamline or optimize the Select statement.

Just wishful thinking...

-Rick