Syntax error at or near join

I’m using the following Select statement to retrieve and count the total of each field across multiple tables. However when it comes to executing the statement i get the error «Syntax Error at or near the word join»

Any help would be greatly appreciated

Query:

Select CompanyStatus,Companyname,
        INNER JOIN Company on usersincompany.companyID=company.companyID,
                INNER JOIN company on users.companyID=Company.companyID,
                 INNER JOIN usersincompany on users.userid=usersincompany.userid,
                INNER JOIN users on userstatus.userstatudid=users.userstatusid,
                INNER JOIN users on project.companyid=users.companyid,
                INNER JOIN users on usersession.userid=users.userid,
                INNER JOIN project on template.projectid=project.projectid,
                INNER JOIN project on merchendisingarea.projectid=project.projectid,
                 INNER JOIN merchendisingarea on publishstatus.publishstatusid=merchendisingarea.publishstatusid,
             INNER JOIN template on merchendisingmodule.templateid=template.templateid,
        INNER JOIN company on companyaccountclassification.classificationtypeid=company.classificationtypeid,
sum(distinct users.userid) as TotalUsers,
sum(case when users.userstatusid =2 then 1 else 0 end) as Activeusers,
sum(case when users.userstatusid =3 then 1 else 0 end) as SuspendedUsers,
sum(distinct usersessionid) as TotalLogin,
sum(distinct merchendisingmoduleid) as CurrentModules,
count( merchendisingmodule.createddate) as Modulescreated,
count( merchendisingmodule.updateddate) as Modulesupdated,
sum(distinct merchendisingareaid) as Currentareas,
count( merchendisingarea.createddate) as AreasCreated,
count( merchendisingarea.updateddate) as Areasupdated,
sum(case when publishingstatus.publishstatusid =1 then 1 else 0 end) as SuccessPublished,
sum(case when publishingstatus.publishstatusid =3 then 1 else 0 end) as FailedPublished  

 from users,company,merchendisingmodule,merchendisingarea,publishingstatus, usersession       group by companystatus, companyname

error: syntax error at or near 
length: 95,
  severity: 'ERROR',
  code: '42601',
  detail: undefined,
  hint: undefined,
  position: '122',
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'scan.l',
  line: '1180',
  routine: 'scanner_yyerror'

const createInitialTables = `
        CREATE TABLE authors (
             id UUID NOT NULL,
             author_name VARCHAR(100) NOT NULL UNIQUE CHECK (author_name <> ''),
             author_slug VARCHAR(100) NOT NULL UNIQUE CHECK (author_slug <> ''),
             PRIMARY KEY (id)
        );

        CREATE TABLE posts (
             id UUID NOT NULL,
             post VARCHAR(500) NOT NULL CHECK (post<> ''),
             post_slug VARCHAR(500) NOT NULL CHECK (post_slug <> ''),
             author_id UUID NOT NULL,
             PRIMARY KEY (id),
             CONSTRAINT fk_authors FOREIGN KEY(author_id) REFERENCES authors(id)
        );

        CREATE TABLE categories (
             id UUID NOT NULL,
             category_name VARCHAR(50) NOT NULL CHECK (category_name <> ''),
             category_slug VARCHAR(50) NOT NULL CHECK (category_slug <> ''),
             post_id UUID NOT NULL,
             PRIMARY KEY (id),
             CONSTRAINT fk_posts FOREIGN KEY(post_id) REFERENCES posts(id)
        );

`;

const insertAuthor = async() => {

    try {

        const data       = await fs.readFile( path.join( __dirname + '../../data/data.json' ) );
        const parsedData = JSON.parse( data.toString() );

        const authorID   = short.generate();
        const authorName = parsedData[ 0 ].author;
        const authorSlug = slugify( parsedData[ 0 ].author, {
            strict: true,
            lower: true
        } );

        const insertData = `
            INSERT INTO authors (id, author_name, author_slug) 
            VALUES 
            (${authorID}, ${authorName}, ${authorSlug});
        `;

        await pool.query( insertData );

        console.log( 'Data inserted successfully!' );

    } catch ( e ) {
        console.log( e );
    }
};

insertAuthor();

2021-10-18 01:23:16.885 +06 [5964] ERROR:  syntax error at or near "Paton" at character 122
2021-10-18 01:23:16.885 +06 [5964] STATEMENT:  
                INSERT INTO authors (id, author_name, author_slug) 
                VALUES 
                (an3cxZh8ZD3tdtqG4wuwPR, Alan Paton, alan-paton);

2 ответа

Лучший ответ

INSERT INTO authors (id, author_name, author_slug) 
VALUES 
(an3cxZh8ZD3tdtqG4wuwPR, Alan Paton, alan-paton);

INSERT INTO authors (id, author_name, author_slug) 
VALUES 
('an3cxZh8ZD3tdtqG4wuwPR', 'Alan Paton', 'alan-paton');

const insertSQL = `
  INSERT INTO authors (id, author_name, author_slug) 
  VALUES ($1, $2, $3);
`;
await pool.query( insertSQL, [authorID, authorName, authorSlug] );

create extension "uuid-ossp";

create table authors (
  id uuid primary key default uuid_generate_v4(),

  -- There's no point in arbitrarily limiting the size of your text fields.
  -- They will only use as much space as they need.
  author_name text not null unique check (author_name <> ''),
  author_slug text not null unique check (author_slug <> '')
);

