database design – MySQL: Is it best practice to check whether some column is NULL by `NOT ISNULL(column_name)`?

Given the following schema, my question is that since I have already added the NOT NULL to IsBanned, would I have to check whether it is not null using the syntax NOT ISNULL(IsBanned)? And what’s the best practice to do the same if I didn’t add NOT NULL?

CREATE TABLE Viewer (
    AccountID varchar(25),
    IsBanned boolean                NOT NULL
                                    DEFAULT 0,
    ...
    CHECK                           (NOT ISNULL(isBanned)),
);

As you can see I

Dataset: selectively map a function onto the nth column

I have this data, which I used the following to Import.

c=Import("data1.csv", "Dataset", HeaderLines -> 1);
d=c(All, {1 -> ToExpression});

enter image description here

I want to convert the 1st col to Minute and want to use the dataset(All,{n->f}) mentioned in MMA’s Dataset document. My idea to operate a function on the 1st col and return a two-col dataset. I tried the following. Some worked, while some did not. Could you please have a check and comment on why some methods did not return what I want. Thanks.

Those worked.

f = UnitConvert(#, "Minutes") &;
d(All, {1 -> f})
d(All, {"t" -> f})

d(All, {"t" -> Function({x}, UnitConvert(x, "Minutes"))})

Those did not.

d(All, {1 -> UnitConvert(#, "Minutes") &}) (*I understand why this one did not work.*)
d(All, {1 -> UnitConvert(#((1)), "Minutes") &})
d(All, {"t" -> UnitConvert(#((1)), "Minutes") &})

This simply blowed my mind. I wanted to return the 1st col itself. However, 1 seems to work as #((1)) and 2 as #((2)). It seems to me anything appears on the right hand left is treated as a Function (Here, as Part)?

d(All, {"t" -> 1})
d(All, {"t" -> 2})

microsoft excel – Look up list of values on different worksheets and return both column and row header

I am trying to look up a list of values in multiple worksheets of a workbook and return both the column header and row header in the cells next to my search value.
I found a code that works perfect for all values in my first column of data (ColB) in the active worksheet. If I add values to my list that are located in another column or another worksheet I get a circular reference error and it places a 0 in my function cells.
I have put my list of values to look up in column 1 under my last row of data and put both functions in the columns next to them.

Can anyone help me fix this problem?

My workbook consists of a growing amount of worksheets in the same format.
Every worksheet has Column headers in Row 1 and row haeaders in Column 1, 81 rows and 40 columns filled with data. The list of values to be looked up can be up to 1000 or more.

    Function MyROW(MyVAL)
With ActiveSheet.UsedRange
    FFIND = False
    For J = .Column + 1 To .Column + .Columns.Count
        For I = .Row + 1 To .Row + .Rows.Count
            If (Cells(I, J) = MyVAL) Then
                FFIND = True
                GoTo MyEND
            End If
        Next I
    Next J
    MyEND:
    If (FFIND) Then MyROW = Cells(I, .Column)
End With

End Function

    Function MyCOLUMN(MyVAL)
With ActiveSheet.UsedRange
    FFIND = False
    For J = .Column + 1 To .Column + .Columns.Count
        For I = .Row + 1 To .Row + .Rows.Count
            If (Cells(I, J) = MyVAL) Then
                FFIND = True
                GoTo MyEND
            End If
        Next I
    Next J
    MyEND:
    If (FFIND) Then MyCOLUMN = Cells(.Row, J)
End With

End Function

enter image description here

json formatting – How to display past list items related to a column value?

I have the following SharePoint list in which the “Participant” columns are text values (names of people) and each item in this list is a training event. “Evenement” is the type of training and “Jour” means day of the training.

What I need to do is whenever someone hovers on the name of a participant – I need their past training events to show.
For example if “Participant 1” is Alpha and his “Jour 1” is on 11-05-2021 then “Jour 2” is on 12-05-2021 and “Jour 3” is 13-05-2021 and finally “Jour 4” is 14-05-2021. I need all this information to show on a hovercard if possible?

I am not sure if this makes complete sense or not and if it’s even possible but any guidance would be appreciated, thank you in advance to anyone who replies and please be advised I’m only starting with JSON .

Sharepoint List Preview

vba – Compare Names and DOB in two Excel sheets and return the value in a third column

Valued programmers, I hope you can help me modify the VBA code below so it works properly.
I have an Excel workbook that consists of two worksheets.
Worksheet “Data” contains Names (Column A), DOB (Column B) and Numbers (Column C) and can go up to around 300k rows.
Worksheet “Search” contains Names(Column A) and DOB (Column B)
I want to compare sheet “Search” with sheet “Data” and return the value from Column “Number” to Column C of the “Search” tab if the DOB is completely identical and if at least part of the name is identical. One of the sheets can for instance have only capitals or an insertion or maiden name added to the name, while in the other sheet this information might be missing.
I tried to alter a code I found online, but am still a newbie at VBA and it already gives me an error at the title. Can’t seem to find out what I did wrong (placed it in a module).
Hope to get some usefull tips.

Option Explicit

    Sub Search()

        Dim i As Long, j As Long
        Dim LastrowS1 As Long, LastrowS2 As Long
        Dim NameS1 As String, DOBS1 As String, NameS2 As String, DOBS2 As String
        Dim NumberS1 As Number

        LastrowS1 = Data.Cells(Data.Rows.Count, "A").End(xlUp).Row
        LastrowS2 = Search.Cells(Search.Rows.Count, "A").End(xlUp).Row

        For i = 2 To LastrowS1
            With ThisWorkbook.Worksheets("Data")
                NameS1 = .Range("A" & i).Value
                DOBS1 = .Range("B" & i).Value
                NumberS1 = .Range("C" & i).Value
            End With
                For j = 2 To LastrowS2
                    With ThisWorkbook.Worksheets("Search")
                        NameS2 = .Range("A" & j).Value
                        DOBS2 = .Range("B" & j).Value
                    End With

                    If NameS1 = NameS2 And DOBS1 = DOBS2 Then
                        Search.Range("C" & j).Value = NumberS1
                        Exit For
                    End If

                Next j

            Next i

    End Sub

sharepoint online – SPO: Using JSON to Show/Hide Modern Form Field Based on Value Selected in Lookup Column

I am trying to modify the new-entry form of a modern list in SharePoint Online by hiding or showing a field based on the value selected in a preceding field on the same form. Microsoft offers straightforward guidance on how to do this using a JSON snippet.

While they don’t list a lookup column as an unsupported field type to use for this purpose, they don’t provide guidance on how to use one in this way, either. And from my testing, you can’t simply grab the value of a lookup field in the described way:

=if(($LookupField)=='Hourly', 'true', 'false')

If I use a choice field instead of a lookup field, this works fine. But doing some digging, in other contexts it looks like the value may be captured in a .lookupValue property, so I tried it this way:

=if(($LookupField.lookupValue)=='Hourly', 'true', 'false')

Still no luck. Does anyone know if it is possible to use the value from a lookup field in this way? And if so, what do I need to do to make the magic happen?

What is best practice for referencing data rows within stored procedures – via PK, code column or data value?

Suppose you have a table for colours with columns:

  • id = automatically incrementing integer, primary key
  • code = short code reference for the colour, unique
  • colour = human-readable name of colour, unique

Example values might be:

Now imagine you have a stored procedure that, at some point, needs to reference this table. Let’s say the business logic says to obtain the colour “Green”. To achieve this, you could have any of the following three WHERE clauses:

  • WHERE id = 2
  • WHERE code = GR
  • WHERE colour = Green

Now, if the system is designed such that it is agreed that a code value, once created, never changes, then, in my view, that is the best column to reference because:

  • It is an alternate key
  • It is human-readable for people who maintain the code
  • It will not be impacted when the business decides to change the colour value to ‘Sea Green’

However, if a legacy table lacks such code values, what, in your opinion, is best practice? To reference the id column, or the colour column?

If you reference the id column, the code is not readable unless you then also add comments – you shouldn’t have to comment simple things like this. It sucks figuring out what statements like WHERE id not in (1, 7, 17, 24, 56) mean.

I’m not sure how often, in reality, the id value might change – but consider if you run a script during development to insert new colours but then delete those and insert some more. If your stored procedure references the id values from that last set of colours inserted but when you create your new colours in your next environment you skip the step that inserted the colours which ended up deleted, then the id values won’t match in that next environment. Bad practice, but it can happen – a developer develops their script on a dev instance not thinking that the id values will conflict with production (which, for example, may have had additional colours created manually by the business before your colour creation script runs).

If you reference the colour column, you run the risk that if the business does ask to update the description from ‘Green’ to ‘Sea Green’, that your procedure will begin to fail.

I suppose a further solution is to implement the code column when you need it, if it isn’t there already – probably the best solution?

postgresql – How do I add a where statement at the end of query for a column that does not have a table to reference from?

select
CASE WHEN (to_char(((case when v.trip_order_start_date is null then case when v.manual_start_date is null then t.required_pickup_date else v.trip_order_start_date end else v.trip_order_start_date end  + (interval  '-1 hours' * ofc.offset)) at time zone 'utc' at time zone 'America/Chicago' + (interval  '-1 hours' * ofc.offset)) at time zone 'utc' at time zone 'America/Chicago', 'YYYYMM') < to_char((now() at time zone 'utc' at time zone 'America/Chicago'), 'YYYYMM')
        AND(coalesce(posted_date, paid_date) IS NULL
            OR to_char((coalesce(v.posted_date, v.paid_date) + (interval  '-1 hours' * ofc.offset)) at time zone 'utc' at time zone 'America/Chicago', 'YYYYMM') > to_char((now() at time zone 'utc' at time zone 'America/Chicago' - interval '1' month), 'YYYYMM'))) THEN 'YES' ELSE 'NO' END AS accrual

WHERE
accrual = 'YES'

The problem is there is no table for me to reference the “accrual” column so I get the below error

(Code: 0, SQL State: 42703) ERROR: column “accrual” does not exist
Position: 10708 (Script position: 10708 – 10715)

postgresql – How to find the counts of word occurrences in a column?

I have a column containing text in postgresql. Now I want to find, for a given input word, the counts of occurrences of the word in my text column.

So I have

  date_col   |  text_col
-------------+-----------------------
 2021-04-02  | This is a test.
-------------+---------------------
 2021-03-30  | A test is a test.
-------------+---------------------
 2021-03-30  | How to test?
-------------+---------------------
 2021-04-01  | One more test

and I want this result for the word 'test':

 count | num_occurrences
-------+-----------------
   3   |  1
-------+-----------------
   1   |  2

meaning 3 times there was excactly one occurrence of "test". Once there were two occurrences of "test" in the same row.

Later, I want to be able to query a given period with the same query.

My initial take was to create a new table with a row for every word like so:

  date_col  |  word 
------------+------------------
 2021-04-02 |  This
------------+------------------
 2021-04-02 |  is
------------+------------------
 2021-04-02 |  a

and do some counting and grouping. But is there a better way?

php – Updating column fields from admin dashboard PDO

Hi, i got this code in PHP and i was in need for updating those collumns from the dashboard. The tables are being created in the while loop, but i cant figure out how to update these values from the button.
Any ideas? im begginer at PDO.

<?php
require_once('connection.php');

try{
    $stmte = $db->query("SELECT * FROM tb_agendamento ");
    $executa = $stmte->execute();
?>
<div class="container">
    <table align="center" class="table table-bordered table-responsive table-hover">
    <tr><td><b>Código de agendamento</td><td><b>Código do cliente</td><td><b>Data</td><td><b>Horário</td><td colspan="2" align="center">Ação</td></tr>

<?php
    if($executa){
        while($reg = $stmte->fetch(PDO::FETCH_OBJ)){ // Para recuperar um ARRAY utilize PDO::FETCH_ASSOC 
?>
            <tr><td><?=$reg->cd_agendamento?></td>
            <td><?=$reg->cd_cliente?></td>
            <td><?=$reg->dt_visita?></td>
            <td><?=$reg->hr_inicio?></td>
            <td><div class="form-group">
            <button type="submit" name="aceitar" class="btn btn-success btn-lg">Aceitar</button>
            </div></td>
            <td><div class="form-group">
            <button type="submit" name="negar" class="btn btn-danger btn-lg ">Negar</button>
            </div></td></tr>

            <?php if(isset($_POST('aceitar')))
{      
    
 echo "<script>alert('test.');</script>";

}       ?>

           
<?php
       }
       print '</table>';
print '</div>';
    }
}catch(PDOException $e){
      echo $e->getMessage();
}

if(isset($_POST('aceitar')))
{      
    
 echo "<script>alert('test');</script>";

}       
if(isset($_POST('negar')))
{
 echo "<script type='text/javascript'> alert('Desculpe! Não poderemos atende-lo.')</script>";
}