Create View Table syntax error

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Create View Table syntax error

Author

Topic

soul_killer
Starting Member

1 Post

Posted - 2012-03-22 : 07:34:14

hi..
below this is my working sql statement. i need to convert this to a VIEW table format.


SELECT a.item, b.item_id, b.item_name, a.certification_id, c.certification_type,  a.certification_serial_no, a.certification_status, a.row_id 
FROM 
(SELECT mitrace_business_location_certification.business_location_id AS item_id, 'GLN' AS item, certification_id, certification_serial_no, certification_status, id AS row_id FROM mitrace_business_location_certification WHERE  certification_status IS NOT NULL AND deleted=FALSE
UNION 
SELECT  mitrace_epc_certification.epc_id AS item_id, 'GTIN' AS item, certification_id, certification_serial_no, epc_certification_status AS certification_status, id AS row_id FROM mitrace_epc_certification WHERE epc_certification_status IS NOT NULL AND deleted=FALSE) a

INNER JOIN (SELECT mitrace_business_location.location_code AS item_name, 'GLN' AS item, id AS item_id FROM mitrace_business_location WHERE location_code IS NOT NULL AND deleted=FALSE
UNION
SELECT mitrace_epc.code AS item_name, 'GTIN' AS item, id AS item_id FROM mitrace_epc WHERE id IS NOT NULL AND deleted=FALSE) b 
ON b.item_id=a.item_id

INNER JOIN mitrace_certification c 
ON c.id=a.certification_id;

When i changed it, it gives syntax error at the 1st inner join.


DROP TABLE IF EXISTS mitrace_view_epc_bizloc_certs;
CREATE OR REPLACE
VIEW mitrace_view_epc_bizloc_certs AS SELECT a.item, b.item_id, b.item_name, a.certification_id, c.certification_type,  a.certification_serial_no, a.certification_status, a.row_id, 

(SELECT mitrace_business_location_certification.business_location_id AS item_id, 'GLN' AS item, certification_id, certification_serial_no, certification_status, id AS row_id FROM mitrace_business_location_certification WHERE  certification_status IS NOT NULL AND deleted=FALSE
UNION 
SELECT  mitrace_epc_certification.epc_id AS item_id, 'GTIN' AS item, certification_id, certification_serial_no, epc_certification_status AS certification_status, id AS row_id FROM mitrace_epc_certification WHERE epc_certification_status IS NOT NULL AND deleted=FALSE) a

INNER JOIN 
(SELECT mitrace_business_location.location_code AS item_name, 'GLN' AS item, id AS item_id FROM mitrace_business_location WHERE location_code IS NOT NULL AND deleted=FALSE
UNION
SELECT mitrace_epc.code AS item_name, 'GTIN' AS item, id AS item_id FROM mitrace_epc WHERE id IS NOT NULL AND deleted=FALSE) b 
ON b.item_id=a.item_id

INNER JOIN mitrace_certification c 
ON c.id=a.certification_id;

please help....

Ifor
Aged Yak Warrior

700 Posts

Posted - 2012-03-22 : 08:26:34

CREATE OR REPLACE VIEW is Oracle syntax.

For MS SQL use either CREATE VIEW or ALTER VIEW

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-22 : 15:23:21

also

DROP TABLE IF EXISTS mitrace_view_epc_bizloc_certs;

should be


IF EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME='mitrace_view_epc_bizloc_certs')
  DROP VIEW mitrace_view_epc_bizloc_certs
GO
...

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-03-22 : 15:31:46

Are you using SQL Server or some other database platform (like Oracle)?

I suspect Oracle, because that SELECT is not valid SQL Server syntax.

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources