PrevPrev Go to previous topic
NextNext Go to next topic
Last Post 11/16/2017 4:52 PM by  Nabil
SQL Count not showing as variable
 2 Replies
Sort:
You are not authorized to post a reply.
Author Messages
Nabil
Programmer/Analyst
St. Luke's Health System
Veteran Member
(132 points)
Veteran Member
Posts:60


Send Message:

--
11/16/2017 1:23 PM

    Hi All,

     

    I'm running a SQL query and doing a count which should determine how the flow would proceed, however, when I try to pull the Count on my Branch Node, I'm failing to see it.

     

    Any ideas why this is happening? 

    Here is the SQL

    SELECT

    PAY.APCOMPANY
    ,PAY.INVOICE
    ,PAY.VENDOR
    ,PAY.INVOICEURL
    ,to_char(cast(PAY.USTIMESTAMP as date),'DD-MM-YYYY') as UPDATE_TIME
    ,PAY.CREATIONDATE
    ,PAY.USTIMESTAMP
    ,API.INVOICE
    ,API.L_INDEX
    ,URL.L_INDEX
    ,COUNT(URL.L_INDEX)



    FROM devapia.PAYABLESINVOICE PAY
    FULL OUTER JOIN DEVLAW.APINVOICE API
    ON PAY.INVOICE = API.INVOICE AND PAY.VENDOR = API.VENDOR

    FULL OUTER JOIN DEVLAW.L_HAPI URL

    ON API.L_INDEX = URL.L_INDEX
    --Look for Sent for Payment invoices updated in the last hour

    WHERE PROCESSINGSTATUS = '8'
    --AND INVOICEURL <> ' '
    AND PAY.USTIMESTAMP > (sysdate-1/24)
    --AND SUBSTR(URL.OBJECT, 1, 6) = 'TYPE=I'
    --AND PAY.INVOICE = 'SIT 2.2 NABIL URL'
    GROUP BY
    PAY.APCOMPANY
    ,PAY.INVOICE
    ,PAY.VENDOR
    ,PAY.INVOICEURL
    ,to_char(cast(PAY.USTIMESTAMP as date),'DD-MM-YYYY')
    ,PAY.CREATIONDATE
    ,PAY.USTIMESTAMP
    ,API.INVOICE
    ,API.L_INDEX
    ,URL.L_INDEX


    ORDER BY PAY.INVOICE ASC

    Thank you,

    Nabil

    John Henley
    Private
    Private
    Senior Member
    (9638 points)
    Senior Member
    Posts:3230


    Send Message:

    --
    11/16/2017 2:05 PM
    Try giving your COUNT() column an alias:
    ,COUNT(URL.L_INDEX) AS URL_COUNT
    Thanks for using the LawsonGuru.com forums!
    John
    Nabil
    Programmer/Analyst
    St. Luke's Health System
    Veteran Member
    (132 points)
    Veteran Member
    Posts:60


    Send Message:

    --
    11/16/2017 4:52 PM
    Thank you John! That did it
    You are not authorized to post a reply.