-1

This one has me about beat. The following is code within a stored procedure that, if run repeatedly, continues to 1) fail to identify a matching row and 2)insert a new identical row. The values and datatypes for SERV_LINE_ID and TYPE_OF_SERVICE in the Source and Target tables are identical. Again, this behavior is repeatable; even after deleting the offending row. Other deleted rows are replaced, but are identified after the first run.

INSERT INTO etl.TargetTable
            (CUSTOMER_NUMBER,
             PCN,
             VISN,
             VA_FACILITY,
             AUTHORIZATION_NUMBER,
             VAMC,
             CLINXX,
             BEGIN_DOS,
             BILLED_AMOUNT,
             DATE_SUBMITTED,
             VA_FORM_TYPE,
             VETERAN_LAST_NAME,
             VETERAN_FIRST_NAME,
             VETERAN_SS,
             TYPE_OF_SERVICE,
             ENDING_DOS,
             CATEGORY_CARE,
             AUTH_TYPE,
             SERVICE_LINE_ID,
             SERVICE_LINE_CHARGE_AMOUNT,
             LAST_EXPORT,
             SYSISEXPORTED,
             SYSISDELETED,
             SYSCHECKSUM,
             SERV_LINE_ID)
SELECT s.CUSTOMER_NUMBER,
       s.PCN,
       s.VISN,
       s.VA_FACILITY,
       s.AUTHORIZATION_NUMBER,
       s.VAMC,
       s.CLINXX,
       s.BEGIN_DOS,
       s.BILLED_AMOUNT,
       s.DATE_SUBMITTED,
       s.VA_FORM_TYPE,
       s.VETERAN_LAST_NAME,
       s.VETERAN_FIRST_NAME,
       s.VETERAN_SS,
       s.TYPE_OF_SERVICE,
       s.ENDING_DOS,
       s.CATEGORY_CARE,
       s.AUTH_TYPE,
       s.SERVICE_LINE_ID,
       s.SERVICE_LINE_CHARGE_AMOUNT,
       NULL,
       0,
       0,
       s.SYSCHECKSUM,
       s.SERV_LINE_ID
FROM   ##Source s
       LEFT OUTER JOIN etl.TargetTable t
                    ON s.SERV_LINE_ID = t.SERV_LINE_ID
                       AND s.TYPE_OF_SERVICE = t.TYPE_OF_SERVICE
                       AND s.DATE_SUBMITTED <= '2018-10-10'
WHERE  t.SERV_LINE_ID IS NULL 
  • 2
    If it's repeatable, can you provide (anonymized) sample data? – Erik Darling Feb 7 at 17:43
  • Where does the global ##temp table get created? You know those disappear when no more sessions reference them, right? I'm just wondering if you have failsafe logic somewhere that is dropping and re-creating it based on the previous state. SQL Server doesn't sometimes decide to join differently. – Aaron Bertrand Feb 7 at 18:00
  • Thanks for looking at this Erik. I think this is what you are asking for: CUSTOMER_NUMBER PCN VISN VA_FACILITY AUTHORIZATION_NUMBER VAMC CLINXX BEGIN_DOS BILLED_AMOUNT DATE_SUBMITTED VA_FORM_TYPE VETERAN_LAST_NAME VETERAN_FIRST_NAME VETERAN_SS TYPE_OF_SERVICE ENDING_DOS CATEGORY_CARE AUTH_TYPE SERVICE_LINE_ID SERVICE_LINE_CHARGE_AMOUNT SYSCHECKSUM SERV_LINE_ID VAMEDICAL 98765432101 NULL 520 9999999999-9 NULL 9/26/2016 2525.25 11/1/2016 HCFA JOE GI 123456789 PROF 9/26/2016 SPECIALTY 2 1525.25 BigHashHere 2020202 – Steve_Malcolm Feb 7 at 18:31
  • Hi Aaron, this is at the top of the collection routine: DROP TABLE IF EXISTS ##Source. Thanks for reviewing. – Steve_Malcolm Feb 7 at 18:34
  • @Steve_Malcolm please update your question with new information. – Erik Darling Feb 7 at 19:22
0

Rant: After spending 7 hours over the past two days it's a bit of a pain to be told that this question indicates a lack of research. /Rant

I've involved you all (thank you) and my team members; and finally stumbled over the solution. I changed the logic in the query to move the filter from the JOIN predicate to the WHERE clause. enter image description here to

enter image description here

I would love for someone to help me understand why this helps. I thought using a filter in the JOIN was the same as having it in the WHERE clause.

  • "using a filter in the JOIN was the same as having it in the WHERE clause" It is when the JOIN is INNER join. When it's OUTER join (LEFT, RIGHT, FULL), no, there is a difference. – ypercubeᵀᴹ Feb 7 at 22:48
  • Thank you ypercube! The response is appreciated. – Steve_Malcolm Feb 8 at 15:04
  • Perhaps if you change the question to what you really were curious: The effects of predicates in the JOIN operator and in the WHERE clause, you may have been given the answer. the trouble is this is a basic point about JOIN operators and that perhaps consulting the Docs would explain. The issue primarily is with what is considered the source material to compare...and your old query was not what you were intending to get. – clifton_h Feb 11 at 0:32

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.