insert into authors (author_name, author_slug) 
values ('Alan Paton', 'alan-paton');

const insertData = `
    INSERT INTO authors (id, author_name, author_slug) 
    VALUES 
    ('${authorID}', '${authorName}', '${authorSlug}');`;  // added the quotes

  Last tested: Feb 2021

Overview

This SQL error generally means that somewhere in the query, there is invalid syntax.
Some common examples:

• Using a database-specific SQL for the wrong database (eg BigQuery supports DATE_ADD, but Redshift supports DATEADD)
• Typo in the SQL (missing comma, misspelled word, etc)
• Missing a sql clause (missed from, join, select, etc)
• An object does not exist in the database or is not accessible from the current query (eg referencing orders.id when there is no orders table joined in the current query, etc)

In some circumstances, the database error message may display extra detail about where the error was raised, which can be helpful in narrowing down where to look.

Error Message

SQL ERROR: syntax error at or near

Troubleshooting

This should generally be the first step to troubleshoot any SQL syntax error in a large query: iteratively comment out blocks of SQL to narrow down where the problem is.

TIP: To make this process easier, change the group by clause to use position references
eg: group by 1,2,3,4,5 instead of group by orders.status, orders.date, to_char(...)...
as well as separate the where and having clauses onto multiple lines.

So for example, say we have the following query:

play_arrow

WITH cte AS (
select id, status, sales_amountfrom orders
)
select status, foo.date, sum(cte.sales_amount), count(*) from cte
join foo on cte.date = foo.date
group by status, foo.date
order by 3 desc

We could start by running just the portion in the CTE:

play_arrow

-- WITH cte AS (
select id, status, sales_amountfrom orders
-- )
-- select status, foo.date, sum(cte.sales_amount), count(*)
-- from cte
-- join foo on cte.date = foo.date
-- group by 1, 2
-- order by 3 desc

Then strip out the aggregates and portions related to them

play_arrow

WITH cte AS (
    select id, status, sales_amountfrom orders
)
select status, foo.date, -- sum(cte.sales_amount), count(*)
from cte
join foo on cte.date = foo.date
-- group by 1, 2
-- order by 3 desc

Iteratively stripping out / adding back in portions of the query until you find the minimum query to trigger the error.

• Lookup functions and syntax If the query is small enough, or if we’ve narrowed the scope enough with 1, google all the functions used in the query and verify that they exist and are being used correctly.

• Verify all objects exist Verify that you’ve joined all tables used in the select, where, and having clause, and that those tables exist in the db. Once we’ve narrowed things down from 1, also check that each column exists in the table specified.

Syntax errors are quite common while coding.

But, things go for a toss when it results in website errors.

PostgreSQL error 42601 also occurs due to syntax errors in the database queries.

At Bobcares, we often get requests from PostgreSQL users to fix errors as part of our Server Management Services.

Today, let’s check PostgreSQL error in detail and see how our Support Engineers fix it for the customers.

What causes error 42601 in PostgreSQL?

PostgreSQL is an advanced database engine. It is popular for its extensive features and ability to handle complex database situations.

Applications like Instagram, Facebook, Apple, etc rely on the PostgreSQL database.

But what causes error 42601?

PostgreSQL error codes consist of five characters. The first two characters denote the class of errors. And the remaining three characters indicate a specific condition within that class.

Here, 42 in 42601 represent the class “Syntax Error or Access Rule Violation“.

In short, this error mainly occurs due to the syntax errors in the queries executed. A typical error shows up as:

Here, the syntax error has occurred in position 119 near the value “parents” in the query.

How we fix the error?

Now let’s see how our PostgreSQL engineers resolve this error efficiently.

Recently, one of our customers contacted us with this error. He tried to execute the following code,

CREATE OR REPLACE FUNCTION prc_tst_bulk(sql text)
RETURNS TABLE (name text, rowcount integer) AS
$$
BEGIN
WITH m_ty_person AS (return query execute sql)
select name, count(*) from m_ty_person where name like '%a%' group by name
union
select name, count(*) from m_ty_person where gender = 1 group by name;
END
$$ LANGUAGE plpgsql;

But, this ended up in PostgreSQL error 42601. And he got the following error message,

ERROR: syntax error at or near "return"
LINE 5: WITH m_ty_person AS (return query execute sql)

Our PostgreSQL Engineers checked the issue and found out the syntax error. The statement in Line 5 was a mix of plain and dynamic SQL. In general, the PostgreSQL query should be either fully dynamic or plain. Therefore, we changed the code as,

RETURN QUERY EXECUTE '
WITH m_ty_person AS (' || sql || $x$)
SELECT name, count(*)::int FROM m_ty_person WHERE name LIKE '%a%' GROUP BY name
UNION
SELECT name, count(*)::int FROM m_ty_person WHERE gender = 1 GROUP BY name$x$;

This resolved the error 42601, and the code worked fine.

[Need more assistance to solve PostgreSQL error 42601?- We’ll help you.]

Conclusion

In short, PostgreSQL error 42601 occurs due to the syntax errors in the code. Today, in this write-up, we have discussed how our Support Engineers fixed this error for our customers.

var google_conversion_label = «owonCMyG5nEQ0aD71QM»;