Quiz and SQL

[terry.curran@cwcom.net]

Alternatively you could always read the PowerHouse Manuals  PH8 manuals
are distributed in PDF form on CD, or available from Cognos Website to
download.  - They all have a section on using relational databases, including
examples of code!!! 

Quoting "Boyle, Joe" <Joe.Boyle@cognos.com>:

> Hi Seamus,
> 
> there is quite a lot to know when using SQL in PowerHouse, and I would
> suggest that you get yourself booked on the 'PowerHouse relational
> interface
> course parts 1 and 2' if at all possible.
> 
> The 'SET LIST SQL' syntax reports the SQL syntax that it will send to
> the
> database, see below for an example of the output generated from a Quiz
> cursor statement, it does not expand non SQL syntax e.g. 'access
> file'.
> 
> Quiz
>  
> > set list sql
> > sql in genesis                  &
> > declare my_cursor cursor for    &
> > select student                  &
> >       , person_no             &
> >         , module_code           &
> >       , module_version        &
> >       , assessment_period     &
> >       , programme             &
> >       , pathway               &
> >       , pathway_point         &
> >       , academic_year         &
> >       , student_module_status &
> >         from student_modules    &
> > where record_type = 'MOD'       &
> > and (module_code between :module_parm_low and :module_parm_high )     
>   &
> > and academic_year = :myyear                                   &
> > and assessment_period = :myasp                                       
> &
> > and (student_module_status = : Mystatus or :Mystatus = 'ALL')
> > 
> > 
> > 
> > acc *paras link to my_cursor
> __ Sql after PowerHouse variables/expression are removed:
> __ SELECT STUDENT, PERSON_NO, MODULE_CODE, MODULE_VERSION,
> ASSESSMENT_PERIOD,
> __        PROGRAMME, PATHWAY, PATHWAY_POINT, ACADEMIC_YEAR,
> __        STUDENT_MODULE_STATUS
> __    FROM STUDENT_MODULES
> __    WHERE RECORD_TYPE = 'MOD' AND
> __          (MODULE_CODE BETWEEN :phE6 AND
> __          :phE5 ) AND
> __          ACADEMIC_YEAR = :phE4 AND
> __          ASSESSMENT_PERIOD = :phE3 AND
> __          (STUDENT_MODULE_STATUS = :phE2 OR
> __          :phE1 = 'ALL' )
> 
> 
> -----Original Message-----
> From: Seamus Browne [mailto:Seamus.Browne@partage.org]
> Sent: 15 April 2002 14:06
> To: powerh-l@cube.swau.edu
> Subject: Quiz and SQL
> 
> 
> Hi,
> I have never used SQL to access a database so I don't know much about
> it.
> Reading the Quiz manual (7.29), I found a section on "Viewing Generated
> SQL
> Code",
> using SET LIST SQL.
> 
> Does this mean that I can type in standrad PH Quiz commands and then
> view
> them as SQL ?
> Sounds great but perhaps I have misuderstood the explanation.
> 
> However, if that is indeed possible, could I have some more details
> please
> as I can't view see any SQL code.
> 
> Any help appreciated.
> TIA.
> Seamus.Browne@partage.org
> 
> 
> 
> = = = = = = = = = = = = = = = = = = = = = = = = = = = =
> Mailing list: powerh-l@lists.swau.edu
> Subscribe: "subscribe" in message body to
> powerh-l-request@lists.swau.edu
> Unsubscribe: "unsubscribe" in message body to
> powerh-l-request@lists.swau.edu
> http://lists.swau.edu/mailman/listinfo/powerh-l
> This list is closed, thus to post to the list you must be a
> subscriber.
> 
> This message may contain privileged and/or confidential information.  If
> you
> have received this e-mail in error or are not the intended recipient,
> you
> may not use, copy, disseminate or distribute it; do not open any
> attachments, delete it immediately from your system and notify the
> sender
> promptly by e-mail that you have done so.  Thank you.
> 



Terry Curran