Source language: Translate to:

Create a View

Questions about our Advanced Database plug-in

Moderator: Neosoft Support

Create a View

Postby antonio45 » Mon Jul 23, 2007 7:39 am

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
antonio45
 
Posts: 42
Joined: Fri Mar 31, 2006 11:37 am

Postby Neosoft Support » Mon Jul 23, 2007 10:18 pm

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.
NeoSoft Support
Neosoft Support
NeoSoft Team
 
Posts: 5593
Joined: Thu Mar 31, 2005 10:48 pm
Location: Oregon, USA

Create a View

Postby antonio45 » Mon Jul 23, 2007 10:56 pm

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
 
Posts: 42
Joined: Fri Mar 31, 2006 11:37 am

Create a View

Postby antonio45 » Mon Jul 23, 2007 11:13 pm

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 ?
antonio45
 
Posts: 42
Joined: Fri Mar 31, 2006 11:37 am

Postby Neosoft Support » Tue Jul 24, 2007 8:49 pm

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.
NeoSoft Support
Neosoft Support
NeoSoft Team
 
Posts: 5593
Joined: Thu Mar 31, 2005 10:48 pm
Location: Oregon, USA

Postby christian » Fri Jul 27, 2007 3:42 am

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
User avatar
christian
 
Posts: 39
Joined: Sat Apr 02, 2005 9:25 am
Location: Germany


Return to NeoBookDBPro

Who is online

Users browsing this forum: No registered users and 2 guests

cron