Why does select with case and union return an empty array with node and sqlite
I've got a problem with a sqlite query on node. The query with SqliteStudio is working, but with Node.js I get an empty array.
Here's the query:
const query =`
SELECT 'telephone' AS type,telephone AS data,CASE
WHEN telephone = (
SELECT data FROM customers_verified_contact_infos
WHERE type='telephone' AND data=j.telephone AND customer_id = ?
) THEN 'true'
ELSE 'false'
END AS verified
FROM customers j WHERE customer_id = ? AND telephone IS NOT NULL
UNION
SELECT 'email' AS type,email AS data,CASE
WHEN email = (
SELECT data FROM customers_verified_contact_infos
WHERE type='email' AND data=email AND customer_id = ?
) THEN 'true'
ELSE 'false'
END AS verified
FROM customers WHERE customer_id = ? AND email IS NOT NULL
UNION
SELECT 'fax' AS type,fax AS data,CASE
WHEN fax = (
SELECT data FROM customers_verified_contact_infos
WHERE type='fax' AND data=fax AND customer_id = ?
) THEN 'true'
ELSE 'false'
END AS verified
FROM customers WHERE customer_id = ? AND fax IS NOT NULL
UNION
SELECT type,data, 'true' AS verified
FROM customers_verified_contact_infos WHERE customer_id = ?
`;
I request two tables:
customers, with some customer information
customers_verified_contact_infos, with list contact information verified
Contact information can be in both or just one table.
The result is a list of rows with data, the type of data, and if it's been verified or not.
Now, the Node.js request:
db.all(query,[customer_id], (err, data) => {
console.log(data);
});
The data is an empty array and err = null.
With SqliteStudio, with the same query and customer, I get:
I tried SELECT by SELECT, and with case I didn't get a result. I don't find anything on internet which can help me.
If someone can help or give me advice, I'd appreciate it.
Thanks
javascript node.js sqlite sqlite3
edited Nov 20 '18 at 17:54
Barmar
429k36253353
asked Nov 20 '18 at 17:26
ZleitedZleited
286
add a comment |
I've got a problem with a sqlite query on node. The query with SqliteStudio is working, but with Node.js I get an empty array.
Here's the query:
const query =`
SELECT 'telephone' AS type,telephone AS data,CASE
WHEN telephone = (
SELECT data FROM customers_verified_contact_infos
WHERE type='telephone' AND data=j.telephone AND customer_id = ?
) THEN 'true'
ELSE 'false'
END AS verified
FROM customers j WHERE customer_id = ? AND telephone IS NOT NULL
UNION
SELECT 'email' AS type,email AS data,CASE
WHEN email = (
SELECT data FROM customers_verified_contact_infos
WHERE type='email' AND data=email AND customer_id = ?
) THEN 'true'
ELSE 'false'
END AS verified
FROM customers WHERE customer_id = ? AND email IS NOT NULL
UNION
SELECT 'fax' AS type,fax AS data,CASE
WHEN fax = (
SELECT data FROM customers_verified_contact_infos
WHERE type='fax' AND data=fax AND customer_id = ?
) THEN 'true'
ELSE 'false'
END AS verified
FROM customers WHERE customer_id = ? AND fax IS NOT NULL
UNION
SELECT type,data, 'true' AS verified
FROM customers_verified_contact_infos WHERE customer_id = ?
`;
I request two tables:
customers, with some customer information
customers_verified_contact_infos, with list contact information verified
Contact information can be in both or just one table.
The result is a list of rows with data, the type of data, and if it's been verified or not.
Now, the Node.js request:
db.all(query,[customer_id], (err, data) => {
console.log(data);
});
The data is an empty array and err = null.
With SqliteStudio, with the same query and customer, I get:
I tried SELECT by SELECT, and with case I didn't get a result. I don't find anything on internet which can help me.
If someone can help or give me advice, I'd appreciate it.
Thanks
javascript node.js sqlite sqlite3
edited Nov 20 '18 at 17:54
Barmar
429k36253353
asked Nov 20 '18 at 17:26
ZleitedZleited
286
add a comment |
I've got a problem with a sqlite query on node. The query with SqliteStudio is working, but with Node.js I get an empty array.
Here's the query:
const query =`
SELECT 'telephone' AS type,telephone AS data,CASE
WHEN telephone = (
SELECT data FROM customers_verified_contact_infos
WHERE type='telephone' AND data=j.telephone AND customer_id = ?
) THEN 'true'
ELSE 'false'
END AS verified
FROM customers j WHERE customer_id = ? AND telephone IS NOT NULL
UNION
SELECT 'email' AS type,email AS data,CASE
WHEN email = (
SELECT data FROM customers_verified_contact_infos
WHERE type='email' AND data=email AND customer_id = ?
) THEN 'true'
ELSE 'false'
END AS verified
FROM customers WHERE customer_id = ? AND email IS NOT NULL
UNION
SELECT 'fax' AS type,fax AS data,CASE
WHEN fax = (
SELECT data FROM customers_verified_contact_infos
WHERE type='fax' AND data=fax AND customer_id = ?
) THEN 'true'
ELSE 'false'
END AS verified
FROM customers WHERE customer_id = ? AND fax IS NOT NULL
UNION
SELECT type,data, 'true' AS verified
FROM customers_verified_contact_infos WHERE customer_id = ?
`;
I request two tables:
customers, with some customer information
customers_verified_contact_infos, with list contact information verified
Contact information can be in both or just one table.
The result is a list of rows with data, the type of data, and if it's been verified or not.
Now, the Node.js request:
db.all(query,[customer_id], (err, data) => {
console.log(data);
});
The data is an empty array and err = null.
With SqliteStudio, with the same query and customer, I get:
I tried SELECT by SELECT, and with case I didn't get a result. I don't find anything on internet which can help me.
If someone can help or give me advice, I'd appreciate it.
Thanks
javascript node.js sqlite sqlite3
edited Nov 20 '18 at 17:54
Barmar
429k36253353
asked Nov 20 '18 at 17:26
ZleitedZleited
286
I've got a problem with a sqlite query on node. The query with SqliteStudio is working, but with Node.js I get an empty array.
Here's the query:
const query =`
SELECT 'telephone' AS type,telephone AS data,CASE
WHEN telephone = (
SELECT data FROM customers_verified_contact_infos
WHERE type='telephone' AND data=j.telephone AND customer_id = ?
) THEN 'true'
ELSE 'false'
END AS verified
FROM customers j WHERE customer_id = ? AND telephone IS NOT NULL
UNION
SELECT 'email' AS type,email AS data,CASE
WHEN email = (
SELECT data FROM customers_verified_contact_infos
WHERE type='email' AND data=email AND customer_id = ?
) THEN 'true'
ELSE 'false'
END AS verified
FROM customers WHERE customer_id = ? AND email IS NOT NULL
UNION
SELECT 'fax' AS type,fax AS data,CASE
WHEN fax = (
SELECT data FROM customers_verified_contact_infos
WHERE type='fax' AND data=fax AND customer_id = ?
) THEN 'true'
ELSE 'false'
END AS verified
FROM customers WHERE customer_id = ? AND fax IS NOT NULL
UNION
SELECT type,data, 'true' AS verified
FROM customers_verified_contact_infos WHERE customer_id = ?
`;
I request two tables:
customers, with some customer information
customers_verified_contact_infos, with list contact information verified
Contact information can be in both or just one table.
The result is a list of rows with data, the type of data, and if it's been verified or not.
Now, the Node.js request:
db.all(query,[customer_id], (err, data) => {
console.log(data);
});
The data is an empty array and err = null.
With SqliteStudio, with the same query and customer, I get:
I tried SELECT by SELECT, and with case I didn't get a result. I don't find anything on internet which can help me.
If someone can help or give me advice, I'd appreciate it.
Thanks
javascript node.js sqlite sqlite3
javascript node.js sqlite sqlite3
edited Nov 20 '18 at 17:54
Barmar
429k36253353
asked Nov 20 '18 at 17:26
ZleitedZleited
286
edited Nov 20 '18 at 17:54
Barmar
429k36253353
asked Nov 20 '18 at 17:26
ZleitedZleited
286
edited Nov 20 '18 at 17:54
Barmar
429k36253353
edited Nov 20 '18 at 17:54
Barmar
429k36253353
edited Nov 20 '18 at 17:54
Barmar
429k36253353
429k36253353
asked Nov 20 '18 at 17:26
ZleitedZleited
286
asked Nov 20 '18 at 17:26
ZleitedZleited
286
asked Nov 20 '18 at 17:26
ZleitedZleited
286
286
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
The query has 7 ? placeholders, but the array [customer_id] only has one element. You need to repeat it for every placeholder.
db.all(query, Array(7).fill(customer_id), (err, data) => {
console.log(data);
});
answered Nov 20 '18 at 17:57
BarmarBarmar
429k36253353
You can also bind one value to all 7 by using?1instead of just?.
– Shawn
Nov 20 '18 at 18:34
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53398360%2fwhy-does-select-with-case-and-union-return-an-empty-array-with-node-and-sqlite%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
The query has 7 ? placeholders, but the array [customer_id] only has one element. You need to repeat it for every placeholder.
db.all(query, Array(7).fill(customer_id), (err, data) => {
console.log(data);
});
answered Nov 20 '18 at 17:57
BarmarBarmar
429k36253353
You can also bind one value to all 7 by using?1instead of just?.
– Shawn
Nov 20 '18 at 18:34
add a comment |
The query has 7 ? placeholders, but the array [customer_id] only has one element. You need to repeat it for every placeholder.
db.all(query, Array(7).fill(customer_id), (err, data) => {
console.log(data);
});
answered Nov 20 '18 at 17:57
BarmarBarmar
429k36253353
You can also bind one value to all 7 by using?1instead of just?.
– Shawn
Nov 20 '18 at 18:34
add a comment |
The query has 7 ? placeholders, but the array [customer_id] only has one element. You need to repeat it for every placeholder.
db.all(query, Array(7).fill(customer_id), (err, data) => {
console.log(data);
});
answered Nov 20 '18 at 17:57
BarmarBarmar
429k36253353
The query has 7 ? placeholders, but the array [customer_id] only has one element. You need to repeat it for every placeholder.
db.all(query, Array(7).fill(customer_id), (err, data) => {
console.log(data);
});
answered Nov 20 '18 at 17:57
BarmarBarmar
429k36253353
answered Nov 20 '18 at 17:57
BarmarBarmar
429k36253353
answered Nov 20 '18 at 17:57
BarmarBarmar
429k36253353
answered Nov 20 '18 at 17:57
BarmarBarmar
429k36253353
429k36253353
You can also bind one value to all 7 by using?1instead of just?.
– Shawn
Nov 20 '18 at 18:34
add a comment |
You can also bind one value to all 7 by using?1instead of just?.
– Shawn
Nov 20 '18 at 18:34
You can also bind one value to all 7 by using
?1 instead of just ?.– Shawn
Nov 20 '18 at 18:34
You can also bind one value to all 7 by using
?1 instead of just ?.– Shawn
Nov 20 '18 at 18:34
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53398360%2fwhy-does-select-with-case-and-union-return-an-empty-array-with-node-and-sqlite%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
