?
Solved

Powerscript question

Posted on 2006-04-12
9
Medium Priority
?
491 Views
Last Modified: 2013-12-26
My code below displays data based on the choice of machine I choose from a drop down menu. I would like to add ALL to the menu and I want the widow to display data for all the machines. How do I achieve it??




integer      row_count, li_downtime
date            ldt_begin_date, ldt_end_date, temp_date
string      machine_num, setting
st_date_machine      st_date_machine
open( w_calendar_range_response_mach )
st_date_machine = message.powerobjectparm
      
      If IsValid(Message.PowerObjectParm) and Not IsNull(Message.PowerObjectParm) Then
     If ClassName(Message.PowerObjectParm) = 'st_date_machine' Then
          st_date_machine = message.powerobjectparm
     Else
          MessageBox("Error","Returned object is not valid")
          Return -1
     End If
Else
     MessageBox("Error","Nothing Returned")
     Return -1
End If

ldt_begin_date = st_date_machine.st_begin_date
ldt_end_date = st_date_machine.st_end_date
machine_num = st_date_machine.st_machine_id
if machine_num <> '-1' then
      if ldt_begin_date > 1949-01-01 and ldt_end_date > 1949-01-01 then
      temp_date = ldt_begin_date
      do while temp_date <= ldt_end_date
      select sum(downtime_minutes)  
    into :li_downtime  
    from machine_downtime  
   where ( machine_id = :machine_num ) and  
         ( capture_date = :temp_date );
                  
                  if li_downtime > 10 then
                        row_count++
                  end if

            temp_date =relativedate(temp_date,1)      
      loop      
      
      tab_1.tabpage_1.dw_1.settransobject( sqlca )
      tab_1.tabpage_1.dw_1.retrieve( ldt_begin_date, ldt_end_date, machine_num, row_count )
      tab_1.tabpage_2.dw_2.settransobject( sqlca )
      tab_1.tabpage_2.dw_2.retrieve( ldt_begin_date, ldt_end_date, machine_num, row_count )
      
else
      
end if
end if


0
Comment
Question by:sushmasriram
  • 5
  • 4
9 Comments
 
LVL 18

Expert Comment

by:diasroshan
ID: 16442860
Hi,

in the SQL of ur DW where u pass the retrieval argument for machine id(machine_downtime.machine_id = :mach_id ),
change it to like statement.... ie. machine_downtime.machine_id like :mach_id

Also, i guess ull have to change ur argument type to string if its number...

Now,
when u select all pass '%' instead of machine id.... ull get what u want...

Cheers,
Rosh
0
 

Author Comment

by:sushmasriram
ID: 16448206
I changed the SQL to machine_downtime.machine_id like :mach_id. The retrieval argument is a string. I did not understand what you meant by "Now,
when u select all pass '%' instead of machine id.... ull get what u want...". Can you explain me more in detail.

0
 
LVL 18

Expert Comment

by:diasroshan
ID: 16451666
Hi,
when u select a machine_id and pass it to the query the query executes something like...
Select ...
....
...
Where machine_downtime.machine_id like '2'
(im assuming u selected machine id as 2)

Now,
if u select the option 'all' ur query shud look like this....
Select ...
....
...
Where machine_downtime.machine_id like '%'

The percentage sign in like statement gets results for all machines... and not for a particular machine id

Cheers,
Rosh
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:sushmasriram
ID: 16468348
Below is my query under the report. Can you include the code for me. I tried it but I am getting errors.  

SELECT machine_downtime.capture_date,  
         machine_downtime.machine_id,  
         machine_downtime.shift_id,  
         downtime_reason.reason_category_text,  
         downtime_reason.reason_category,  
         machine_downtime.reason_code,  
         downtime_reason.reason_text,  
         machine_downtime.downtime_minutes  
    FROM downtime_reason,  
         machine_downtime  
   WHERE ( machine_downtime.reason_code = downtime_reason.reason_code ) and  
         ( ( machine_downtime.capture_date between :begin_date and :end_date ) AND  
         ( machine_downtime.machine_id like :mach_id ) AND  
         ( downtime_reason.reason_category_text = 'PRODUCTION' ) AND  
         ( machine_downtime.downtime_minutes > 1 ) )

Thanks
Sushma

0
 
LVL 18

Accepted Solution

by:
diasroshan earned 2000 total points
ID: 16468540
Hi,

ok... i dont know how ur accepting the option of machine id or ALL... but here is a pseudocode for ur reference...

If option = 'ALL' Then
st_date_machine.st_machine_id = '%'
Else
st_date_machine.st_machine_id = <ur machine id>  //1,2,3.... whatever
End If

Now,
ur DW will be retrieved normally as,
ldt_begin_date = st_date_machine.st_begin_date
ldt_end_date = st_date_machine.st_end_date
machine_num = st_date_machine.st_machine_id

