Ошибка недопустимое имя объекта sql

This is the error message returned:

Msg 208, Level 16, State 1, Line 1
Invalid object name ‘ENG_PREP’.

It happens after I try the following query:

insert into ENG_PREP VALUES('572012-01-1,572012-01-2,572012-01-3,572013-01-1,572013-01-2',
'',
'500',
'',
'A320 P.001-A',
'Removal of the LH Wing Safety Rope',
'',
'',
'',
'0',
'',
'AF',
'12-00-00-081-001',
'',
'',
'',
'',
'',
'',
'' )

NewTable это алиас выборки в конкретном запросе, к нему нельзя обращаться еще раз как к таблице.

Можно сделать например так, используя CTE:

WITH NewTable(Name,Sum_weight) as(
     SELECT Name, SUM(Weight)
       FROM Supplier S, Product P, P_S_Connect PS 
      WHERE S.SupplierID=PS.SupplierID AND P.ProductID=PS.ProductID
      GROUP BY Name
)
SELECT Name 
  FROM NewTable
 WHERE Sum_weight=(SELECT MAX(Sum_weight) FROM NewTable);

Или так, используя оконные функции:

SELECT Name FROM (
    SELECT Name, Sum_weight,
           MAX(Sum_weight) over() as Max_weight
      FROM (
        SELECT Name, SUM(Weight) AS Sum_weight
          FROM Supplier S, Product P, P_S_Connect PS 
         WHERE S.SupplierID=PS.SupplierID AND P.ProductID=PS.ProductID
         GROUP BY Name
      ) A
   ) A
  WHERE Sum_weight=Max_weight

А на стандартном SQL, без расширений типа CTE или оконных функций боюсь только так:

SELECT Name
  FROM Supplier S, Product P, P_S_Connect PS 
 WHERE S.SupplierID=PS.SupplierID AND P.ProductID=PS.ProductID
 GROUP BY Name
HAVING SUM(Weight)=
 (
  SELECT MAX(Sum_weight)
    FROM (
      SELECT SUM(Weight) AS Sum_weight
        FROM Supplier S, Product P, P_S_Connect PS 
       WHERE S.SupplierID=PS.SupplierID AND P.ProductID=PS.ProductID
       GROUP BY Name
    ) A
 )

Да, сорри за глупые вопросы, просто мне нужно как можно скорее научиться писать простые запросы, один хороший человек выслал мне книгу SQL в примерах и задачах, там все очень ясно написано на простых примерах, для таких людей как я. Но, каких то вещей там нет, поэтому приходиться искать компромисс между быстротой освоения базовых запросов и качеством…

Добавлено через 29 секунд
На сайте уже зарегестрировался, спасибо за ресурс))

Добавлено через 21 секунду
КНигу качаю…

Добавлено через 2 минуты
А Вы не знаете где есть учебные задачи и уже готовые БД, чтобы их можно было скчать и работать с ними?

If you encounter error Msg 208, Level 16 “Invalid object name ‘OPENJSON’.”, you are probably trying to use the OPENJSON() function on a database with a compatibility level of less than 130.

OPENJSON() is only available under compatibility level 130 or higher.

To fix this, either increase the compatibility level of your database to 130 or higher, or change to a database that already has the appropriate compatibility level.

Example of Error

Here’s an example of some basic code that will cause this error.

USE Pets;
SELECT * FROM OPENJSON('["Cat","Dog","Bird"]');

Result:

Msg 208, Level 16, State 1, Line 1
Invalid object name 'OPENJSON'.

When your database compatibility level is lower than 130, SQL Server can’t find and run the OPENJSON() function.

In my case, the database I was trying to run this on had a compatibility level of 120.

Check the Compatibility Level of the Database

You can query sys.databases to check the compatibility level of the database (or all databases if you prefer).

SELECT compatibility_level
FROM sys.databases
WHERE name = 'Pets';

Result:

+-----------------------+
| compatibility_level   |
|-----------------------|
| 120                   |
+-----------------------+

As suspected, this database has a compatibility level of less than 130.

Solution 1

The most obvious solution is to increase the compatibility level of the database for which you’re trying to run OPENJSON() against.

ALTER DATABASE Pets  
SET COMPATIBILITY_LEVEL = 150;

Running that code will increase the database’s compatibility level to 150, which is more than high enough to support the OPENJSON() function.

If we check the compatibility level again, we can see that it’s increased to 150.

SELECT compatibility_level
FROM sys.databases
WHERE name = 'Pets';

Result:

+-----------------------+
| compatibility_level   |
|-----------------------|
| 150                   |
+-----------------------+

Now we can run the original code without error.

USE Pets;
SELECT * FROM OPENJSON('["Cat","Dog","Bird"]');

Result:

+-------+---------+--------+
| key   | value   | type   |
|-------+---------+--------|
| 0     | Cat     | 1      |
| 1     | Dog     | 1      |
| 2     | Bird    | 1      |
+-------+---------+--------+

Solution 2

If for some reason you can’t, or don’t want to, change the compatibility level of the database, you could switch to a database that already has the appropriate compatibility level.

Obviously, this may or may not be suitable, depending on whether you need to insert your parsed JSON into the database or not.

Anyway, to do this, you could query sys.databases for a suitable database.

SELECT 
    name,
    compatibility_level
FROM sys.databases;

Result:

+--------------------+-----------------------+
| name               | compatibility_level   |
|--------------------+-----------------------|
| master             | 150                   |
| tempdb             | 150                   |
| model              | 150                   |
| msdb               | 150                   |
| Music              | 150                   |
| Test               | 150                   |
| WideWorldImporters | 130                   |
| World              | 140                   |
| Pets               | 120                   |
+--------------------+-----------------------+

Fortunately in this case, all other databases are 130 or higher. So we could switch to any one of them.

USE World;
SELECT * FROM OPENJSON('["Cat","Dog","Bird"]');

Result:

+-------+---------+--------+
| key   | value   | type   |
|-------+---------+--------|
| 0     | Cat     | 1      |
| 1     | Dog     | 1      |
| 2     | Bird    | 1      |
+-------+---------+--------+

Последнее редактирование модератором: 16.11.2020