postGre SQL requesting type cast when datatypes match?
So I've been trying this query here:
SELECT
account.customer_client_code,
account.client_account_number,
account.first_name,
account.last_name,
debt_trans.trans_date,
debt_trans.debt_id,
debt_trans.debt_trans_id,
debt_trans.receiver,
debt_trans.type_name,
debt_trans.client_id,
debt_trans.amount
FROM
debt_trans
INNER JOIN
account
ON
account.client_id= debt_trans.client_id
/* Both fields here are of int8 datatype */
WHERE
debt_trans.customer_id = 'CMC'
and
account.customer_client_code like ('UBC2903','UBCO')
and
debt_trans.type_name='Credit Card'
and
debt_trans.operator = 'RECEIVED'
/*All of these fields here are of varchar datatype */
But I've checked and double checked all the datatypes and there shouldn't be any type casting. The error message I get is the following:
"ERROR: operator does not exist: character varying ~~ record Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts. Position: 492"
Any hints on what's the problem?
sql postgresql
asked Nov 24 '18 at 18:13
Rafa RegoRafa Rego
11
add a comment |
So I've been trying this query here:
SELECT
account.customer_client_code,
account.client_account_number,
account.first_name,
account.last_name,
debt_trans.trans_date,
debt_trans.debt_id,
debt_trans.debt_trans_id,
debt_trans.receiver,
debt_trans.type_name,
debt_trans.client_id,
debt_trans.amount
FROM
debt_trans
INNER JOIN
account
ON
account.client_id= debt_trans.client_id
/* Both fields here are of int8 datatype */
WHERE
debt_trans.customer_id = 'CMC'
and
account.customer_client_code like ('UBC2903','UBCO')
and
debt_trans.type_name='Credit Card'
and
debt_trans.operator = 'RECEIVED'
/*All of these fields here are of varchar datatype */
But I've checked and double checked all the datatypes and there shouldn't be any type casting. The error message I get is the following:
"ERROR: operator does not exist: character varying ~~ record Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts. Position: 492"
Any hints on what's the problem?
sql postgresql
asked Nov 24 '18 at 18:13
Rafa RegoRafa Rego
11
Can you determine which character is at position 492?
– nachbar
Nov 24 '18 at 21:21
add a comment |
So I've been trying this query here:
SELECT
account.customer_client_code,
account.client_account_number,
account.first_name,
account.last_name,
debt_trans.trans_date,
debt_trans.debt_id,
debt_trans.debt_trans_id,
debt_trans.receiver,
debt_trans.type_name,
debt_trans.client_id,
debt_trans.amount
FROM
debt_trans
INNER JOIN
account
ON
account.client_id= debt_trans.client_id
/* Both fields here are of int8 datatype */
WHERE
debt_trans.customer_id = 'CMC'
and
account.customer_client_code like ('UBC2903','UBCO')
and
debt_trans.type_name='Credit Card'
and
debt_trans.operator = 'RECEIVED'
/*All of these fields here are of varchar datatype */
But I've checked and double checked all the datatypes and there shouldn't be any type casting. The error message I get is the following:
"ERROR: operator does not exist: character varying ~~ record Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts. Position: 492"
Any hints on what's the problem?
sql postgresql
asked Nov 24 '18 at 18:13
Rafa RegoRafa Rego
11
So I've been trying this query here:
SELECT
account.customer_client_code,
account.client_account_number,
account.first_name,
account.last_name,
debt_trans.trans_date,
debt_trans.debt_id,
debt_trans.debt_trans_id,
debt_trans.receiver,
debt_trans.type_name,
debt_trans.client_id,
debt_trans.amount
FROM
debt_trans
INNER JOIN
account
ON
account.client_id= debt_trans.client_id
/* Both fields here are of int8 datatype */
WHERE
debt_trans.customer_id = 'CMC'
and
account.customer_client_code like ('UBC2903','UBCO')
and
debt_trans.type_name='Credit Card'
and
debt_trans.operator = 'RECEIVED'
/*All of these fields here are of varchar datatype */
But I've checked and double checked all the datatypes and there shouldn't be any type casting. The error message I get is the following:
"ERROR: operator does not exist: character varying ~~ record Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts. Position: 492"
Any hints on what's the problem?
sql postgresql
sql postgresql
asked Nov 24 '18 at 18:13
Rafa RegoRafa Rego
11
asked Nov 24 '18 at 18:13
Rafa RegoRafa Rego
11
edited Nov 24 '18 at 18:31
Rafa Rego
asked Nov 24 '18 at 18:13
Rafa RegoRafa Rego
11
asked Nov 24 '18 at 18:13
Rafa RegoRafa Rego
11
asked Nov 24 '18 at 18:13
Rafa RegoRafa Rego
11
11
Can you determine which character is at position 492?
– nachbar
Nov 24 '18 at 21:21
add a comment |
Can you determine which character is at position 492?
– nachbar
Nov 24 '18 at 21:21
Can you determine which character is at position 492?
– nachbar
Nov 24 '18 at 21:21
Can you determine which character is at position 492?
– nachbar
Nov 24 '18 at 21:21
add a comment |
1 Answer
1
active
oldest
votes
The error message is pertaining to this line:
account.customer_client_code like ('UBC2903','UBCO')
If you are trying to see if account.customer_client_code is one of 'UBC2903' or 'UBCO', then you should use the IN operator:
account.customer_client_code IN ('UBC2903','UBCO')
answered Nov 24 '18 at 18:17
jkatz05jkatz05
811
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%2f53461056%2fpostgre-sql-requesting-type-cast-when-datatypes-match%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 error message is pertaining to this line:
account.customer_client_code like ('UBC2903','UBCO')
If you are trying to see if account.customer_client_code is one of 'UBC2903' or 'UBCO', then you should use the IN operator:
account.customer_client_code IN ('UBC2903','UBCO')
answered Nov 24 '18 at 18:17
jkatz05jkatz05
811
add a comment |
The error message is pertaining to this line:
account.customer_client_code like ('UBC2903','UBCO')
If you are trying to see if account.customer_client_code is one of 'UBC2903' or 'UBCO', then you should use the IN operator:
account.customer_client_code IN ('UBC2903','UBCO')
answered Nov 24 '18 at 18:17
jkatz05jkatz05
811
add a comment |
The error message is pertaining to this line:
account.customer_client_code like ('UBC2903','UBCO')
If you are trying to see if account.customer_client_code is one of 'UBC2903' or 'UBCO', then you should use the IN operator:
account.customer_client_code IN ('UBC2903','UBCO')
answered Nov 24 '18 at 18:17
jkatz05jkatz05
811
The error message is pertaining to this line:
account.customer_client_code like ('UBC2903','UBCO')
If you are trying to see if account.customer_client_code is one of 'UBC2903' or 'UBCO', then you should use the IN operator:
account.customer_client_code IN ('UBC2903','UBCO')
answered Nov 24 '18 at 18:17
jkatz05jkatz05
811
answered Nov 24 '18 at 18:17
jkatz05jkatz05
811
answered Nov 24 '18 at 18:17
jkatz05jkatz05
811
answered Nov 24 '18 at 18:17
jkatz05jkatz05
811
811
add a comment |
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%2f53461056%2fpostgre-sql-requesting-type-cast-when-datatypes-match%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
Can you determine which character is at position 492?
– nachbar
Nov 24 '18 at 21:21