RADar-AZDelta/archived-Rabbit-in-a-Blender
1
0
Fork 0
mirror of https://github.com/RADar-AZDelta/Rabbit-in-a-Blender.git synced 2026-03-23 20:37:03 +00:00
Code Issues 6 Projects Packages Wiki Activity

[GH-ISSUE #89] Note-nlp: Reserved word offset geeft problemen #52

New issue
Closed
opened 2026-03-23 20:28:44 +00:00 by mirror · 2 comments
mirror commented 2026-03-23 20:28:44 +00:00
Owner
Copy link

Originally created by @FannyDHondt on GitHub (Jul 4, 2024).
Original GitHub issue: https://github.com/RADar-AZDelta/Rabbit-in-a-Blender/issues/89

Originally assigned to: @pjlammertyn on GitHub.

Note_nlp werkt nog niet. Ik krijg volgende error

Name "offset" not found inside t at [13:15]

Query:

CREATE OR REPLACE TABLE `azd-dev-data-fdhondt01.omop.note_nlp`
LIKE `azd-dev-data-fdhondt01.omop.note_nlp`AS (
    WITH cte_uploaded_tables AS (
        SELECT *
        FROM `azd-dev-data-fdhondt01.work.note_nlp__upload__davinci_prostaat`
    ), cte_keys_swapped AS (
        SELECT
            swap_pk.y as `note_nlp_id`,
            IFNULL(swap_fk0.y, 0) as `note_id`,
            IFNULL(swap_ci0.conceptId, 0) as `section_concept_id`,
            t.`snippet`,
            t.`"offset"`,
            t.`lexical_variant`,
            IFNULL(swap_ci1.conceptId, 0) as `note_nlp_concept_id`,
            IFNULL(swap_ci2.conceptId, 0) as `note_nlp_source_concept_id`,
            t.`nlp_system`,
            t.`nlp_date`,
            t.`nlp_datetime`,
            t.`term_exists`,
            t.`term_temporal`,
            t.`term_modifiers`
        FROM cte_uploaded_tables t
        LEFT OUTER JOIN `azd-dev-data-fdhondt01.work.note_nlp__section_concept_id_usagi` swap_ci0 on swap_ci0.sourceCode = t.`section_concept_id`
                and swap_ci0.mappingStatus = 'APPROVED'
        LEFT OUTER JOIN `azd-dev-data-fdhondt01.work.note_nlp__note_nlp_concept_id_usagi` swap_ci1 on swap_ci1.sourceCode = t.`note_nlp_concept_id`
                and swap_ci1.mappingStatus = 'APPROVED'
        LEFT OUTER JOIN `azd-dev-data-fdhondt01.work.note_nlp__note_nlp_source_concept_id_usagi` swap_ci2 on swap_ci2.sourceCode = t.`note_nlp_source_concept_id`
                and swap_ci2.mappingStatus = 'APPROVED'
        INNER JOIN `azd-dev-data-fdhondt01.work.note_nlp_id_swap` swap_pk on swap_pk.x = t.note_nlp_id
            and IFNULL(swap_pk.`section_concept_id`, 0) = IFNULL(swap_ci0.conceptId, 0)
            and IFNULL(swap_pk.`note_nlp_concept_id`, 0) = IFNULL(swap_ci1.conceptId, 0)
            and IFNULL(swap_pk.`note_nlp_source_concept_id`, 0) = IFNULL(swap_ci2.conceptId, 0)
        LEFT OUTER JOIN `azd-dev-data-fdhondt01.work.note_id_swap` swap_fk0 on swap_fk0.x = t.`note_id` 
    ), cte_duplicates AS (
        SELECT *
            , ROW_NUMBER() OVER(
                PARTITION BY
                note_nlp_id
                , `section_concept_id`
                , `note_nlp_concept_id`
                , `note_nlp_source_concept_id`
            ) AS rn
        FROM cte_keys_swapped
    )
    SELECT * EXCEPT(rn)
    FROM cte_duplicates
    WHERE rn = 1
)

Ik heb de query eens gerund in BigQuery en het werkt als t."offset", veranderd wordt in t.offset,

Originally posted by @FannyDHondt in https://github.com/RADar-AZDelta/Rabbit-in-a-Blender/issues/75#issuecomment-2208990120

Originally created by @FannyDHondt on GitHub (Jul 4, 2024). Original GitHub issue: https://github.com/RADar-AZDelta/Rabbit-in-a-Blender/issues/89 Originally assigned to: @pjlammertyn on GitHub. Note_nlp werkt nog niet. Ik krijg volgende error ``` Name "offset" not found inside t at [13:15] Query: CREATE OR REPLACE TABLE `azd-dev-data-fdhondt01.omop.note_nlp` LIKE `azd-dev-data-fdhondt01.omop.note_nlp`AS ( WITH cte_uploaded_tables AS ( SELECT * FROM `azd-dev-data-fdhondt01.work.note_nlp__upload__davinci_prostaat` ), cte_keys_swapped AS ( SELECT swap_pk.y as `note_nlp_id`, IFNULL(swap_fk0.y, 0) as `note_id`, IFNULL(swap_ci0.conceptId, 0) as `section_concept_id`, t.`snippet`, t.`"offset"`, t.`lexical_variant`, IFNULL(swap_ci1.conceptId, 0) as `note_nlp_concept_id`, IFNULL(swap_ci2.conceptId, 0) as `note_nlp_source_concept_id`, t.`nlp_system`, t.`nlp_date`, t.`nlp_datetime`, t.`term_exists`, t.`term_temporal`, t.`term_modifiers` FROM cte_uploaded_tables t LEFT OUTER JOIN `azd-dev-data-fdhondt01.work.note_nlp__section_concept_id_usagi` swap_ci0 on swap_ci0.sourceCode = t.`section_concept_id` and swap_ci0.mappingStatus = 'APPROVED' LEFT OUTER JOIN `azd-dev-data-fdhondt01.work.note_nlp__note_nlp_concept_id_usagi` swap_ci1 on swap_ci1.sourceCode = t.`note_nlp_concept_id` and swap_ci1.mappingStatus = 'APPROVED' LEFT OUTER JOIN `azd-dev-data-fdhondt01.work.note_nlp__note_nlp_source_concept_id_usagi` swap_ci2 on swap_ci2.sourceCode = t.`note_nlp_source_concept_id` and swap_ci2.mappingStatus = 'APPROVED' INNER JOIN `azd-dev-data-fdhondt01.work.note_nlp_id_swap` swap_pk on swap_pk.x = t.note_nlp_id and IFNULL(swap_pk.`section_concept_id`, 0) = IFNULL(swap_ci0.conceptId, 0) and IFNULL(swap_pk.`note_nlp_concept_id`, 0) = IFNULL(swap_ci1.conceptId, 0) and IFNULL(swap_pk.`note_nlp_source_concept_id`, 0) = IFNULL(swap_ci2.conceptId, 0) LEFT OUTER JOIN `azd-dev-data-fdhondt01.work.note_id_swap` swap_fk0 on swap_fk0.x = t.`note_id` ), cte_duplicates AS ( SELECT * , ROW_NUMBER() OVER( PARTITION BY note_nlp_id , `section_concept_id` , `note_nlp_concept_id` , `note_nlp_source_concept_id` ) AS rn FROM cte_keys_swapped ) SELECT * EXCEPT(rn) FROM cte_duplicates WHERE rn = 1 ) ``` Ik heb de query eens gerund in BigQuery en het werkt als t.`"offset"`, veranderd wordt in t.`offset`, _Originally posted by @FannyDHondt in https://github.com/RADar-AZDelta/Rabbit-in-a-Blender/issues/75#issuecomment-2208990120_
mirror 2026-03-23 20:28:44 +00:00
  • closed this issue
  • added the
    bug
         label
mirror commented 2026-03-23 20:28:45 +00:00
Author
Owner
Copy link

@pjlammertyn commented on GitHub (Jul 4, 2024):

In de CSV van OHDSI zit de staan er quotes rond offset!

Logisch is anders.

image

<!-- gh-comment-id:2209118539 --> @pjlammertyn commented on GitHub (Jul 4, 2024): In de CSV van OHDSI zit de [staan er quotes rond offset](https://github.com/OHDSI/CommonDataModel/blob/main/inst/csv/OMOP_CDMv5.4_Field_Level.csv?plain=1#L221C10-L221C22)! Logisch is anders. ![image](https://github.com/RADar-AZDelta/Rabbit-in-a-Blender/assets/1187178/ebaaaa5f-5e84-4fdc-b321-c30cd5dd2486)
mirror commented 2026-03-23 20:28:45 +00:00
Author
Owner
Copy link

@pjlammertyn commented on GitHub (Jul 4, 2024):

image

<!-- gh-comment-id:2209130728 --> @pjlammertyn commented on GitHub (Jul 4, 2024): ![image](https://github.com/RADar-AZDelta/Rabbit-in-a-Blender/assets/1187178/a3a19d02-855f-4b50-a9ce-fbb6e847ac00)
Sign in to join this conversation.
No Branch/Tag specified
Branches Tags
main
109-episode_event-table-not-filled
issue95-make-link-between-episode_parent_id-and-episode_id
dependabot/pip/requests-2.32.0
gh-pages
0.0.74
0.0.73
0.0.72
0.0.71
0.0.70
0.0.69
0.0.68
0.0.67
0.0.66
0.0.65
0.0.64
0.0.63
0.0.62
0.0.61
0.0.60
0.0.59
0.0.58
0.0.57
0.0.56
0.0.54
0.0.53
0.0.52
0.0.51
0.0.50
0.0.48
0.0.47
0.0.45
0.0.44
0.0.42
0.0.40
0.0.39
0.0.38
0.0.37
0.0.36
0.0.35
0.0.34
Labels
Clear labels   
bug

   
cannot reproduce

   
duplicate

   
enhancement

   
experiment

   
invalid

   
pull-request

Mirrored from GitHub Pull Request

  
question

   
wontfix
No labels
bug
cannot reproduce
duplicate
enhancement
experiment
invalid
pull-request
question
wontfix
Milestone
Clear milestone
No items
No milestone
Projects
Clear projects
No items
No project
Assignees
Clear assignees
No assignees
1 participant
Notifications
Due date
The due date is invalid or out of range. Please use the format "yyyy-mm-dd".

No due date set.

Dependencies

No dependencies set.

Reference
RADar-AZDelta/archived-Rabbit-in-a-Blender#52
No description provided.