问:
Check col1 if duplicate then check col2 if distinct
Hey guys I've been struggling to get a simple query done in SnowFlake.
A sample of the data is explained below,

Number
ID

2559
23

2559
33

2559
55

2550
89

2499
14

2499
14

Based on the table above I would like to create a column that checks if (column Number) have duplicated values if yes; then check (column ID) if the values associated with that Number were duplicated or distinct. If values were duplicated then return 0 if values were distinct then return 1.

The desired outcome is as follows,

Number
ID
Check

2559
23
1

2559
33
1

2559
55
1

2550
89
0

2499
14
0

2499
14
0

and the last outcome should be on the Number column level (group by) and would be like the following table,

Number
Check

2559
1

2550
0

2499
0

Any suggestions are appreciated.

Edited tables

The scenario has changed based on new requirements

The original table have two IDs (ID1 and ID2) and those IDs must be linked to two different tables T-ID1 and T-ID2.

Number
ID1
ID2

2559
23
null

2559
33
null

2559
55
null

2550
89
null

2499
14
null

2499
14
null

2498
null
14

2498
null
50

2498
null
null

So the idea is if Number has duplicated IDs (in ID1 or ID2) then return 1, if Number has unique ID (in ID1 or ID2) then return ID1 or ID2. if Number have null values for ID1 and ID2 then ignore the entire row.

This is the query I am using - everything works based on solution provided below, but I am getting nulls along with ID values.

ID1 is linked to Supplier_ID_Ekuep and ID2 is linked to Supplier_ID_Qava.

```<code>select wbd.WAYBILL_ID, sup.WAYBILL_NO,
       sup.Supplier_ID_Qava, supq.Supplier_Qava, supq.Supllier_City_Qava, supq.Supplier_Qava_Add,
       sup.Supplier_ID_Ekuep, supc.Supplier_Ekuep, supc.Supllier_City_Ekuep, supc.Supplier_Ekuep_Add
from WAYBILL_PRODUCTS wbd
left join (select wb.WAYBILL_ID, ow.WAYBILL_NO,
       case when mult.multi_supp_qava = 1 then '1'
            when mult.multi_supp_qava = 0 then op.ID_Supplier_Qava
            else null end as Supplier_ID_Qava,
       case when mult.multi_supp_ekuep = 1 then '1'
            when mult.multi_supp_ekuep = 0 then op.ID_Supplier_Ekuep
            else null end as Supplier_ID_Ekuep
from WAYBILL_PRODUCTS wb
left join (select ID, ORDER_ID, WAYBILL_NO from ORDER_WAYBILLS) ow on wb.WAYBILL_ID = ow.id
left join (select opr.ID_ORDER, opr.ID_PRODUCT, ss.ID_Supplier_Ekuep, ss.ID_Supplier_Qava
          from ORDER_PRODUCTS opr
left join (select s.id, supc.*, supq.*
          from SUPPLIERS s
left join (select ID_SUPPLIER as ID_Supplier_Ekuep
from EKUEP_KSA_EKUEP_STAGING.PS_SUPPLIER where USED_SUPPLIER_ID is null) supc on supc.ID_Supplier_Ekuep = s.ID_SUPPLIER and s.STORE_ID = 1
left join (select ID_SUPPLIER as ID_Supplier_Qava
from QAVASHOP_KSA_QAVAISH_MAINSHOP.PS_SUPPLIER where USED_SUPPLIER_ID is null) supq on supq.ID_Supplier_Qava = s.ID_SUPPLIER and s.STORE_ID = 2) ss on opr.SUPPLIER_ID = ss.ID
group by opr.ID_ORDER, opr.ID_PRODUCT, ss.ID_Supplier_Ekuep, ss.ID_Supplier_Qava) op on op.ID_PRODUCT = wb.PRODUCT_ID and op.ID_ORDER = ow.ORDER_ID
------------------------------------------------------------------------------------------------------------------------
left join (select wb.WAYBILL_ID, ow.WAYBILL_NO,
       (count(distinct op.ID_Supplier_Qava) > 1)::int as multi_supp_qava,
       (count(distinct op.ID_Supplier_Ekuep) > 1)::int as multi_supp_ekuep
from WAYBILL_PRODUCTS wb
left join (select ID, ORDER_ID, WAYBILL_NO from ORDER_WAYBILLS) ow on wb.WAYBILL_ID = ow.id
left join (select opr.ID_ORDER, opr.ID_PRODUCT, ss.ID_Supplier_Ekuep, ss.ID_Supplier_Qava
          from ORDER_PRODUCTS opr
left join (select s.id, supc.ID_Supplier_Ekuep, supq.ID_Supplier_Qava
          from SUPPLIERS s
left join (select ID_SUPPLIER as ID_Supplier_Ekuep
from EKUEP_KSA_EKUEP_STAGING.PS_SUPPLIER where USED_SUPPLIER_ID is null) supc on supc.ID_Supplier_Ekuep = s.ID_SUPPLIER and s.STORE_ID = 1
left join (select ID_SUPPLIER as ID_Supplier_Qava
from QAVASHOP_KSA_QAVAISH_MAINSHOP.PS_SUPPLIER where USED_SUPPLIER_ID is null) supq on supq.ID_Supplier_Qava = s.ID_SUPPLIER and s.STORE_ID = 2
    ) ss on opr.SUPPLIER_ID = ss.ID
group by opr.ID_ORDER, opr.ID_PRODUCT, ss.ID_Supplier_Ekuep, ss.ID_Supplier_Qava) op on op.ID_PRODUCT = wb.PRODUCT_ID and op.ID_ORDER = ow.ORDER_ID
where _FIVETRAN_DELETED = 0
group by wb.WAYBILL_ID, ow.WAYBILL_NO) mult on mult.WAYBILL_NO = ow.WAYBILL_NO
------------------------------------------------------------------------------------------------------------------------
group by wb.WAYBILL_ID, ow.WAYBILL_NO, mult.multi_supp_qava, mult.multi_supp_ekuep, op.ID_Supplier_Qava, op.ID_Supplier_Ekuep) sup on wbd.WAYBILL_ID = sup.WAYBILL_ID

left join
(select ps.ID_SUPPLIER                  as ID_Supplier_Qava,
        ps.name                         as Supplier_Qava,
        listagg(distinct pa.CITY, ', ') as Supllier_City_Qava,
        ps.DATE_ADD                     as Supplier_Qava_Add
from QAVASHOP_KSA_QAVAISH_MAINSHOP.PS_SUPPLIER ps
left join (select * from QAVASHOP_KSA_QAVAISH_MAINSHOP.PS_ADDRESS) pa on pa.ID_SUPPLIER = ps.ID_SUPPLIER and pa._FIVETRAN_DELETED = 0
where ps.USED_SUPPLIER_ID is null and ps._FIVETRAN_DELETED = 0
group by ID_Supplier_Qava, Supplier_Qava, Supplier_Qava_Add) supq on supq.ID_Supplier_Qava = sup.Supplier_ID_Qava

left join
(select ps.ID_SUPPLIER                  as ID_Supplier_Ekuep,
        ps.name                         as Supplier_Ekuep,
        listagg(distinct pa.CITY, ', ') as Supllier_City_Ekuep,
        ps.DATE_ADD                     as Supplier_Ekuep_Add
from EKUEP_KSA_EKUEP_STAGING.PS_SUPPLIER ps
left join (select * from EKUEP_KSA_EKUEP_STAGING.PS_ADDRESS) pa on pa.ID_SUPPLIER = ps.ID_SUPPLIER and pa._FIVETRAN_DELETED = 0
where ps.USED_SUPPLIER_ID is null and ps._FIVETRAN_DELETED = 0
group by ID_Supplier_Ekuep, Supplier_Ekuep, Supplier_Ekuep_Add) supc on supc.ID_Supplier_Ekuep = sup.Supplier_ID_Ekuep
where _FIVETRAN_DELETED = 0
group by wbd.WAYBILL_ID, sup.WAYBILL_NO,
       sup.Supplier_ID_Qava, supq.Supplier_Qava, supq.Supllier_City_Qava, supq.Supplier_Qava_Add,
       sup.Supplier_ID_Ekuep, supc.Supplier_Ekuep, supc.Supllier_City_Ekuep, supc.Supplier_Ekuep_Add
</code>```

The output I am getting for Waybill_No = 43265104 is as follows, I just want to get rid of the null row.

答:
```<code>    left join (
        select
            ID,
            ORDER_ID,
            WAYBILL_NO
        from ORDER_WAYBILLS
    ) ow
        on wb.WAYBILL_ID = ow.id
</code>```

should just be:

```<code>    LEFT JOIN order_waybills AS ow
        ON wb.waybill_id = ow.id
</code>```

Gaint Refactoing Starts Here

So I start refactoring this SQL, the first part was to ignore my prior "this could be simpler" and move that into a CTE ob_cte, so it was "just simple" then I notice the first two sections had large block you noted as op that where the same, so that becomes a CTE op_cte also then that gives us:

```<code>WITH ob_cte AS (
    SELECT
        id,
        order_id,
        waybill_no
    FROM order_waybills
), op_etc AS (
    SELECT
        opr.id_order,
        opr.id_product,
        ss.id_supplier_ekuep,
        ss.id_supplier_qava
    FROM order_products AS opr
    LEFT JOIN (
        SELECT
            s.id,
            supc.id_supplier_ekuep,
            supq.id_supplier_qava
        FROM suppliers AS s
        LEFT JOIN (
            SELECT
                id_supplier AS id_supplier_ekuep
            FROM ekuep_ksa_ekuep_staging.ps_supplier
            WHERE used_supplier_id IS NULL
        ) AS supc
            ON supc.id_supplier_ekuep = s.id_supplier
                AND s.store_id = 1
        LEFT JOIN (
            SELECT
                id_supplier AS id_supplier_qava
            FROM qavashop_ksa_qavaish_mainshop.ps_supplier
            WHERE used_supplier_id IS NULL
        ) AS supq
            ON supq.id_supplier_qava = s.id_supplier
                AND s.store_id = 2
    ) AS ss
        ON opr.SUPPLIER_ID = ss.ID
    GROUP BY opr.id_order, opr.id_product, ss.id_supplier_ekuep, ss.id_supplier_qava
)

SELECT
    wbd.waybill_id,
    sup.waybill_no,
    sup.supplier_id_qava,
    supq.supplier_qava,
    supq.supllier_city_qava,
    supq.supplier_qava_add,
    sup.supplier_id_ekuep,
    supc.supplier_ekuep,
    supc.supllier_city_ekuep,
    supc.supplier_ekuep_add
FROM waybill_products AS wbd
LEFT JOIN (
    SELECT
        wb.waybill_id,
        ow.waybill_no,
        CASE
            WHEN mult.multi_supp_qava = 1 THEN '1'
            WHEN mult.multi_supp_qava = 0 THEN op.ID_Supplier_Qava
            ELSE NULL
        end as supplier_id_qava,
        case
            WHEN mult.multi_supp_ekuep = 1 THEN '1'
            WHEN mult.multi_supp_ekuep = 0 THEN op.ID_Supplier_Ekuep
            ELSE NULL
        END AS supplier_id_ekuep
    FROM waybill_products AS wb
    LEFT JOIN ob_cte AS ow
        ON wb.waybill_id = ow.id
    LEFT JOIN op_etc AS op
        ON op.id_product = wb.product_id
            AND op.id_order = ow.order_id
    ------------------------------------------------------------------------------------------------------------------------
    left join (
        select
            wb.WAYBILL_ID,
            ow.WAYBILL_NO,
            (count(distinct op.ID_Supplier_Qava) > 1)::int as multi_supp_qava,
            (count(distinct op.ID_Supplier_Ekuep) > 1)::int as multi_supp_ekuep
        from WAYBILL_PRODUCTS wb
        LEFT JOIN ob_cte AS ow
            ON wb.waybill_id = ow.id
        left join op_etc AS op
            on op.ID_PRODUCT = wb.PRODUCT_ID
                and op.ID_ORDER = ow.ORDER_ID
        where _FIVETRAN_DELETED = 0
        group by wb.WAYBILL_ID, ow.WAYBILL_NO
    ) AS mult
        on mult.WAYBILL_NO = ow.WAYBILL_NO
    ------------------------------------------------------------------------------------------------------------------------
    group by wb.WAYBILL_ID, ow.WAYBILL_NO, mult.multi_supp_qava, mult.multi_supp_ekuep, op.ID_Supplier_Qava, op.ID_Supplier_Ekuep
) AS sup
    on wbd.WAYBILL_ID = sup.WAYBILL_ID
left join (
    select
        ps.id_supplier                  AS id_supplier_qava,
        ps.name                         AS supplier_qava,
        ps.date_add                     AS supplier_qava_add,
        LISTAGG(DISTINCT pa.CITY, ', ') AS supllier_city_qava
    from QAVASHOP_KSA_QAVAISH_MAINSHOP.PS_SUPPLIER ps
    left join (
        select *
        from QAVASHOP_KSA_QAVAISH_MAINSHOP.PS_ADDRESS
    ) pa
        ON pa.ID_SUPPLIER = ps.ID_SUPPLIER
            AND pa._fivetran_deleted = 0
    WHERE ps.USED_SUPPLIER_ID is null
        AND ps._fivetran_deleted = 0
    GROUP BY 1, 2, 3
) AS supq
    on supq.ID_Supplier_Qava = sup.Supplier_ID_Qava
left join (
    select
        ps.id_supplier                  AS id_supplier_ekuep,
        ps.name                         AS supplier_ekuep,
        ps.date_add                     AS supplier_ekuep_add,
        LISTAGG(DISTINCT pa.CITY, ', ') AS supllier_city_ekuep
    FROM ekuep_ksa_ekuep_staging.ps_supplier AS ps
    LEFT JOIN FROM ekuep_ksa_ekuep_staging.ps_address AS pa
        ON pa.id_supplier = ps.id_supplier
            and pa._fivetran_deleted = 0
    WHERE ps.used_supplier_id IS NULL
        and ps._fivetran_deleted = 0
    GROUP BY 1, 2, 4
) AS supc
    ON supc.id_supplier_ekuep = sup.supplier_id_ekuep
WHERE _fivetran_deleted = 0
GROUP BY wbd.waybill_id, sup.waybill_no,
       sup.supplier_id_qava, supq.supplier_qava,
       supq.supllier_city_qava, supq.supplier_qava_add,
       sup.supplier_id_ekuep, supc.supplier_ekuep,
       supc.supllier_city_ekuep, supc.supplier_ekuep_add;
</code>```

