• If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Announcement

Collapse
No announcement yet.

NAB datasource SQL statement rules

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

  • #16
    Rob,

    The select does not work in the data source. It does work on Run SQL Scripts. See below:

    Lateral example.png

    Comment


    • #17
      Please provide the SQL via text “pasting” the SQL that isn’t working and we will add it to the our Valance 6 feature request/bug list.

      Thanks

      Comment


      • #18
        Code:
        select a.vouchr, a.vendor, x.account, x.amount
        from GLORIA/horfile as a
        cross join lateral(values (a.acT001, a.amT001),
        (a.acT002, a.amt002),
        (a.acT003, a.amT003))
        as x (account, amount) where x.amount > 0
        Thanks Johnny.

        FYI, the definition of horfile is:

        Code:
        A R HORFILER
        A VOUCHR 15A TEXT('VOUCHER')
        A VENDOR 15A TEXT('VENDOR')
        A ACT001 15A TEXT('ACCOUNT 01')
        A AMT001 8S 0 TEXT('AMOUNT 01')
        A ACT002 15A TEXT('ACCOUNT 02')
        A AMT002 8S 0 TEXT('AMOUNT 02')
        A ACT003 15A TEXT('ACCOUNT 03')
        A AMT003 8S 0 TEXT('AMOUNT 03')

        Comment


        • #19
          Johnny,

          The following SQL also works on the Run SQL Scripts window but does not work in the data source:

          Code:
          select vouchr, vendor, account, amount
          from GLORIA/horfile as s,
          lateral(values (acT001, amT001),
          (acT002, amt002),
          (acT003, amT003))
          as t(account, amount)
          Thanks

          Comment


          • #20
            why the "as s" after from and "as t" before (account, amount)?

            Comment


            • #21
              The "as s" is optional, it's just providing an alias to the columns in horfile. It's not being used in this case, but if any of the column names were not unique to horfile you could use "s.field_name" as shorthand for saying "horfile.field_name".

              The "as t" part is mandatory, as it's being used to provide a table identifier to the two-column lateral. Again, the column names are unique so "t" is not being used (i.e., via "t.field_name"), but you could specify if for improved clarity on which column is coming from where.

              So Gloria's statement could be restated this way:
              Code:
              SELECT s.vouchr, s.vendor, t.account, t.amount
                FROM gloria/horfile AS s,
                     LATERAL(VALUES (act001, amt001),
                                    (act002, amt002),
                                    (act003, amt003)) AS t (account, amount)

              I should mention that many developers leave off the "AS" part, since it's implied by any string that follows a table reference...
              Code:
              SELECT s.vouchr, s.vendor, t.account, t.amount
                FROM gloria/horfile s,
                     LATERAL(VALUES (act001, amt001),
                                    (act002, amt002),
                                    (act003, amt003)) t (account, amount)
              :


              Comment


              • #22
                gotcha wasn't sure why the "s" was being used at all... the "t" I was confused about because it would not let me leave it off - which I thought I could do since it was not used as a qualifier with the dot...

                Comment


                • #23
                  it looks like the gotcha with the cross join lateral is that you can't do an order by... at least this doesn't work with the order by clause in it (you take it out and it works fine)

                  Code:
                  select
                  a.podate,
                  c.dept concat '-' concat c.divn as "account",
                  b.name,
                  c.amount
                  from nfcclibf.npofile a
                  join nfcclibf.nvenmast b on b.vendno = a.povend
                  cross join lateral (values
                  (podp1, podv1, poam1),
                  (podp2, podv2, poam2),
                  (podp3, podv3, poam3))
                  c(dept, divn, amount)
                  where c.amount <> 0
                  Last edited by dlstrawn; 05-04-2021, 02:51 PM.

                  Comment


                  • #24
                    An ORDER BY should work fine in that statement. Are you placing it after the WHERE clause?

                    Comment


                    • #25
                      This may be due to something I have wrong, but here is what happens when I add the order by clause:

                      ScreenShots.docx

                      Comment


                      • #26
                        I'm not seeing anything wrong with that statement, nor do I see any error message in your screenshot... What happens if you paste it into Run SQL Scripts in ACS and try running it there?

                        Comment


                        • #27
                          there is no error, it starts but never finishes, because it goes into a LCKW condition... (see the green screen screen shot). I will try it in ACS...

                          Comment


                          • #28
                            ok, it does work in ACS... must be something strange in RDi... it does not pass syntax checking in NAB. See below...

                            ScreenShot2.docx

                            Comment


                            • #29
                              Yes, we're aware of that. It will work in the next build, which should be out by next week.

                              In the meantime, try using it in a View. Keep in mind you cannot specify an ORDER BY clause in the View, but you can in the SELECT clause of your NAB data source that pulls from the View.
                              Last edited by robert.swanson; 05-05-2021, 10:41 AM.

                              Comment


                              • #30
                                gotcha! Thanks!

                                Comment

                                Working...
                                X