Create a View

[antonio45]

Hi,

I want to create a view in MSSQL like this ;

create view Invoices AS
SELECT Orders.ShipName, Orders.ShipAddress, Customers.CompanyName AS CustomerName,(FirstName + ' ' + LastName) AS Salesperson,
Shippers.CompanyName As ShipperName,
"Order Details".ProductID, Products.ProductName, "Order Details".UnitPrice, "Order Details".Quantity,
"Order Details".Discount,
(CONVERT(money,("Order Details".UnitPrice*Quantity*(1-Discount)/100))*100) AS ExtendedPrice, Orders.Freight
FROM Shippers INNER JOIN
(Products INNER JOIN
(
(Employees INNER JOIN
(Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID)
ON Employees.EmployeeID = Orders.EmployeeID)
INNER JOIN "Order Details" ON Orders.OrderID = "Order Details".OrderID)
ON Products.ProductID = "Order Details".ProductID)
ON Shippers.ShipperID = Orders.ShipVia



I used dbpExecSQL action but I coudn't be succesfully. Can you Help me ?

Mehmet BAKITKAL

[Neosoft Support]

Can you execute your code on the server or as a stored procedure? Any error messages? Have you tried dbpCreateView?

We can help with DBPro questions and problems, but for questions strictly about SQL syntax you may have better luck visiting an SQL specific forum.

[antonio45]

Hi,

It is working at Query Analyzer but it is not working in Dbpro. I need it in dbpro. If you don't support 'Create view' in more than 1 table I must know .

Thank you

Mehmet BAKITKAL

[antonio45]

I want to make 'Create View' action as a variable like this.

CREATE VIEW vw_ViewTable
AS
SELECT [ViewColumns]
FROM [MasterTable]
LEFT OUTER JOIN [DetailTable]
ON [MasterTable].[MasterLinkField] = [DetailTable].[DetailLinkField]

And user can define master table, detail table , master link field, detail link field in my application. So I want to create a view in dbpro and show this view in a grid.

dbpCreatView action is using only one table.Can I use Inner Join or Left Outer Join while usnig more than 1 table in this action ?

[Neosoft Support]

DBPro doesn't actually execute any SQL code itself. In most cases it simply passes your code on to the database driver which passes it along to the database server. The server executes the code. If the code won't execute correctly, it could have something to do with the driver.

For complicated code, you might have better luck creating a stored procedure which executes entirely on the server. Then once your view is created, you should be able to open it with dbpOpenTable. In most situations a view can be accessed just like a table.

The built-in dbpCreatView action only works with a single table. Behind the scenes, dbpCreatView builds a simple SQL statement and sends it to the server.

[christian]

Hello,
it is a good idea to first test any SQL-command code in the administration tool which comes with your SQL-database installation program (PGADMIN for postgreSQL), before executing it as SQLEXEC. So you get the error line and character-no.
Christian