Jobstream - how to manipulate records in a loop fetched by SQL statement(s)

[Yvonimus]

Hi all,

Who can tell me how to use the SQL statements of a jobstream procedure? I have to create several records dependent from given start and end values. I think the following process will solve my issue and describes what I mean, but I have no idea yet how to use the SQL statements ???

select records from an existing table (every record contains an identifier, a start and an end value)
       --Example records: {a;11;15};{b;50;59}
Fetch record While not EOF      --1st loop {a} 2nd loop {b}
Ã,  For x >= start to end            --insert record for every value between start and end value {10 - 25}
Ã,  Ã,  insert into table2 values (ID ; start ; end ; x)            --will insert 5 records for 'a' and 10 records for 'b'
Ã,  next x
Loop

I had a look at the DS user guide, but found no useful informationÃ,  :(

How can I select the given values and fetch row by row? what stands 'alias' and 'CursorId' for and how can I use them? .....HELP....
Many thanks in advance for every hint

Kind regards
Yvonimus

[GoWestGw]

Not sure exactly what you are trying to achieve, can you elaborate?

[CoginAustin]

Shoulds like you should be using a fact build to move some data from point A to point B based upon a filter you create.

Using a SQL Block , I do not believe, is the correct way to go.

[rory]

Hi,

With the 'start' and 'end' fields, what do these represent?  If you can create a table with all possible values you can then join to the existing table of ranges using a 'between' clause to get the range identifier.  Depending on the volume of date and the volatility of it, you could set this up manually.

I suppose you could use a jobstream with a counter variable but that's probably overkill based on your examples.  How many items are we talking about?

Regards,

Rory.

[Yvonimus]

Hi all,

I was looking for a loop statement in a procedure node looking like this. This is only a simple one, but can be extented very well.

$SQLstmnt := 'SELECT rows_inserted FROM ds_component_run run, ds_delivery_hist hist
  WHERE component_name = "" AND run.audit_id = hist.audit_id and dbalias = ''<text>''
  and end_timestamp = (SELECT max(maxrun.end_timestamp) FROM ds_component_run maxrun WHERE component_name = "")';

$Cursor := SQLPrepare( 'Database', $SQLstmnt, TRUE);

IF $Cursor IS NULL
THEN
    return FALSE;
ELSE
begin
  IF SQLFetch( $Cursor ) <> 0
  THEN
    return FALSE;
  ELSE
  begin   
    IF SQLData( $Cursor, 1 ) = '0'
    THEN
      return FALSE;
    ELSE
      return TRUE;
  end
end
SQLClose( $Cursor );

[chad.ongstad]

THE CODE BELOW SHOULD DO WHAT YOU ARE LOOKING FOR.. PLEASE NOTE THAT THE TABLE YOU ARE SELECTING FROM AND INSERTING INTO MUST BE SEPERATE SOURCES. OTHERWISE YOU WILL RECIEVE AN ERROR. I'VE FOUND THAT THIS OCCURS WHEN A CONNECTION IS OPEN AND YOU ATTEMPT TO OPEN ANOTHER WITHIN THE SAME FUNCTION.. THE BEST WAY AROUND THIS WOULD BE TO WRITE OUT ALL YOUR INSERT STATEMENTS TO A TEXT FILE AND ONCE YOUR CURSOR IS CLOSED READ THEM BACK IN AND EXECUTE THEM .





$v_sqlid := SQLPrepare( 'SOURCEA','SELECT ''A'' AS ID, 1 AS START_VAL, 10 AS END_VAL',FALSE );

while SQLFetch( $v_sqlid  )= 0
do
  begin
      $v_rslt_id := SQLData( $v_sqlid,1 );
      $v_rslt_min := SQLData( $v_sqlid,2 );
      $v_rslt_max := SQLData( $v_sqlid,3 );
     
      while $v_rslt_min <= $v_rslt_max
            do
               Begin

              $v_sql_txt2 := CONCAT('INSERT INTO t1 VALUES (''',$v_rslt_id,''',',$v_rslt_min,',',$v_rslt_max,')');
               sql('SOURCEB',$v_sql_txt2);
               $v_rslt_min := $v_rslt_min +1;
               End;       

return $v_sql_txt2;

  end;

[vetteheadracer]

I am doing something similar andwhat I have done is written a pl/sql procedure which creates records going forward in time and then I have a DS job that updtes them. Maybe this solution would work for you.