Re: Bourne & sql & I must be doing something wrong

Tony! <NoSpam@Thank.You.com> wrote: >When running a script I made up, I get an error ... > >Database selected. > > > 217: Column (o) not found in any table in the query (or SLV is >undefined). >Error in line 15>Near character position 1 > >___________________________________________________________________________ >Below is the pertinent part of the script in question >___________________________________________________________________________ > > >: >clear >echo " Prototype of PMS Reporting System" >echo " Beta " >echo "\\n\\n" >echo "please enter the start date (mmddyyyy):\\c" >read date1 >echo "Please enter the end date (mmddyyyy):\\c" >read date2 >echo "Please enter the Priority ("1", "2", "3"):\\c" >read num >echo "(O)pen or (C)losed or (A)ll ("O","C","A"):\\c" >read oca >if [ $oca = "O" ] >then >stat="and status = O" >fi > >if [ $oca = "C" ] >then >stat="and status = C" >fi > >if [ $oca = "A" ] >then >stat=" " >fi > >echo "\\n\\n " >cat << __ending > tonycool.sql >database help_desk@iota; >unload to kooky.txt >select report_dt, report_tm, resolv_dt, resolv_tm, ticket_no, >facility, >operator, status, assn_to, priority,> > INTERVAL(0) MINUTE(9) TO MINUTE + ( >(EXTEND(EXTEND(resolv_dt, YEAR TO DAY), YEAR TO MINUTE) + > (resolv_tm - DATETIME(00:00) HOUR TO MINUTE) - >(EXTEND(EXTEND(report_dt, YEAR TO DAY), YEAR TO MINUTE) + > (report_tm - DATETIME(00:00) HOUR TO MINUTE)))) elapsed_mins > >from help where report_dt between "$date1" and "$date2" and priority = >"$num" >${stat} > > >group by report_dt, report_tm, resolv_dt, resolv_tm, ticket_no, >facility, >operator, priority, status, assn_to; > >__ending > > >chmod 777 tonycool.sql > >isql < tonycool.sql > >cat kooky.txt | tr -d ' \\t' > kooky >rm kooky.txt >echo "To Printer? or To Screen? \\c" >read ans >case $ans in ><Snip> > > > >_________________________ >The script was working fine until I added the if statements for the >Open/closed/all question it prompts the user for, then places as a >variable in the sql portion. >How can I get this to work? > >How i'd like to have it, is for the user to enter either capital or >lower case... (i.e- c or C for (c)losed) >Helllllllllp? > >-Tony!- I'd change the multiple if statements to a case statement: case $oca in C|c) stat="and status = C";; ... esac Do the same for your other options. I'd also dump your sql to a file and then look to see what it really says. That usually leads me in the right direction. carlos carlos Currently on hiatus from unemployment. ---------------------- Do you do Linux? :) Get your FREE @linuxstart.com email address at: http://www.linuxstart.com