Post reply

Get values from a SQL Query to use with an IN clause

  • May 16, 2023 at 3:21 pm

    #4190835

    My Query gives me a list of GUID's that I would like to use to help create a single returned result with the values from my query within an IN clause.

    My Query

    SELECT DISTINCT 
     t.Field_2_Id AS EncounterId
    INTO #temp
    FROM BillingEncounter.[Procedure]
    RIGHT JOIN (SELECT * FROM [CosmosDirect].[Missing_Id] WHERE TableName =  'BillingEncounter.Procedure') t ON t.Field_1_Id = BillingProcedureId AND t.Field_2_Id = EncounterId
    WHERE BillingProcedureId IS NULL

    SELECT * FROM #Temp

    Results

    4410f746-b95f-4746-8de6-b61e3d3d569h

    6eae0bea-f4fa-4577-96e0-747ee887b0dr

    74a8685f-c641-46ec-a039-55a9b9cfea93

    9e914ac8-9226-493f-aa06-ca458b4111f4

    cd05f2b0-2545-49d5-a57c-35702cb4f715

    ef042840-c76e-4177-8222-be1f6eaf7750

    I would like to take the results from this query to write something like this, so I can paste this in Cosmos (it hardcodes everything to the IN and uses my values from my query to put into the IN clause.

    WHERE contains(c.partitionKey, "BillingEncounter/17792") and c.encounterId IN

    (

    '4410f746-b95f-4746-8de6-b61e3d3d569h',

    '6eae0bea-f4fa-4577-96e0-747ee887b0dr',

    '74a8685f-c641-46ec-a039-55a9b9cfea93',

    '9e914ac8-9226-493f-aa06-ca458b4111f4',

    'cd05f2b0-2545-49d5-a57c-35702cb4f715',

    'ef042840-c76e-4177-8222-be1f6eaf7750'

    )

  • May 16, 2023 at 3:40 pm

    #4190867

    As you already have the results in #temp, the query can be written as follows

    WHERE contains(c.partitionKey, "BillingEncounter/17792") 
    AND EXISTS (SELECT 1 FROM #temp t where t.EncounterId = c.EncounterId)

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply