Joined: 08 Oct 2004 Posts: 274 Topics: 52 Location: California
Posted: Wed May 09, 2007 6:47 pm Post subject: Select with a few 'Left' Joins - need help with last join
I'm having trouble coding a join on a table whose PK is FAMILY_ID, RELATION_ID, and FROM_DATE. I need to get the MAX FROM_DATE from this table for the matching FAMILY_ID and RELATION_ID. The join in question is the last JOIN, on LEFT JOIN FDBEL.REG_MASTER E
Code:
SELECT '321', CURRENT DATE
,A.PROV_ID, A.CLAIM_ID, SUBSTR(DIGITS(A.PART_SSN),2,9)
,RTRIM(A.FNAME) || ' '|| RTRIM(A.LNAME) AS NAME
,A.ADD1
,RTRIM(A.CITY) || ', '|| RTRIM(A.STATE) ||'. '|| RTRIM(A.ZIP) AS AD
,REPLACE (A.LINE01,'\N', X'0D' || X'25') AS LINE01
,REPLACE (A.LINE02,'\N', X'0D' || X'25') AS LINE02
,REPLACE (A.LINE03,'\N', X'0D' || X'25') AS LINE03
,REPLACE (A.LINE04,'\N', X'0D' || X'25') AS LINE04
,REPLACE (A.LINE05,'\N', X'0D' || X'25') AS LINE05
,B.TAXID, B.TAXID_TYPE, B.NAME1
,C.OCC_FROM_DATE, C. OCC_TO_DATE, C.TOT_CHRG_AMT
,IFNULL (VARCHAR(D.ADJUSTERCODE), REPEAT(' ',3)) AS ADJUSTER
,IFNULL (VARCHAR(E.FIRST_NAME), REPEAT(' ',8)) AS PAT_NAME
FROM FDBMD.LETTERS_MEDICAL A
LEFT JOIN FDBMD.PROVIDER B
ON A.PROV_ID = B.PROVIDER_ID
LEFT JOIN FDBMD.MEDHDR C
ON A.CLAIM_ID = C.CLAIM_ID
LEFT JOIN FDBIS.ADJUSTERS D
ON A.USERID = D.ADJUSTERNAME
LEFT JOIN FDBEL.REG_MASTER E
ON A.FAMILY_ID = E.FAMILY_ID
AND A.RELATION_ID = E.RELATION_ID
AND E.THRU_DATE = MAX THRU DATE
WHERE A.LETTERID = 11
[quote][/quote]
I hope this makes sense. _________________ Thanks,
NASCAR9
Joined: 26 Nov 2002 Posts: 12378 Topics: 75 Location: San Jose
Posted: Thu May 10, 2007 8:44 am Post subject:
Nascar9,
Try this untested sql. I added a temp table to get the max date from the last left join
Code:
SELECT CHAR('321')
,CURRENT DATE
,A.PROV_ID
,A.CLAIM_ID
,SUBSTR(DIGITS(A.PART_SSN),2,9)
,RTRIM(A.FNAME) || ' '|| RTRIM(A.LNAME) AS NAME
,A.ADD1
,RTRIM(A.CITY) || ', '|| RTRIM(A.STATE) ||'. '|| RTRIM(A.ZIP) AS AD
,REPLACE (A.LINE01,'\N', X'0D' || X'25') AS LINE01
,REPLACE (A.LINE02,'\N', X'0D' || X'25') AS LINE02
,REPLACE (A.LINE03,'\N', X'0D' || X'25') AS LINE03
,REPLACE (A.LINE04,'\N', X'0D' || X'25') AS LINE04
,REPLACE (A.LINE05,'\N', X'0D' || X'25') AS LINE05
,B.TAXID
,B.TAXID_TYPE
,B.NAME1
,C.OCC_FROM_DATE
,C. OCC_TO_DATE
,C.TOT_CHRG_AMT
,IFNULL (VARCHAR(D.ADJUSTERCODE), REPEAT(' ',3)) AS ADJUSTER
,IFNULL (VARCHAR(E.FIRST_NAME), REPEAT(' ',8)) AS PAT_NAME
FROM FDBMD.LETTERS_MEDICAL A
LEFT JOIN FDBMD.PROVIDER B
ON A.PROV_ID = B.PROVIDER_ID
LEFT JOIN FDBMD.MEDHDR C
ON A.CLAIM_ID = C.CLAIM_ID
LEFT JOIN FDBIS.ADJUSTERS D
ON A.USERID = D.ADJUSTERNAME
LEFT JOIN (SELECT FAMILY_ID
,RELATION_ID
,MAX(THRU_DATE)
FROM FDBEL.REG_MASTER
GROUP BY FAMILY_ID
,RELATION_ID) E
ON A.FAMILY_ID = E.FAMILY_ID
AND A.RELATION_ID = E.RELATION_ID
WHERE A.LETTERID = 11
Joined: 26 Nov 2002 Posts: 12378 Topics: 75 Location: San Jose
Posted: Thu May 10, 2007 9:46 am Post subject:
Nascar9,
you got the syntax wrong. you need to qualify the table outside and also your Group by list does not include Firstname. Pay attention to Qualifier at the end of the Group by
Code:
SELECT CHAR('321')
,CURRENT DATE
,A.PROV_ID
,A.CLAIM_ID
,IFNULL (VARCHAR(E.FIRST_NAME), REPEAT(' ',8))
FROM FDBMD.LETTERS_MEDICAL A
LEFT JOIN (SELECT FAMILY_ID
,RELATION_ID
,FIRST_NAME
,MAX(THRU_DATE)
FROM FDBEL.REG_MASTER
GROUP BY FAMILY_ID
,RELATION_ID
,FIRST_NAME) E
ON A.FAMILY_ID = E.FAMILY_ID
AND A.RELATION_ID = E.RELATION_ID
WHERE A.LETTERID = 11;
Joined: 08 Oct 2004 Posts: 274 Topics: 52 Location: California
Posted: Thu May 10, 2007 10:26 am Post subject:
kolusu,
The SQL ran, but Grouped every row in FDBEL.REG_MASTER. I think it does the 'Join On' after it has the 'Group BY' result set.
Can a 'WHERE' be added? I'm tiring with zero success.
Again Thanks for your help. _________________ Thanks,
NASCAR9
Joined: 08 Oct 2004 Posts: 274 Topics: 52 Location: California
Posted: Thu May 10, 2007 11:09 am Post subject:
kolusu, REG_MASTER contains over 3.5 million rows. The data is Time Sensitive. That is why I need the most current occurrence. Here are a few columns from the table. You should be able to get a feel from the example. If not I will provide more.
Code:
FAMILY_ID RELATION_ID FROM_DATE THRU_DATE FIRST_NAME
452549 0 1/2/1950 12/31/9999 DON
452549 1 1/2/1950 7/31/1978 JEAN
452549 1 11/1/1986 12/31/9999 JEANETTE
Joined: 26 Nov 2002 Posts: 12378 Topics: 75 Location: San Jose
Posted: Thu May 10, 2007 12:01 pm Post subject:
NASCAR9,
Try this
Code:
SELECT CHAR('321')
,CURRENT DATE
,A.PROV_ID
,A.CLAIM_ID
,IFNULL (VARCHAR(E.FIRST_NAME), REPEAT(' ',8))
FROM FDBMD.LETTERS_MEDICAL A
LEFT JOIN (SELECT FAMILY_ID
,RELATION_ID
,FIRST_NAME
FROM FDBEL.REG_MASTER
WHERE (FAMILY_ID,THRU_DATE) IN (SELECT FAMILY_ID
,MAX(THRU_DATE)
FROM FDBEL.REG_MASTER
GROUP BY FAMILY_ID)) E
ON A.FAMILY_ID = E.FAMILY_ID
AND A.RELATION_ID = E.RELATION_ID
WHERE A.LETTERID = 11;
Joined: 08 Oct 2004 Posts: 274 Topics: 52 Location: California
Posted: Thu May 10, 2007 1:19 pm Post subject:
kolusu,
Thanks for the SQL. It runs in about half the time of the first solution. Problem is it still takes 32 seconds per occurrence. I need this to run in sub-second per occurrence. The solution will be a stored procedure with a Temp table.
This query is used to create letters that are mailed daily. The volume dictates sub-second.
Again thaks for ALL your Effort! _________________ Thanks,
NASCAR9
Joined: 26 Nov 2002 Posts: 12378 Topics: 75 Location: San Jose
Posted: Thu May 10, 2007 1:23 pm Post subject:
NASCAR9,
You are using a lot of scalar functions(RTRIM, Replace...) which will slow your processing. How about coding that functionality in the program instead of SQL?
Joined: 08 Oct 2004 Posts: 274 Topics: 52 Location: California
Posted: Thu May 10, 2007 1:45 pm Post subject:
kolusu, The query runs sub-second until I add the last join on REG_MASTER. Actually in Visual Explain the cost are some of lowest I've seen.
The requirement for this project is to create a query/stored procedure that a Mail/Merge process can call.
Your idea on a program is correct, I just can't use one for this project. _________________ Thanks,
NASCAR9
Joined: 20 Oct 2006 Posts: 1411 Topics: 26 Location: germany
Posted: Thu May 10, 2007 2:33 pm Post subject:
i understand that it is time sensitive, but why not split the processing, strip the REG_MASTER based on a right-outer-join with the mail file on the right and the join would be familyid, relationid, and letter id = 11? then allow the mail/merger process call a simpler proc to format and derive the max thru date? _________________ Dick Brenholtz
American living in Varel, Germany
SELECT CHAR('321')
,CURRENT DATE
,A.PROV_ID
,A.CLAIM_ID
,IFNULL (VARCHAR(E.FIRST_NAME), REPEAT(' ',8))
FROM FDBMD.LETTERS_MEDICAL A
LEFT JOIN (SELECT X.FAMILY_ID
,X.RELATION_ID
,X.FIRST_NAME
FROM FDBEL.REG_MASTER X
INNER JOIN FDBMD.LETTERS_MEDICAL Y
ON Y.LETTERID = 11
AND Y.FAMILY_ID = X.FAMILY_ID
AND Y.RELAION_ID = X.RELATION_ID
WHERE X.THRU_DATE =
(SELECT MAX(Z.THRU_DATE)
FROM FDBEL.REG_MASTER Z
WHERE Z.FAMILY_ID = X.FAMILY_ID
AND Z.RELATION_ID = X.RELATION_ID)) E
ON A.FAMILY_ID = E.FAMILY_ID
AND A.RELATION_ID = E.RELATION_ID
WHERE A.LETTERID = 11;
This should create temp table E with only relevant narrowed down info instead of grouping all the records from FDBEL.REG_MASTER table. Also, please make sure that table FDBEL.REG_MASTER have composite index defined on family_id + relation_id fields.
I think using scalar or other functions in the select list of query like this should not have any noticeable impact on run time of the query, unless this query is run in succession many number of times. It mainly depends on how predicates are resolved and how much information is drilled in order to reach to the result set.
Joined: 08 Oct 2004 Posts: 274 Topics: 52 Location: California
Posted: Mon May 21, 2007 9:36 am Post subject:
pharia, Thank You for your response. I did test the query and it ran sub-second. The results were corret also. This is a double bonus Honestly, we've changed the application to provide the need data from FDBEL.REG_MASTER, it already has it.
I will save this code in my SQL Bag of Tricks.
Thank Again!!
dbzTHEdinoasaur, Thank You for your input. _________________ Thanks,
NASCAR9
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
Honestly, we've changed the application to provide the need data from FDBEL.REG_MASTER, it already has it.