# Help with a Select Query Function

**URL:** <https://discourse.nodered.org/t/help-with-a-select-query-function/22341>\
**Category:** General\
**Created:** [27 February 2020 20:47 UTC](https://discourse.nodered.org/t/help-with-a-select-query-function/22341 "2020-02-27T20:47:55Z")\
**Posts on this page:** 14\
**Page:** 1

<div class="post-metadata">

**Author:** ![SamBozman](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/sambozman/32/17606_2.png) [@SamBozman](https://discourse.nodered.org/u/SamBozman)\
**Post date:** [27 February 2020 20:47 UTC](https://discourse.nodered.org/t/help-with-a-select-query-function/22341/1 "2020-02-27T20:47:55Z")

</div>

Hi, I need help formatting a query from a node-red function node to a sqlite database. I have already designed and verified my database and the query I am trying to use works properly within sqlite. The problem is I do not know how to properly format the query to use it within the node-red function node.

Here is the query as I use it in sqlite:  
**Select Contacts.Email FROM Contacts WHERE Contacts.Status = "PMI\_Notify";**

Here is my attempt at using it with in node-red:  
**msg.topic = "Select Contacts.Email FROM Contacts WHERE Contacts.Status = "PMI\_Notify";"**

I get an error as : "SyntaxError: Unexpected identifier"  
I know, from my research it is something to do with the fact that "PMI\_Notify" has quotes around it but I can not figure out how to properly format the query.  
Can anyone help?

Thanks  
Clan

---

<div class="post-metadata">

**Author:** ![bakman2](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/bakman2/32/6207_2.png) [@bakman2](https://discourse.nodered.org/u/bakman2)\
**Post date:** [27 February 2020 20:51 UTC](https://discourse.nodered.org/t/help-with-a-select-query-function/22341/2 "2020-02-27T20:51:53Z")

</div>

Double quotes. Enclose the sql statement in double quotes and the field values in single quotes.

---

<div class="post-metadata">

**Author:** ![TotallyInformation](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/totallyinformation/32/31_2.png) [@TotallyInformation](https://discourse.nodered.org/u/TotallyInformation)\
**Post date:** [27 February 2020 20:52 UTC](https://discourse.nodered.org/t/help-with-a-select-query-function/22341/3 "2020-02-27T20:52:15Z")

</div>

Or the other way around 😀

---

<div class="post-metadata">

**Author:** ![SamBozman](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/sambozman/32/17606_2.png) [@SamBozman](https://discourse.nodered.org/u/SamBozman)\
**Post date:** [27 February 2020 20:56 UTC](https://discourse.nodered.org/t/help-with-a-select-query-function/22341/4 "2020-02-27T20:56:38Z")

</div>

I just tried that as:  
**msg.topic = "Select Contacts.Email FROM Contacts = 'PMI\_Notify' ;"**  
However, I now get an error as:  
**"Error: SQLITE\_ERROR: near "=": syntax error"**  
(Thanks for the idea though...🙂

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [27 February 2020 21:01 UTC](https://discourse.nodered.org/t/help-with-a-select-query-function/22341/5 "2020-02-27T21:01:54Z")

</div>

You have lost the WHERE.

---

<div class="post-metadata">

**Author:** ![TotallyInformation](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/totallyinformation/32/31_2.png) [@TotallyInformation](https://discourse.nodered.org/u/TotallyInformation)\
**Post date:** [27 February 2020 21:03 UTC](https://discourse.nodered.org/t/help-with-a-select-query-function/22341/6 "2020-02-27T21:03:42Z")

</div>

Some databases don't like single quotes - I can't remember whether SQLite is sensitive to that but it may be better to use single quotes on the outer string and keep the double quotes for the SQL itself.

---

<div class="post-metadata">

**Author:** ![SamBozman](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/sambozman/32/17606_2.png) [@SamBozman](https://discourse.nodered.org/u/SamBozman)\
**Post date:** [27 February 2020 21:07 UTC](https://discourse.nodered.org/t/help-with-a-select-query-function/22341/7 "2020-02-27T21:07:46Z")

</div>

Thank Colin. Yes, I did forget the 'WHERE' clause and afterwards it works perfectly. Thanks to everyone who tried to help. I don't know what I would do if not for people like you.

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [27 February 2020 21:11 UTC](https://discourse.nodered.org/t/help-with-a-select-query-function/22341/8 "2020-02-27T21:11:59Z")

</div>

I believe that if PMI\_Notify is a column name then it should be in double quotes, if it is a string literal then it should be in single quotes, but if double quotes are used and there is no column PMI\_Notify then it will be interpreted as a literal. I think that this is being deprecated however and later sqlite versions will generate a warning if double quotes are used and there is no matching column name.

---

<div class="post-metadata">

**Author:** ![SamBozman](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/sambozman/32/17606_2.png) [@SamBozman](https://discourse.nodered.org/u/SamBozman)\
**Post date:** [27 February 2020 21:17 UTC](https://discourse.nodered.org/t/help-with-a-select-query-function/22341/9 "2020-02-27T21:17:02Z")

</div>

OK, I will watch for that. In my case it was a literal string referencing a column in my database. Take care

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [27 February 2020 21:26 UTC](https://discourse.nodered.org/t/help-with-a-select-query-function/22341/10 "2020-02-27T21:26:17Z")

</div>

> [@SamBozman](#):
>
> a literal string referencing a column

Not sure what you mean by that. If PMI\_Notify is a column name, so you want to test for the Status and PMI\_Notify columns containing the same value then it should be in double quotes (so the outer quotes must be single). If it is a string and you are testing the Status column to see if it contains that string (I suspect that is what you are doing) then it should be in single quotes.

---

<div class="post-metadata">

**Author:** ![bakman2](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/bakman2/32/6207_2.png) [@bakman2](https://discourse.nodered.org/u/bakman2)\
**Post date:** [27 February 2020 21:36 UTC](https://discourse.nodered.org/t/help-with-a-select-query-function/22341/11 "2020-02-27T21:36:05Z")

</div>

I thought the "proper" way was to use backticks for identifiers and single quotes for strings (i read contradictions about it whether or not is double/single)

```auto
msg.topic = "Select `Email` FROM Contacts WHERE `Status` = 'PMI_Notify';"

```

But most databases have fallback compatbility built in.

---

<div class="post-metadata">

**Author:** ![SamBozman](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/sambozman/32/17606_2.png) [@SamBozman](https://discourse.nodered.org/u/SamBozman)\
**Post date:** [27 February 2020 21:41 UTC](https://discourse.nodered.org/t/help-with-a-select-query-function/22341/12 "2020-02-27T21:41:54Z")

</div>

Sorry, PMI\_Notify is the literal string stored in the 'Status' text column of the database. This is for email contacts that need to be notified of an upcoming PMI. Other IT errors, for instance are sent to other contacts that control programming errors and they have a literal string 'IT' in the status column. Sorry for the confusion.

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [27 February 2020 21:56 UTC](https://discourse.nodered.org/t/help-with-a-select-query-function/22341/13 "2020-02-27T21:56:16Z")

</div>

I think the SQL Standard is to use double quotes for identifiers, and sqlite adheres better to the standard than most, but sqlite also allows backticks for identifiers for compatibility with mysql and even `[]` for compatibility with Microsoft dbs.

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/1X/d073cd938eafa2e558d7c2cd59003b3ef4963033.png) [@system](https://discourse.nodered.org/u/system)\
**Post date:** [12 March 2020 22:02 UTC](https://discourse.nodered.org/t/help-with-a-select-query-function/22341/14 "2020-03-12T22:02:43Z")

</div>

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