Now op_etc itself, that GROUP BY at the end is really just a DISTINCT so we can swap that in. Also in ss of that op_cte

```<code>        SELECT
            s.id,
            supc.id_supplier_ekuep,
            supq.id_supplier_qava
        FROM suppliers AS s
        LEFT JOIN (
            SELECT
                id_supplier AS id_supplier_ekuep
            FROM ekuep_ksa_ekuep_staging.ps_supplier
            WHERE used_supplier_id IS NULL
        ) AS supc
            ON supc.id_supplier_ekuep = s.id_supplier
                AND s.store_id = 1
        LEFT JOIN (
            SELECT
                id_supplier AS id_supplier_qava
            FROM qavashop_ksa_qavaish_mainshop.ps_supplier
            WHERE used_supplier_id IS NULL
        ) AS supq
            ON supq.id_supplier_qava = s.id_supplier
                AND s.store_id = 2
</code>```

those to left joins, keep only values that are NULL and then LEFT JOIN of two requirements which means we are get a row per suppliers the IS NULL can be moved to the ON with no impact, which means the rename can be moved higher up, and the sub-select dropped, so that section can become:

```<code>SELECT
    s.id,
    supc.id_supplier AS id_supplier_ekuep,
    supq.id_supplier AS id_supplier_qava
FROM suppliers AS s
LEFT JOIN ekuep_ksa_ekuep_staging.ps_supplier AS supc
    ON supc.id_supplier = s.id_supplier
        AND s.store_id = 1
        AND supc.used_supplier_id IS NULL
LEFT JOIN qavashop_ksa_qavaish_mainshop.ps_supplier AS supq
    ON supq.id_supplier = s.id_supplier
        AND s.store_id = 2
        AND supq.used_supplier_id IS NULL
</code>```

thus the op_cte becomes:

```<code>), op_etc AS (
    SELECT DISTINCT
        opr.id_order,
        opr.id_product,
        ss.id_supplier_ekuep,
        ss.id_supplier_qava
    FROM order_products AS opr
    LEFT JOIN (
        SELECT
            s.id,
            supc.id_supplier AS id_supplier_ekuep,
            supq.id_supplier AS id_supplier_qava
        FROM suppliers AS s
        LEFT JOIN ekuep_ksa_ekuep_staging.ps_supplier AS supc
            ON supc.id_supplier = s.id_supplier
                AND s.store_id = 1
                AND supc.used_supplier_id IS NULL
        LEFT JOIN qavashop_ksa_qavaish_mainshop.ps_supplier AS supq
            ON supq.id_supplier = s.id_supplier
                AND s.store_id = 2
                AND supq.used_supplier_id IS NULL
    ) AS ss
        ON opr.SUPPLIER_ID = ss.ID
)
</code>```

which actually means the ss can just be left joined as first order things like:

```<code>), op_etc AS (
    SELECT DISTINCT
        opr.id_order,
        opr.id_product,
        supc.id_supplier AS id_supplier_ekuep,
        supq.id_supplier AS id_supplier_qava
    FROM order_products AS opr
    LEFT JOIN suppliers AS s
        opr.SUPPLIER_ID = s.ID
    LEFT JOIN ekuep_ksa_ekuep_staging.ps_supplier AS supc
        ON supc.id_supplier = s.id_supplier
            AND s.store_id = 1
            AND supc.used_supplier_id IS NULL
    LEFT JOIN qavashop_ksa_qavaish_mainshop.ps_supplier AS supq
        ON supq.id_supplier = s.id_supplier
            AND s.store_id = 2
            AND supq.used_supplier_id IS NULL
)
</code>```

ah thats much more refreshing..

in the sub-select sup` I am worried, that you are grouping by values that are not in you output set, so you will get duplicate rows

in this case block

```<code>CASE
    WHEN mult.multi_supp_qava = 1 THEN '1'
    WHEN mult.multi_supp_qava = 0 THEN op.ID_Supplier_Qava
    ELSE NULL
end as supplier_id_qava,
</code>```

you are group not by the output supplier_id_qava or it's position 3 but mult.multi_supp_qava, op.ID_Supplier_Qava

so if op.ID_Supplier_Qava is not null when mult.multi_supp_qava = 1 and can have different values, the supplier_id_qava will output 1 on both rows, but they will not merge, as op.ID_Supplier_Qava is different. This is the root of my strong dislike for using the input to the SELECT section as the grouping clauses of the aggregation of those values. because through transformation, they can become the same on the output, but remain different on the input, and produce hard to debug deuplicates. And if the flip case that they are alway safe/null/constant in the odd case, this is not clear to the SQL reader. (on this point I have to say a strong thank you for have used the aliases on select etc, as that alway confidance of where those values are coming from).

So again the GROUP BY on sup has no agration of values so this is a hidden DISTINCT, but it not giving distinct values perhaps in all cases.

So after moving some sub-selects into CTE's as I find it makes each select layer show, just what it is doing, with have to leap over smaller details we get SQL like this:

```<code>WITH ob_cte AS (
    SELECT
        id,
        order_id,
        waybill_no
    FROM order_waybills
), op_etc AS (
    SELECT DISTINCT
        opr.id_order,
        opr.id_product,
        supc.id_supplier AS id_supplier_ekuep,
        supq.id_supplier AS id_supplier_qava
    FROM order_products AS opr
    LEFT JOIN suppliers AS s
        opr.SUPPLIER_ID = s.ID
    LEFT JOIN ekuep_ksa_ekuep_staging.ps_supplier AS supc
        ON supc.id_supplier = s.id_supplier
            AND s.store_id = 1
            AND supc.used_supplier_id IS NULL
    LEFT JOIN qavashop_ksa_qavaish_mainshop.ps_supplier AS supq
        ON supq.id_supplier = s.id_supplier
            AND s.store_id = 2
            AND supq.used_supplier_id IS NULL
), mult_cte AS (
    SELECT
        wb.waybill_id,
        ow.waybill_no,
        (COUNT(DISTINCT op.id_supplier_qava) > 1)::int AS multi_supp_qava,
        (COUNT(DISTINCT op.id_supplier_ekuep) > 1)::int AS multi_supp_ekuep
    FROM waybill_products AS wb
    LEFT JOIN ob_cte AS ow
        ON wb.waybill_id = ow.id
    LEFT JOIN op_etc AS op
        ON op.id_product = wb.product_id
            AND op.id_order = ow.order_id
    WHERE _fivetran_deleted = 0
    GROUP BY wb.waybill_id, ow.waybill_no
), supq_cte AS (
    SELECT
        ps.id_supplier                  AS id_supplier_qava,
        ps.name                         AS supplier_qava,
        ps.date_add                     AS supplier_qava_add,
        LISTAGG(DISTINCT pa.city, ', ') AS supllier_city_qava
    FROM qavashop_ksa_qavaish_mainshop.ps_supplier AS ps
    LEFT JOIN qavashop_ksa_qavaish_mainshop.ps_address AS pa
        ON pa.id_supplier = ps.id_supplier
            AND pa._fivetran_deleted = 0
    WHERE ps.used_supplier_id is null
        AND ps._fivetran_deleted = 0
    GROUP BY 1, 2, 3
), supc_cte AS (
    SELECT
        ps.id_supplier                  AS id_supplier_ekuep,
        ps.name                         AS supplier_ekuep,
        ps.date_add                     AS supplier_ekuep_add,
        LISTAGG(DISTINCT pa.city, ', ') AS supllier_city_ekuep
    FROM ekuep_ksa_ekuep_staging.ps_supplier AS ps
    LEFT JOIN FROM ekuep_ksa_ekuep_staging.ps_address AS pa
        ON pa.id_supplier = ps.id_supplier
            AND pa._fivetran_deleted = 0
    WHERE ps.used_supplier_id IS NULL
        AND ps._fivetran_deleted = 0
    GROUP BY 1, 2, 4
), sup_cte AS (
    SELECT DISTINCT
        wb.waybill_id,
        ow.waybill_no,
        CASE
            WHEN mult.multi_supp_qava = 1 THEN '1'
            WHEN mult.multi_supp_qava = 0 THEN op.ID_Supplier_Qava
            ELSE NULL
        end as supplier_id_qava,
        case
            WHEN mult.multi_supp_ekuep = 1 THEN '1'
            WHEN mult.multi_supp_ekuep = 0 THEN op.ID_Supplier_Ekuep
            ELSE NULL
        END AS supplier_id_ekuep
    FROM waybill_products AS wb
    LEFT JOIN ob_cte AS ow
        ON wb.waybill_id = ow.id
    LEFT JOIN op_etc AS op
        ON op.id_product = wb.product_id
            AND op.id_order = ow.order_id
    ------------------------------------------------------------------------------------------------------------------------
    LEFT JOIN mult_cte AS mult
        on mult.WAYBILL_NO = ow.WAYBILL_NO
    ------------------------------------------------------------------------------------------------------------------------
)

SELECT
    wbd.waybill_id,
    sup.waybill_no,
    sup.supplier_id_qava,
    supq.supplier_qava,
    supq.supllier_city_qava,
    supq.supplier_qava_add,
    sup.supplier_id_ekuep,
    supc.supplier_ekuep,
    supc.supllier_city_ekuep,
    supc.supplier_ekuep_add
FROM waybill_products AS wbd
LEFT JOIN sup_cte AS sup
    ON wbd.WAYBILL_ID = sup.WAYBILL_ID
LEFT JOIN supq_cte AS supq
    ON supq.id_supplier_qava = sup.supplier_id_qava
LEFT JOIN supc_cte AS supc
    ON supc.id_supplier_ekuep = sup.supplier_id_ekuep
