Multiple DST in TABLE FILE

[Ricardo Lara]

Hi,

I'm using version 8203, and I was told that I shouldn't use multiple DST within the same TABLE FILE because it doesn't give you the distinct values in the hierarchy the fields are listed in the TABLE FILE (dependent). That I should create a HOLD with the first field, and then do another HOLD with it for the second field.

I was using it even with more than 2 DSTs and didn't see any issue, so I was wondering if someone can confirm if I can use multiple DSTs or not.

This is a sample of the way I've been using it:

TABLE FILE LEDGER

PRINT 

 DST.YEAR

 DST.ACCOUNT

END

I checked in the manual and found that PRINTDST controls on how DST works, and the default value is "OLD" and this is the way I need the distinct to work (dependent on each other).

Output sample (I added the hyphens):

YEAR - ACCOUNT

1985-1010

1985-1020

1985-1030

1985-1100

1985-1200

1986-1010

The "NEW" value gave me all the values in the table repeating some of them and this is not what I need.

Thanks in advance,

Ricardo

[Toby Mills]

Hi Ricardo

If I'm understanding your goal correctly, you might try one of these:

TABLE FILE LEDGER

SUM

MAX.ACCOUNT NOPRINT

BY YEAR

BY ACCOUNT

END

Or, if you get better looking SQL out of it, stick with DST but just add a BY for the YEAR:

TABLE FILE LEDGER

PRINT

DST.ACCOUNT

BY YEAR

END

Do either of those work for you?

As a side note, it's worth turning on your SQL traces just to make sure you're getting an efficient answer.

[Ricardo Lara]

Thanks for your answer Toby,

I did some changes to the first example you gave me and I got the result I wanted.

The scenario I have is that I have tables that have Year, Month (1-12), and Week of the month (1-4), and I wanted to use DST with each one of these fields so I could check there are no missing months and weeks in the data.

This is what I was trying to do, and was told I shouldn't use more than one DST in a TABLE FILE:

TABLE FILE AZ_TEST_TABLE

PRINT

DST.AZ_YEAR

DST.AZ_MONTH

DST.AZ_WEEK_OF_MONTH

END

-RUN

This is what I end up doing:

TABLE FILE AZ_TEST_TABLE

PRINT

  DST.AZ_WEEK_OF_MONTH

BY AZ_YEAR

BY AZ_MONTH

END

Both of them gave me the same result, so, just for curiosity I would like to know why I shouldn't use several DST in a TABLE FILE.

I'm not familiar with SQL traces, so if you can point me in the right direction I'd really appreciate it.

Thanks!

[David Beagan]

In older Creating Reports with WebFOCUS Language manuals it would caution you:

 

This might be what the person was thinking of when they said you shouldn't use multiple DST.

In later releases, the Creating Reports manual explicitly states:

 

Mixing DST. and BY seems confusing to me. For getting a list of distinct values with BY, you don't even need a verb PRINT. My preference would be to simply:

TABLE FILE AZ_TEST_TABLE

BY AZ_YEAR

BY AZ_MONTH

BY AZ_WEEK_OF_MONTH

END

[Ricardo Lara]

I didn't know I could use a TABLE FILE without a verb. Using BYs without DST solves my problem.

I had read in the manual that part where it says that it is possible to use multiple DST, but got confused when I was told not to use them.

Thanks for your help !

[Toby Mills]

Hi Ricardo and David

I feel like the Walters could expound on this more

<old guy core FOCUS lesson>

I think you ALWAYS end up with a VERB whether you know it or not.

There are 4 verbs - we often forget about a couple of these:

PRINT, LIST, SUM and COUNT.

Let me use the CAR and COUNTRY for example purposes to show what I think goes on when you don't specify a VERB:

TABLE FILE CARBY COUNTRYEND

If I run this, I'll get what you expect based on our discussion above. Just 1 column, right?

PAGE 1COUNTRYENGLANDFRANCEITALYJAPANW GERMANY

But internally, I think it generates a SUM COUNTRY NOPRINT BY COUNTRY - it adds the first fieldname it sees in the BY statements to the output. Then it NOPRINT's it just to be nice. Because WF knows you probably didn't want to see this.

Here's a way to prove that and might help if you see 2 of the same FIELDNAME in your HOLD files. Try this:

TABLE FILE CARBY COUNTRYON TABLE HOLDEND-RUN?FF HOLD

Ricardo - you might not have seen that ?FF HOLD before. I could say ?FF CAR for example and get a list of Field/Formats (thus the FF) for the Master file I name. We'll use ?FF to ask what fields and formats exist in the hold file.

Here's what you'll see:

Detail: 0 NUMBER OF RECORDS IN TABLE= 5 LINES= 5 FILENAME= HOLD COUNTRY E01 A10 COUNTRY E02 A10

