Solved

SQL command returns different results running against a Great Plains table

Posted on 2007-03-22
7
383 Views
Last Modified: 2013-12-11
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
0
Comment
Question by:jdr0606
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
7 Comments
 
LVL 14

Assisted Solution

by:puranik_p
puranik_p earned 200 total points
ID: 18778051
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
 

Author Comment

by:jdr0606
ID: 18779343
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
 
LVL 14

Expert Comment

by:puranik_p
ID: 18780078
In fact, I wanted to know the same thing...
what exactly is...{ts '2007-03-22 00:00:00'}?
0
Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

 
LVL 3

Expert Comment

by:Pioneermfg
ID: 18792320
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
 

Author Comment

by:jdr0606
ID: 18792478
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
 
LVL 3

Expert Comment

by:Pioneermfg
ID: 18793148
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
 
LVL 3

Accepted Solution

by:
Pioneermfg earned 300 total points
ID: 18793293
"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

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
What is GIS method of Geometry data type? 6 36
T-SQL: Trying to use a "NOT IN (Subquery)" in CASE Statement 2 30
Do not display comma when no last name 8 48
SQL query 45 41
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how the fundamental information of how to create a table.

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question