[Date Prev][Date Next][Thread Prev][Thread Next]
[Author Index] [Date Index] [Thread Index]
[SQR-USERS Info] [SQRUG Home Page]

RE: [sqr-users] Run PS Query in SQR

Perhaps it would be feasible to intercept the user's desires one more step
down the road.  Don't use "Peoplesoft Query" to record SQL, and then use SQR
to execute the SQL.  Use Peoplesoft Query to produce a list of students,
have the users save the list to a text file, and then let the SQR program
read the list from a text file.

I know Aleksandra's goal is to give the users the maximum flexibility; any
query against any tables producing any group of fields.  That level of ease
would probably take far more time to deliver than the application is worth.

-----Original Message-----
From: Yuri Lakef [mailto:lakef250@yahoo.com]
Sent: Wednesday, March 05, 2003 3:58 PM
To: sqr-users@sqrug.org
Subject: RE: [sqr-users] Run PS Query in SQR



Here's a list of Query Definition PeopleTools tables:
PSQRYBIND         - Prompts
PSQRYCRITERIA  - Criteria
PSQRYDEFN        - Definitions
PSQRYDEXPR      - Expressions
PSQRYFIELD        - Fields
PSQRYRECORD   - Records
PSQRYSELECT    - Selects

 "Walcker, Paul" <pwalcker@isd.sbcounty.gov> wrote:Maybe I can help a
little. PeopleSoft provides a product that allows an end
user to generate queries (much like you would in Microsoft Access). The end
user can drag columns onto the workspace for selecting or for including in
the criteria. All of this information is eventually saved in a series of
tables (though I can't tell you what they are off the top of my head).
Conceptually, an SQR could pull the data out of these tables and construct a
query just like PSQuery does. Of course coming up with the correction
information for a bind variable is gonna be tricky. 

I personally think that it might create a support nightmare. You can always
count on end users to do the unexpected. I'd be a little more tempted to
create a run control table where the user enters a row for each individual
that needs to receive an email. The SQR would then loop through the run
control table and call the email module for each iteration.

Paul

-----Original Message-----
From: Knapp, Richard [mailto:KnappR@umsystem.edu]
Sent: Wednesday, March 05, 2003 2:50 PM
To: sqr-users@sqrug.org
Subject: RE: [sqr-users] Run PS Query in SQR



I'm stumped. I started out thinking you are fetching the text of a stored
procedure into a variable but my understanding tattered quickly after that.
I suspect we are having a communication problem. Can you provide an example
or, better yet, disregard all of your interpretations of how to solve the
problem and just describe the problem itself. For example: 1. You want to
send e-mail 2. To do this, you need the appropriate e-mail addresses and 3.
a way to send out the messages. The students you want to send to are first
year students. Information about students' year (class) is in one table,
say student_class and the e-mail addresses are in another table, say
addresses. Your SQR fires off a query to get all the first year student
ids. Embedded in this query is a call to another query to get the address
for each row returned by the first query based on the id returned by the
first query. Embedded in the second query is a call to another procedure
that constructs the e!
-mail command as a string and issues the call system using command with that
string. The beauty of SQR.

Do you have any SQR documentation available? And just to help me know what
is a communication problem and what isn't - How much SQL and SQR experience
do you have?

Richard Knapp
Database Programmer/Analyst
Institutional Research and Planning
University of Missouri System
573-882-8856
knappr@umsystem.edu


-----Original Message-----
From: Aleksandra Gacevic [mailto:AGacevic@scu.edu]
Sent: Wednesday, March 05, 2003 4:11 PM
To: sqr-users@sqrug.org; Knapp, Richard
Subject: RE: [sqr-users] Run PS Query in SQR


Hi Richard,

My problem is how to get a list of students, when sql that returns the list
is, let's say, stored in one single variable, $sql. Another problem is how
to get the sql in that variable, when all I have is bunch of query tables,
one storing records involved, other fields, third criteria, forth prompt
values etc., and they all have to be combined in order to build the sql
itself.

Thanks.

Aleksandra

>>> KnappR@umsystem.edu 03/05/03 11:01AM >>>

I don't understand what query tool tables are so maybe I don't understand
your question but what I think I understand about your question is: You
want to send e-mail to some students. You are going to make a list of
student's in SQR. I assume the SQR will also be getting their e-mail
addresses, no? At the point in your SQR where the addresses are returned, I
would branch to a procedure that assembled an operating system e-mail
command based on the address and whatever other information you have. I
would then call system using that command.

If the text of the message varies with each e-mail, you can write that text
out first and send it as a file overwriting the text for each e-mail.

Richard Knapp
Database Programmer/Analyst
Institutional Research and Planning
University of Missouri System
573-882-8856
knappr@umsystem.edu 


-----Original Message-----
From: Aleksandra Gacevic [mailto:AGacevic@scu.edu] 
Sent: Wednesday, March 05, 2003 12:34 PM
To: sqr-users@sqrug.org 
Subject: [sqr-users] Run PS Query in SQR


Hi,

I'm planning to use SQR to send email to every student whose emplid shows up
in the query (from PS Query) provided by user. Even if I manage to build sql
from Query tool tables (it will be tricky, anyone did it?), I will have
totally dynamic sql to run in SQR. It will be select query, and will have
variable number of columns (with unknown types) and tables involved. 

Do you think it's doable? Any suggestions would be appreciated...



Aleksandra Gacevic
Programmer/Analyst
Santa Clara University
408 551 7124


_______________________________________________
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


---------------------------------
Do you Yahoo!?
Yahoo! Tax Center - forms, calculators, tips, and more

_______________________________________________
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