How do I dynamically choose which view to create.... 2

[macleod1021]

Hi everyone...I'm having a brain fart here and beano(R) didn't help

I have 2 views created that work great. The problem is that I need to dynamically determine the applications version number (which I've done) and then create the view based off of the version.

To keep this simple, I'm only concerned with 2 versions; 8 & 9.

What I've done so far is created each view and verify that they work individually. What I thought would work was to declare a variable, query for the app's version number and assign it to the variable. Then I used an if...else statement. Here's a shortened down version of what I have:

If (@version = 8)

Set QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE VIEW dbo.ActiveReceipts
AS
SELECT TOP 1000....(rest of view truncated for size)

GO

ELSE
IF (@version = 9)
PRINT 'Version 9'




I removed the other view creation script in order to test to make sure that it was working. This is how I discovered that it was running the first script, no matter what version number was returned. I also used a set command to make sure that @version was 9 and it still created the view.

Any help would be greatly appreciated.

Thanks in advance.

 

[bborissov]

You must put all rowns after IF ... in BEGIN END if you want they to be executed in IF statement:

If (@version = 8)
   BEGIN
        Set QUOTED_IDENTIFIER ON
        GO
        SET ANSI_NULLS ON
        GO
        CREATE VIEW dbo.ActiveReceipts
        AS
        SELECT TOP 1000....(rest of view truncated for size)
        GO
   END
ELSE IF (@version = 9)
    BEGIN
        PRINT 'Version 9'
    END

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[gmmastros]

Boris is right regarding the BEGIN/END statements. When using IF if you only have 1 line that you want to run, then begin/end is optional. If you want multiple lines, then you need to use begin/end.

I think you need to remove the GO's in order to get the result you are looking for.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[macleod1021]

OK. I got the aspect of the using BEGIN/END. I removed the "GO"'s. Now I've discovered another set back

I broke it down to a simple command that gave me the same error as when I ran the long script. The script I ran is

if (true = true)
BEGIN
	CREATE VIEW TestView
	AS
	SELECT * 
	FROM dbo.MDS_SYS003
END

The error I get is Incorrect syntax near 'VIEW'. If I removed the BEGIN/END statements it runs with no problems. Any suggestions???

 

[bborissov]

From BOL:
Creates a virtual table that represents the data in one or more tables in an alternative way. CREATE VIEW must be the first statement in a query batch.
So try:

declare @sql varchar(8000)
if (1 = 1)
   begin
      SET @sql = 'CREATE VIEW TestView
                         AS
                         SELECT *
                         FROM dbo.MDS_SYS003'
      EXEC(@sql)
   end

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[gmmastros]

try...

if 1=1
BEGIN
    exec ('CREATE VIEW TestView
           AS
           SELECT * 
           FROM dbo.MDS_SYS003')
END

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gmmastros]

Sorry Boris.

I didn't see that you had already posted what is, essentially, the same solution.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[bborissov]

Nothing to sorry about. We both trying to help. Like someone said (I can't remeber who) Great minds think alike

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[macleod1021]

And you both have great minds...unfortunately, mine is burned out. I so need a vacation.

I've got the script almost running with your great help. When I run it, I'm getting an Unclosed quotation mark. I'm going cross-eyed looking for it

 

[bborissov]

Take a beer and try tomorow

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[macleod1021]

Works for me...I'm going to tell my boss you said it was OK )

 

[bborissov]

Yea, tell your boss that this is the strogest advice for friday

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[macleod1021]

hmmmm....didn't even get a verbal answer from him

Although another manager in his room said that if I went to the store and got a 12-pack...and at least 2 of them made it back, I could leave! Only problem is that I can't guarantee they would make it back

 

[macleod1021]

OK...fresh Monday Morning eyes have helped nothing

Here's what I've determined so far. If I run the script by itself in QA, it runs fine. However, if I enclose it within an EXEC statement, I begin getting unclosed quotation errors.

Here's the entire script I'm trying to run. The problem (if I'm understanding it correctly) is when I add in literals to create the needed columns in the first select in order to use union. As I stated, running this by itself works fine. When I enclose it in EXEC('...') is when I get the errors. If anyone can point me in the right direction so I can figure this out I'll greatly appreciate it.

CREATE VIEW dbo.MDS_LBL_RECEIPT
AS
SELECT     TOP 11000 dbo.POP10310.PONUMBER AS 'PO_Number', dbo.POP10300.VNDDOCNM AS 'Vendor_Doc', dbo.POP10300.VENDORID AS 'Vendor_ID', 
                      dbo.POP10300.VENDNAME AS 'Vendor_Name', dbo.POP10300.receiptdate AS 'Date', dbo.POP10310.ITEMNMBR AS 'Item_Number', 
                      dbo.IV00101.ITEMDESC AS 'Description', dbo.IV00101.ITMSHNAM AS 'Short_Desc', dbo.IV00101.ITMGEDSC AS 'Gen_Desc', 
                      dbo.IV00101.ITMCLSCD AS 'Class_ID', dbo.POP10310.LOCNCODE AS 'Site_ID', dbo.POP10310.BIN AS 'Bin', 
                      SUM(dbo.POP10310.UMQTYINB * dbo.POP10500.QTYSHPPD) AS 'Qty_in_Base', dbo.POP10310.UOFM AS 'Line_U_of_M', 
                      dbo.POP10300.POPRCTNM AS 'Receipt_No', SUM(dbo.POP10500.QTYSHPPD) AS 'Quantity', '' AS 'Serial/Lot', '' AS 'Lot_Attrib_1', '' AS 'Lot_Attrib_2', 
                      '' AS 'Lot_Attrib_3', '' AS 'Lot_Attrib_4', '' AS 'Lot_Attrib_5'
FROM         dbo.POP10300 INNER JOIN
                      dbo.POP10310 ON dbo.POP10300.POPRCTNM = dbo.POP10310.POPRCTNM INNER JOIN
                      dbo.POP10500 ON dbo.POP10310.PONUMBER = dbo.POP10500.PONUMBER AND dbo.POP10310.POPRCTNM = dbo.POP10500.POPRCTNM AND 
                      dbo.POP10310.RCPTLNNM = dbo.POP10500.RCPTLNNM INNER JOIN
                      dbo.IV00101 ON dbo.POP10310.ITEMNMBR = dbo.IV00101.ITEMNMBR
WHERE     (dbo.IV00101.ITMTRKOP = 1)
GROUP BY dbo.POP10310.PONUMBER, dbo.POP10310.POPRCTNM, dbo.POP10300.VNDDOCNM, dbo.POP10300.VENDORID, dbo.POP10300.VENDNAME, 
                      dbo.POP10300.receiptdate, dbo.POP10310.ITEMNMBR, dbo.IV00101.ITEMDESC, dbo.POP10310.LOCNCODE, dbo.POP10310.BIN, 
                      dbo.POP10310.UOFM, dbo.POP10300.POPRCTNM, dbo.IV00101.ITMSHNAM, dbo.IV00101.ITMGEDSC, dbo.IV00101.ITMCLSCD
UNION
SELECT     dbo.POP10310.PONUMBER AS PO_NUMBER, dbo.POP10300.VNDDOCNM AS VENDOR_DOC, dbo.POP10300.VENDORID AS VENDOR_ID, 
                      dbo.POP10300.VENDNAME AS VENDOR_NAME, dbo.POP10300.receiptdate AS RECEIPT_DATE, dbo.POP10310.ITEMNMBR AS ITEMNMBR, 
                      dbo.IV00101.ITEMDESC AS DESCRIPTION, dbo.IV00101.ITMSHNAM, dbo.IV00101.ITMGEDSC, dbo.IV00101.ITMCLSCD, 
                      dbo.POP10310.LOCNCODE AS SITE, dbo.POP10310.BIN, dbo.POP10310.UMQTYINB * dbo.POP10330.SERLTQTY AS QUANTITY_INBASE, 
                      dbo.POP10310.UOFM AS LINE_UOFM, dbo.POP10300.POPRCTNM, dbo.POP10330.SERLTQTY AS QUANTITY, 
                      dbo.POP10330.SERLTNUM AS SERLNMBR, '' AS LOTATRB1, '' AS LOTATRB2, '' AS LOTATRB3, '' AS LOTATRB4, '' AS LOTATRB5
FROM         dbo.POP10300 INNER JOIN
                      dbo.POP10310 ON dbo.POP10300.POPRCTNM = dbo.POP10310.POPRCTNM INNER JOIN
                      dbo.IV00101 ON dbo.POP10310.ITEMNMBR = dbo.IV00101.ITEMNMBR INNER JOIN
                      dbo.POP10330 ON dbo.POP10310.POPRCTNM = dbo.POP10330.POPRCTNM AND dbo.POP10310.RCPTLNNM = dbo.POP10330.RCPTLNNM
WHERE     (dbo.IV00101.ITMTRKOP = 2)
GROUP BY dbo.POP10310.PONUMBER, dbo.POP10310.POPRCTNM, dbo.POP10300.VNDDOCNM, dbo.POP10300.VENDORID, dbo.POP10300.VENDNAME, 
                      dbo.POP10300.receiptdate, dbo.POP10310.ITEMNMBR, dbo.IV00101.ITEMDESC, dbo.POP10310.LOCNCODE, dbo.POP10310.BIN, 
                      dbo.POP10310.UOFM, dbo.POP10300.POPRCTNM, dbo.IV00101.ITMSHNAM, dbo.IV00101.ITMGEDSC, dbo.IV00101.ITMCLSCD, 
                      dbo.POP10330.SERLTNUM, dbo.POP10310.UMQTYINB * dbo.POP10330.SERLTQTY, dbo.POP10330.SERLTQTY
UNION
SELECT     dbo.POP10310.PONUMBER AS PO_NUMBER, dbo.POP10300.VNDDOCNM AS VENDOR_DOC, dbo.POP10300.VENDORID AS VENDOR_ID, 
                      dbo.POP10300.VENDNAME AS VENDOR_NAME, dbo.POP10300.receiptdate AS RECEIPT_DATE, dbo.POP10310.ITEMNMBR AS ITEMNMBR, 
                      dbo.IV00101.ITEMDESC AS DESCRIPTION, dbo.IV00101.ITMSHNAM, dbo.IV00101.ITMGEDSC, dbo.IV00101.ITMCLSCD, 
                      dbo.POP10310.LOCNCODE AS SITE, dbo.POP10310.BIN, dbo.POP10310.UMQTYINB * dbo.POP10330.SERLTQTY AS QUANTITY_INBASE, 
                      dbo.POP10310.UOFM AS LINE_UOFM, dbo.POP10300.POPRCTNM, dbo.POP10330.SERLTQTY AS QUANTITY, 
                      dbo.POP10330.SERLTNUM AS SERLNMBR, dbo.IV00301.LOTATRB1 AS LOTATRB1, dbo.IV00301.LOTATRB2 AS LOTATRB2, 
                      dbo.IV00301.LOTATRB3 AS LOTATRB3, dbo.IV00301.LOTATRB4 AS LOTATRB4, dbo.IV00301.LOTATRB5 AS LOTATRB5
FROM         dbo.POP10300 INNER JOIN
                      dbo.POP10310 ON dbo.POP10300.POPRCTNM = dbo.POP10310.POPRCTNM INNER JOIN
                      dbo.IV00101 ON dbo.POP10310.ITEMNMBR = dbo.IV00101.ITEMNMBR INNER JOIN
                      dbo.POP10330 ON dbo.POP10310.POPRCTNM = dbo.POP10330.POPRCTNM AND dbo.POP10310.RCPTLNNM = dbo.POP10330.RCPTLNNM INNER JOIN
                      dbo.IV00301 ON dbo.POP10330.ITEMNMBR = dbo.IV00301.ITEMNMBR AND dbo.POP10330.SERLTNUM = dbo.IV00301.LOTNUMBR
WHERE     (dbo.IV00101.ITMTRKOP = 3)
GROUP BY dbo.POP10310.PONUMBER, dbo.POP10310.POPRCTNM, dbo.POP10300.VNDDOCNM, dbo.POP10300.VENDORID, dbo.POP10300.VENDNAME, 
                      dbo.POP10300.receiptdate, dbo.POP10310.ITEMNMBR, dbo.IV00101.ITEMDESC, dbo.POP10310.LOCNCODE, dbo.POP10310.BIN, 
                      dbo.POP10310.UOFM, dbo.POP10300.POPRCTNM, dbo.IV00101.ITMSHNAM, dbo.IV00101.ITMGEDSC, dbo.IV00101.ITMCLSCD, 
                      dbo.POP10330.SERLTNUM, dbo.POP10310.UMQTYINB * dbo.POP10330.SERLTQTY, dbo.POP10330.SERLTQTY, dbo.IV00301.LOTATRB1, 
                      dbo.IV00301.LOTATRB2, dbo.IV00301.LOTATRB3, dbo.IV00301.LOTATRB4, dbo.IV00301.LOTATRB5
GO

 

[gmmastros]

Basically, EXEC allows you to execute dynamic SQL. Exec is delimited by apostrophes, so if you want an apostrophe in your string, then you need to double it.

if 1=1
BEGIN
    exec ('CREATE VIEW TestView
           AS
           SELECT *
           [!]''This is a string'' As ColumnAlias,
           '''' As ThisIsAnEmptyString[/!] 
           FROM dbo.MDS_SYS003')
END

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[macleod1021]

dang...and I knew that too. Wasn't even remotely thinking down those lines.

You're awesome George!