3 Replies Latest reply on Dec 1, 2016 2:39 AM by Magnus Fransson

    Wish some help with a SQL query.

    Magnus Fransson

      Good evening everyone,

       

      I do my best to extend my knowledge in using ExecuteSQL() function.

      Now, I reached as far as I can read in beverly's excellent "Missing Reference".

       

      Here's what I've done so far:

      Let
      (
          [
              FieldSep = Char ( 9 )    // Tab
          ;
              RecSep = "¶"        // "New Line"
          ;
              ProdArt_Table = Quote ( GetValue ( Substitute ( GetFieldName ( Pra__ProduktArtikel::!Pra_ProduktArtikel_ID ) ; [ "::" ; "¶" ] ) ; 1 ) )
          ;
              ProdArt_ArtField = Quote ( GetValue ( Substitute ( GetFieldName ( Pra__ProduktArtikel::!Ar_Artikel ) ; [ "::" ; "¶" ] ) ; 2 ) )
          ;
              ProdArt_ProdField = Quote ( GetValue ( Substitute ( GetFieldName ( Pra__ProduktArtikel::!Pr_Produkt ) ; [ "::" ; "¶" ] ) ; 2 ) )
          ;
              ProdArt_QuantField = Quote ( GetValue ( Substitute ( GetFieldName ( Pra__ProduktArtikel::Pra62_Antal_ab ) ; [ "::" ; "¶" ] ) ; 2 ) )    // Quantity
          ;
              ArtStor_Table = Quote ( GetValue ( Substitute ( GetFieldName ( As__ArtikelSaldo::!As_ArtikelSaldo_ID ) ; [ "::" ; "¶" ] ) ; 1 ) )
          ;
              ArtStor_ArtField = Quote ( GetValue ( Substitute ( GetFieldName ( As__ArtikelSaldo::!Ar_Artikel ) ; [ "::" ; "¶" ] ) ; 2 ) )
          ;
              ArtStor_StorField = Quote ( GetValue ( Substitute ( GetFieldName ( As__ArtikelSaldo::!Föa_FöretagsAdress ) ; [ "::" ; "¶" ] ) ; 2 ) )
          ;
              ArtStor_QuantField = Quote ( GetValue ( Substitute ( GetFieldName ( As__ArtikelSaldo::As811_Tillgängligt_B ) ; [ "::" ; "¶" ] ) ; 2 ) )    // Quantity
          ;
              QProd = Orr__OrderRad::!Pr_Produkt    // Question
          ;
              QStor = Orr__OrderRad::!Föa_FöretagsAdress_Lager_ab    // Question
          ;
              OrderQuant = Orr__OrderRad::Orr62_Antal    // Quantity
          ;
              SQL = "SELECT (" & ProdArt_Table & "." & ProdArt_QuantField & "*" & OrderQuant & "), " & ArtStor_Table & "." & ArtStor_QuantField & " FROM " & ProdArt_Table & " JOIN " & ArtStor_Table & " ON " & ProdArt_Table & "." & ProdArt_ArtField & " = " & ArtStor_Table & "." & ArtStor_ArtField & " WHERE " & ProdArt_Table & "." & ProdArt_ProdField & " = ? AND " & ArtStor_Table & "." & ArtStor_StorField & " = ?"
          ;
              Error = EvaluationError ( ExecuteSQL ( SQL ; FieldSep ; RecSep ; QProd ; QStor ) )
          ;
              IsError = GetAsBoolean ( Error ≠ 0 )
          ;
              Result = Case ( not IsError ; ExecuteSQL ( SQL ; FieldSep ; RecSep ; QProd ; QStor ) )
          ]
      ;
          "SQL: " & SQL & "¶QProd: " & QProd & "¶QStor: " & QStor & "¶OrderQuant: " & OrderQuant & "¶Error: " & Error & "¶Result:¶" &
          Result
      )
      

       

      And here are the results:

      SQL: SELECT ("Pra__ProduktArtikel"."Pra62_Antal_ab"*3), "As__ArtikelSaldo"."As811_Tillgängligt_B" FROM "Pra__ProduktArtikel" JOIN "As__ArtikelSaldo" ON "Pra__ProduktArtikel"."!Ar_Artikel" = "As__ArtikelSaldo"."!Ar_Artikel" WHERE "Pra__ProduktArtikel"."!Pr_Produkt" = ? AND "As__ArtikelSaldo"."!Föa_FöretagsAdress" = ?
      QProd: GSPBXDEMO1
      QStor: 1
      OrderQuant: 3
      Error: 0
      Result:
      3    0
      6    0
      6    10
      

       

      So far, everything works just as expected.

      There are six rows of troubleshooting data, which should not remain in the final version. (1-6.)

      Then there are three rows of two columns of actual resulting data. (7-9.)

      The left column represents what is ordered and the right column of what's in stock.

       

      • What I want to do next is to compare the values in two columns to determine if all items on that row can be delivered (boolean results expected).

       

      • After that, all rows should be "merged" to one boolean value, telling if everything can be delivered or not.

       

      Can anyone tell me if the compare operators can be used in the SELECT area to compare two values?

       

      And how do I best, evaluate all rows, in to one value?

       

      With best regards Magnus Fransson.

        • 1. Re: Wish some help with a SQL query.
          philmodjunk

          What you can do is use this list of results to build an expression that you can use with the evaluate function to get your single Boolean value. Make your comparison operator the field separator and make " and " your row separator.

          1 of 1 people found this helpful
          • 2. Re: Wish some help with a SQL query.
            Magnus Fransson

            Hi philmodjunk,

             

            Very interesting suggestion. Not at all what I imagined it all. I shall test it immediate.

            Thank you for showing me what's outside the box.

             

            With best regards Magnus Fransson.

            • 3. Re: Wish some help with a SQL query.
              Magnus Fransson

              Hi,

              Here are the updates made in according with Phil's suggestions:

              Let
              (
                  [
                      FieldSep = " > "
                  ;
                      RecSep = " ) OR ( "
                  ;
                      ProdArt_Table = Quote ( GetValue ( Substitute ( GetFieldName ( Pra__ProduktArtikel::!Pra_ProduktArtikel_ID ) ; [ "::" ; "¶" ] ) ; 1 ) )
                  ;
                      ProdArt_ArtField = Quote ( GetValue ( Substitute ( GetFieldName ( Pra__ProduktArtikel::!Ar_Artikel ) ; [ "::" ; "¶" ] ) ; 2 ) )
                  ;
                      ProdArt_ProdField = Quote ( GetValue ( Substitute ( GetFieldName ( Pra__ProduktArtikel::!Pr_Produkt ) ; [ "::" ; "¶" ] ) ; 2 ) )
                  ;
                      ProdArt_QuantField = Quote ( GetValue ( Substitute ( GetFieldName ( Pra__ProduktArtikel::Pra62_Antal_ab ) ; [ "::" ; "¶" ] ) ; 2 ) )    // Quantity
                  ;
                      ArtStor_Table = Quote ( GetValue ( Substitute ( GetFieldName ( As__ArtikelSaldo::!As_ArtikelSaldo_ID ) ; [ "::" ; "¶" ] ) ; 1 ) )
                  ;
                      ArtStor_ArtField = Quote ( GetValue ( Substitute ( GetFieldName ( As__ArtikelSaldo::!Ar_Artikel ) ; [ "::" ; "¶" ] ) ; 2 ) )
                  ;
                      ArtStor_StorField = Quote ( GetValue ( Substitute ( GetFieldName ( As__ArtikelSaldo::!Föa_FöretagsAdress ) ; [ "::" ; "¶" ] ) ; 2 ) )
                  ;
                      ArtStor_QuantField = Quote ( GetValue ( Substitute ( GetFieldName ( As__ArtikelSaldo::As811_Tillgängligt_B ) ; [ "::" ; "¶" ] ) ; 2 ) )    // Quantity
                  ;
                      QProd = Orr__OrderRad::!Pr_Produkt    // Question
                  ;
                      QStor = Orr__OrderRad::!Föa_FöretagsAdress_Lager_ab    // Question
                  ;
                      OrderQuant = Orr__OrderRad::Orr62_Antal    // Quantity
                  ;
                      SQL = "SELECT (" & ProdArt_Table & "." & ProdArt_QuantField & "*" & OrderQuant & "), " & ArtStor_Table & "." & ArtStor_QuantField & " FROM " & ProdArt_Table & " JOIN " & ArtStor_Table & " ON " & ProdArt_Table & "." & ProdArt_ArtField & " = " & ArtStor_Table & "." & ArtStor_ArtField & " WHERE " & ProdArt_Table & "." & ProdArt_ProdField & " = ? AND " & ArtStor_Table & "." & ArtStor_StorField & " = ?"
                  ;
                      Error1 = EvaluationError ( ExecuteSQL ( SQL ; FieldSep ; RecSep ; QProd ; QStor ) )
                  ;
                      PartResult = "GetAsBoolean ( ( " & ExecuteSQL ( SQL ; FieldSep ; RecSep ; QProd ; QStor ) & " ) )"
                  ;
                      Error2 = EvaluationError ( PartResult )
                  ;
                      IsError = GetAsBoolean ( ( Error1 ≠ 0 ) or ( Error2 ≠ 0 ) )
                  ;
                      Result = Case ( not IsError ; Evaluate ( PartResult ) )
                  ]
              ;
                  "SQL: " & SQL & "¶QProd: " & QProd & "¶QStor: " & QStor & "¶OrderQuant: " & OrderQuant & "¶Error1: " & Error1 & "¶Error2: " & Error2 & "¶PartResult: " & PartResult & "¶Result: " &
                  Result
              )
              

               

              And the result:

              SQL: SELECT ("Pra__ProduktArtikel"."Pra62_Antal_ab"*3), "As__ArtikelSaldo"."As811_Tillgängligt_B" FROM "Pra__ProduktArtikel" JOIN "As__ArtikelSaldo" ON "Pra__ProduktArtikel"."!Ar_Artikel" = "As__ArtikelSaldo"."!Ar_Artikel" WHERE "Pra__ProduktArtikel"."!Pr_Produkt" = ? AND "As__ArtikelSaldo"."!Föa_FöretagsAdress" = ?
              QProd: GSPBXDEMO1
              QStor: 1
              OrderQuant: 3
              Error1: 0
              Error2: 0
              PartResult: GetAsBoolean ( ( 3 > 0 ) OR ( 6 > 0 ) OR ( 6 > 10 ) )
              Result: 1
              

               

              With best regards Magnus Fransson.