AnsweredAssumed Answered

Wish some help with a SQL query.

Question asked by Magnus Fransson on Nov 30, 2016
Latest reply on Dec 1, 2016 by 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.

Outcomes