SQL command returns different results running against a Great Plains table

I recently upgraded a Great Plains application from one version to another and the following SQL statement worked fine but after the upgrade the statement basically returns all the rows when run regardless of the date (SOP10100.actlship) or batch number (SOP10100.BACHNUMB) used as the filter. I'm running a SQL 2000 database.

Any ideas where to begin?

 SELECT SOP10100.SOPNUMBE, SOP10200.ITEMNMBR, SOP10200.QTYFULFI, SOP10200.QTYTOINV, SOP10100.SOPTYPE, SOP10100.BACHNUMB, SOP10100.ACTLSHIP
 FROM   KG.dbo.SOP10100 SOP10100 INNER JOIN KG.dbo.SOP10200 SOP10200 ON SOP10100.SOPNUMBE=SOP10200.SOPNUMBE
 WHERE  SOP10100.ACTLSHIP<{ts '2007-03-22 00:00:00'} AND SOP10100.SOPTYPE=2 AND SOP10100.BACHNUMB<>'ship2/28/07' AND SOP10200.QTYFULFI>0
 ORDER BY SOP10100.SOPNUMBE
jdr0606Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
PioneermfgConnect With a Mentor Commented:
"ts" is the ODBC timestamp.  your query works with or without this piece (assuming you want all records prior to the date you have in your query).
0
 
puranik_pConnect With a Mentor Commented:
Is SOP10100.ACTLSHIP a date field?
if yes, this may be a problem...
SOP10100.ACTLSHIP<{ts '2007-03-22 00:00:00'}

What exactly is ts?

change it to
SOP10100.ACTLSHIP<'2007-03-22 00:00:00'
0
 
jdr0606Author Commented:
The SOP10100.ACTLSHIP is a date field and as a follow-up what is the difference between using the SOP10100.ACTLSHIP<{ts '2007-03-22 00:00:00'} versus SOP10100.ACTLSHIP<'2007-03-22 00:00:00' for the same field type?  Can you clarify the SQL syntax definition for {ts '2007-03-22 00:00:00'}?

Thanks
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
puranik_pCommented:
In fact, I wanted to know the same thing...
what exactly is...{ts '2007-03-22 00:00:00'}?
0
 
PioneermfgCommented:
jdr0606;
  it looks like you wrote this in Access and not SQL.  What version of GP are you on now?  Also, I would verify the ACTLSHIP column to make sure the ACTLSHIP does not show 01/01/1900 (GP default date) if it does, i can give you a simple access query to update the ACTLSHIP from the SOP30200 table.
0
 
jdr0606Author Commented:
Pioneermfg, that doesn't surprise me, this is part of a script that I inherited from a previous developer from several years ago.
There are valid dates in the ACTLSHIP fields (i.e. 2007-01-31) but it sounds like you're saying the ...{ts '2007-03-22 00:00:00'} is actually Access syntax, what does it mean?

Thanks
0
 
PioneermfgCommented:
can you tell me what you are trying to accomplish with this query? based on what you posted, this will return anything prior to the date specifed.  what did this query return prior to upgrading GP?
0
All Courses

From novice to tech pro — start learning today.