MIDRANGE dot COM Mailing List Archive



Home » MIDRANGE-L » April 2014

sql problem trying to get only 1 record.



fixed

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.

Here is my original sql statement

sqlstmt = 'select * from gisowner.jtx_jobs a +
left outer join gisowner.jtx_job_holds b +
on a.Job_id = b.Job_id +
left outer join gisowner.JTX_HOLD_TYPES c +
on b.HOLD_TYPE_ID = c.id +
join 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) >= ? +
and convert(char(10),a.End_date,120) <= ? +
and convert(char(10),a.end_date,120) +
convert(char(10),a.due_date,120) order by a.job_id ' ;

Gisowner.jtx_job_holds can have multiple records with different holddates.
With this statement I am getting some duplicates.

So I'm testing now within sql server itself and here is what I currently have.

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
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.





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

This mailing list archive is Copyright 1997-2014 by MIDRANGE dot COM and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available here. If you have questions about this, please contact