SQL Related Articles: Creating views to retrieve data from tables
2003-04-30 -- Geoff Bomford
Newsgroups: softvelocity.products.c55ee
Create a view of the file and don't nominate any fields, that way the
structure always matches the file definition in both dictionaries.
MyView VIEW(MyFile)
END
OPEN(MyView)
MyView{Prop:SQL} = 'SELECT * FROM MyFile WHERE...'
CLOSE(MyView)
Geoff Bomford
www.comformark.com.au
Computer services For Marketing
"Arnor Baldvinsson" wrote in message
news:3eaf063d.28885390@news.softvelocity.com...
> Hi Glenn,
>
> On Tue, 29 Apr 2003 14:33:43 -0500, "Glenn Rathke"
> wrote:
>
> >Don't know if it has changed in other versions of Clarion, but SUrf has
> >mentioned not to use Select * but rather Select field_one, field_2, where
> >the fields are in the order in the dictionary
>
> If you need the whole record anyway for processing purposes, there is
> no advantage of listing the fields over using * as far as I can tell.
> Also consider this:
>
> MyFile...
> Record Record
> F1 String(10)
> F2 Long
> F3 String(20)
> F4 String(5)
> End
>
> Now if you do:
>
> MyFile{Prop:SQL} = 'Select MYF.F1, MYF.F4 from ' & Name(MyFile)
> Next(MyFile)
>
> I don't think the values are going to be correct because this doesn't
> match the file structure. I may be wrong on this, I haven't tried it.
> I know that if you use 'Select *' on a table and the field order in
> the db don't match the field order in the file structure you'll get
> some odd errors about data conversions - at least with Sybase
> SQLAnywhere
>
> Using a view and only projecting the fields you need would probably be
> a better option, but I'm not quite there yet
>
> Best regards,
>
> Arnór Baldvinsson
Possible Speed Increases
2003-09-02
�
Karl Greenwood
Using Select field1, field2 ... fieldn should be faster then Select * as
the server has to query its system tables for a field list. In tests using
clarion and asp with ms sql the speed increase was measurable with
mulitple transactions. Having said that a seperate test on a cold fusion
app with ms sql showed no increase.
And as a side note only use Select * if you really need all fields to be
returned
Printed April 28, 2024, 9:22 pm
This article has been viewed/printed 35112 times.
Google search
has resulted in 45 hits on this article since January 25, 2004.