` Printed Icetips Article
Icetips Article

SQL Related Articles: Using same file structure to retrieve multiple fields
2002-07-08 -- Andy Stapleton
Newsgroups: TopSpeed.Products.Drivers

Sure......

Definition of the RESULTTABLE in SQL

Create Table ResultTable
    Integer1   integer
    String1   Varchar(255),
    Date1      Date,
    Decimal1  Decimal(12,2));

Clarion DCT definition

ResultTable   FILE,PRe(Res),Driver(ODBC),NAME(DBO.ResultTable)
Integer1          Long
Integer2          Long,NAME(INteger1)
Integer3          Long,NAME(INtege1)
Integer4          Long,NAME(INteger1)
Integer5          Long,NAME(INteger1)
String1            String(255)
String2            String(255),NAME(String1)
String3            String(255),NAME(String1)
String4            String(255),NAME(String1)
String5            String(255),NAME(String1)
Date1              Date
Date2              Date,NAME(DATE1)
Date3              Date,NAME(DATE1)
Date4              Date,NAME(DATE1)
Date5              Date,NAME(DATE1)
Decimal1           Decimal(12,2)
Decimal2           Decimal(12,2),NAME(Decimal1)
Decimal3           Decimal(12,2),NAME(Decimal1)
Decimal4           Decimal(12,2),NAME(Decimal1)
Decimal5           Decimal(12,2),NAME(Decimal1)
         END


IN my CW Code.....

myResults  VIEW(ResultTable)
            Project(Res:String1)
            Project(Res:String2)
            Project(Res:String3)
            Project(Res:Date1)
            Project(Res:Decimal1)
        END

  Open(MyResults)
  if Errorcode();Stop(Error()).

    MyResults{Prop:Sql}='Select ord.OrderNo, Cus.CompanyName,
Cus.ContactNAme, Ord.Orderdate, Ord.Amount '&|
                                       '  From '&NAME(Orders)&' as
ORD,'&NAME(Customer)&' as Cus '&|
                                       ' Where Ord.CustSysid = Cus.custSysid ' &|
                                       ' And Ord.Orderdate Between
<39>'&Format(Loc:Startdate,@d10-)&'<39> and <39>'&|
                                       Format(Loc:EndDate,@d10-)&'<39>'
 If Errorcode();Stop(FileError()).
 Loop
    Next(MyResults)
    IF Errorcode();Break.
        do something with the records, print,  or whatever.
 END !Loop


Now I did make the above a bit more involved for a reason, this will give a
good report for a customer order by date, also you can see how to do a date
range using local variables as well... this does a inner join on the
customer / order file.


--
Andy <> Stapleton
CIO DB|Wired.com / (ccs) Cowboy Computing Solutions
www.dbwired.com      Web and Clarionet hosint
www.ccscowboy.com CCS SQL templates
www.Paywire.com    ACH / Credit Card payment processing




"Edgard L. Riba"  wrote in message
news:3d2a035f@news.softvelocity.com...
> Hi Andy,
>
> You got me completely lost here .     Could you give an example?
>
> > usiing a VIEW structure in CW I can specify whatever fields I need for
the
> > result comimg back from SQL....
> > open(ViewResult)
> > ViewResult{Prop:sql}='Select Custsysid,Custname,CustEntryDate from
> > '&NAME(Customerfile)
> > yada yada yada...
>
> How do you declare the VIEW structure for ViewResult?
>
>
> Best regards,
> Edgard L. Riba
> www.tabogasoftware.com
>
>
>
Printed May 2, 2024, 5:50 pm
This article has been viewed/printed 35111 times.
Google search has resulted in 35 hits on this article since January 25, 2004.