🚀 go-pugleaf

"Bud1960" <amanz@northweststate.edu> wrote:
>
>Hello,
>
>I have an Oracle SQL script called "Time_Sheet_Progress.sql"
that causes an ORA-00979 GROUP BY error when run in BDS, but runs
fine in both SQL*Plus and TOAD.
>
>[script]
>SELECT a.spriden_last_name AS "Emp Last Name",
>       a.spriden_first_name AS "Emp First Name",
>       b.spriden_last_name AS "Mgr Last Name",
>       b.spriden_first_name AS "Mgr First Name",
>       case when perjobs_status_ind = 'C' then 'Complete'
>            when perjobs_status_ind = 'A' then 'Approved'
>            when perjobs_status_ind = 'E' then 'Error'
>            when perjobs_status_ind = 'I' then 'InProcess'
>            when perjobs_status_ind = 'R' then 'Returned'
>            when perjobs_status_ind = 'P' then 'Pending'
>            else perjobs_status_ind
>       end AS "Job Status",
>       case
>         when perrout_queue_status_ind = 'A' then 'Approved'
>         when perrout_queue_status_ind = 'O' then 'Overridden'
>         when perrout_queue_status_ind = 'Q' then 'InQueue'
>         when perrout_queue_status_ind = 'P' then 'Pending'
>         else perrout_queue_status_ind
>       end AS "Queue Status",
>       perjobs_comment AS "Comment",
>       perrout_status_date_time AS "Status Date",
>       perjobs_payno AS "Pay No",
>       SUM(NVL(perhour_hrs,0)) AS "# Hrs."
>  FROM spriden a,
>       spriden b,
>       perrout,
>       perhour,
>       perjobs
>WHERE  a.spriden_pidm = perjobs_pidm
>   AND a.spriden_change_ind IS NULL
>   AND b.spriden_pidm = perrout_appr_pidm
>   AND b.spriden_change_ind IS NULL
>   AND perhour_jobs_seqno (+) = perjobs_seqno
>   AND perrout_jobs_seqno = perjobs_seqno
>   AND perjobs_payno = :payno -- perjobs_payno is an integer value
>GROUP BY a.spriden_last_name,
>         a.spriden_first_name,
>         b.spriden_last_name,
>         b.spriden_first_name,
>         perjobs_status_ind,
>         perrout_queue_status_ind,
>         perjobs_comment,
>         perrout_status_date_time,
>		 perjobs_payno
>[/script}
>
>[code]
>
>TStringList *sl = new TStringList;
>sl->LoadFromFile("Time_Sheet_Progress.sql");
>ADOQuery1->SQL->Text = sl->Text;
>
>ADOQuery1->Parameters->ParseSQL(ADOQuery1->SQL->Text, true);
>ADOQuery1->Parameters->Items[0]->Value = txtPayNo->Text.ToInt();
>
>  // Set the query in active mode.
>try {
>	ADOQuery1->Active = true;
>// ... other stuff
>} catch (Exception &ex) {
>// The error brings me here...
>	ShowMessage("There has been an error.\rPlease notify the Computer Center of the following message:\r\r\t" + ex.Message);
>}
>[/code}
>
>If anyone has any idea how to work around this, please post it.
>
>TIA,
>Bud


Also, I apologize for not pressing enter at the end of each line, 
causing long line lengths, please forgive me! 

Bud

