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

["Aleksandra Gacevic" <AGacevic@scu.edu>]

Hi Steve,

The first part - building sql out of query tables - is doable, I have no 
doubts. 
The second part - running it in SQR to identify students - probably not, 
because it would be totally dynamic sql. That's where I mentioned variable - 
imagine there is one string that contains the whole sql, with any number of 
fields selected (of any types) and any number of tables - how to run it in SQR 
and get selected fields (email address for example, or any merge field) in SQR 
variables?

I considered query approach because it is very open and generic. Every day 
users might think of new population of students they want to send new email 
text to. They would simply have to create a new query with any selection 
criteria they want (and a new text message, of course), pass them to the email 
program - and voila! Query was supposed to be one option, the other would be 
text file to identify students (for example output of cclettergen process). 
That option is so much easier to program, so my final solution will probably 
include only that part - ask users to run a query, save produced list of 
students in a file, upload the file to UNIX and then let my program read the 
list from the file (as someone on the listserv suggested already). It's more 
work for users, but is still very flexible and  so much easier for me to 
implement :-).

Aleksandra

>>> sstein26@comcast.net 03/08/03 05:30PM >>>
Aleksandra -

This seems like it should be do-able, since that is how Peoplesoft creates
its SQL, by using these tables.  Depending on how complicated the query is,
you will have to incorporate various features.

The single variable, I assume, is the e-mail address you will be passing to
the e-mail program.  I have not done this, I have always used looping and
fired the e-mail one adressee at a time.  But it works to haev more than one
addressee on the e-mail... I don't know if there's some kind of limit -
maybe the e-mail program defines that.

By the way, I am very interested in why you are going this route.  Is it
because you find that query tool has become the best methodology for
allowing your users to create criteria that returns exactly what they want
for a mailing list?

- Steve


----- Original Message -----
From: "Aleksandra Gacevic" <AGacevic@scu.edu>
To: <sqr-users@sqrug.org>; <KnappR@umsystem.edu>
Sent: Wednesday, March 05, 2003 5:10 PM
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