WHERE _fivetran_deleted = 0
GROUP BY wbd.waybill_id, sup.waybill_no,
       sup.supplier_id_qava, supq.supplier_qava,
       supq.supllier_city_qava, supq.supplier_qava_add,
       sup.supplier_id_ekuep, supc.supplier_ekuep,
       supc.supllier_city_ekuep, supc.supplier_ekuep_add;
</code>```

And at the end we see again you are suffering from duplicate data, that you are trying to clean away with yet another GROUP BY.. so we will switch to a DISTINCT

```<code>SELECT DISTINCT
    wbd.waybill_id,
    sup.waybill_no,
    sup.supplier_id_qava,
    supq.supplier_qava,
    supq.supllier_city_qava,
    supq.supplier_qava_add,
    sup.supplier_id_ekuep,
    supc.supplier_ekuep,
    supc.supllier_city_ekuep,
    supc.supplier_ekuep_add
FROM waybill_products AS wbd
LEFT JOIN sup_cte AS sup
    ON wbd.WAYBILL_ID = sup.WAYBILL_ID
LEFT JOIN supq_cte AS supq
    ON supq.id_supplier_qava = sup.supplier_id_qava
LEFT JOIN supc_cte AS supc
    ON supc.id_supplier_ekuep = sup.supplier_id_ekuep
WHERE _fivetran_deleted = 0
</code>```

Right so what was your question again?

you want to get rid of all the nulls... if this is so "why so many left joins" or returning null from CASE statements..

Right so in the picture to show a waybill_no with values, and another where supplier_id_qava is null, given the later comes from

```<code>LEFT JOIN sup_cte AS sup
    ON wbd.WAYBILL_ID = sup.WAYBILL_ID
</code>```

and everything else connect via sup you should change that from a LEFT JOIN to just a JOIN

Body:
Hey guys I've been struggling to get a simple query done in SnowFlake.
A sample of the data is explained below,

Number
ID

2559
23

2559
33

2559
55

2550
89

2499
14

2499
14

Based on the table above I would like to create a column that checks if (column Number) have duplicated values if yes; then check (column ID) if the values associated with that Number were duplicated or distinct. If values were duplicated then return 0 if values were distinct then return 1.

The desired outcome is as follows,

Number
ID
Check

2559
23
1

2559
33
1

2559
55
1

2550
89
0

2499
14
0

2499
14
0

and the last outcome should be on the Number column level (group by) and would be like the following table,

Number
Check

2559
1

2550
0

2499
0

Any suggestions are appreciated.

Edited tables

The scenario has changed based on new requirements

The original table have two IDs (ID1 and ID2) and those IDs must be linked to two different tables T-ID1 and T-ID2.

Number
ID1
ID2

2559
23
null

2559
33
null

2559
55
null

2550
89
null

2499
14
null

2499
14
null

2498
null
14

2498
null
50

2498
null
null

So the idea is if Number has duplicated IDs (in ID1 or ID2) then return 1, if Number has unique ID (in ID1 or ID2) then return ID1 or ID2. if Number have null values for ID1 and ID2 then ignore the entire row.

This is the query I am using - everything works based on solution provided below, but I am getting nulls along with ID values.

ID1 is linked to Supplier_ID_Ekuep and ID2 is linked to Supplier_ID_Qava.

select wbd.WAYBILL_ID, sup.WAYBILL_NO,
       sup.Supplier_ID_Qava, supq.Supplier_Qava, supq.Supllier_City_Qava, supq.Supplier_Qava_Add,
       sup.Supplier_ID_Ekuep, supc.Supplier_Ekuep, supc.Supllier_City_Ekuep, supc.Supplier_Ekuep_Add
from WAYBILL_PRODUCTS wbd
left join (select wb.WAYBILL_ID, ow.WAYBILL_NO,
       case when mult.multi_supp_qava = 1 then '1'
            when mult.multi_supp_qava = 0 then op.ID_Supplier_Qava
            else null end as Supplier_ID_Qava,
       case when mult.multi_supp_ekuep = 1 then '1'
            when mult.multi_supp_ekuep = 0 then op.ID_Supplier_Ekuep
            else null end as Supplier_ID_Ekuep
from WAYBILL_PRODUCTS wb
left join (select ID, ORDER_ID, WAYBILL_NO from ORDER_WAYBILLS) ow on wb.WAYBILL_ID = ow.id
left join (select opr.ID_ORDER, opr.ID_PRODUCT, ss.ID_Supplier_Ekuep, ss.ID_Supplier_Qava
          from ORDER_PRODUCTS opr
left join (select s.id, supc.*, supq.*
          from SUPPLIERS s
left join (select ID_SUPPLIER as ID_Supplier_Ekuep
from EKUEP_KSA_EKUEP_STAGING.PS_SUPPLIER where USED_SUPPLIER_ID is null) supc on supc.ID_Supplier_Ekuep = s.ID_SUPPLIER and s.STORE_ID = 1
left join (select ID_SUPPLIER as ID_Supplier_Qava
from QAVASHOP_KSA_QAVAISH_MAINSHOP.PS_SUPPLIER where USED_SUPPLIER_ID is null) supq on supq.ID_Supplier_Qava = s.ID_SUPPLIER and s.STORE_ID = 2) ss on opr.SUPPLIER_ID = ss.ID
group by opr.ID_ORDER, opr.ID_PRODUCT, ss.ID_Supplier_Ekuep, ss.ID_Supplier_Qava) op on op.ID_PRODUCT = wb.PRODUCT_ID and op.ID_ORDER = ow.ORDER_ID
------------------------------------------------------------------------------------------------------------------------
left join (select wb.WAYBILL_ID, ow.WAYBILL_NO,
       (count(distinct op.ID_Supplier_Qava) > 1)::int as multi_supp_qava,
       (count(distinct op.ID_Supplier_Ekuep) > 1)::int as multi_supp_ekuep
from WAYBILL_PRODUCTS wb
left join (select ID, ORDER_ID, WAYBILL_NO from ORDER_WAYBILLS) ow on wb.WAYBILL_ID = ow.id
left join (select opr.ID_ORDER, opr.ID_PRODUCT, ss.ID_Supplier_Ekuep, ss.ID_Supplier_Qava
          from ORDER_PRODUCTS opr
left join (select s.id, supc.ID_Supplier_Ekuep, supq.ID_Supplier_Qava
          from SUPPLIERS s
left join (select ID_SUPPLIER as ID_Supplier_Ekuep
from EKUEP_KSA_EKUEP_STAGING.PS_SUPPLIER where USED_SUPPLIER_ID is null) supc on supc.ID_Supplier_Ekuep = s.ID_SUPPLIER and s.STORE_ID = 1
left join (select ID_SUPPLIER as ID_Supplier_Qava
from QAVASHOP_KSA_QAVAISH_MAINSHOP.PS_SUPPLIER where USED_SUPPLIER_ID is null) supq on supq.ID_Supplier_Qava = s.ID_SUPPLIER and s.STORE_ID = 2
    ) ss on opr.SUPPLIER_ID = ss.ID
group by opr.ID_ORDER, opr.ID_PRODUCT, ss.ID_Supplier_Ekuep, ss.ID_Supplier_Qava) op on op.ID_PRODUCT = wb.PRODUCT_ID and op.ID_ORDER = ow.ORDER_ID
where _FIVETRAN_DELETED = 0
group by wb.WAYBILL_ID, ow.WAYBILL_NO) mult on mult.WAYBILL_NO = ow.WAYBILL_NO
------------------------------------------------------------------------------------------------------------------------
group by wb.WAYBILL_ID, ow.WAYBILL_NO, mult.multi_supp_qava, mult.multi_supp_ekuep, op.ID_Supplier_Qava, op.ID_Supplier_Ekuep) sup on wbd.WAYBILL_ID = sup.WAYBILL_ID

left join
(select ps.ID_SUPPLIER                  as ID_Supplier_Qava,
        ps.name                         as Supplier_Qava,
        listagg(distinct pa.CITY, ', ') as Supllier_City_Qava,
        ps.DATE_ADD                     as Supplier_Qava_Add
from QAVASHOP_KSA_QAVAISH_MAINSHOP.PS_SUPPLIER ps
left join (select * from QAVASHOP_KSA_QAVAISH_MAINSHOP.PS_ADDRESS) pa on pa.ID_SUPPLIER = ps.ID_SUPPLIER and pa._FIVETRAN_DELETED = 0
where ps.USED_SUPPLIER_ID is null and ps._FIVETRAN_DELETED = 0
group by ID_Supplier_Qava, Supplier_Qava, Supplier_Qava_Add) supq on supq.ID_Supplier_Qava = sup.Supplier_ID_Qava

left join
(select ps.ID_SUPPLIER                  as ID_Supplier_Ekuep,
        ps.name                         as Supplier_Ekuep,
        listagg(distinct pa.CITY, ', ') as Supllier_City_Ekuep,
        ps.DATE_ADD                     as Supplier_Ekuep_Add
from EKUEP_KSA_EKUEP_STAGING.PS_SUPPLIER ps
left join (select * from EKUEP_KSA_EKUEP_STAGING.PS_ADDRESS) pa on pa.ID_SUPPLIER = ps.ID_SUPPLIER and pa._FIVETRAN_DELETED = 0
where ps.USED_SUPPLIER_ID is null and ps._FIVETRAN_DELETED = 0
group by ID_Supplier_Ekuep, Supplier_Ekuep, Supplier_Ekuep_Add) supc on supc.ID_Supplier_Ekuep = sup.Supplier_ID_Ekuep
where _FIVETRAN_DELETED = 0
group by wbd.WAYBILL_ID, sup.WAYBILL_NO,
       sup.Supplier_ID_Qava, supq.Supplier_Qava, supq.Supllier_City_Qava, supq.Supplier_Qava_Add,
       sup.Supplier_ID_Ekuep, supc.Supplier_Ekuep, supc.Supllier_City_Ekuep, supc.Supplier_Ekuep_Add

The output I am getting for Waybill_No = 43265104 is as follows, I just want to get rid of the null row.

Body:
    left join (
        select
            ID,
            ORDER_ID,
            WAYBILL_NO
        from ORDER_WAYBILLS
    ) ow
        on wb.WAYBILL_ID = ow.id

should just be:

    LEFT JOIN order_waybills AS ow
        ON wb.waybill_id = ow.id

Gaint Refactoing Starts Here

So I start refactoring this SQL, the first part was to ignore my prior "this could be simpler" and move that into a CTE ob_cte, so it was "just simple" then I notice the first two sections had large block you noted as op that where the same, so that becomes a CTE op_cte also then that gives us:

WITH ob_cte AS (
    SELECT
        id,
        order_id,
        waybill_no
    FROM order_waybills
), op_etc AS (
    SELECT
        opr.id_order,
        opr.id_product,
        ss.id_supplier_ekuep,
        ss.id_supplier_qava
    FROM order_products AS opr
    LEFT JOIN (
        SELECT
            s.id,
            supc.id_supplier_ekuep,
            supq.id_supplier_qava
        FROM suppliers AS s
        LEFT JOIN (
            SELECT
                id_supplier AS id_supplier_ekuep
            FROM ekuep_ksa_ekuep_staging.ps_supplier
            WHERE used_supplier_id IS NULL
        ) AS supc
            ON supc.id_supplier_ekuep = s.id_supplier
                AND s.store_id = 1
        LEFT JOIN (
            SELECT
                id_supplier AS id_supplier_qava
            FROM qavashop_ksa_qavaish_mainshop.ps_supplier
            WHERE used_supplier_id IS NULL
        ) AS supq
            ON supq.id_supplier_qava = s.id_supplier
                AND s.store_id = 2
    ) AS ss
        ON opr.SUPPLIER_ID = ss.ID
    GROUP BY opr.id_order, opr.id_product, ss.id_supplier_ekuep, ss.id_supplier_qava
)

SELECT
    wbd.waybill_id,
    sup.waybill_no,
    sup.supplier_id_qava,
    supq.supplier_qava,
    supq.supllier_city_qava,
    supq.supplier_qava_add,
    sup.supplier_id_ekuep,
    supc.supplier_ekuep,
    supc.supllier_city_ekuep,
    supc.supplier_ekuep_add
FROM waybill_products AS wbd
LEFT JOIN (
    SELECT
        wb.waybill_id,
        ow.waybill_no,
        CASE
            WHEN mult.multi_supp_qava = 1 THEN '1'
            WHEN mult.multi_supp_qava = 0 THEN op.ID_Supplier_Qava
            ELSE NULL
        end as supplier_id_qava,
        case
            WHEN mult.multi_supp_ekuep = 1 THEN '1'
            WHEN mult.multi_supp_ekuep = 0 THEN op.ID_Supplier_Ekuep
            ELSE NULL
        END AS supplier_id_ekuep
    FROM waybill_products AS wb
    LEFT JOIN ob_cte AS ow
        ON wb.waybill_id = ow.id
    LEFT JOIN op_etc AS op
        ON op.id_product = wb.product_id
            AND op.id_order = ow.order_id
    ------------------------------------------------------------------------------------------------------------------------
    left join (
        select
            wb.WAYBILL_ID,
            ow.WAYBILL_NO,
            (count(distinct op.ID_Supplier_Qava) > 1)::int as multi_supp_qava,
            (count(distinct op.ID_Supplier_Ekuep) > 1)::int as multi_supp_ekuep
        from WAYBILL_PRODUCTS wb
        LEFT JOIN ob_cte AS ow
            ON wb.waybill_id = ow.id
        left join op_etc AS op
            on op.ID_PRODUCT = wb.PRODUCT_ID
                and op.ID_ORDER = ow.ORDER_ID
        where _FIVETRAN_DELETED = 0
        group by wb.WAYBILL_ID, ow.WAYBILL_NO
    ) AS mult
        on mult.WAYBILL_NO = ow.WAYBILL_NO
    ------------------------------------------------------------------------------------------------------------------------
    group by wb.WAYBILL_ID, ow.WAYBILL_NO, mult.multi_supp_qava, mult.multi_supp_ekuep, op.ID_Supplier_Qava, op.ID_Supplier_Ekuep
) AS sup
    on wbd.WAYBILL_ID = sup.WAYBILL_ID
left join (
    select
        ps.id_supplier                  AS id_supplier_qava,
        ps.name                         AS supplier_qava,
        ps.date_add                     AS supplier_qava_add,
        LISTAGG(DISTINCT pa.CITY, ', ') AS supllier_city_qava
    from QAVASHOP_KSA_QAVAISH_MAINSHOP.PS_SUPPLIER ps
    left join (
        select *
        from QAVASHOP_KSA_QAVAISH_MAINSHOP.PS_ADDRESS
    ) pa
        ON pa.ID_SUPPLIER = ps.ID_SUPPLIER
            AND pa._fivetran_deleted = 0
    WHERE ps.USED_SUPPLIER_ID is null
        AND ps._fivetran_deleted = 0
    GROUP BY 1, 2, 3
) AS supq
    on supq.ID_Supplier_Qava = sup.Supplier_ID_Qava
left join (
    select
        ps.id_supplier                  AS id_supplier_ekuep,
        ps.name                         AS supplier_ekuep,
        ps.date_add                     AS supplier_ekuep_add,
        LISTAGG(DISTINCT pa.CITY, ', ') AS supllier_city_ekuep
    FROM ekuep_ksa_ekuep_staging.ps_supplier AS ps
    LEFT JOIN FROM ekuep_ksa_ekuep_staging.ps_address AS pa
        ON pa.id_supplier = ps.id_supplier
            and pa._fivetran_deleted = 0
    WHERE ps.used_supplier_id IS NULL
        and ps._fivetran_deleted = 0
    GROUP BY 1, 2, 4
) AS supc
    ON supc.id_supplier_ekuep = sup.supplier_id_ekuep
WHERE _fivetran_deleted = 0
GROUP BY wbd.waybill_id, sup.waybill_no,
       sup.supplier_id_qava, supq.supplier_qava,
       supq.supllier_city_qava, supq.supplier_qava_add,
       sup.supplier_id_ekuep, supc.supplier_ekuep,
       supc.supllier_city_ekuep, supc.supplier_ekuep_add;

Now op_etc itself, that GROUP BY at the end is really just a DISTINCT so we can swap that in. Also in ss of that op_cte

        SELECT
            s.id,
            supc.id_supplier_ekuep,
            supq.id_supplier_qava
        FROM suppliers AS s
        LEFT JOIN (
            SELECT
                id_supplier AS id_supplier_ekuep
            FROM ekuep_ksa_ekuep_staging.ps_supplier
            WHERE used_supplier_id IS NULL
        ) AS supc
            ON supc.id_supplier_ekuep = s.id_supplier
                AND s.store_id = 1
        LEFT JOIN (
            SELECT
                id_supplier AS id_supplier_qava
            FROM qavashop_ksa_qavaish_mainshop.ps_supplier
            WHERE used_supplier_id IS NULL
        ) AS supq
            ON supq.id_supplier_qava = s.id_supplier
                AND s.store_id = 2

those to left joins, keep only values that are NULL and then LEFT JOIN of two requirements which means we are get a row per suppliers the IS NULL can be moved to the ON with no impact, which means the rename can be moved higher up, and the sub-select dropped, so that section can become:

SELECT
    s.id,
    supc.id_supplier AS id_supplier_ekuep,
    supq.id_supplier AS id_supplier_qava
FROM suppliers AS s
LEFT JOIN ekuep_ksa_ekuep_staging.ps_supplier AS supc
    ON supc.id_supplier = s.id_supplier
        AND s.store_id = 1
        AND supc.used_supplier_id IS NULL
LEFT JOIN qavashop_ksa_qavaish_mainshop.ps_supplier AS supq
    ON supq.id_supplier = s.id_supplier
        AND s.store_id = 2
        AND supq.used_supplier_id IS NULL

thus the op_cte becomes:

), op_etc AS (
    SELECT DISTINCT
        opr.id_order,
        opr.id_product,
        ss.id_supplier_ekuep,
        ss.id_supplier_qava
    FROM order_products AS opr
    LEFT JOIN (
        SELECT
            s.id,
            supc.id_supplier AS id_supplier_ekuep,
            supq.id_supplier AS id_supplier_qava
        FROM suppliers AS s
        LEFT JOIN ekuep_ksa_ekuep_staging.ps_supplier AS supc
            ON supc.id_supplier = s.id_supplier
                AND s.store_id = 1
                AND supc.used_supplier_id IS NULL
        LEFT JOIN qavashop_ksa_qavaish_mainshop.ps_supplier AS supq
            ON supq.id_supplier = s.id_supplier
                AND s.store_id = 2
                AND supq.used_supplier_id IS NULL
    ) AS ss
        ON opr.SUPPLIER_ID = ss.ID
)

which actually means the ss can just be left joined as first order things like:

), op_etc AS (
    SELECT DISTINCT
        opr.id_order,
        opr.id_product,
        supc.id_supplier AS id_supplier_ekuep,
        supq.id_supplier AS id_supplier_qava
    FROM order_products AS opr
    LEFT JOIN suppliers AS s
        opr.SUPPLIER_ID = s.ID
    LEFT JOIN ekuep_ksa_ekuep_staging.ps_supplier AS supc
        ON supc.id_supplier = s.id_supplier
            AND s.store_id = 1
            AND supc.used_supplier_id IS NULL
    LEFT JOIN qavashop_ksa_qavaish_mainshop.ps_supplier AS supq
        ON supq.id_supplier = s.id_supplier
            AND s.store_id = 2
            AND supq.used_supplier_id IS NULL
)

ah thats much more refreshing..

in the sub-select sup` I am worried, that you are grouping by values that are not in you output set, so you will get duplicate rows

