Hi
First, what are the problems you are seeing with your Tfile field?
Can you post an example with the field definition? Is this a straight Tfile
in <8> or an F; correlative with a Tfile? I'm able to select a simple Tfile
on my version.
Second, you should always be able to add new field definitions to a system
safely so long as you don't change existing ones. That's the whole point of
having separate dictionaries in the first place. If your vendor has problems
with that, you can create a separate dictionary file, copy the existing
definitions across to have a working set to begin with, then use that by
adding a USING clause to your SQL statements, e.g.
SELECT * FROM MyFile USING DICT MyOtherDict;
In general, UniVerse has never been that happy with A and S style
dictionaries when using anything other than RetrieVe commands. For something
like SQL support, I guess it might need the association information found in
a D or S type to know how to normalize multivalued data (multivalued fields
shown as a separate child table).
The other, equally ugly option might be to use an EVAL expression:
SELECT ID, EVAL "XLATE('BOOK_AUTHORS',AUTHOR_ID, 1,'X')" AS AuthorName FROM
BOOK_TITLES;
Or, of course, just use a SQL JOIN..
SELECT BOOK_TITLES.TITLE_ID, BOOK_AUTHORS.FULLNAME FROM BOOK_TITLES LEFT
JOIN
BOOK_AUTHORS ON BOOK_TITLES.AUTHOR_ID = BOOK_AUTHORS.AUTHOR_ID;
Or stepping back, may I ask why you are using SQL with UniObjects?
Brian
> -----Original Message-----
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] On Behalf Of
> Abbey, Ting (Budget Office)
> Sent: 07 May 2007 14:07
> To: [email protected]
> Subject: RE: [U2] How to SELECT (SQL) a TFile field?
>
> Brian,
>
> I don't have the control over changing the fields. We bought
> a proprietary system that built on UniVerse. To access the
> data, I use UniObject and SQL SELECT statements.
>
> Anyway, does that mean SQL SELECT is not "compatible" with
> the Tfile fields? Are there any tricks or tips that I can use
> to still SELECT the Tfile fields?
>
> Thanks,
> TA
>
>
> -----Original Message-----
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] On Behalf Of
> [EMAIL PROTECTED]
> Sent: Saturday, May 05, 2007 4:13 AM
> To: [email protected]
> Subject: RE: [U2] How to SELECT (SQL) a TFile field?
>
> The best option is probably to change them into TRANS() or
> XLATE() fields.
>
> Much more user friendly. If you're not familiar with the syntax:
>
> 1> I
> 2> XLATE("filename",keyfield,remote_field_number,"X")
> 3> conversion
> 4> header
> 5> length just
> 6> S or M (Single/Multivalued)
>
> or
>
> 2> TRANS(filename,keyfield,remote_field_name,"X")
>
> eg.
>
> I
> XLATE("CUSTOMERS",CUSTNO,1,"X")
> MCU
> Surname
> 20L
> S
>
> or
>
> I
> TRANS(CUSTOMERS,CUSTNO,SURNAME,"X")
> MCU
> Surname
> 20L
> S
>
> Also if you want SQL compatible dictionaries it's a good idea
> to avoid periods in the name (underscores are fine).
>
> And remember you can use EVAL expressions as well.
>
> Brian
>
> I have some Tfile fields in the DB. I can select them using
> the LIST command but I am having trouble to do so with SQL
> SELECT command.
>
>
>
> How to select the fields with SQL Select?
>
>
>
> Thanks,
>
> TA
> -------
> u2-users mailing list
> [email protected]
> To unsubscribe please visit http://listserver.u2ug.org/
> -------
> u2-users mailing list
> [email protected]
> To unsubscribe please visit http://listserver.u2ug.org/
> -------
> u2-users mailing list
> [email protected]
> To unsubscribe please visit http://listserver.u2ug.org/
-------
u2-users mailing list
[email protected]
To unsubscribe please visit http://listserver.u2ug.org/
