This may be an easy question to answer but just looking for confirmation. I would like to reference a field from a join, but would this be the same as most sql statements that use the table.fieldname convention? What happens in my case is that I usually run a selection from a base table such as CARS and call it IMPORTS, then I rejoin that back with the original CARS table, but then I might have duplicate field names. If I wanted to reference the IMPORT.PRICE instead of the CARS.PRICE, would I just use PRICE or can I actually try to specify it as mentioned?

Thanks!

You can use IMPORT.PRICE.

When you "rejoin" what I'm assuming is a HOLD file to the SQL table, you are aware that efficient SQL will not be passed to the DBMS...

Well, basically it's the join that occurs in the FOCUS text. Following your advice I'd do something like:

JOIN CAR_VIN IN IMPORTS TO CAR_VIN IN CARS

TABLE FILE IMPORTS
PRINT
IMPORTS.IMPORTS_PRICE
CARS.CARS_PRICE
IMPORTS_VIN
BY IMPORTS_VIN NOPRINT
END

Is this correct? I think I tried it once but I had gotten an error on it, so I ended up using a DEFINE on the first hold to keep the names separate.

Thanks!

This message has been edited. Last edited by: gregson06,

I don't see anything wrong with the code. But what's going on behind the scenes is what I was concerned with. When you join a HOLD file to a SQL table and use SUM, WebFOCUS may not be able to generate efficient SQL.

As to the Address issue, are all the addresses stored in the same table? Have you looked into the TAG attribute in the JOIN syntax?

JOIN field1 [AND field1a...] IN host [TAG tag1]TO [ALL] field2 [AND field2a...] IN crfile [TAG tag2] [AS joinname]
END

e.g.

JOIN HOME_ADD_CODE IN TABLE1 to ADD_CODE in ADD_TBL TAG HOME AS J1
JOIN WORK_ADD_CODE IN TABLE1 to ADD_CODE in ADD_TBL TAG WORK AS J2

TABLE FILE TABLE1
PRINT 
HOME.ADD_LINE1 HOME.CITY
WORK.ADD_LINE1 WORK.CITY
...

Efficient it is not, I agree, however if you want have you considered using the ASNAMES option.
TABLE FILE CAR
PRINT PRICE AS IMPORT_PRICE
...
ON TABLE SET ASNAMES ON
ON TABLE HOLD AS IMPORTS
END

Now IMPORTS will have a different name for price.

Of course, without knowing just what you are really trying to do, it seems if you are just wanting information from the file in this case 'imports' why not just pull all the data you need on the first pass?

Francis,

As to the tag attribute, I think I'll try it out next time.

As far as the join itself is concerned, I am not sure what would be a more efficient manner of calling the join. I understood that a call for data using joins would be returned with an answer set on the first pass. Whereas if a hold is made, there is a second pass required but I just wanted to confirm the nomenclature for declaring a field name that exists in two tables.

Leah, the ASNAMES I did not know could be done in the TABLE block. I tried something similar with COMPUTE but I can't do 'where' statements on a COMPUTE field and is why I went to defines. Are where's allowed for ASNAMES?

I was running a define that would reduce the sum of a column in question. However, since I used a sum, I could not verify what the original values were since each grouping (or BY's) had various rows. After calling the join, I could reference the original number of rows and their values to make sure that the summary was complete.

E.G.

TABLE FILE CARS
SUM
SALES
BY CAR
ON TABLE HOLD AS HOLD1
END

JOIN CAR ON HOLD1 TO ALL CAR ON CARS
TABLE FILE HOLD1
PRINT
HOLD1.SALES AS 'TOTAL'
HOLD2.SALES AS 'UNIT'
BY CAR
END

This message has been edited. Last edited by: gregson06,

quote:

Are where's allowed for ASNAMES?

Yes if the where clause is in the hold file where the field now has a new name.

As to piece of mind, have you considered producing two reports with one pass of the data, one a summary and one a detail with the data subtotaled, then by pass the detail one once you have piece of mind.

Yeah, that was a considerable option that would have save much turmoil and confusion, but I suppose that the keystrokes were a bit more comfortable. This would also have worked great, too. I will try to use the AS feature on one of my next projects as I didn't think that the AS names held in a hold. In my mentality, I figured that they were placeholders for the column names and not the actual metadata of the hold itself. In fact, you can still use the original name on the next procedure, I believe, and I think that's what threw me off of that.

Strange you say that asnames didn't hold?

Actually, just now verified it, ASNAMES does work, had not seen the ON TABLE SET ASNAMES ON declaration. In terms of efficiency, is this more recommendable than using a DEFINE? I'm not sure which would translate to SQL more fluidly.

Thanks!

quote:

In terms of efficiency, is this more recommendable than using a DEFINE?

In my humble opinion, YES.