in this case block

CASE
    WHEN mult.multi_supp_qava = 1 THEN '1'
    WHEN mult.multi_supp_qava = 0 THEN op.ID_Supplier_Qava
    ELSE NULL
end as supplier_id_qava,

you are group not by the output supplier_id_qava or it's position 3 but mult.multi_supp_qava, op.ID_Supplier_Qava

so if op.ID_Supplier_Qava is not null when mult.multi_supp_qava = 1 and can have different values, the supplier_id_qava will output 1 on both rows, but they will not merge, as op.ID_Supplier_Qava is different. This is the root of my strong dislike for using the input to the SELECT section as the grouping clauses of the aggregation of those values. because through transformation, they can become the same on the output, but remain different on the input, and produce hard to debug deuplicates. And if the flip case that they are alway safe/null/constant in the odd case, this is not clear to the SQL reader. (on this point I have to say a strong thank you for have used the aliases on select etc, as that alway confidance of where those values are coming from).

So again the GROUP BY on sup has no agration of values so this is a hidden DISTINCT, but it not giving distinct values perhaps in all cases.

So after moving some sub-selects into CTE's as I find it makes each select layer show, just what it is doing, with have to leap over smaller details we get SQL like this:

WITH ob_cte AS (
    SELECT
        id,
        order_id,
        waybill_no
    FROM order_waybills
), op_etc AS (
    SELECT DISTINCT
        opr.id_order,
        opr.id_product,
        supc.id_supplier AS id_supplier_ekuep,
        supq.id_supplier AS id_supplier_qava
    FROM order_products AS opr
    LEFT JOIN suppliers AS s
        opr.SUPPLIER_ID = s.ID
    LEFT JOIN ekuep_ksa_ekuep_staging.ps_supplier AS supc
        ON supc.id_supplier = s.id_supplier
            AND s.store_id = 1
            AND supc.used_supplier_id IS NULL
    LEFT JOIN qavashop_ksa_qavaish_mainshop.ps_supplier AS supq
        ON supq.id_supplier = s.id_supplier
            AND s.store_id = 2
            AND supq.used_supplier_id IS NULL
), mult_cte AS (
    SELECT
        wb.waybill_id,
        ow.waybill_no,
        (COUNT(DISTINCT op.id_supplier_qava) > 1)::int AS multi_supp_qava,
        (COUNT(DISTINCT op.id_supplier_ekuep) > 1)::int AS multi_supp_ekuep
    FROM waybill_products AS wb
    LEFT JOIN ob_cte AS ow
        ON wb.waybill_id = ow.id
    LEFT JOIN op_etc AS op
        ON op.id_product = wb.product_id
            AND op.id_order = ow.order_id
    WHERE _fivetran_deleted = 0
    GROUP BY wb.waybill_id, ow.waybill_no
), supq_cte AS (
    SELECT
        ps.id_supplier                  AS id_supplier_qava,
        ps.name                         AS supplier_qava,
        ps.date_add                     AS supplier_qava_add,
        LISTAGG(DISTINCT pa.city, ', ') AS supllier_city_qava
    FROM qavashop_ksa_qavaish_mainshop.ps_supplier AS ps
    LEFT JOIN qavashop_ksa_qavaish_mainshop.ps_address AS pa
        ON pa.id_supplier = ps.id_supplier
            AND pa._fivetran_deleted = 0
    WHERE ps.used_supplier_id is null
        AND ps._fivetran_deleted = 0
    GROUP BY 1, 2, 3
), supc_cte AS (
    SELECT
        ps.id_supplier                  AS id_supplier_ekuep,
        ps.name                         AS supplier_ekuep,
        ps.date_add                     AS supplier_ekuep_add,
        LISTAGG(DISTINCT pa.city, ', ') AS supllier_city_ekuep
    FROM ekuep_ksa_ekuep_staging.ps_supplier AS ps
    LEFT JOIN FROM ekuep_ksa_ekuep_staging.ps_address AS pa
        ON pa.id_supplier = ps.id_supplier
            AND pa._fivetran_deleted = 0
    WHERE ps.used_supplier_id IS NULL
        AND ps._fivetran_deleted = 0
    GROUP BY 1, 2, 4
), sup_cte AS (
    SELECT DISTINCT
        wb.waybill_id,
        ow.waybill_no,
        CASE
            WHEN mult.multi_supp_qava = 1 THEN '1'
            WHEN mult.multi_supp_qava = 0 THEN op.ID_Supplier_Qava
            ELSE NULL
        end as supplier_id_qava,
        case
            WHEN mult.multi_supp_ekuep = 1 THEN '1'
            WHEN mult.multi_supp_ekuep = 0 THEN op.ID_Supplier_Ekuep
            ELSE NULL
        END AS supplier_id_ekuep
    FROM waybill_products AS wb
    LEFT JOIN ob_cte AS ow
        ON wb.waybill_id = ow.id
    LEFT JOIN op_etc AS op
        ON op.id_product = wb.product_id
            AND op.id_order = ow.order_id
    ------------------------------------------------------------------------------------------------------------------------
    LEFT JOIN mult_cte AS mult
        on mult.WAYBILL_NO = ow.WAYBILL_NO
    ------------------------------------------------------------------------------------------------------------------------
)

