When I run this SQL inside the SQL suiteapp (VERSION 1.2.5 DEVELOPED BY MATT BERNSTEIN), it works and returns the correct result, but when I run this script, it either returns no data or items that it shouldn't return

let sql = `
    SELECT 
        transaction.id,
        transaction.type,
        transaction.trandate,
        transaction.memo,
        transaction.status,
        transaction.custbody19,
        transaction.transferLocation
    FROM 
        transaction 
    WHERE 
        transaction.type = 'TrnfrOrd'
        AND transaction.trandate < ADD_MONTHS(SYSDATE, -7)
`;

    log.debug("SuiteQL SQL", sql);

    let mySuiteQLQuery = query.runSuiteQL({ query: sql });

    let results = mySuiteQLQuery.asMappedResults();

    log.debug("Query Results", results);

When I write SuiteQL, I am not able to use the single character transaction status field in a where clause like your query is trying to do. I either need to use

where status in ('SalesOrd:G')

in the where clause, or, my preference,

where BUILTIN.DF(status) in ('Sales Order : Billed')

to make those values readable to the next person reading the query (likely me 6 months from now).

Thank you, that works what I can't figure out how one SQL app it works and one it doesn't

Can you send me a link to the suiteapp you mentioned?

I'm sorry, it's a suite bundle, SuiteQL Monaco Editor, and the other one, SuiteQL Query Tool Version 2021.2. Developed by Tim Dietrich.

it depends what method is being ran to get the query results. runSuiteQL vs runSuiteQLPaged. They behave differently with specific fields like the transaction status

that makes sense