No operator matches the given name and argument type

Question

I tried to search in google for what's wrong with my sql statement but with no success!

[Err] ERROR: operator does not exist: character varying * character varying LINE 16: "Quantity" * "Unit Price" as "Total"

HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.

SELECT
"Trade Date",
"Due Date",
"Reference",
"Type",
"Symbol",
to_number("Quantity", '99999999D99')AS "Quantity",
to_number("Unit Price", '99999999D99')AS "Unit Price",
to_number("Amount", '99999999D99')AS "Amount",
to_number("Commission", '99999999D99')AS "Commission",
to_number("Charge", '99999999D99')AS "Charge",
to_number("VAT", '99999999D99')AS "VAT",
to_number("Net Amount", '99999999D99')AS "Net Amount",
"W/H",
to_number("Net Price", '99999999D99')AS "Net Price",
"Quantity" * "Unit Price" as "Total"

FROM
"public".aa

a_horse_with_no_name a_horse_with_no_name · Accepted Answer · 2018-01-22T09:16:49

You can't use a column alias on the same level where you define it. So the reference to "Quantity" in the expression "Quantity" * "Unit Price" refers to the real (varchar) column, not to the alias you have defined.

If you want to access the value of the alias, you need to use a derived table:

SELECT "Trade Date",
       "Due Date",
       "Reference",
       "Type",
       "Symbol",
        "Quantity",
        "Unit Price",
        "Amount",
        "Commission",
        "Charge",
        "VAT",
        "Net Amount",
        "W/H",
        "Net Price",
        "Quantity" * "Unit Price" as "Total"
FROM (
  SELECT "Trade Date",
         "Due Date",
         "Reference",
         "Type",
         "Symbol",
          to_number("Quantity", '99999999D99') AS "Quantity",
          to_number("Unit Price", '99999999D99') AS "Unit Price",
          to_number("Amount", '99999999D99') AS "Amount",
          to_number("Commission", '99999999D99') AS "Commission",
          to_number("Charge", '99999999D99') AS "Charge",
          to_number("VAT", '99999999D99')  AS "VAT",
          to_number("Net Amount", '99999999D99') AS "Net Amount",
          "W/H",
          to_number("Net Price", '99999999D99')AS "Net Price"
  FROM "public".aa        
) t

However the much better solution would be to not store numbers in varchar columns. Numbers should be stored in columns defined as numeric or integer, never in columns defined as varchar or text

No operator matches the given name and argument type

1 Answers