[Date Prev][Date Next][Thread Prev][Thread Next]
[Author Index]
[Date Index]
[Thread Index]
[SQR-USERS Info]
[SQRUG Home Page]
RE: [sqr-users] How to pass variable from a Shell Script to aSQRprogram
You have to start from the shell script. Create the environment variable there
_AND_ run SQR from the script. This is because SQR will create a new and
different command environment every time call system is used so that command
will not work.
Richard Knapp
EAS - Data Warehouse Group
University of Missouri
615 Locust Street #300 or
712 Lewis Hall
Columbia, MO 65201
573-882-8856 (LSB)
573-882-4126 (Lewis)
knappr@umsystem.edu
-----Original Message-----
From: sqr-users-bounces+knappr=umsystem.edu@sqrug.org
[mailto:sqr-users-bounces+knappr=umsystem.edu@sqrug.org] On Behalf Of Rajesh
Subramaniam
Sent: Monday, November 02, 2009 9:22 AM
To: This list is for discussion about the SQR database reporting language from
Hyperion Solutions.
Subject: Re: [sqr-users] How to pass variable from a Shell Script to a
SQRprogram
To my surprise, your suggestion was the first thing I tried being a SQR
programmer :) and was really wondering why this simple thing is not working.
Everytime a null value is being passed from the Shell Script to the SQR
code. So the SQR code does not fetch the required result.
-Raj
On Fri, Oct 30, 2009 at 3:20 PM, Putney II, Zynon E <zputney@paychex.com>wrote:
> And you can still do that. You have the line in the shell script.
>
> Change the line in the shell script that says
> sqrt ProspectTrial /
>
> To
>
> sqrt ProspectTrial / $1
> -- or however you store the variable. You run the shell script, and
> pass the parameter to the SQR.
>
> It's very simple. We do this all the time in our SQR development.
>
>
>
>
> -----Original Message-----
> From: sqr-users-bounces+zputney=paychex.com@sqrug.org
> [mailto:sqr-users-bounces+zputney <sqr-users-bounces%2Bzputney>=
> paychex.com@sqrug.org] On Behalf Of
> Rajesh Subramaniam
> Sent: Friday, October 30, 2009 4:15 PM
> To: This list is for discussion about the SQR database reporting
> languagefrom Hyperion Solutions.
> Subject: Re: [sqr-users] How to pass variable from a Shell Script to a
> SQRprogram
>
> Yup, I agree it would make things simple. But due to some reasons, my
> requirement is to get the input from the shell script and not from the
> command line while running the SQR code.
>
> The bottom line is, I have to run the shell script and the output should
> be generated i.e the queried results should be displayed.
>
> Anyways, thanks for the help
>
> -Rajesh.
>
> On Fri, Oct 30, 2009 at 3:53 PM, Putney II, Zynon E
> <zputney@paychex.com>wrote:
>
> > Append the call to the SQR program to include the state, and then use
> > an input statement inside the SQR:
> >
> > i.e.:
> >
> > Program call:
> > sqrt ProspectTrial / NY
> >
> > In SQR:
> > Input $st_abb 'Input State Abbreviation' MAXLEN=2 Uppercase $st_abb
> >
> > Should be very simple, and you don't need to read it from a file.
> >
> > -----Original Message-----
> > From: sqr-users-bounces+zputney=paychex.com@sqrug.org
> > [mailto:sqr-users-bounces+zputney
><sqr-users-bounces%2Bzputney><sqr-users-bounces%2Bzputney>=
> > paychex.com@sqrug.org] On Behalf Of Rajesh Subramaniam
> > Sent: Friday, October 30, 2009 3:47 PM
> > To: This list is for discussion about the SQR database reporting
> > languagefrom Hyperion Solutions.
> > Subject: [sqr-users] How to pass variable from a Shell Script to a
> > SQRprogram
> >
> > Hi,
> >
> > I wanted to know how to pass a variable from a shell script to a SQR
> > program? My shell script and SQR program is as shown below
> >
> > In my program, I have to pass a State Abbreviation like 'NY' ,'TX' etc
>
> > through a shell script. The output of the shell script is input to the
>
> > SQR code. So based on what state abbreviation the SQR code receives
> > from the shell script the data will be fetched from the database and
> > the result will be displayed.
> >
> > I ran my SQR code by giving a state abbreviation directly in the Query
>
> > and got the correct output. The problem I am facing is, I do not know
> > how to get the input from the shell script.
> >
> >
> > Shell Script-
> >
> > if [ $# -eq 0 ]
> > then
> > echo "$0 : you must give a state abbreviations as an argument"
> > exit 1
> > fi
> > echo $1
> > echo "entering the function"
> >
> > function returnValue
> > {
> > return $1
> > }
> > returnValue
> >
> > sqrt ProspectTrial /
> >
> >
> > SQR code- ProspectTrial
> > begin-report
> > do main
> > end-report
> >
> > begin-procedure main
> >
> > let $err_file = 'Error.txt'
> > let $input_file = '/home/jtyson/RajProject1/testVar.sh' // I am
> > getting the input here from the shell script here open $input_file as
>
> > 1 for-reading record=100 read 1 into $st_abb:2 close 1
> > display 'checking input conditions'
> > if isnull($st_abb)
> > display 'the state abbreviation is a null value'
> > else
> > let $len_st_abb = length($st_abb)
> > let $upst_abb = upper($st_abb)
> > move $len_st_abb to #len_st_abb
> > evaluate #len_st_abb
> > when > 2
> > display 'the state abbreviation is greater than
>
> > 2'
> > break
> > when < 2
> > display 'the state abbreviation is lesser than
> > 2'
> > break
> > end-evaluate
> > if($upst_abb != $st_abb)
> > display 'the input is not in uppercase'
> > end-if
> > open $err_file as 2 for-writing record=100
> > write 2 from $err_path 'error has occured and cannot proceed
> > further:'
> > display 'an error has occured due to invalid input'
> > close 2
> > end-if
> > display 'check 1 before SQL call'
> > do create_SQLquery
> >
> > end-procedure
> > begin-procedure create_SQLquery
> > display 'check 2 before opening output file'
> > let $output_file = 'Output.txt'
> >
> > open $output_file as 3 for-writing record=10000 display 'check 3
> > before executing query'
> > let $empId = ' '
> > let $empPN = ' '
> > let $empMI = ' '
> > let $empLN = ' '
> > let $empAddr1 = ' '
> > let $empAddr2 = ' '
> > let $empTermReason = ' '
> > begin-select
> > emp_id &empId
> > emp_firstname &empFN
> > emp_middle_init &empMI
> > emp_lastname &empLN
> > emp_employee_nbr &empNo
> > emp_addr1 &empAddr1
> > emp_addr2 &empAddr2
> > emp_term_reason &empTermReason
> > move &empId to $empId
> > move &empFN to $empFN
> > move &empMI to $empMI
> > move &empLN to $empLN
> > move &empNo to $empNo
> > move &empAddr1 to $empAddr1
> > move &empAddr2 to $empAddr2
> > move &empTermReason to $empTermReason
> > do write_output
> > from sales.emp
> > where emp_state = $st_abb
> > end-select
> > close 3
> > end-procedure
> > begin-procedure write_output
> >
> > write 3 from $empId:3
> > write 3 from $empFN:20 ' ' $empMI:20 ' ' $empLN:20 write 3 from
> > $empNo:20 write 3 from $empAddr1:30 if $empAddr2 != ' '
> > write 3 from $empAddr2:30
> > end-if
> > write 3 from ' '
> >
> > end-procedure
> >
> >
> > ~
> > ~
> > ~
> > ~
> >
> > _______________________________________________
> > sqr-users mailing list
> > sqr-users@sqrug.org
> > http://www.sqrug.org/mailman/listinfo/sqr-users
> >
> >
> > -----------------------------------------
> > The information contained in this message may be privileged,
> > confidential, and protected from disclosure. If the reader of this
> > message is not the intended recipient, or any employee or agent
> > responsible for delivering this message to the intended recipient, you
>
> > are hereby notified that any dissemination, distribution, or copying
> > of this communication is strictly prohibited. If you have received
> > this communication in error, please notify us immediately by replying
> > to the message and deleting it from your computer.
> >
> > Thank you. Paychex, Inc.
> >
> > _______________________________________________
> > sqr-users mailing list
> > sqr-users@sqrug.org
> > http://www.sqrug.org/mailman/listinfo/sqr-users
> >
>
> _______________________________________________
> sqr-users mailing list
> sqr-users@sqrug.org
> http://www.sqrug.org/mailman/listinfo/sqr-users
>
>
> _______________________________________________
> sqr-users mailing list
> sqr-users@sqrug.org
> http://www.sqrug.org/mailman/listinfo/sqr-users
>
_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users
_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users