I am trying to alias a table because I have to join to it twice in the same query.
Here’s what the query should do. But I need to output it to a text file.
------------------------------------------------------------------------------
This works in the Procedure Editor but I need a pipe delimited text file as the output so here have I have so far:
This works as well but I do not have the pcp.rescName Field. How do I join to the rescMast table again? How do I include that field? Is there a way to join to the rescMast table twice using an alias in the second example?
Thanks you,
Will
Here’s what the query should do. But I need to output it to a text file.
------------------------------------------------------------------------------
Code:
SELECT
appMastr.apptNum,
appMastr.facKey,
appMastr.rescNum,
appMastr.apptStat,
appMastr.apptType,
appMastr.apptDt,
appMastr.apptTm,
appMastr.apptDur,
appMastr.apptReason,
appMastr.mastNum,
faclMast.facName,
rescMast.rescName,
apptStat.ddesc,
patnMast.lName,
patnMast.fName,
patnMast.mName,
patnMast.salutation,
patnMast.patnTitle,
patnMast.socNum,
patnMast.dob,
patnMast.deceasedDt,
patnMast.sex,
patnMast.mrtlCd,
patnMast.raceCd,
patnMast.vip,
patnMast.adNum,
patnMast.street1,
patnMast.street2,
patnMast.city,
patnMast.state,
patnMast.zip,
patnMast.phoneEvn,
patnMast.phoneDay,
patnMast.Comment,
patnMast.rescNum,
pcp.rescName
FROM appMastr
JOIN faclMast ON (faclMast.facKey = appMastr.facKey)
JOIN rescMast ON (rescMast.rescNum = appMastr.rescNum)
JOIN apptStat ON (apptStat.apptStat= appMastr.apptStat)
JOIN patnMast ON (patnMast.mastNum = appMastr.mastNum)
JOIN rescMast pcp ON (pcp.rescNum = patnMast.rescNum)This works in the Procedure Editor but I need a pipe delimited text file as the output so here have I have so far:
Code:
[FONT=Consolas][SIZE=3]DEFINE VARIABLE lngCt AS INTEGER.[/SIZE][/FONT]
[FONT=Consolas][SIZE=3]DEFINE QUERY Phytel_APPT FOR appMastr FIELDS (apptNum facKey rescNum apptStat apptType apptDt apptTm apptDur apptReason mastNum),[/SIZE][/FONT]
[SIZE=3][FONT=Consolas] faclMast FIELDS (facName),[/FONT][/SIZE]
[SIZE=3][FONT=Consolas] rescMast FIELDS (rescName),[/FONT][/SIZE]
[SIZE=3][FONT=Consolas] apptStat FIELDS (dDesc),[/FONT][/SIZE]
[SIZE=3][FONT=Consolas] patnMast FIELDS (lName fName mName salutation patnTitle socNum dob deceasedDt sex mrtlCd raceCd vip adNum street1 street2 city state zip phoneEvn phoneDay rescNum). [/FONT][/SIZE]
[FONT=Consolas][SIZE=3]/*------------------------------------------------------------------*/[/SIZE][/FONT]
[FONT=Consolas][SIZE=3]/* specify output is to go to file */[/SIZE][/FONT]
[FONT=Consolas][SIZE=3]/*------------------------------------------------------------------*/[/SIZE][/FONT]
[FONT=Consolas][SIZE=3]OUTPUT TO VALUE("C:\Program Files\Phytel.net\Outgoing\APPT\APPT.dat").[/SIZE][/FONT]
[FONT=Consolas][SIZE=3]/*------------------------------------------------------------------*/[/SIZE][/FONT]
[FONT=Consolas][SIZE=3]/* open query and loop through it */[/SIZE][/FONT]
[FONT=Consolas][SIZE=3]/*------------------------------------------------------------------*/[/SIZE][/FONT]
OPEN QUERY Phytel_APPT FOR EACH appMastr,
EACH faclMast WHERE faclMast.facKey = appMastr.facKey,
EACH rescMast WHERE rescMast.rescNum = appMastr.rescNum,
EACH apptStat WHERE apptStat.apptStat = appMastr.apptStat,
EACH patnMa WHERE patnMast.mastNum = appMastr.mastNum.
REPEAT:
SET lngCt = lngCt + 1.
GET NEXT Phytel_APPT.
EXPORT DELIMITER "|" ""
"JAG001-A4"
"JAG001-A4"
STRING(TODAY) + " " + STRING(TIME, "HH:MM:SS")
STRING(TODAY) + "-" + STRING(TIME, "HH:MM:SS") + "-" + STRING(lngCt, "99999999")
appMastr.apptNum
appMastr.facKey
appMastr.rescNum
appMastr.apptStat
appMastr.apptType
appMastr.apptDt
appMastr.apptTm
appMastr.apptDur
REPLACE(REPLACE(REPLACE(appMastr.apptReason, "~n", ""), "~r", ""), "~r~n", "")
appMastr.mastNum
faclMast.facName
rescMast.rescName
apptStat.ddesc
patnMast.lName
patnMast.fName
patnMast.mName
patnMast.salutation
patnMast.patnTitle
patnMast.socNum
patnMast.dob
patnMast.deceasedDt
patnMast.sex
patnMast.mrtlCd
patnMast.raceCd
patnMast.vip
patnMast.adNum
patnMast.street1
patnMast.street2
patnMast.city
patnMast.state
patnMast.zip
patnMast.phoneEvn
patnMast.phoneDay
patnMast.rescNum.
END.
CLOSE QUERY Phytel_APPT.
OUTPUT CLOSE.This works as well but I do not have the pcp.rescName Field. How do I join to the rescMast table again? How do I include that field? Is there a way to join to the rescMast table twice using an alias in the second example?
Thanks you,
Will