MsSQL unique data Dropdown

General
1

I want to filter on date column in database. Then I want to show the sensor names that will come from here in the dropdown. (uniquely).

Here's what I can do

f1

start='2021-08-01'
end='2021-08-05'

msg.payload ="SELECT * FROM mat where date between '"+start+"' and  '"+end+"';";
return msg;

f2

var newmsg = msg;
newmsg.options = [x];
var value;

for (var i = 0; i < msg.payload.length; i++) {
    //canal = msg.payload[i].address;
    value = msg.payload[i].sensor;
    newmsg.options[i] = {};
    newmsg.options[i] = value;

}

return msg;

image
image

2

A better way to do this...

image
image554Ă—702 23.9 KB

... this will negate an SQL injection and is easier to configure and understand.

PS, this uses node-red-contrib-mssql-plus which has many improvements and bug fixes over the 3y old node-red-contrib-mssql

1 Like
3

You could group everything with a SQL statement or transfer all data to Node-RED first and filter it there. I would try to do it in SQL because then you're receiving only relevant data.

4

do you think the data will be unique?

5

Yes.

That's what DISTINCT is for.

You could also use group by

6

Can we see the above screenshot without your blue notes?

7

thank you, it worked

8

Is there a shortcut that I can print the number of data coming to the drop-down ?

9

what do you mean?

you mean a debug node to see what is going into the node?

10

image
image1578Ă—644 6.23 KB

To print the number of data in the text box

11

what number, from where? from the database? as an index count?

12

I want to count the number of data. As in the example, 8 pieces

13

This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.