Views Beginner 1

[mlowe9]

Would someone be willing to give me a very basic example of setting up and using a view? I have been through the help file many times, and it just isn't clicking for me. If you'd be willing to give me an example that would work. Just a simple example would help me tremendously. Please...PLEASE...and Thank you. I'm using 5.5 ABC...

 

[ShankarJ]

Hi!

A Clarion view is basically a way of retrieving data from one or more related tables/files either from an ISAM or SQL database. In a SQL database, the backend database engine does most of the optimization but for ISAM files, the View Engine does it. The View performance will be optimal for ISAM tables if appropriate keys are available.

So, in the example of VIEW in the help :

[small]
ViewOrder VIEW(Customer)                   !Declare VIEW structure
      PROJECT(Cus:AcctNumber,Cus:Name)
      JOIN(Hea:AcctKey,Cus:AcctNumber)     !Join Header file
       PROJECT(Hea:OrderNumber)
       JOIN(Dtl:OrderKey,Hea:OrderNumber)  !Join Detail file
        PROJECT(Dtl:Item,Dtl:Quantity)
        JOIN(Pro:ItemKey,Dtl:Item)         !Join Product file
         PROJECT(Pro:Description,Pro:Price)
        END
       END

      END
     END
[/small]

declares that :

CUSTOMER is the main table which is related to the HEADER table by CUS:AcctNumber = HEA:AcctNumber using HEA:AcctKey which is then related to the DETAIL table by HEA:OrderNumber = DTL:OrderNumber by DTL:OrderKey which is than related to the PRODUCT table by DTL:Item = PRO:Item by the PRO:ItemKey.

The columns/fields being retrieved in this view are :

Cus:AcctNumber
Cus:Name
Hea:OrderNumber
Dtl:Item
Dtl:Quantity
Proescription
Prorice

To Access the same table without a VIEW would be :

[small]
SET(CUS:AcctKey)
LOOP
   NEXT(CUSTOMER)

   IF ERRORCODE() THEN BREAK.

   CLEAR(HEA:Record)
   HEA:AcctNumber = CUS:AcctNumber
   SET(HEA:AcctKey, HEA:AcctKey)
   LOOP
      NEXT(HEADER)

      IF ERRORCODE() OR HEA:AcctNumber <> CUS:AcctNumber THEN BREAK.

      CLEAR(DTL:Record)
      DTL:OrderNumber = HEA:OrderNumber
      SET(DTL:OrderKey, DTL:OrderKey)
      LOOP
         NEXT(DETAIL)

         IF ERRORCODE() OR DTL:OrderNumber <> HEA:OrderNumber THEN BREAK.

         CLEAR(PRO:Record)
         PRO:Item = DTL:Item
         GET(PRODUCT, PRO:ItemKey)
         IF ERRORCODE() THEN CLEAR(PRO:Record).

         ... YOUR PROCESSING CODE HERE ...

      END

   END
END
[/small]

So the hierarchy would be :

CUSTOMER (Parent)
|
--- HEADER (Child)
|
--- DETAIL (GrandChild) --- PRODUCT (Lookup)


Regards

 

[mlowe9]

Thank you. Very helpful.

Using your example, if there were a particular "Product" record I wanted in the join, do I have to loop through all the related records, or can I filter the specific record I want. I assume I could use some filter property like PROP:SQL, PROP:SQLFilter, or PROP:Filter...

I've only been working with Clarion for around a year, but mostly very simple programming syntax. I really want to learn more - but the resources available are scarce, so I REALLY appreciate your help. I was used to doing web programming, VB, etc before so I could pretty much go anywhere and find what I wanted...not anymore!

 

[mlowe9]

Oh, I guess I need to ask: My particular situation has a "main" table with many related child tables. So using your example, would I do my view like this:

ViewOrder VIEW(Customer)                   
      PROJECT(Cus:AcctNumber,Cus:Name)
      JOIN(Hea:AcctKey,Cus:AcctNumber)
       PROJECT(Hea:OrderNumber)
      END
      JOIN(Dtl:AcctKey,Cus:AcctNumber)
        PROJECT(Dtl:Item,Dtl:Quantity)     
      END
      JOIN(Pro:ItemKey,Dtl:Item)       
        PROJECT(Pro:Description,Pro:Price)
      END
    END

 

[mlowe9]

Oops...I didn't quite put that the way I wanted. But the structure is what I was referring to. I realize the linking fields would be different.

 

[ShankarJ]

Hi!

In your example, this :

JOIN(Dtl:AcctKey,Cus:AcctNumber)
PROJECT(Dtl:Item,Dtl:Quantity)
END
JOIN(Pro:ItemKey,Dtl:Item)
PROJECT(Proescription,Prorice)
END

needs to be:

JOIN(Dtl:AcctKey,Cus:AcctNumber)
PROJECT(Dtl:Item,Dtl:Quantity)
JOIN(Pro:ItemKey,Dtl:Item)
PROJECT(Proescription,Prorice)
END
END

So if your requirement is to retrieve all the child tables of the parent table, you might need seperate views or all your records will be duplicated. Maybe a view is not necessary as you loop thru all the child tables.

SET(ParentFileKey)
LOOP
NEXT(ParentFile)

IF ERRORCODE() OR <break condition> THEN BREAK.

CLEAR(Child1Record)
<assign child 1 linking fields from parent>
SET(Child1ParentKey, Child1ParentKey)
LOOP
NEXT(Child1File)

IF ERRORCODE() OR Child1LinkFields <> ParentFields THEN BREAK.

END

CLEAR(Child2Record)
<assign child 2 linking fields from parent>
SET(Child2ParentKey, Child2ParentKey)
LOOP
NEXT(Child2File)

IF ERRORCODE() OR Child2LinkFields <> ParentFields THEN BREAK.

END

...
END

Regards

 

[mlowe9]

Can you tell me why this won't work? I get nothing in my message. I'm just trying to make the simplest view I can, and I still can't get it working. I'm kind of basing it on information you're giving with some example code I have.

TEST_JML             PROCEDURE       

View:CLM        View(Claims)
                  PROJECT(CLM:Clm_ID,CLM:PFName,CLM:PMI,CLM:PLName)
                .

  CODE
  PUSHBIND

  SET(CLM:CLM_IDKey)                          ;IF ERRORCODE() THEN MESSAGE('ERROR1').
  View:CLM{Prop:Filter} = 'CLM:clm_id = 2'    ;IF ERRORCODE() THEN MESSAGE('ERROR1').
  OPEN(View:CLM)                              ;IF ERRORCODE() THEN MESSAGE('ERROR1').

  message(CLM:PFName & ' ' & CLM:PMI & ' ' & CLM:PLName,CLM:CLM_ID)

  CLOSE(View:CLM)

I do have related tables, but I'm just trying to get it working with one so I can build on it.

Thanks Again,
Matt

 

[mlowe9]

WAIT WAIT...I THINK I HAVE SOMETHING!!!
I guess I need a NEXT to call the record??? Oh....how exciting!

Thanks again ShankarJ, hopefully I can get it from here - but don't be surprised to see me again. Here's another star for all your help!

Thanks
Matt

 

[ShankarJ]

Hi Matt (at last a name),

I think you have got it. Views need to be processed like a table i.e. LOOP ... NEXT(View) ... END.

Could you tell me which version of Clarion you are using and whether you code in ABC/Legacy (your code looks legacy though).

Thanks for the star.

Regards

 

[mlowe9]

Using 5.5, and it is ABC, but I think we're using the legacy commands for now. I did glance at the ViewManager stuff...but I was having so many problems just getting the simple legacy code to work.