#!/bin/ksh # Change path to ksh as needed for your environment # anc_order.ksh # by daniel.martin@mckesson.com - Fri May 21 13:41:11 CDT 2004 # 06/08/2004: Minor mods to display interface bsnm=`basename $0 .ksh` # .ksh extension is removed from filename dtst=`date +%Y%m%d` # used as part of output file name [[ -z "$ORACLE_SID" ]] && ORACLE_SID="qa" sqout="/tmp/${bsnm}_${ORACLE_SID}_${dtst}.out" iuo='@' # "at" used as Oracle output field-separator fdo='|' # pipe Used as awk output field-separator # Change value for opw as needed: [[ -z "$ORACLE_USER" ]] && opw="ccdba/ccnulls" || opw="$ORACLE_USER" clear cat < SELECT placer_app_id, COUNT(*) AS ENTRIES FROM anc_order GROUP BY placer_app_id ORDER BY placer_app_id ; SQLH awk '{printf("%13s %9d\n", $1, $2)}' | tee -a $sqout cat <>$sqout echo "User has has chosen to fetch every "$nv"-th row from the anc_order table." >>$sqout cat < row-counter, placer_app_id, placer_order_id, facility_id, pat_seq, o_occur_seq, service_code, coding_system, filler_app_id, filler_order_id, anc_status, status_desc, source_system, staff_seq THIS echo "Press [Enter] to proceed: \c" read sqyzznyx echo "Data as of: `date`\n" | tee -a $sqout # as before, pipe sqlplus output into awk for formatting and processing cat < SELECT placer_app_id, placer_order_id, facility_id, pat_seq, o_occur_seq, service_code, coding_system, filler_app_id, filler_order_id, anc_status, status_desc, source_system, staff_seq FROM anc_order WHERE placer_app_id = '$aid' ORDER BY placer_app_id, placer_order_id ; SQLH awk -v u="$iuo" -v o="$fdo" -v skip="$nv" ' # main() {gsub(" ","") # comment this line out to preserve all double spaces if (NR == 1) {prt = 1 ; ctr = skip} if (prt == 1) {printf("%5d ", NR) fld = split($0,anc,u) for (i = 1; i <= fld; ++i) printf("%s%s",anc[i], o) printf("\n") lrp = $0 # last row printed prt = 0 # turn printing back off } cur = $0 # value of current row --ctr # decrement counter if (ctr == 0) {prt = 1 ; ctr = skip} } END \ {if (lrp != cur) {printf("%5d ", NR) fld = split(cur,anc,u) for (i = 1; i <= fld; ++i) printf("%s%s",anc[i], o) printf("\n") } }' | tee -a $sqout echo "\nEOF" >>$sqout chmod 666 $sqout 2>/dev/null echo "\nAll output above has been captured into \"$sqout\".\n" # EOF