SELECT
    wbd.waybill_id,
    sup.waybill_no,
    sup.supplier_id_qava,
    supq.supplier_qava,
    supq.supllier_city_qava,
    supq.supplier_qava_add,
    sup.supplier_id_ekuep,
    supc.supplier_ekuep,
    supc.supllier_city_ekuep,
    supc.supplier_ekuep_add
FROM waybill_products AS wbd
LEFT JOIN sup_cte AS sup
    ON wbd.WAYBILL_ID = sup.WAYBILL_ID
LEFT JOIN supq_cte AS supq
    ON supq.id_supplier_qava = sup.supplier_id_qava
LEFT JOIN supc_cte AS supc
    ON supc.id_supplier_ekuep = sup.supplier_id_ekuep
WHERE _fivetran_deleted = 0
GROUP BY wbd.waybill_id, sup.waybill_no,
       sup.supplier_id_qava, supq.supplier_qava,
       supq.supllier_city_qava, supq.supplier_qava_add,
       sup.supplier_id_ekuep, supc.supplier_ekuep,
       supc.supllier_city_ekuep, supc.supplier_ekuep_add;

And at the end we see again you are suffering from duplicate data, that you are trying to clean away with yet another GROUP BY.. so we will switch to a DISTINCT

SELECT DISTINCT
    wbd.waybill_id,
    sup.waybill_no,
    sup.supplier_id_qava,
    supq.supplier_qava,
    supq.supllier_city_qava,
    supq.supplier_qava_add,
    sup.supplier_id_ekuep,
    supc.supplier_ekuep,
    supc.supllier_city_ekuep,
    supc.supplier_ekuep_add
FROM waybill_products AS wbd
LEFT JOIN sup_cte AS sup
    ON wbd.WAYBILL_ID = sup.WAYBILL_ID
LEFT JOIN supq_cte AS supq
    ON supq.id_supplier_qava = sup.supplier_id_qava
LEFT JOIN supc_cte AS supc
    ON supc.id_supplier_ekuep = sup.supplier_id_ekuep
WHERE _fivetran_deleted = 0

Right so what was your question again?

you want to get rid of all the nulls... if this is so "why so many left joins" or returning null from CASE statements..

Right so in the picture to show a waybill_no with values, and another where supplier_id_qava is null, given the later comes from

LEFT JOIN sup_cte AS sup
    ON wbd.WAYBILL_ID = sup.WAYBILL_ID

and everything else connect via sup you should change that from a LEFT JOIN to just a JOIN


来源: stackexchange

问:
Image rotation in imagemagick thumbnail
I create a thumbnail of an image create by user's phone with php on sql server, but the thumb result rotate by 90°.

this is the code:

```<code>function generateThumbnail($imgk, $width, $height, $quality)
{
    if (is_file($imgk)) {
        $imagick = new Imagick(realpath($imgk));
        $imagick->setImageFormat('jpeg');
        $imagick->setImageCompression(Imagick::COMPRESSION_JPEG);
        $imagick->setImageCompressionQuality($quality);
        $imagick->thumbnailImage($width, $height, false, false);
        $filename_no_ext = reset(explode('.', $imgk));
        if (file_put_contents($filename_no_ext . '_thumb' . '.jpg', $imagick) === false) {
            throw new Exception("Could not put contents.");
        }
        return true;
    }
    else {
        throw new Exception("No valid image provided with {$imgk}.");
    }
}
</code>```

the result are like this (in most of image):

Maybe the exif data is the problem but I don't have idea for solution.

答:
```<code>    $orientation = $imagick->getImageOrientation();
    switch ($orientation) {
        case 8:
            $imagick->rotateimage("#000", -90);
            break;
        case 3:
            $imagick->rotateimage("#000", 180);
            break;
        case 6:
            $imagick->rotateimage("#000", 90);
            break;
    }
</code>```

The files save the information about device rotation in EXIF. This can be obtained from getImageOrientation function. This code help to rotate images according to device rotation.

ContentLicense: CC BY-SA 4.0

Title: Image rotation in imagemagick thumbnail

Body:
I create a thumbnail of an image create by user's phone with php on sql server, but the thumb result rotate by 90°.

this is the code:

function generateThumbnail($imgk, $width, $height, $quality)
{
    if (is_file($imgk)) {
        $imagick = new Imagick(realpath($imgk));
        $imagick->setImageFormat('jpeg');
        $imagick->setImageCompression(Imagick::COMPRESSION_JPEG);
        $imagick->setImageCompressionQuality($quality);
        $imagick->thumbnailImage($width, $height, false, false);
        $filename_no_ext = reset(explode('.', $imgk));
        if (file_put_contents($filename_no_ext . '_thumb' . '.jpg', $imagick) === false) {
            throw new Exception("Could not put contents.");
        }
        return true;
    }
    else {
        throw new Exception("No valid image provided with {$imgk}.");
    }
}

the result are like this (in most of image):

Maybe the exif data is the problem but I don't have idea for solution.

Body:
    $orientation = $imagick->getImageOrientation();
    switch ($orientation) {
        case 8:
            $imagick->rotateimage("#000", -90);
            break;
        case 3:
            $imagick->rotateimage("#000", 180);
            break;
        case 6:
            $imagick->rotateimage("#000", 90);
            break;
    }

The files save the information about device rotation in EXIF. This can be obtained from getImageOrientation function. This code help to rotate images according to device rotation.


问:
vue.js not displaying date in input
I'm using vue.js v2. From my api I get an object with some date fields in ISO format but vue.js is not correctly displaying those value in an input tag.

```<code><input type="date" class="form-control" v-model="lastPortalInvoiceDate">
</code>```

Value found via the vue debug extension in Edge

```<code>"2022-02-06T23:00:00Z"
</code>```

Value returned in JSON from api

```<code>{
    "data": {
        "portalInvoiceNumberPrefix": "I-{{BY}}-",
        "portalInvoiceFilePrefix": "ABC",
        "lastPortalInvoiceNumber": 1,
        "lastPortalInvoiceDate": "2022-02-06T23:00:00Z",
        "nextBookYearSwitchDate": "2022-02-10T23:00:00Z",
        "id": 1,
        "value": 1
    },
    "tenantId": 1,
    "responseType": "ok",
    "responseMessages": [
        "Successfully read PortalInvoiceConfig"
    ]
}
</code>```

答:
Select a date with datepicker and see the changes in your data. You will see the output in format 'yyyy-mm-dd'.
You must format your date. For this, you can split your time string to parse date only.

```<code>yourDateStr = yourDateStr.split('T')[0]; //if your date is "2022-02-06T23:00:00Z" format
</code>```

Parsing with new Date(yourDateStr) will cause you trouble setting the right time zone. So, if you want a simple solution you could do just parse the date.

Title: vue.js not displaying date in input

Body:
I'm using vue.js v2. From my api I get an object with some date fields in ISO format but vue.js is not correctly displaying those value in an input tag.

<input type="date" class="form-control" v-model="lastPortalInvoiceDate">

Value found via the vue debug extension in Edge

"2022-02-06T23:00:00Z"

Value returned in JSON from api

{
    "data": {
        "portalInvoiceNumberPrefix": "I-{{BY}}-",
        "portalInvoiceFilePrefix": "ABC",
        "lastPortalInvoiceNumber": 1,
        "lastPortalInvoiceDate": "2022-02-06T23:00:00Z",
        "nextBookYearSwitchDate": "2022-02-10T23:00:00Z",
        "id": 1,
        "value": 1
    },
    "tenantId": 1,
    "responseType": "ok",
    "responseMessages": [
        "Successfully read PortalInvoiceConfig"
    ]
}

Body:
Select a date with datepicker and see the changes in your data. You will see the output in format 'yyyy-mm-dd'.
You must format your date. For this, you can split your time string to parse date only.

yourDateStr = yourDateStr.split('T')[0]; //if your date is "2022-02-06T23:00:00Z" format

Parsing with new Date(yourDateStr) will cause you trouble setting the right time zone. So, if you want a simple solution you could do just parse the date.


问:
Flutter & Shared Preferences: How do i save and read back a List<Object> using setStringList? Also, how do i remove a specific object from the List?
```<code>class Restaurant {
  Restaurant({
    required this.name,
    required this.description,
    required this.address,
    required this.imageUrl,
  });

  Restaurant.fromJson(Map<String, Object?> json)
      : this(
          name: json['name']! as String,
          description: json['description']! as String,
          address: json['address']! as String,
          imageUrl: json['imageUrl'] as String,
        );

  final String name;
  final String description;
  final String address;
  final String imageUrl;

  Map<String, Object?> toJson() {
    return {
      'name': name,
      'description': description,
      'address': address,
      'imageUrl': imageUrl,
    };
  }
}
</code>```

I get the list of restaurants from Firebase and when the user clicks Favorite Icon, I want to save that restaurant locally and retrieve the list of favorite restaurants and show it on another page. I know I can do it on Firebase directly but for now, I want it saved locally.

I am looking forward to hearing from all of you. Thank you.

答:
Like this</p>
```<code>List<Restaurant> restaturants = [Restuarant(...),Restuarant(...)];
List<String> encodedRestaturants = restaturants.map((res)=>json.encode(res.toJson())).toList();

//to write
prefs.setStringList("restaturants",encodedRestaturants);

//to read
List<String> decodedRestaturantsString = prefs.getStringList("restaturants");
List<Restaurant> decodedRestaturants = decodedRestaturantsString.map((res)=>Restaturant.fromJson(json.decode(res))).toList();

</code>```

Body:
class Restaurant {
  Restaurant({
    required this.name,
    required this.description,
    required this.address,
    required this.imageUrl,
  });

  Restaurant.fromJson(Map<String, Object?> json)
      : this(
          name: json['name']! as String,
          description: json['description']! as String,
          address: json['address']! as String,
          imageUrl: json['imageUrl'] as String,
        );

  final String name;
  final String description;
  final String address;
  final String imageUrl;

  Map<String, Object?> toJson() {
    return {
      'name': name,
      'description': description,
      'address': address,
      'imageUrl': imageUrl,
    };
  }
}

I get the list of restaurants from Firebase and when the user clicks Favorite Icon, I want to save that restaurant locally and retrieve the list of favorite restaurants and show it on another page. I know I can do it on Firebase directly but for now, I want it saved locally.

I am looking forward to hearing from all of you. Thank you.

Body:
Like this

List<Restaurant> restaturants = [Restuarant(...),Restuarant(...)];
List<String> encodedRestaturants = restaturants.map((res)=>json.encode(res.toJson())).toList();

//to write
prefs.setStringList("restaturants",encodedRestaturants);

//to read
List<String> decodedRestaturantsString = prefs.getStringList("restaturants");
List<Restaurant> decodedRestaturants = decodedRestaturantsString.map((res)=>Restaturant.fromJson(json.decode(res))).toList();


问:
Using SQL Compact 4 in Visual C# 2010 express
It is posible to use SQL Server Compact 4 inside Visual C# 2010 Express? I tried reinstall it many times (with Web Platform Installer and without it) but I still cannot see option for SQL Compct 4 in Data Source selection window.

But what is strange, I can see it in Visual Web Developer 2010 Express.

Is there any way how I can access to version 4 from Visual C# 2010 Express?

答: Version 4.0 only shows for Web Projects, not for desktop projects - as stated here: http://blogs.msdn.com/b/sqlservercompact/archive/2011/03/15/sql-server-compact-4-0-tooling-support-in-visual-studio-2010-sp1-and-visual-web-developer-express-2010-sp1.aspx</p>

