Bourne & sql & I must be doing something wrong

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 15Near 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!-

You need to quote the status code tests eg stat="and status = 'O'" "Tony!" 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!- -- Paul Watson # WF Software Ltd # You are only young once Tel: +44 1436 674729 # but you can be immature Fax: +44 1436 678693 # for ever www.wfsoftware.com #

Tony! wrote in message ... >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). [snip] >if [ $oca = "O" ] >then >stat="and status = O" >fi [snip] You want: stat="and status = 'O'" >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) There are various ways of enabling the user to enter input in upper or lower case. One way is: if [ "$oca" = "O" -o "$oca" = "o" ] Note that I've also surrounded $oca with double quotes. Without it the condition will fail if the user enters nothing (a carriage-return) in response to the prompt. Other ways of enabling mixed-case input is to use tr to convert the input to upper-case before passing into your if condition, oca=`echo $oca | tr '[a-z]' '[A-Z]'` or using your database's toupper (or similar function), eg: stat="and status = toupper('$oca')" Dave. -- If you reply to this posting by email, remove the "nospam" from my email address first.

Thanks to all for the replies! They were all helpful! I aooreciate it!! Tony! On Thu, 13 Apr 2000 20:09:27 +0100, "Dave Wotton" <Dave.Wotton@dwotton.nospam.clara.co.uk> wrote: >Tony! wrote in message ... >>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).> >[snip] > >>if [ $oca = "O" ] >>then >>stat="and status = O" >>fi > > >[snip] > >You want: stat="and status = 'O'" > >>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) > >There are various ways of enabling the user to enter input in upper >or lower case. One way is: > >if [ "$oca" = "O" -o "$oca" = "o" ] > >Note that I've also surrounded $oca with double quotes. Without it >the condition will fail if the user enters nothing (a carriage-return) >in response to the prompt. > >Other ways of enabling mixed-case input is to use tr to convert the >input to upper-case before passing into your if condition, > > oca=`echo $oca | tr '[a-z]' '[A-Z]'` > >or using your database's toupper (or similar function), eg: > > stat="and status = toupper('$oca')" > >Dave. >-- >If you reply to this posting by email, remove the "nospam" from my email >address first. > > > >