RE: sql problem trying to get only 1 record.

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.

-snip-

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
= c.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;

--buck
--
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,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at http://archive.midrange.com/midrange-l.


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.