tab_1.tabpage_1.dw_1.retrieve( ldt_begin_date, ldt_end_date, machine_num, row_count )

Cheers,
Rosh



Cheers,
Rosh
0
 

Author Comment

by:sushmasriram
ID: 16502309
Thanks your solution worked. I am  capturing a value called row_count from the program below. Right now, for ALL option it is retrieving row_count as zero. I want it to be row_count *12(as I have 12 machines), if ALL is chosen. How do I achieve it?


integer      row_count, li_downtime
date            ldt_begin_date, ldt_end_date, temp_date
string      machine_num, setting

st_date_machine      st_date_machine

open( w_calendar_range_response_mach )

st_date_machine = message.powerobjectparm

If IsValid(Message.PowerObjectParm) and Not IsNull(Message.PowerObjectParm) Then
     If ClassName(Message.PowerObjectParm) = 'st_date_machine' Then
          st_date_machine = message.powerobjectparm
     Else
          MessageBox("Error","Returned object is not valid")
          Return -1
     End If
Else
     MessageBox("Error","Nothing Returned")
     Return -1
End If

ldt_begin_date = st_date_machine.st_begin_date
ldt_end_date = st_date_machine.st_end_date
machine_num = st_date_machine.st_machine_id
if machine_num <> '-1' then
      if ldt_begin_date > 1949-01-01 and ldt_end_date > 1949-01-01 then
      temp_date = ldt_begin_date
      do while temp_date <= ldt_end_date
      select sum(downtime_minutes)  
    into :li_downtime  
    from machine_downtime  
   where ( machine_id = :machine_num ) and  
         ( capture_date = :temp_date );
                  
                  if li_downtime > 10 then
                        row_count++
                  end if

            temp_date =relativedate(temp_date,1)      
      loop      
      tab_1.tabpage_1.dw_1.settransobject( sqlca )
      tab_1.tabpage_1.dw_1.retrieve( ldt_begin_date, ldt_end_date, machine_num, row_count )
      tab_1.tabpage_2.dw_2.settransobject( sqlca )
      tab_1.tabpage_2.dw_2.retrieve( ldt_begin_date, ldt_end_date, machine_num, row_count )
      
      
else
      
end if


            
            
end if
0
 
LVL 18

Expert Comment

by:diasroshan
ID: 16513680
Hi,

I really dont know ur Business Rules for the above code or what rowcount actually is...
But try this and make necessary changes in it,

Datastore ds_all
ds_all = Create Datastore
ds_all.DataObject = 'dw_all_machine'  //the SQL for this DW is 'Select distinct machine_id from machine_downtime'
ds_all.SetTransObject(SQLCA)

if machine_num = '%' then
ll_Cnt  = ds_all.Retrieve()
For i = 1 to ll_Cnt
machine_num = ds_all.Object.machine_id[i]
     if ldt_begin_date > 1949-01-01 and ldt_end_date > 1949-01-01 then
     temp_date = ldt_begin_date
     do while temp_date <= ldt_end_date
     select sum(downtime_minutes)  
    into :li_downtime  
    from machine_downtime  
   where ( machine_id = :machine_num ) and  
         ( capture_date = :temp_date );
               
               if li_downtime > 10 then
                    row_count++
               end if

          temp_date =relativedate(temp_date,1)    
     loop    
Next
     tab_1.tabpage_1.dw_1.settransobject( sqlca )
     tab_1.tabpage_1.dw_1.retrieve( ldt_begin_date, ldt_end_date, machine_num, row_count )
     tab_1.tabpage_2.dw_2.settransobject( sqlca )
     tab_1.tabpage_2.dw_2.retrieve( ldt_begin_date, ldt_end_date, machine_num, row_count )    
end if

Cheers,
Rosh
0
 

Author Comment

by:sushmasriram
ID: 16526912
I am getting errors with the above script. I think the for loop has a problem.
0
 
LVL 18

Expert Comment

by:diasroshan
ID: 16531689
Hi,

what error are u gettin!!!

i have given a basic idea... u have to code accordingly... the idea is to loop thru all the machines in case of ALL option selected....

Cheers,
Rosh
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In our object-oriented world the class is a minimal unit, a brick for constructing our applications. It is an abstraction and we know well how to use it. In well-designed software we are not usually interested in knowing how objects look in memory. …
Here is a helpful source code for C++ Builder programmers that allows you to manage and manipulate HTML content from C++ code, while also handling HTML events like onclick, onmouseover, ... Some objects defined and used in this source include: …
THe viewer will learn how to use NetBeans IDE 8.0 for Windows to perform CRUD operations on a MySql database.
The viewer will learn how to use and create new code templates in NetBeans IDE 8.0 for Windows.
Suggested Courses

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question