April 20, 2007, 04:06 PM
JOEJoin problems
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?
April 20, 2007, 04:24 PM
LeahJOIN
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.
April 20, 2007, 04:31 PM
FrankDutchthe mixed use of capitals and non-capitals might give you all the problems.
April 20, 2007, 04:40 PM
JOEThanks,
I'll give it a try and let you know.
April 22, 2007, 03:56 PM
JOEIs 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
April 22, 2007, 04:27 PM
FrankDutchShow 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.
April 22, 2007, 10:30 PM
StanFrank 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.
April 23, 2007, 02:57 AM
PiipsterDon'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.