- May 30, 2023
- Posted by: Caroline Ininga Majute
- Category: SAP BUSINESS ONE TIPS
No Comments
What is a View?
An SQL view is an ‘artificial table’ definition created by sql statements and stored in the database as an object. The ‘artificial table’ can be reused over and over again by other queries.
Syntax of a view is as follows:
CREATE VIEW [db_name.] view_name [(column_list)] AS select-statement
An example of a view created by the above syntax is shown below;
CREATE VIEW “CO_LIVE”.”TRAINING1″ ( “Doc. Date”, “DocNum”,”CardCode” )
AS
SELECT A.”Doc. Date”, A.”DocNum”, A.”CardCode”
FROM
(SELECT
‘Invoice’ AS Type
,T0.”DocDate” AS “Doc. Date”
,T0.”DocNum”
,T0.”CardCode”
FROM
OINV T0
INNER JOIN OCRD T1 ON T0.”CardCode” = T1.”CardCode”
LEFT OUTER JOIN INV12 T2 ON T0.”DocEntry” = T2.”DocEntry”
WHERE
T0.”CANCELED” = ‘N’
UNION ALL
SELECT
‘Credit’ AS Type
,T0.”DocDate” AS “Doc. Date”
,T0.”DocNum”
,T0.”CardCode”
FROM
ORIN T0
INNER JOIN OCRD T1 ON T0.”CardCode” = T1.”CardCode”
LEFT OUTER JOIN INV12 T2 ON T0.”DocEntry” = T2.”DocEntry”
WHERE
T0.”CANCELED” = ‘N’
)A
WITH READ ONLY
- To create the above view example in HANA studio or SQL server management studio, right click the database and click ‘Open SQL Console‘ and paste your code.
- The following view’s data can be pulled by a simple sql statement in sap business one query generator as follows;
SELECT * from “COJ_LIVE”.”TRAINING1″
I am Caroline Ininga Majute, a SAP Business One ERP System Consultant based in Nairobi, Kenya. For more inquires about sap business one, contact me at: [email protected]