ContentLicense: CC BY-SA 3.0


问:
How to compare ends of strings in C?
I want to make sure my string ends with ".foo".  I am using C, a language I am not totally familiar with.  The best way I have found to do it is below.  Any C gurus want to make sure I'm doing this elegantly and wisely?</p>

```<code>int EndsWithFoo(char *str)
{
    if(strlen(str) >= strlen(".foo"))
    {
        if(!strcmp(str + strlen(str) - strlen(".foo"), ".foo"))
        {
            return 1;
        }
    }
    return 0;
}
</code>```

答:
Or...</p>
```<code>#include <stdbool.h>
#include <stdio.h>
#include <string.h>

bool strendscmp(const char* haystack, const char* needle) {
    size_t len_str = strlen(haystack);
    size_t len_ending = strlen(needle);
    return len_str >= len_ending && strcmp(&haystack[(len_str - len_ending)], needle) == 0;
}

//SOME TESTS
int main(int argc, char** argv) {
    printf("%s\n", strendscmp("abc", "bc") ? "true" : "false"); //true
    printf("%s\n", strendscmp("abc", "d") ? "true" : "false"); //false
    printf("%s\n", strendscmp("abc", "") ? "true" : "false"); //true
    printf("%s\n", strendscmp("sumo", "omo") ? "true" : "false"); //false
    printf("%s\n", strendscmp("babbbba", "bbaabaab") ? "true" : "false"); //false
    printf("%s\n", strendscmp("dadaab", "bdadaab") ? "true" : "false"); //false
}
</code>```

OwnerDisplayName: JoeF

ContentLicense: CC BY-SA 2.5

Title: How to compare ends of strings in C?

Body:
I want to make sure my string ends with ".foo".  I am using C, a language I am not totally familiar with.  The best way I have found to do it is below.  Any C gurus want to make sure I'm doing this elegantly and wisely?

int EndsWithFoo(char *str)
{
    if(strlen(str) >= strlen(".foo"))
    {
        if(!strcmp(str + strlen(str) - strlen(".foo"), ".foo"))
        {
            return 1;
        }
    }
    return 0;
}

Body:
Or...

#include <stdbool.h>
#include <stdio.h>
#include <string.h>

bool strendscmp(const char* haystack, const char* needle) {
    size_t len_str = strlen(haystack);
    size_t len_ending = strlen(needle);
    return len_str >= len_ending && strcmp(&haystack[(len_str - len_ending)], needle) == 0;
}

//SOME TESTS
int main(int argc, char** argv) {
    printf("%s\n", strendscmp("abc", "bc") ? "true" : "false"); //true
    printf("%s\n", strendscmp("abc", "d") ? "true" : "false"); //false
    printf("%s\n", strendscmp("abc", "") ? "true" : "false"); //true
    printf("%s\n", strendscmp("sumo", "omo") ? "true" : "false"); //false
    printf("%s\n", strendscmp("babbbba", "bbaabaab") ? "true" : "false"); //false
    printf("%s\n", strendscmp("dadaab", "bdadaab") ? "true" : "false"); //false
}


问:
How to autoselect option depending on input's value
I need select's option to be actually selected on document ready depending on an input's value.

Example:

```<code><select id="mySelect">
<option value="1">myVal1</option>
<option value="2">myVal2</option>
</select>

<input type="text" id="myId" value="2">
</code>```

For example, we've got a predefined value '2' of the input on page load. I need select-box to be auto-selected with it's option having the same value as input 'myId'. I mean 'mayVal1' must be selected.

When input has no value select must behave as default.

Thanks!

Body:
I need select's option to be actually selected on document ready depending on an input's value.

Example:

<select id="mySelect">
<option value="1">myVal1</option>
<option value="2">myVal2</option>
</select>

<input type="text" id="myId" value="2">

For example, we've got a predefined value '2' of the input on page load. I need select-box to be auto-selected with it's option having the same value as input 'myId'. I mean 'mayVal1' must be selected.

When input has no value select must behave as default.

Thanks!


问:
During state change in React, is it possible to append values to URL
I was wondering, if, on state change, I can add something like this to URL

www.abc.com/?state=on

without page reloading.

答:
You could use the useNavigate hook to navigate to any path you'd want to (This is available from react-router-v6 onwards, if you are using an older version, consider using useHistory). This doesn't cause any page reloads, further using useParams you could also extract the param values to make necessary computations in the target component.

```<code>import {useNavigate} from 'react-router-dom'
//OR
import {useHistory} from 'react-router-dom' //Prior to react-router-v6

export default function Component(props){
  const navigate = useNavigate()
  //OR
  const history = useHistory()

  useEffect(() => {
    history.push({pathname:'/',
     search:'?state=on'
    })
    //OR
    navigate({
     pathname:'/',
     search:'?state=on'},{replace:false}) //Set replace=true if you'd want to disable back navigation, false otherwise.
  }, [state]);

  //REST OF THE COMPONENT LOGIC
  .
  .
  .
}
</code>```

I suggest you check out the docs of react router for more information.

Body:
You could use the useNavigate hook to navigate to any path you'd want to (This is available from react-router-v6 onwards, if you are using an older version, consider using useHistory). This doesn't cause any page reloads, further using useParams you could also extract the param values to make necessary computations in the target component.

import {useNavigate} from 'react-router-dom'
//OR
import {useHistory} from 'react-router-dom' //Prior to react-router-v6

export default function Component(props){
  const navigate = useNavigate()
  //OR
  const history = useHistory()

  useEffect(() => {
    history.push({pathname:'/',
     search:'?state=on'
    })
    //OR
    navigate({
     pathname:'/',
     search:'?state=on'},{replace:false}) //Set replace=true if you'd want to disable back navigation, false otherwise.
  }, [state]);

  //REST OF THE COMPONENT LOGIC
  .
  .
  .
}

I suggest you check out the docs of react router for more information.


问:
Kibana Alerts aknowlidge-store-delete
Could you please help me with alerts in Kibana coming from Wazuh as FIM?
I am successfully getting alerts from wazuh agents and showing it in Agent events.

But I am able only to check the alert. There is no button to aknowledge or delete seen alert.
As I am new in wazuh monitoring, could you please let me know, how can I store or set aletrs as completed?

Thank you.

答: The problem is that the alerts are indexed and you can't remove them(you can remove the index). The alerts' purpose is not to be deleted, the alerts allow you to have an "activity history". I think it is good to have all the alerts during the time, even if you have solved or checked that alert.</p>

Title: Kibana Alerts aknowlidge-store-delete


问:
How can we play video in Alert Dialogue Flutter
To play video within the alert dialog I am using the below code. But Video is not playing. Is it possible to play video within the Alert Dialog? Does anyone has a solution to this please share it with me.

Future<void> _initializeVideoPlayerFuture;

  VideoPlayerController _controller;

_controller = VideoPlayerController.network(
                                  video_base_url+videoUrl,
                                );

                                // Initialize the controller and store the Future for later use.
                                _initializeVideoPlayerFuture = _controller.initialize();

                                // Use the controller to loop the video.
                                _controller.setLooping(true);

                                // If the video is playing, pause it.
                                if (_controller.value.isPlaying) {
                                  _controller.pause();
                                } else {
                                  // If the video is paused, play it.
                                  _controller.play();
                                }

                                ContainerResponsive(
                                alignment: Alignment.center,
                                width: MediaQuery.of(context).size.width-50,
                                height: 150,
                                child: FutureBuilder(
                                  future: _initializeVideoPlayerFuture,
                                  builder: (context, snapshot) {
                                    if (snapshot.connectionState == ConnectionState.done) {
                                      // If the VideoPlayerController has finished initialization, use
                                      // the data it provides to limit the aspect ratio of the video.
                                      return AspectRatio(
                                        aspectRatio: _controller.value.aspectRatio,
                                        // Use the VideoPlayer widget to display the video.
                                        child: VideoPlayer(_controller),
                                      );
                                    } else {
                                      // If the VideoPlayerController is still initializing, show a
                                      // loading spinner.
                                      return const Center(
                                        child: CircularProgressIndicator(),
                                      );
                                    }
                                  },
                                )

                              ),```

答:
Make a new Stateful widget class like this for video player , initialized video player controller and video player widget here

import 'package:approved/src/constants/colors.dart';
import 'package:flutter/material.dart';
import 'package:flutter_svg/svg.dart';
import 'package:video_player/video_player.dart';

class VideoWidget extends StatefulWidget {
  final String? url;
  final bool? play;

  const VideoWidget({@required this.url, @required this.play});
  @override
  _VideoWidgetState createState() => _VideoWidgetState();
}

class _VideoWidgetState extends State<VideoWidget> {
  late VideoPlayerController _controller;
  Future<void>? _initializeVideoPlayerFuture;

  @override
  void initState() {
    super.initState();
    _controller = VideoPlayerController.network(widget.url!);
    _initializeVideoPlayerFuture = _controller.initialize().then((_) {
      // Ensure the first frame is shown after the video is initialized, even before the play button has been pressed.
      setState(() {});
    });

    if (widget.play!) {
      _controller.play();
      _controller.setLooping(true);
    }
  }

  @override
  void didUpdateWidget(VideoWidget oldWidget) {
    if (oldWidget.play != widget.play) {
      if (widget.play!) {
        _controller.play();
        _controller.setLooping(true);
      } else {
        _controller.pause();
      }
    }
    super.didUpdateWidget(oldWidget);
  }

  @override
  void dispose() {
    _controller.dispose();
    super.dispose();
  }

  @override
  Widget build(BuildContext context) {
    return Stack(children: [
      Container(
        color: Colors.white,
        child: FutureBuilder(
          future: _initializeVideoPlayerFuture,
          builder: (context, snapshot) {
            if (snapshot.connectionState == ConnectionState.done) {
              return VideoPlayer(_controller);
            } else {
              return const Center(
                child: CircularProgressIndicator(),
              );
            }
          },
        ),
      ),

    ]);
  }
}```
</div>
</div>
</p>
<p>Now you can use this <strong>widget</strong> anywhere you have to just pass the video url and playing parameter true/false.</p>
<p>Like I am using in <strong>showDialog</strong> method</p>
<p><div class="snippet" data-lang="js" data-hide="false" data-console="true" data-babel="false">
<div class="snippet-code">
<pre class="snippet-code-html lang-html prettyprint-override"><code>playVideo(
    BuildContext context,

  ) {
    return showDialog(
        context: context,
        builder: (context) {
          return Center(
              child: SizedBox(
                  height: 300,
                  width: 300,
                  child: VideoWidget(url: url, play: true)));
        });
  }</code>```

Body:
Make a new Stateful widget class like this for video player , initialized video player controller and video player widget here

import 'package:approved/src/constants/colors.dart';
import 'package:flutter/material.dart';
import 'package:flutter_svg/svg.dart';
import 'package:video_player/video_player.dart';

class VideoWidget extends StatefulWidget {
  final String? url;
  final bool? play;

  const VideoWidget({@required this.url, @required this.play});
  @override
  _VideoWidgetState createState() => _VideoWidgetState();
}

class _VideoWidgetState extends State<VideoWidget> {
  late VideoPlayerController _controller;
  Future<void>? _initializeVideoPlayerFuture;

  @override
  void initState() {
    super.initState();
    _controller = VideoPlayerController.network(widget.url!);
    _initializeVideoPlayerFuture = _controller.initialize().then((_) {
      // Ensure the first frame is shown after the video is initialized, even before the play button has been pressed.
      setState(() {});
    });

    if (widget.play!) {
      _controller.play();
      _controller.setLooping(true);
    }
  }

  @override
  void didUpdateWidget(VideoWidget oldWidget) {
    if (oldWidget.play != widget.play) {
      if (widget.play!) {
        _controller.play();
        _controller.setLooping(true);
      } else {
        _controller.pause();
      }
    }
    super.didUpdateWidget(oldWidget);
  }

  @override
  void dispose() {
    _controller.dispose();
    super.dispose();
  }

  @override
  Widget build(BuildContext context) {
    return Stack(children: [
      Container(
        color: Colors.white,
        child: FutureBuilder(
          future: _initializeVideoPlayerFuture,
          builder: (context, snapshot) {
            if (snapshot.connectionState == ConnectionState.done) {
              return VideoPlayer(_controller);
            } else {
              return const Center(
                child: CircularProgressIndicator(),
              );
            }
          },
        ),
      ),

    ]);
  }
}

Now you can use this widget anywhere you have to just pass the video url and playing parameter true/false.

Like I am using in showDialog method

playVideo(
    BuildContext context,

  ) {
    return showDialog(
        context: context,
        builder: (context) {
          return Center(
              child: SizedBox(
                  height: 300,
                  width: 300,
                  child: VideoWidget(url: url, play: true)));
        });
  }


问:
show/hide on radio button checked/unchecked- knockout js
I have 2 radio buttons, When I click on the option A, a particular div has to be shown, and when click on the option B, 1st div should be hidden and 2nd div should be shown. Below is the code.

```<code><div class="form-group">
                    <input  type="radio" name="test-radio" id="test-radio-Option-A" value="true" data-bind="click: test">
                    <label for="test-radio-Option-A">Text Message</label>
                </div>
                <div class="form-group">
                    <input  type="radio" name="test-radio" id="test-radio-Option-B" value="false">
                    <label for="test-radio-Option-B">Text Message</label>
                </div>