Bud1960 wrote:
> "Bud1960" <amanz@northweststate.edu> wrote:
>> Hello,
>>
>> I have an Oracle SQL script called "Time_Sheet_Progress.sql"
> that causes an ORA-00979 GROUP BY error when run in BDS, but runs
> fine in both SQL*Plus and TOAD.
>> [script]
>> SELECT a.spriden_last_name AS "Emp Last Name",
>>       a.spriden_first_name AS "Emp First Name",
>>       b.spriden_last_name AS "Mgr Last Name",
>>       b.spriden_first_name AS "Mgr First Name",
>>       case when perjobs_status_ind = 'C' then 'Complete'
>>            when perjobs_status_ind = 'A' then 'Approved'
>>            when perjobs_status_ind = 'E' then 'Error'
>>            when perjobs_status_ind = 'I' then 'InProcess'
>>            when perjobs_status_ind = 'R' then 'Returned'
>>            when perjobs_status_ind = 'P' then 'Pending'
>>            else perjobs_status_ind
>>       end AS "Job Status",
>>       case
>>         when perrout_queue_status_ind = 'A' then 'Approved'
>>         when perrout_queue_status_ind = 'O' then 'Overridden'
>>         when perrout_queue_status_ind = 'Q' then 'InQueue'
>>         when perrout_queue_status_ind = 'P' then 'Pending'
>>         else perrout_queue_status_ind
>>       end AS "Queue Status",
>>       perjobs_comment AS "Comment",
>>       perrout_status_date_time AS "Status Date",
>>       perjobs_payno AS "Pay No",
>>       SUM(NVL(perhour_hrs,0)) AS "# Hrs."
>>  FROM spriden a,
>>       spriden b,
>>       perrout,
>>       perhour,
>>       perjobs
>> WHERE  a.spriden_pidm = perjobs_pidm
>>   AND a.spriden_change_ind IS NULL
>>   AND b.spriden_pidm = perrout_appr_pidm
>>   AND b.spriden_change_ind IS NULL
>>   AND perhour_jobs_seqno (+) = perjobs_seqno
>>   AND perrout_jobs_seqno = perjobs_seqno
>>   AND perjobs_payno = :payno -- perjobs_payno is an integer value
>> GROUP BY a.spriden_last_name,
>>         a.spriden_first_name,
>>         b.spriden_last_name,
>>         b.spriden_first_name,
>>         perjobs_status_ind,
>>         perrout_queue_status_ind,
>>         perjobs_comment,
>>         perrout_status_date_time,
>> 		 perjobs_payno
>> [/script}
>>
>> [code]
>>
>> TStringList *sl = new TStringList;
>> sl->LoadFromFile("Time_Sheet_Progress.sql");
>> ADOQuery1->SQL->Text = sl->Text;
>>
>> ADOQuery1->Parameters->ParseSQL(ADOQuery1->SQL->Text, true);
>> ADOQuery1->Parameters->Items[0]->Value = txtPayNo->Text.ToInt();
>>
>>  // Set the query in active mode.
>> try {
>> 	ADOQuery1->Active = true;
>> // ... other stuff
>> } catch (Exception &ex) {
>> // The error brings me here...
>> 	ShowMessage("There has been an error.\rPlease notify the Computer Center of the following message:\r\r\t" + ex.Message);
>> }
>> [/code}
>>
>> If anyone has any idea how to work around this, please post it.
>>
>> TIA,
>> Bud
> 
> 
> Also, I apologize for not pressing enter at the end of each line, 
> causing long line lengths, please forgive me! 
> 
> Bud

Well, it appears that I have answered my own question!  In the script I 
replaced the line...
 >  FROM spriden a,
 >       spriden b,
 >       perrout,
 >       perhour,
 >       perjobs
 >WHERE  a.spriden_pidm = perjobs_pidm
 >   AND a.spriden_change_ind IS NULL
 >   AND b.spriden_pidm = perrout_appr_pidm
 >   AND b.spriden_change_ind IS NULL
 >   AND perhour_jobs_seqno (+) = perjobs_seqno
with...>  FROM spriden a,
 >       spriden b,
 >       perrout,
 >       perjobs
   OUTER JOIN perhour
      ON perhour_jobs_seqno = perjobs_seqno
 >WHERE  a.spriden_pidm = perjobs_pidm
 >   AND a.spriden_change_ind IS NULL
 >   AND b.spriden_pidm = perrout_appr_pidm
 >   AND b.spriden_change_ind IS NULL

and now life is good!

Bud