SQL script to create a view

Question

SQL script to create a view

Phil Henry

Mr or Mrs. 500

Points: 528
More actions
September 15, 2004 at 6:07 am

#63430

Im a real newbie and amateur here so please forgive the basic question I ask
SQL2000 -
I have a script that delivers a SQL view of our Sales Invoice'daybook' - discluding any sales invoice line that has a nominal code of A200, and also discluding any line where the Sales/Job contains a Purchase Order line referring to work placed out as opposed to our producing it ourselves. The script is :

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[dev_ch_invoicedaybook]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[dev_ch_invoicedaybook]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE VIEW dbo.dev_ch_invoicedaybook
AS
SELECT     InvoiceLines.Nominal, InvoiceHead.JobNo, InvoiceHead.InvoiceNo, InvoiceHead.Datedb, MainJobDetails.Ref1, MainJobDetails.Ref3,
                      InvoiceHead.AccountName, InvoiceHead.IsCreditNote,
                      SUM(CASE WHEN InvoiceLines.Nominal = 'A200' THEN 0 ELSE ISNULL(((InvoiceHead.IsCreditNote - 1) * - 2 - 1) * (InvoiceLines.NettAmount), 0) END)
                      AS Nett, (CASE WHEN EXISTS
                          (SELECT     *
                            FROM          PurchaseOrderLines
                            WHERE      PurchaseOrderLines.JobNo = InvoiceHead.JobNo AND PurchaseOrderLines.ProductCode IN ('out printwebk', 'out printb1j', 'out printb1k',
                                                   'out printb2j', 'out printb2k', 'out printb1qty', 'outwebperk', 'outwebjob', 'out printwebj')) THEN 1 ELSE 0 END) AS OutworkExists
FROM         InvoiceHead INNER JOIN
                      InvoiceLines ON InvoiceLines.InvoiceNo = InvoiceHead.InvoiceNo AND isnull(InvoiceHead.JobNo, '') = isnull(InvoiceLines.JobNo, '') LEFT OUTER JOIN
                      MainJobDetails ON MainJobDetails.JobNo = InvoiceHead.JobNo
WHERE     InvoiceLines.Nominal <> 'A200'
GROUP BY InvoiceHead.JobNo, InvoiceHead.InvoiceNo, InvoiceHead.Datedb, MainJobDetails.Ref1, MainJobDetails.Ref3, InvoiceHead.AccountName,
                      InvoiceHead.IsCreditNote, InvoiceLines.Nominal
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

I then write a Crystal report based upon the content of that view.

I now want to recreate that view - but this time - still discluding the A200 nominalled lines, but including evreything else.

If I just delete the outwork expression

(CASE WHEN EXISTS
                          (SELECT     *
                            FROM          PurchaseOrderLines
                            WHERE      PurchaseOrderLines.JobNo = InvoiceHead.JobNo AND PurchaseOrderLines.ProductCode IN ('out printwebk', 'out printb1j', 'out printb1k',
                                                   'out printb2j', 'out printb2k', 'out printb1qty', 'outwebperk', 'outwebjob', 'out printwebj')) THEN 1 ELSE 0 END)

I get an error message
Server: Msg 156, Level 15, State 1, Procedure dev_ch_invoicedaybook, Line 7
Incorrect syntax near the keyword 'AS'.
If I delete the as nett and everything else the script works but then I havent got the nett values which I do need to report on, as part of the view -

what am I doing wrong?

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

AJ Ahrens SSC-Insane Points: 20676 More actions · Answer 1

Can you please post the revised code that you have removed parts from so we can see where the syntax error is coming from?

Good Hunting!

AJ Ahrens

webmaster@kritter.net

Phil Henry Mr or Mrs. 500 Points: 528 More actions · Answer 2

thx for your help offer

Im now sorted......

Typical - you look at a problem for ages and cannot see wood for trees

then as soon as you ask for help- the answer hits you in the face

In my case - of the items I removed, I left behind a comma and a item line reference I didnt want to see

My new script works a treat

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[dev_ch_invoicedaybookmain]') and OBJECTPROPERTY(id,

N'IsView') = 1)

drop view [dbo].[dev_ch_invoicedaybookmain]

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_NULLS ON

GO

CREATE VIEW dbo.dev_ch_invoicedaybookmain

AS

SELECT InvoiceLines.Nominal, InvoiceHead.JobNo, InvoiceHead.InvoiceNo, InvoiceHead.Datedb, MainJobDetails.Ref1,

MainJobDetails.Ref3,

InvoiceHead.AccountName, InvoiceHead.IsCreditNote,

SUM(CASE WHEN InvoiceLines.Nominal = 'A200' THEN 0 ELSE ISNULL(((InvoiceHead.IsCreditNote - 1) * - 2

- 1) * (InvoiceLines.NettAmount), 0) END)

AS Nett

GROUP BY InvoiceHead.JobNo, InvoiceHead.InvoiceNo, InvoiceHead.Datedb, MainJobDetails.Ref1, MainJobDetails.Ref3,

InvoiceHead.AccountName,

InvoiceHead.IsCreditNote, InvoiceLines.Nominal

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO