Hello,

I am unable to get the joins right on my focus code. I have to hard code this due the GUI being inoperative on my PC. This data is being pulled off of a Sequel server.

Code:

* File cvtappevent.fex



JOIN
APP_EVENTS.APPLICATION IN APP_EVENTS TO
BUSSEG01.APPCRYPTIC IN BUSSEG01 AS J0
End

JOIN APP_EVENTS.APPLICATION AND APP_EVENTS.EVENT_PARTKEY IN APP_EVENTS TO
TRANSFER_EVENTS.APPLICATION AND TRANSFER_EVENTS.EVENT_PARTKEY IN TRANSFER_EVENTS AS J1
END


TABLE FILE APP_EVENTS
SUM
'CNT.APP_EVENTS.BUSSEG1/I7' AS 'Total Calls'
SUM
'CNT.TRANSFER_EVENTS.BUSSEG1/I7' AS 'TRANSFER CALLS'
BY
'APP_EVENTS.BUSSEGA' NOPRINT
BY
'APP_EVENTS.BUSSEG1'
WHERE event_partkey EQ 2007041700
-*WHERE event_partkey GE 2007041200 AND event_partkey LE 2007041200
-*WHERE APP_EVENTS.APPLICATION EQ '1st_Horizon_2'
WHERE (machine_id EQ 'PPNTVA08') OR (machine_id EQ 'PPNTVA17') OR (machine_id EQ 'PPNTVA07') OR (machine_id EQ 'PPNTVA16') OR (machine_id EQ 'PPNTVA04') OR (machine_id EQ 'PPNTVA13') OR (machine_id EQ 'PPNTVA05') OR (machine_id EQ 'PPNTVA12') OR (machine_id EQ 'PPNTVA14') OR (machine_id EQ 'PPNTVA03') OR (machine_id EQ 'PPNTVA19') OR (machine_id EQ 'PPNTVA06') OR (machine_id EQ 'PPNTVA20') OR (machine_id EQ 'PPNTVA15')
WHERE event_action EQ 'CHAIN'


ON TABLE PCHOLD FORMAT EXCEL
END

Are my joins incorrect?

JOIN
APP_EVENTS.APPLICATION IN APP_EVENTS TO
BUSSEG01.APPCRYPTIC IN BUSSEG01 AS J0
END

JOIN
APP_EVENTS.APPLICATION AND APP_EVENTS.EVENT_PARTKEY IN APP_EVENTS TO
TRANSFER_EVENTS.APPLICATION AND TRANSFER_EVENTS.EVENT_PARTKEY IN TRANSFER_EVENTS AS J1
END

You're first 'end' was 'End' not 'END' will that help. Since don't know what is in the files themselves.

the mixed use of capitals and non-capitals might give you all the problems.

Thanks,
I'll give it a try and let you know.

Is there any way to speed up the data retrieval process? I can not tell if this join works or not. I keep getting a timout error or something to that nature. I'm pulling data off of sequel server. Thanks,

Joe

Show us the masters.
It might be important how the keys and indices in the databases are made.
how many records do you expect.
Maybe it is better to first get the records from app_event that you need (only one day)
the double link might give some problems.

can you do the join on the database and make a view there?

So the answer is not so easy based upon only the FEX.

Frank is probably your better source, but as way of translation, if your files are very large it may be worthwhile to do a run of the two files into hold files first and order them on the field you are joining on. You can also do them
ON TABLE HOLD AS HDL1 FORMAT FOCUS INDEX FieldName

Generally speaking the smaller you can make the files before the join the better. If you are ultimately going to do a limit, do it early.

Just my two cents worth.

Don't forget about SQL tracing as well. Turn it on to see the generated SQL and use the SET XRETRIEVAL=OFF to just generate the SQL without extracting the data. This should help you to see if you are generating optimal sql.