<div style="display: none" data-bind="visible: showDiv">
                test Div
            </div>
</code>```

Following is the script I tried:(coffee script)

```<code>@showPhone = ko.observable false

test: =>
    @showPhone true
</code>```

using this if I click on the 1st radio button, I was able to see the div, but I don't know if its the right way Can someone please guide me through this?

Thanks.

答:
You'll be needing the two following bindings:

checked: to put on your radio-buttons

visible: to put on your divs you want to display

your html will look like this:

```<code><div class="form-group">
                <input  type="radio" name="test-radio" id="test-radio-Option-A" value="true" data-bind="checked: optionA">
                <label for="test-radio-Option-A">Text Message</label>
            </div>
            <div class="form-group">
                <input  type="radio" name="test-radio" id="test-radio-Option-B" value="false" data-bind="checked: optionB">
                <label for="test-radio-Option-B">Text Message</label>
            </div>
<div data-bind="visible: optionA">
            test Div A
        </div>
<div data-bind="visible: optionB">
            test Div B
        </div>
</code>```

Your viewModel will need 2 boolean observables optionA and optionB for the view to bind to in this manner

Body:
I have 2 radio buttons, When I click on the option A, a particular div has to be shown, and when click on the option B, 1st div should be hidden and 2nd div should be shown. Below is the code.

                    <input  type="radio" name="test-radio" id="test-radio-Option-A" value="true" data-bind="click: test">
                    Text Message

                    <input  type="radio" name="test-radio" id="test-radio-Option-B" value="false">
                    Text Message

                test Div

Following is the script I tried:(coffee script)

@showPhone = ko.observable false

test: =>
    @showPhone true

using this if I click on the 1st radio button, I was able to see the div, but I don't know if its the right way Can someone please guide me through this?

Thanks.

Body:
You'll be needing the two following bindings:

checked: to put on your radio-buttons

visible: to put on your divs you want to display

your html will look like this:

<div class="form-group">
                <input  type="radio" name="test-radio" id="test-radio-Option-A" value="true" data-bind="checked: optionA">
                <label for="test-radio-Option-A">Text Message</label>
            </div>
            <div class="form-group">
                <input  type="radio" name="test-radio" id="test-radio-Option-B" value="false" data-bind="checked: optionB">
                <label for="test-radio-Option-B">Text Message</label>
            </div>
<div data-bind="visible: optionA">
            test Div A
        </div>
<div data-bind="visible: optionB">
            test Div B
        </div>

Your viewModel will need 2 boolean observables optionA and optionB for the view to bind to in this manner


问:
Open a text file containing a table with no clear separator
I have to open a text file which contain data that I want to open as a tibble or dataframe. The data are stored in a tbale with white space as separator (I suppose). The probleme is that whitespace is also used in the column "PI". I have no idea how to open it without doing any complicated script to scan all of it and parse it with regex.
Do you have any tips ? I think it was designed to have visualy aligned column when we open it as a text file.

here is a sample of the file.

```<code>  DAC                                                                PI    SPROF_DATE_UPDATE DATA_MODE      WMO CYCLE         PROFILE_DATE      LON     LAT        MPD_RAW      MPD_ADJ        Z_RAW       Z_ADJ
 aoml  STEPHEN RISER , KENNETH JOHNSON                                   2021-05-12 18:52:52         R  5903612   038  2012-07-04 01:15:50    8.651 -41.466     -89.313473          NaN     5.439052         NaN
 aoml  STEPHEN RISER , KENNETH JOHNSON                                   2020-12-03 02:03:33         R  5903717   138  2014-02-03 18:07:45 -164.533 -69.958     134.866767          NaN     7.884637         NaN
 aoml  STEPHEN RISER , KENNETH JOHNSON                                   2021-05-12 22:44:39         R  5903717   139  2014-02-10 20:05:49 -164.302 -70.061      99.848464          NaN     5.803396         NaN
 aoml  STEPHEN RISER , KENNETH JOHNSON                                   2021-05-12 22:44:53         R  5903717   140  2014-02-17 22:06:53 -164.146 -70.246     107.535752          NaN     6.260275         NaN
</code>```

答:
Fixing @danlooo

First: header separator is single space.

Second: sometimes before - there is just a single space

```<code>data = readLines("testdata.txt")
data[1] = gsub(pattern ="\\s+" ,replacement = "\t" ,x = data[1])
modified_data = gsub(pattern ="\\s-" ,replacement = "  -" ,x = data)
modified_data = gsub(pattern ="\\s{2,}" ,replacement = "\t" ,x = modified_data)
final_data = paste0(modified_data,collapse = "\n")
write(final_data, "finaldata.txt")
finaldatatable <- read_delim("finaldata.txt",
                        delim = "\t", escape_double = FALSE,
                        trim_ws = TRUE)
</code>```

Body:
I have to open a text file which contain data that I want to open as a tibble or dataframe. The data are stored in a tbale with white space as separator (I suppose). The probleme is that whitespace is also used in the column "PI". I have no idea how to open it without doing any complicated script to scan all of it and parse it with regex.
Do you have any tips ? I think it was designed to have visualy aligned column when we open it as a text file.

here is a sample of the file.

  DAC                                                                PI    SPROF_DATE_UPDATE DATA_MODE      WMO CYCLE         PROFILE_DATE      LON     LAT        MPD_RAW      MPD_ADJ        Z_RAW       Z_ADJ
 aoml  STEPHEN RISER , KENNETH JOHNSON                                   2021-05-12 18:52:52         R  5903612   038  2012-07-04 01:15:50    8.651 -41.466     -89.313473          NaN     5.439052         NaN
 aoml  STEPHEN RISER , KENNETH JOHNSON                                   2020-12-03 02:03:33         R  5903717   138  2014-02-03 18:07:45 -164.533 -69.958     134.866767          NaN     7.884637         NaN
 aoml  STEPHEN RISER , KENNETH JOHNSON                                   2021-05-12 22:44:39         R  5903717   139  2014-02-10 20:05:49 -164.302 -70.061      99.848464          NaN     5.803396         NaN
 aoml  STEPHEN RISER , KENNETH JOHNSON                                   2021-05-12 22:44:53         R  5903717   140  2014-02-17 22:06:53 -164.146 -70.246     107.535752          NaN     6.260275         NaN

Body:
Fixing @danlooo

First: header separator is single space.

Second: sometimes before - there is just a single space

data = readLines("testdata.txt")
data[1] = gsub(pattern ="\\s+" ,replacement = "\t" ,x = data[1])
modified_data = gsub(pattern ="\\s-" ,replacement = "  -" ,x = data)
modified_data = gsub(pattern ="\\s{2,}" ,replacement = "\t" ,x = modified_data)
final_data = paste0(modified_data,collapse = "\n")
write(final_data, "finaldata.txt")
finaldatatable <- read_delim("finaldata.txt",
                        delim = "\t", escape_double = FALSE,
                        trim_ws = TRUE)


问:
How to return a string from native code to managed code in Windows Azure?
I have found this tutorial about how to interop C# code and unmanaged C++ code on Windows Azure very helpful http://msdn.microsoft.com/en-us/hh351534

However when I modify it, I run into some unforeseen errors.

Instead of returning and displaying an int from unmanaged code, I would like it to return a string an display that string in C# code. So in my experiment, I changed the function AddNumbers() from

```<code>NATIVECALCULATOR_API int AddNumbers(int left, int right){
    return left+right;
}
</code>```

into

```<code>NATIVECALCULATOR_API char *AddNumbers(int left, int right){
    return "a string";
}
</code>```

so that when I hit the button on ASP.NET page, it will return "a string".

However, in default.aspx.cs, the web page codebehind file, it tells me I can't change

```<code>static extern int AddNumbers(int left, int right);
</code>```

into

```<code>static extern char *AddNumbers(int left, int right);
</code>```

because "pointers can only be used in unsafe context"

And from there I have tried many methods, but I am unable to convert AddNumbers() into a string to display.

So basically, can someone show me how to modify the tutorial to display a string instead of a int.

答:
What C# is trying to tell you is that it won't let you use raw pointers willingly. Thankfully we can still pass strings (even raw char strings) from unmanaged code to managed code through Data Marshalling. In the case of strings, the system translates a char* in C++ to a string in C#.

For your specific question, change AddNumbers in default.aspx.cs from

```<code>static extern int AddNumbers(int left, int right);
</code>```

to

```<code>static extern string AddNumbers(int left, int right);
</code>```

Likewise, change the C++ code to read like so

```<code>NATIVECALCULATOR_API char* AddNumbers(int left, int right)
{
    return "a string";
}
</code>```

The data marshalling should correctly pass the string from one to the other.

Here's the MSDN article that explains data marshalling in (MUCH) more detail.

Body:
I have found this tutorial about how to interop C# code and unmanaged C++ code on Windows Azure very helpful http://msdn.microsoft.com/en-us/hh351534

However when I modify it, I run into some unforeseen errors.

Instead of returning and displaying an int from unmanaged code, I would like it to return a string an display that string in C# code. So in my experiment, I changed the function AddNumbers() from

NATIVECALCULATOR_API int AddNumbers(int left, int right){
    return left+right;
}

into

NATIVECALCULATOR_API char *AddNumbers(int left, int right){
    return "a string";
}

so that when I hit the button on ASP.NET page, it will return "a string".

However, in default.aspx.cs, the web page codebehind file, it tells me I can't change

static extern int AddNumbers(int left, int right);

into

static extern char *AddNumbers(int left, int right);

because "pointers can only be used in unsafe context"

And from there I have tried many methods, but I am unable to convert AddNumbers() into a string to display.

So basically, can someone show me how to modify the tutorial to display a string instead of a int.

Body:
What C# is trying to tell you is that it won't let you use raw pointers willingly. Thankfully we can still pass strings (even raw char strings) from unmanaged code to managed code through Data Marshalling. In the case of strings, the system translates a char* in C++ to a string in C#.

For your specific question, change AddNumbers in default.aspx.cs from

static extern int AddNumbers(int left, int right);

to

static extern string AddNumbers(int left, int right);

Likewise, change the C++ code to read like so

NATIVECALCULATOR_API char* AddNumbers(int left, int right)
{
    return "a string";
}

The data marshalling should correctly pass the string from one to the other.

Here's the MSDN article that explains data marshalling in (MUCH) more detail.


问:
Query Doesn't pick a column i m passing in laravel
Here is my migrate

```<code>    public function up()
    {
     Schema::create('add_admins', function (Blueprint $table) {
            $table->id();
            $table->string('firstName');
            $table->string('lastName');
            $table->string('email');
            $table->string('mobile');
            $table->string('password');
            $table->date('expiry_date');
            $table->string('showAdminP');
            $table->timestamps();

            $table->index('id');
        });
    }
