ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

Array in SQLRPGLE

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Array in SQLRPGLE

    Hi All, Can you plz help me with below query...
    Trying to use array in sql (SQLRPGLE) to check if a value is present in array or not like:-

    Dcl-ds Array_Ds Qualified Dim(200);
    Item Zoned(3:0);
    End-ds;
    //Here already populated array Array_Ds.Item from file using sql
    Exec Sql Select Count(*) into: Count from FileA where
    Field1 in :Array_Ds.Item;

    So getting error while compiling like - *RNF5343 30 2 Array has too many omitted indexes; specification is ignored.ay in SQLRPGLE

  • #2
    HI Iceberg,

    I think you can't write your sql statement so, probably you have to pass from a prepare statement, something like this:


    Code:
    Dcl-S Arr Zoned(3) dim(200);
    Dcl-S SqlString VarChar(1000);
    Dcl-S Counter Int(10);
    Dcl-S Idx Int(5);
    Dcl-C A '''';
    // __________________________________________________ __________________________
    
    Arr(1) = 'B';
    Arr(2) = 'M';
    
    SqlString = 'Select Count(*) from filea where Field1 in (';
    
    For Idx = 1 to %Elem(Arr);
    
      If Arr(Idx) = 0;
        Leave;
      EndIf;
    
      If Idx > 1;
        SqlString += ', ';
      EndIf;
      SqlString += A+ %Char(Arr(Idx)) +A;
    EndFor;
    
    SqlString += ') ';
    Exec Sql
      Prepare Counter from :SqlString;
    If SqlState <> '00000';
      Dsply 'Error';
    EndIf;
    Exec Sql
      Declare CounterCur cursor for Counter;
    Exec Sql
      Open CounterCur;
    If SqlState <> '00000'; 
      Dsply 'Error';
    EndIf;
    
    Exec Sql
      Fetch next from CounterCur into :Counter;
    If SqlState <> '00000';
      Dsply 'Error';
    EndIf;
    
    Exec Sql
      Close CounterCur;
    *InLr = *On;
    Maybe someone else have a different solution...

    Bye

    Comment


    • Iceberg
      Iceberg commented
      Editing a comment
      Thanks Paolinosal

  • #3
    also is it possible to count on condition in db2 like this or do we have any alternative:-

    SELECT
    COUNT(CASE WHEN Field1 in (SELECT Distinct Field2 FROM File2)
    then 1 end) from File1

    Comment


    • #4
      But if i use below its working

      SELECT
      COUNT(CASE WHEN Field1 in ('A', 'B')
      then 1 end) from File1

      But i want value from another file

      Comment


      • #5
        Thy this (using Field2 from File2 as the values contained in Field1 from File1 that you want to count):
        Code:
        with Types as (select distinct Field2 as Field2 from File2)
             select count(*) from File1 join Types on Field1 =Field2

        Comment


        • Brian Rusch
          Brian Rusch commented
          Editing a comment
          * Try this... Sure wish I could edit my posts.

      • #6
        So can i use it in SQLRPGLE?

        Comment


        • #7
          Sure. Doing it this way eliminates the need for an array to store the values (unless you need that for some other part of the program).
          Code:
          Exec Sql with Types as (select distinct Field2 as Field2 from File2)
                     select count(*) into: Count from File1 join Types on Field1 = Field2

          Comment


          • #8
            Also, I would recommend that you choose a different name than 'SqlString'. When coding SQLRPGLE, you should never create your own variable that begins with 'sql'. These names are reserved for the SQL preprocessor's variables.

            Comment


            • #9
              Wow thanks Brian thats what am looking for, But while compiling am getting multiformat error like above file1 is multimember and i want to read all the member in sqlrpgle. I checked in google that there is a way like creating alias but i have 6-7 member in file so it would be lots of work any alternative?

              Comment


              • #10
                The only thing I can think of in that situation is to create a logical file over all of the physical file members and then use the logical file name in the SQL. The downside to this is that if the physical file has members added or removed, the logical will have to be recreated.

                Comment


                • #11
                  yeah tats what am using Like i have LF FileLF created using File1/File2/File3/File4/File5, but when am using file FileLF in Exec Sql in strsql and while compiling getting error like File 'FileLF' in *LIBL has more than one format.

                  Comment


                  • #12
                    There's a difference between a multiformat logical file and a multimember logical file. A multiformat logical is created over multiple files with different formats and a multimember logical is created over multiple members of the same file which all have the same format. I'm suggesting that you create a multimember logical which should work.

                    Comment


                    • #13
                      oops , k sorry its a multiformat logical file. and i can not compile this sqlrpgle pgm cause of this...

                      Comment


                      • #14
                        I'm confused, are the values that you're looking to compare to contained in multiple members or in multiple files or both?

                        Comment

                        Working...
                        X