Home > OS >  Adding custom value to SELECT
Adding custom value to SELECT

Time:11-04

I have this query:

SELECT r.record_id,
       r.part_id,
       r.record_dt,
       a.association_part_a,
       a.association_part_b,
       a.association_type,
       a.association_id,
       r.product_id
FROM   dbo.synfact_association AS a
       INNER JOIN dbo.synfact_record AS r
               ON a.record_id = r.record_id
WHERE  ( r.part_id IN (SELECT part_id
                       FROM   dbo.synfact_record AS synfact_record_1
                       WHERE  ( record_status = 1 )
                              AND ( record_type = 0 )) )
       AND ( r.product_id IN ( 38, 39, 40, 41,
                               42, 43, 44, 45,
                               46, 37, 47, 48,
                               49, 50, 51, 52,
                               53, 54, 58, 59 ) )
       AND ( r.record_id > 499 )
       AND ( r.record_status = 1 ) 

I want to use the record_dt of the second query as a replacement of the record_dt in the first query. I want to do that by linking the values on the part_id since they both have it. First query is giving a list of record_id that has part_id, the second query is taking the latest record_dt for each record_id. My end goal is to take the latest record_dt from the second query to replace in the first query. I can link them both on the part_id.

I need to replace the r.record_dt with this SELECT:

SELECT DISTINCT RECORD_DT FROM synfact_record WHERE PROCESS_STEP_LIST_ID IN (21,22,23) ORDER BY RECORD_DT DESC

This is what the first query does

RECORD_ID PART_ID RECORD_DT ASSOCIATION_PART_A ASSOCIATION_PART_B ASSOCIATION_TYPE ASSOCIATION_ID PRODUCT_ID
286660 SYN12021020100018 2021-02-15 11:18:11.840 SYN12021020100018 21-02-01-000003 unique_id 452028 39
287146 SYN12021020300773 2021-02-17 07:30:59.603 SYN12021020300773 2102-00-005218 unique_id 455735 38
287147 SYN12021020300774 2021-02-17 07:31:04.780 SYN12021020300774 2102-00-005219 unique_id 455736 38

The second query returns:

RECORD_DT
2021-10-20 11:36:02.670
2021-10-20 11:35:29.263
2021-10-20 11:34:59.583

I want to replace the record_dt by the one with the second SELECT.

I know I must append it to the first query, I just don't know how...

Which method is best to do this?

CodePudding user response:

If You can LIMIT 1 to your select clause, you can use query like this:

SELECT r.record_id,
       r.part_id,
    CASE
        WHEN r.PROCESS_STEP_LIST_ID IN (21 , 22, 23) THEN 
            (SELECT DISTINCT RECORD_DT FROM synfact_record WHERE PROCESS_STEP_LIST_ID IN (21,22,23) ORDER BY RECORD_DT DESC LIMIT 1)
        ELSE r.RECORD_DT
    END as record_dt,
       a.association_part_a,
       a.association_part_b,
       a.association_type,
       a.association_id,
       r.product_id
FROM   dbo.synfact_association AS a
       INNER JOIN dbo.synfact_record AS r
               ON a.record_id = r.record_id

  • Related