14 Replies Latest reply on Aug 12, 2016 1:37 PM by philipHPG

    SQL query question

    Tom_Droz

      I struggle with SQL and the lack of example code I can find.  I am trying to create output that would look like this:

       

       

      4-2-025919316935-3977568-03283-193278224295859 | Inv/Cust/Stk: 1045, Name1 1111 1994  Plymouth Voyager

       

      4-2-011218208443-4818777-00706-074062334242450 | Inv/Cust/Stk: 1044, Name2 4310 2005 Lincoln Navigator

       

      Field one is:Shop::__kp_ID

      Field two is:Shop::Display_Header

      I want the most recently created records that have a value in Field: Shop::OpenClosed  of >0

       

      My statement reads (which I modified form a working SQL and I think has a custom function, not sure why) is:

      ExecuteSQL (
      //sqlQuery
      "SELECT " & GFN ( Shop::__kp_ID )& ", " & GFN (Shop::Display_Header)&
      " FROM "  & GTN ( Shop::__kp_ID ) &
      " WHERE ( Shop::OpenClosed )  > 1"&
      " ORDER BY " & GFN ( Shop::z_RecordCreationTimestamp ) & " DESC" &
      Case($$VehicleOffSet>0;" OFFSET "  & $$VehicleOffSet*25  & " ROWS ") &
      " FETCH FIRST 30 ROWS ONLY"

      ;
      //fieldSeparator
      " | ";
      //rowSeparator
      "¶" ;
      //arguments...
      ""
      )

       

      Can anyone give me a fix on this?

       

      thanks!!!