Define a Temp-Table with a single field exception

[Pavan Yadav]

Hi All,
Anybody can please help me out...

I want to define a Temp-Table like a Given temp-table but i don't want a particular field from the original TABLE.
For Ex. I want a Temp-table ttcust like Customer but i don't want the field Comments.
So can we do this ??

 

[vinod_home]

search the forums, i thought this was answered a month ago.

HTH

 

[Cringer]

Why do you not want the comments? If you're not going to use it, just don't assign it?

 

[Pavan Yadav]

I am going to make a new temp-table.....
& in that it's the requirement...
it's just an example....
In original i have a table with 78 fields & from that i want to remove just 2-fields for my temp-Table.

 

[Pavan Yadav]

Vinod...
i didn't get that in last threads i checked...
please let me know if u have some info for the same....!!

 

[GregTomkins]

I don't believe this is possible... you need a DEFINE ... LIKE ... EXCEPT kind of thing, but AFAIK it doesn't exist.

I dislike DEFINE... LIKE, but I use it from time to time anyway.

 

[Pavan Yadav]

Exactly,
There should be something like DEFINE .... LIKE...
but how to put except for this....??

Or is there some way we can do with system tables??

 

[vinod_home]

My bad, the last one was for indexes on a temp-table. I think your options would be to use a dynamic-table and create it based on your requirement. You can also dump out the table structure into a .i and remove the fields that you dont want and use that to create a temp-table, you can do that from the dictionary.

HTH

 

[Pavan Yadav]

Thanks Vinod for the showing the way for that....
But, i am not as much expert, so please if possible can u tell me in somewhat detail how to dump that to a .i file...
bcuz if dump the .df file.. thn it may nt serve the purpose.... & i only knows that way of dumping the schema.

& how Dynamic-Table works over here...?/
Please provide me some idea for the same... & rest i will try to dig into it ....

 

[GregTomkins]

Dynamic temp-tables are great and for sure could do what you want, but, they are way more complicated, require a totally different syntax, and are prone to run-time (as opposed to compile-time) failure. For what you are doing, I think I would just find the existing temp-table definition, copy it, rename it, remove the fields you don't want, and move on. That will take 5 minutes, versus several hours and probable future bugs.

 

[tamhas]

Given that there is no define like except, I think you are working too hard. Just define it like and don't assign the fields you don't care about. Pretend like they don't exist. What's it going to break?

Actually, I advise against using LIKE anyway, so the real solution is to just put in the definition you want.

 

[Pavan Yadav]

Thanks....

But Tamhas, Actualy i have to change my Schema of a table having 76 fields & now newly i am supposed to have 74 fields,
So in that case i was trying that out to check sort of feasilbility with Defining Temp-Table......

Well, i will try my head out with dynamic concept if i can get...
or i will try for dump tooo if i can have something with that,

 

[RealHeavyDude]

I cannot help but stress on Tamhas' recommendation not to use LIKE in temp-table and variable definitions. There are many good reasons not to do so - mostly because your business logic should not depend on a specific database design, instead you should implement a data access layer which takes care of that.

But, there is a TEMP-DB maintenance tool which you will find in the tools section of the AppBuilder which allows you to easily handle temp-table definitions separated out in include files. All you need to use it, is that you connect a dedicated database using TEMP-DB as logical database name when connecting to it. One of the features of this tool is to generate a temp-table definition that exactly reflects a database table. You can go on from there and modify it so that it will fit your needs ...

Regards, RealHeavyDude.

 

[tamhas]

With or without the AppBuilder or OEA tools, the code to generate a TT definition from a DB table is trivial and you can easily edit the result to suit.

One of the problems with LIKE is that it assumes a DB connection. E.g., imagine an AppServer process which fills a TT and sends it to the client. If the client TT definition uses LIKE, then the client also needs to be connected to the DB.

 

[Krishan Gopal]

Hi Pawan,

As it's been already told its of no use doing this thing, first of all due to AppServer and if you work with Dynamic Temp-Table then you have to work with Dynamic Query, that is little bit boring and doesn't provide so much of freedom as we have working with normal tables. Though here is one sample of Code, see if it can solve your purpose -

/* Here in this code fields Comments & Address will not be included, If you want to check just uncomment the commented code and you will see error */

DEF VAR tth AS HANDLE NO-UNDO.
DEF VAR bf_handle AS HANDLE NO-UNDO.
DEF VAR bf_handle_cust AS HANDLE NO-UNDO.
DEF VAR qh AS HANDLE NO-UNDO.
DEF VAR fld1 AS HANDLE NO-UNDO.
DEF VAR fld2 AS HANDLE NO-UNDO.
DEF VAR fld3 AS HANDLE NO-UNDO.

bf_handle_cust = BUFFER customer:HANDLE.

CREATE TEMP-TABLE tth.
tth:ADD-FIELDS-FROM("Customer", ",Comments,Address,").
tth:TEMP-TABLE-PREPARE("tCust").
bf_handle = tthEFAULT-BUFFER-HANDLE.

FOR EACH customer WHERE CustNum < 15:
bf_handle:BUFFER-CREATE().
bf_handle:BUFFER-COPY(bf_handle_cust).
END.


CREATE QUERY qh.
qh:SET-BUFFERS(bf_handle).
qh:QUERY-PREPARE("FOR EACH tCust").
qh:QUERY-OPEN().

fld1 = bf_handle:BUFFER-FIELD("CustNum").
fld2 = bf_handle:BUFFER-FIELD("Name").
/* fld3 = bf_handle:BUFFER-FIELD("Comments"). */

REPEAT:
qh:GET-NEXT().
IF qh:QUERY-OFF-END THEN LEAVE.
DISPLAY fld1:BUFFER-VALUE() FORMAT "X(10)".
DISPLAY fld2:BUFFER-VALUE() FORMAT "X(20)".
/* DISPLAY fld3:BUFFER-VALUE() FORMAT "X(20)". */
END