Thanks buck,

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Buck Calabro
Sent: Monday, April 21, 2014 3:42 PM
To: midrange-l@xxxxxxxxxxxx
Subject: Re: sql problem trying to get only 1 record.

On 4/21/2014 2:54 PM, Smith, Mike wrote:
I am writing an rpg program connecting to sql server that joins several files together. One of the files has or potentially can have multiple records and I only want to select the record with the most current hold date.


select a.JOB_ID,a.DESCRIPTION, d.receiveddate, max(b.HOLD_DATE) from
workflowmanager.gisowner.jtx_jobs a left outer join
workflowmanager.gisowner.jtx_job_holds b on a.Job_id = b.Job_id left
outer join workflowmanager.gisowner.JTX_HOLD_TYPES c on b.HOLD_TYPE_ID
= join workflowmanager.gisowner.wmx_workflow d on a.job_id =
d.job_id where a.job_Type_ID = 1 and (convert(char(10),a.End_date,120)
= '2014-01-01'
and convert(char(10),a.End_date,120) <= '2014-03-31' ) and
convert(char(10),a.end_date,120) > convert(char(10),a.due_date,120)
group by a.job_id, a.description, d.receiveddate, b.hold_date

The file names are slightly different, but its simply because sql server is making me add the database name.
I've also only included a few of the fields I actually need for testing purposes.
I still get multiple records from gisowner.jtx_job_holds but I'm not
sure why

JOB_ID DESCRIPTION receiveddate (No column name)
32002 2130537 2013-12-24 12:00:00.0000000 NULL
32003 2130430 2013-12-23 12:00:00.0000000 2014-01-13 14:56:13.0000000
32004 2130433 2013-12-23 12:00:00.0000000 2014-01-13 14:31:30.0000000
32004 2130433 2013-12-23 12:00:00.0000000 2014-01-13 14:51:18.0000000

Drop b.hold_date from your GROUP BY.
To see why, query only JTX_JOB_HOLDS twice:

select job_id, max(hold_date) from jtx_job_holds group by job_id; select job_id, max(hold_date) from jtx_job_holds group by job_id, hold_date;

This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at

NOTICE: This message, including any attachment, is intended as a confidential and privileged communication. If you have received this message in error, or are not the named recipient(s), please immediately notify the sender and delete this message.

This thread ...


Return to Archive home page | Return to MIDRANGE.COM home page