Forum Discussion

Kusi's avatar
Kusi
New Contributor
5 months ago

Extensibility Rules - SQL - Custom Data Tables

This syntax only inserts one line and the line does not have 'entity currency' or 'group currency' description in the [currency] column. Please advise on what the issue could be:

INSERT INTO XFC_005_RCP_FinResults ( [PROJ_ID], [PROJ_DESC], [SCENARIO], [TIME], [VIEW], [ACCOUNT], [EOJ], [EOJ_AVG_FX_RATE], [AMOUNT_INPUT], [AMOUNT_FACTORISED], [FACTOR_PERC], CURRENCY )
SELECT [PROJ_ID], [PROJ_DESC], [SCENARIO], [TIME], [VIEW], [ACCOUNT], [EOJ], [EOJ_AVG_FX_RATE], [AMOUNT_INPUT], [AMOUNT_FACTORISED], [FACTOR_PERC],
CASE WHEN RowNum = 1 THEN 'Entity currency' WHEN RowNum = 2 THEN 'Group currency' END AS CURRENCY
FROM (
SELECT *, ROW_NUMBER() OVER (PARTITION BY [PROJ_ID] ORDER BY [SCENARIO], [TIME], [ACCOUNT], [CURRENCY]) AS RowNum
FROM XFC_005_RCP_FinResults
WHERE CURRENCY = 'Project currency'
) AS t
WHERE RowNum <= 2
AND NOT EXISTS (
SELECT 1
FROM XFC_005_RCP_FinResults
WHERE [PROJ_ID] = t.[PROJ_ID]
AND CURRENCY = CASE WHEN RowNum = 1 THEN 'Entity currency' WHEN RowNum = 2 THEN 'Group currency' END
);

Data Integration
  • Kusi's avatar
    Kusi
    New Contributor
    5 months ago

    To clarify on the ask:

    1. I need the table to insert two new lines per [PROJ_ID] column. 

    1.a. The original line will have 'project currency' in the [CURRENCY] column

    1.b. The two new lines should have 'entity currency' and 'group currency' respectively.

     

    Currently, this just inserts one line per [PROJ_ID] and adds nothing to the [CURRENCY] column

  • DanielWillis's avatar
    DanielWillis
    Valued Contributor
    5 months ago

    The above is a bit confusing to me, both the SQL and the requirement. Perhaps only because a bit more understanding of the data is required.

    I wonder if you tried to do this in 3 separate insert statements you either get there a lot easier and can then either make a decision on whether to keep it as 3 inserts or find out your logic is a lot simpler and you can bring them back together.