</code>```

Here is my controller

```<code>    public function addAdmin()
    {
        $firstName=$_POST['firstName'];
        $lastName=$_POST['lastName'];
        $email=$_POST['email'];
        $mobile=$_POST['mobile'];
        $password=$_POST['password'];
        $cpassword=$_POST['cpassword'];

        $hashed = Hash::make('password', ['rounds' => 12]);

        if($password==$cpassword)
        {
            $main=['firstName' => $firstName , 'lastName' => $lastName , 'email' => $email , 'mobile' => $mobile ,'password' => $hashed , 'adminShowP' => $password];
            // dd($main);
            \App\Models\AddAdmin::create($main);
            return view('admin.addAdmin');
        }
        else
        {
            ?>
            <script type="text/javascript">alert("Please Enter Correct Password")</script>
            <?php
            return view('admin.addAdmin');
        }
    }
</code>```

Here is my Model

```<code>    class AddAdmin extends Model
{
    protected $fillable = [
        'firstName',
        'lastName',
        'email',
        'mobile',
        'expiry_date'
    ];
    public function user()
    {
        return $this->belongsTo(User::class);
    }
}
</code>```

It give an error as: SQLSTATE[HY000]: General error: 1364 Field 'password' doesn't have a default value (SQL: insert into add_admins (firstName, lastName, email, mobile, updated_at, created_at) values (firstName, lastName, xyz@example.com, 2121212121, 2022-02-08 10:53:42, 2022-02-08 10:53:42))

答: Thank You.... I forget to add password column in fillable array in model</p>

Body:
Here is my migrate

    public function up()
    {
     Schema::create('add_admins', function (Blueprint $table) {
            $table->id();
            $table->string('firstName');
            $table->string('lastName');
            $table->string('email');
            $table->string('mobile');
            $table->string('password');
            $table->date('expiry_date');
            $table->string('showAdminP');
            $table->timestamps();

            $table->index('id');
        });
    }

Here is my controller

    public function addAdmin()
    {
        $firstName=$_POST['firstName'];
        $lastName=$_POST['lastName'];
        $email=$_POST['email'];
        $mobile=$_POST['mobile'];
        $password=$_POST['password'];
        $cpassword=$_POST['cpassword'];

        $hashed = Hash::make('password', ['rounds' => 12]);

        if($password==$cpassword)
        {
            $main=['firstName' => $firstName , 'lastName' => $lastName , 'email' => $email , 'mobile' => $mobile ,'password' => $hashed , 'adminShowP' => $password];
            // dd($main);
            \App\Models\AddAdmin::create($main);
            return view('admin.addAdmin');
        }
        else
        {
            ?>

            <?php
            return view('admin.addAdmin');
        }
    }

Here is my Model

    class AddAdmin extends Model
{
    protected $fillable = [
        'firstName',
        'lastName',
        'email',
        'mobile',
        'expiry_date'
    ];
    public function user()
    {
        return $this->belongsTo(User::class);
    }
}

It give an error as: SQLSTATE[HY000]: General error: 1364 Field 'password' doesn't have a default value (SQL: insert into add_admins (firstName, lastName, email, mobile, updated_at, created_at) values (firstName, lastName, xyz@example.com, 2121212121, 2022-02-08 10:53:42, 2022-02-08 10:53:42))


问:
Can't run my Node.js Typescript project TypeError [ERR_UNKNOWN_FILE_EXTENSION]: Unknown file extension ".ts" for /app/src/App.ts
When I try to start my app on Heroku I got the following stack trace.
It is just a basic ts.app like you see with ts-node and nodemon.

I am really interested in what the answer is going to be.

```<code>2020-05-30T00:03:12.201106+00:00 heroku[web.1]: Starting process with command `npm start`
2020-05-30T00:03:14.405285+00:00 app[web.1]:
2020-05-30T00:03:14.405303+00:00 app[web.1]: > discordtoornamentmanager@1.0.0 start /app
2020-05-30T00:03:14.405303+00:00 app[web.1]: > ts-node src/App.ts
2020-05-30T00:03:14.405304+00:00 app[web.1]:
2020-05-30T00:03:14.833655+00:00 app[web.1]: (node:23) ExperimentalWarning: The ESM module loader is experimental.
2020-05-30T00:03:14.839311+00:00 app[web.1]: TypeError [ERR_UNKNOWN_FILE_EXTENSION]: Unknown file extension ".ts" for /app/src/App.ts
2020-05-30T00:03:14.839312+00:00 app[web.1]:     at Loader.defaultGetFormat [as _getFormat] (internal/modules/esm/get_format.js:65:15)
2020-05-30T00:03:14.839314+00:00 app[web.1]:     at Loader.getFormat (internal/modules/esm/loader.js:113:42)
2020-05-30T00:03:14.839315+00:00 app[web.1]:     at Loader.getModuleJob (internal/modules/esm/loader.js:244:31)
2020-05-30T00:03:14.839315+00:00 app[web.1]:     at processTicksAndRejections (internal/process/task_queues.js:97:5)
2020-05-30T00:03:14.839316+00:00 app[web.1]:     at Loader.import (internal/modules/esm/loader.js:178:17)
2020-05-30T00:03:14.847801+00:00 app[web.1]: npm ERR! code ELIFECYCLE
2020-05-30T00:03:14.847998+00:00 app[web.1]: npm ERR! errno 1
2020-05-30T00:03:14.848957+00:00 app[web.1]: npm ERR! discordtoornamentmanager@1.0.0 start: `ts-node src/App.ts`
2020-05-30T00:03:14.849050+00:00 app[web.1]: npm ERR! Exit status 1
2020-05-30T00:03:14.849172+00:00 app[web.1]: npm ERR!
2020-05-30T00:03:14.849254+00:00 app[web.1]: npm ERR! Failed at the discordtoornamentmanager@1.0.0 start script.
2020-05-30T00:03:14.849337+00:00 app[web.1]: npm ERR! This is probably not a problem with npm. There is likely additional logging output above.
2020-05-30T00:03:14.854859+00:00 app[web.1]:
2020-05-30T00:03:14.854998+00:00 app[web.1]: npm ERR! A complete log of this run can be found in:
2020-05-30T00:03:14.855069+00:00 app[web.1]: npm ERR!     /app/.npm/_logs/2020-05-30T00_03_14_850Z-debug.log
2020-05-30T00:03:14.907689+00:00 heroku[web.1]: Process exited with status 1
2020-05-30T00:03:14.943718+00:00 heroku[web.1]: State changed from starting to crashed
</code>```

This is my package.json

```<code>{
   "name": "discordtoornamentmanager",
   "version": "1.0.0",
   "description": "",
   "main": "dist/app.js",
   "type": "module",
   "scripts": {
      "test": "echo \"Error: no test specified\" && exit 1",
      "dev": "nodemon -x ts-node src/App.ts",
      "start": "ts-node src/App.ts"
   },
   "keywords": [],
   "author": "",
   "license": "ISC",
   "dependencies": {
      "@types/node": "^14.0.5",
      "axios": "^0.19.2",
      "discord.js": "^12.2.0",
      "pg": "^8.2.1",
      "reflect-metadata": "^0.1.10",
      "typeorm": "0.2.25",
      "typescript": "^3.9.3",
      "nodemon": "^2.0.4",
      "ts-node": "8.10.1"

   }
}
</code>```

And this is my tsconfig

```<code>{
   "compilerOptions": {
      "lib": [
         "es6"
      ],
      "target": "es6",
      "module": "commonjs",
      "moduleResolution": "node",
      "outDir": "dist",
      "resolveJsonModule": true,
      "emitDecoratorMetadata": true,
      "esModuleInterop": true,
      "experimentalDecorators": true,
      "sourceMap": true
   },
   "include": ["src/**/*.ts"],
   "exclude": ["node_modules", "**/*.spec.ts"]
}
</code>```

答:
Update:
Thanks to some maintainers, a working solution is now officially documented by webpack. Just take a small look at the official webpack documentation.

The second solution is similar to other answers here and works perfectly fine in my case.

Body:
When I try to start my app on Heroku I got the following stack trace.
It is just a basic ts.app like you see with ts-node and nodemon.

I am really interested in what the answer is going to be.

2020-05-30T00:03:12.201106+00:00 heroku[web.1]: Starting process with command `npm start`
2020-05-30T00:03:14.405285+00:00 app[web.1]:
2020-05-30T00:03:14.405303+00:00 app[web.1]: > discordtoornamentmanager@1.0.0 start /app
2020-05-30T00:03:14.405303+00:00 app[web.1]: > ts-node src/App.ts
2020-05-30T00:03:14.405304+00:00 app[web.1]:
2020-05-30T00:03:14.833655+00:00 app[web.1]: (node:23) ExperimentalWarning: The ESM module loader is experimental.
2020-05-30T00:03:14.839311+00:00 app[web.1]: TypeError [ERR_UNKNOWN_FILE_EXTENSION]: Unknown file extension ".ts" for /app/src/App.ts
2020-05-30T00:03:14.839312+00:00 app[web.1]:     at Loader.defaultGetFormat [as _getFormat] (internal/modules/esm/get_format.js:65:15)
2020-05-30T00:03:14.839314+00:00 app[web.1]:     at Loader.getFormat (internal/modules/esm/loader.js:113:42)
2020-05-30T00:03:14.839315+00:00 app[web.1]:     at Loader.getModuleJob (internal/modules/esm/loader.js:244:31)
2020-05-30T00:03:14.839315+00:00 app[web.1]:     at processTicksAndRejections (internal/process/task_queues.js:97:5)
2020-05-30T00:03:14.839316+00:00 app[web.1]:     at Loader.import (internal/modules/esm/loader.js:178:17)
2020-05-30T00:03:14.847801+00:00 app[web.1]: npm ERR! code ELIFECYCLE
2020-05-30T00:03:14.847998+00:00 app[web.1]: npm ERR! errno 1
2020-05-30T00:03:14.848957+00:00 app[web.1]: npm ERR! discordtoornamentmanager@1.0.0 start: `ts-node src/App.ts`
2020-05-30T00:03:14.849050+00:00 app[web.1]: npm ERR! Exit status 1
2020-05-30T00:03:14.849172+00:00 app[web.1]: npm ERR!
2020-05-30T00:03:14.849254+00:00 app[web.1]: npm ERR! Failed at the discordtoornamentmanager@1.0.0 start script.
2020-05-30T00:03:14.849337+00:00 app[web.1]: npm ERR! This is probably not a problem with npm. There is likely additional logging output above.
2020-05-30T00:03:14.854859+00:00 app[web.1]:
2020-05-30T00:03:14.854998+00:00 app[web.1]: npm ERR! A complete log of this run can be found in:
2020-05-30T00:03:14.855069+00:00 app[web.1]: npm ERR!     /app/.npm/_logs/2020-05-30T00_03_14_850Z-debug.log
2020-05-30T00:03:14.907689+00:00 heroku[web.1]: Process exited with status 1
2020-05-30T00:03:14.943718+00:00 heroku[web.1]: State changed from starting to crashed

This is my package.json

{
   "name": "discordtoornamentmanager",
   "version": "1.0.0",
   "description": "",
   "main": "dist/app.js",
   "type": "module",
   "scripts": {
      "test": "echo \"Error: no test specified\" && exit 1",
      "dev": "nodemon -x ts-node src/App.ts",
      "start": "ts-node src/App.ts"
   },
   "keywords": [],
   "author": "",
   "license": "ISC",
   "dependencies": {
      "@types/node": "^14.0.5",
      "axios": "^0.19.2",
      "discord.js": "^12.2.0",
      "pg": "^8.2.1",
      "reflect-metadata": "^0.1.10",
      "typeorm": "0.2.25",
      "typescript": "^3.9.3",
      "nodemon": "^2.0.4",
      "ts-node": "8.10.1"

   }
}

And this is my tsconfig

{
   "compilerOptions": {
      "lib": [
         "es6"
      ],
      "target": "es6",
      "module": "commonjs",
      "moduleResolution": "node",
      "outDir": "dist",
      "resolveJsonModule": true,
      "emitDecoratorMetadata": true,
      "esModuleInterop": true,
      "experimentalDecorators": true,
      "sourceMap": true
   },
   "include": ["src/**/*.ts"],
   "exclude": ["node_modules", "**/*.spec.ts"]
}