https://netsuiteprofessionals.com logo
Join Slack
Powered by
SuiteQL Question: Trying to get a condition set on...
# integrations
a
SuiteQL Question: Trying to get a condition set on a Date so I have this: transaction.
trandate >= TO_DATE('2020-05-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
However, that doesn't seem to be working. Any assistance is appreciated.
t
I think "trandate" is a date, and not a timestamp. So maybe try this? 
trandate >= TO_DATE('2020-05-01', 'YYYY-MM-DD')
a
No dice.
t
What does the full SELECT statement look like?
a
SELECT a.id as a_id, a.trandate as a_TransDate, a.tranid as a_TranID, a.entity as a_Entity, a.trackingnumberlist as a_TrackingNumber, b.createdfrom as b_CreatedFrom, b.linesequencenumber as b_lineno, b.item as b_Item, e.itemid as e_actualitemno, e.description as e_description, b.itemtype as b_ItemType, b.quantity as b_qty, BUILTIN.DF(b.units) as b_units, c.entityTitle as c_EntityTitle, d.d_CustomerPO from transaction a left join transactionline b on a.ID = b.transaction left join entity c on a.entity = c.id inner join (select id, otherrefnum as d_CustomerPO from transaction where type = 'SalesOrd') d on b.createdfrom = d.id join item e on b.item = e.id where a.type = 'ItemShip' and c.entityid in ('125-1', '1393', '1393-1', '1393-2', '1393-3', '1393-4','1393-5','1393-6', '1393-7', '1393-serv', '1739', '2095','2095-1','2095-3','2095-4','2095-6','3752','4023-2','4023-1') and a.custbody_edi_asn_sent is NULL and trandate >= TO_DATE('2020-05-01', 'YYYY-MM-DD')"
The query works without the trandate, but when I add that it gives me 0 results
t
This is a longshot, but have you tried prefixing trandate? So... 
a.trandate >= TO_DATE('2020-05-01', 'YYYY-MM-DD')
a
Yeah, I tried that after I pasted and realized it wasn't prefixed.
I'm stumped. I used the Chrome Plugin with the Custom Workbook to see how it returned the SuiteQL, and thats what I used, but it doesn't work for me.
ok, so 
SELECT * from transaction a where a.trandate >= TO_DATE('2020-06-01', 'YYYY-MM-DD')
does work
I need to go back and review my query
Thanks so much for the suggestion!
t
No problem. I wish I could help more, but it's hard for me to replicate the query because you've got a custom field in there, specific entity IDs, etc.
a
sure, I understand. I'll get it. Just might take me some finagling. I appreciate your time!
t
I would start with this, and slowly add things into it breaks: 
SELECT
a.id as a_id,
a.trandate as a_TransDate
from
transaction a
where
a.trandate >= TO_DATE('2020-05-01', 'YYYY-MM-DD')
a
That is my plan 🙂
t
Good luck! I hope you solve it without a lot of fuss.
SuiteQL is awesome... until it isn't.
a
such is life, right?
t
Yep. Or, "Such is NetSuite."
a
touche
t
One more suggestion: Have you tried dialing back the date a bit? Maybe set it to 2020-04-01 or something?
a
so that gave me a result.
t
Doh!
Well it's a start.
a
indeed, but not sure why when the custom workbook shows transactions, the SuiteQL isn'.
Ok, let me take that transaction and a newer one and see what other condition I may have incorrect
seriously, thanks. Best interaction on this slack yet 🙂
t
No problem at all. I've become a sort of SuiteQL junkie lately, so this was a nice challenge.
a
Well if I come up with anymore doozees I'll let you know 🙂
t
I'm using it quite a bit with SuiteTalk REST, which adds its own challenge.
a
I'm using it with REST as well.
So I think it might have to do with the custom field. In the Chrome addin its doing the following, and I don't know how to adjust for SuiteQL with REST: 
NVL("TRANSACTION".custbody_edi_asn_sent, 'F') = ?
if you have any ideas I'd love to hear them. TIA!~
nvm, it needed to be a.custbody_edi_asn_sent ='F'
146 Views
Open in Slack
PreviousNext
Powered by