How about that? 2 COUNTRY fields. That's because FOCUS is adding the SUM COUNTRY NOPRINT at the beginning of your request to be nice. The BY Fields are first in the HOLD file and the NOPRINTed field is at the end. It's kind of hard to see when there's just the one field.

Lets drill this info in the brain a little more. Here's a slightly different way to see at this behavior when we use more fields in our BY statemtents. First, since I brought it up, lets do this:

?FF CAR

That shows me all my options for fields (not segments - just fields and formats):

Detail: FILENAME= CAR COUNTRY COUNTRY A10 CAR CARS A16 MODEL MODEL A24 BODYTYPE TYPE A12 SEATS SEAT I3 DEALER_COST DCOST D7 RETAIL_COST RCOST D7 SALES UNITS I6 LENGTH LEN D5 WIDTH WIDTH D5 HEIGHT HEIGHT D5 WEIGHT WEIGHT D6 WHEELBASE BASE D6.1 FUEL_CAP FUEL D6.1 BHP POWER D6 RPM RPM I5 MPG MILES D6 ACCEL SECONDS D6 WARRANTY WARR A40 STANDARD EQUIP A40

Now I'll make a report with 4 columns:

TABLE FILE CARBY COUNTRYBY CARBY MODELBY BODYTYPEWHERE COUNTRY EQ 'ENGLAND';WHERE CAR EQ 'TRIUMPH';END

That report output looks like this:

COUNTRY CAR MODEL BODYTYPEENGLAND TRIUMPH TR7 HARDTOP

4 columns right? If I were to add up the length of all the columns to know how wide a SAVE file might be, I'd think it's 62 characters wide (the 4 field lengths added together as shown above in ?FF).

COUNTRY COUNTRY A10 CAR CARS A16 MODEL MODEL A24 BODYTYPE TYPE A12

But I'd be wrong - lets add on ON TABLE SAVE to the picture and see what FOCUS actually writes to this file:

TABLE FILE CARBY COUNTRYBY CARBY MODELBY BODYTYPEWHERE COUNTRY EQ 'ENGLAND';WHERE CAR EQ 'TRIUMPH';ON TABLE SAVEEND

The cool thing about ON TABLE SAVE is that it shows you the field lengths that are in the save file:

Detail: 0 NUMBER OF RECORDS IN TABLE= 1 LINES= 1 ALPHANUMERIC RECORD NAMED SAVE 0 FIELDNAME ALIAS FORMAT LENGTH COUNTRY COUNTRY A10 10 CAR CARS A16 16 MODEL MODEL A24 24 BODYTYPE TYPE A12 12 COUNTRY COUNTRY A10 10 TOTAL 72

Notice it's 10 characters bigger than we thought because of the unseen NOPRINT on COUNTRY adds 10 more characters to the end.

Hope that helps explain a little of why it seems like you're not using a VERB or VERB OBJECT, but actually, you are...

</old core FOCUS lesson>

[Ricardo Lara]

Hi Toby,

I ran your scripts but I got different results.

This one gave me just one field:

TABLE FILE CAR

BY COUNTRY

ON TABLE HOLD

END

-RUN

0 NUMBER OF RECORDS IN TABLE= 5 LINES= 5

?FF HOLD

FILENAME= HOLD

COUNTRY E01 A10

-*=============================

This one gave me 62 characters length:

TABLE FILE CAR

BY COUNTRY

BY CAR

BY MODEL

BY BODYTYPE

WHERE COUNTRY EQ 'ENGLAND';

WHERE CAR EQ 'TRIUMPH';

ON TABLE SAVE

END

0 NUMBER OF RECORDS IN TABLE= 1 LINES= 1

ALPHANUMERIC RECORD NAMED SAVE

0 FIELDNAME ALIAS FORMAT LENGTH

COUNTRY COUNTRY A10 10

CAR CARS A16 16

MODEL MODEL A24 24

BODYTYPE TYPE A12 12

TOTAL 62

Could be WF versions, source or configuration?

Thanks!

[Toby Mills]

Hi Ricardo

Sounds like somewhere in your environment you have a

SET HOLDLIST=PRINTONLY

My results are from the default

SET HOLDLIST=ALL

You can check your setting by using :

? SET HOLDLIST

It'll show you if it's set to PRINTONLY (which really makes sense for most people to use PRINTONLY).

If you want to see my results you might need to add

SET HOLDLIST=ALL

prior to your test focexecs.

This setting is probably made way up in your EDASPROF.prf ...

Let me know how it goes iwith ALL instead of PRINTONLY.

I bet that's the difference.

[Ricardo Lara]

Yeap, that was the difference, got your results.

Thanks!