RE: [sqr-users] Change SQL to SQR

[Bob Stone <bstone@fastenal.com>]

You cant have the "as some_display_name" at the end of each column.  SQR
isn't expecting it.  To have it 'as some display name' you use an &variable.




-----Original Message-----
From: sqr-users-bounces+bstone=fastenal.com@sqrug.org
[mailto:sqr-users-bounces+bstone=fastenal.com@sqrug.org]On Behalf Of
Chuyen Do
Sent: Monday, December 13, 2004 2:23 PM
To: SQR USERS
Subject: [sqr-users] Change SQL to SQR


Any body can Help me ?
This select works for SQL Plus:

Select mmis_mirror.mmis_student_master.ssan as Empl_ID_TO_TRANS,
termmatch.termdt as EffDt_TO_TRANS,
mmis_mirror.mmis_student_master.Apo_address_3 as Country_First_Logic,
mmis_mirror.mmis_student_master.apo_address_1 as Address1,
mmis_mirror.mmis_student_master.apo_address_2 as Address2,
mmis_mirror.mmis_student_master.Apo_address_3 as Address3
from mmis_mirror.mmis_student_master,
(Select distinct(mmis_mirror.mmis_stu_course_regr.ssan) as termssn,
mmis_mirror.stx_otd.beg_regist_date as termdt
from mmis_mirror.mmis_stu_course_regr, mmis_mirror.stx_otd,
(Select mmis_mirror.mmis_stu_course_regr.ssan as maxssn,
max(mmis_mirror.mmis_stu_course_regr.acad_year_term) as maxtrm
from mmis_mirror.mmis_stu_course_regr
group by mmis_mirror.mmis_stu_course_regr.ssan) maxterm
where mmis_mirror.mmis_stu_course_regr.ssan = maxterm.maxssn
and mmis_mirror.stx_otd.acad_year_from||mmis_mirror.stx_otd.term_number
= maxterm.maxtrm) termmatch
where mmis_mirror.mmis_student_master.ssan = termmatch.termssn
and mmis_mirror.mmis_student_master.ssan = 538022538;

**********************************************
Then I bring it into SQR program:

BEGIN-SELECT DISTINCT on-error=db_error

mmis_mirror.mmis_student_master.ssan as Empl_ID_TO_TRANS,
termmatch.termdt as EffDt_TO_TRANS,
mmis_mirror.mmis_student_master.Apo_address_3  as Country_First_Logic,
mmis_mirror.mmis_student_master.apo_address_1  as Address1,
mmis_mirror.mmis_student_master.apo_address_2 as Address2,
mmis_mirror.mmis_student_master.Apo_address_3 as Address3

   SHOW 'I AM IN SELECT MAIL'

from mmis_mirror.mmis_student_master,
(Select distinct(mmis_mirror.mmis_stu_course_regr.ssan) as termssn,
mmis_mirror.stx_otd.beg_regist_date as termdt
from mmis_mirror.mmis_stu_course_regr, mmis_mirror.stx_otd,
(Select mmis_mirror.mmis_stu_course_regr.ssan as maxssn,
max(mmis_mirror.mmis_stu_course_regr.acad_year_term) as maxtrm
from mmis_mirror.mmis_stu_course_regr
group by mmis_mirror.mmis_stu_course_regr.ssan) maxterm
where mmis_mirror.mmis_stu_course_regr.ssan = maxterm.maxssn
and mmis_mirror.stx_otd.acad_year_from||mmis_mirror.stx_otd.term_number
= maxterm.maxtrm) termmatch
where mmis_mirror.mmis_student_master.ssan = termmatch.termssn
and mmis_mirror.mmis_student_master.ssan = 538022538

**********************************************
These are error message SQR gave to me:

Error on line 1134:
   (SQR 3729) SQL expression is missing &name or has unbalanced
parentheses.
mmis_mirror.mmis_student_master.ssan as Empl_ID_TO_TRANS,

Error on line 1135:
   (SQR 3729) SQL expression is missing &name or has unbalanced
parentheses.
termmatch.termdt as EffDt_TO_TRANS,

Error on line 1136:
   (SQR 3729) SQL expression is missing &name or has unbalanced
parentheses.
mmis_mirror.mmis_student_master.Apo_address_3  as Country_First_Logic,

Error on line 1137:
   (SQR 3729) SQL expression is missing &name or has unbalanced
parentheses.
mmis_mirror.mmis_student_master.apo_address_1  as Address1,

Error on line 1138:
   (SQR 3729) SQL expression is missing &name or has unbalanced
parentheses.
mmis_mirror.mmis_student_master.apo_address_2 as Address2,

Error on line 1143:
   (SQR 3728) SQL expression not ended, perhaps missing &name.
from mmis_mirror.mmis_student_master,

Errors were found in the program file.

SQR for PeopleSoft: Program Aborting.

Thank you in advance.

_______________________________________________
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