I believe the reason to why you get the error message is because in the first way of creating the table, you are referencing each column by itself. Which means there will need to be two Keys in the existing table for this CREATE statement to work. However the existing table only has one Key for both columns together (id_p, id_c).
So since in the existing ...
There is a security consequence to setting SQL92_SECURITY to false.
The configuration option SQL92_SECURITY specifies whether table-level
SELECT privileges are required to execute an update or delete that
references table column values. If this option is disabled (set to
Other than the obvious solution (grant select on data.xtest to app;), you can revert to the old behavior by setting SQL92_SECURITY to FALSE. In 11.2, its default value was FALSE, but in 19c, its default value is TRUE.
alter system set sql92_security=false scope=spfile;
Then restart the database, as this parameter can not be changed online.
Type conversion in expression
(CONVERT(nvarchar(23),[soh].[SalesOrderID],0)) may affect
"CardinalityEstimate" in query plan choice
Since you are not aware about this warning message.It is important to point other example where this warning message MATTER.
When there is data type mismatch between 2 Join condition then also you get this error.