Create View ?

Collapse
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • adz1809
    New Member
    • Dec 2006
    • 27
    #1

    Create View ?

    Hi,

    How do I change this statement into a create view :

    SELECT dbo.OFW_New_Pro gramme_Awards.A wardID AS AwardID, dbo.OFW_New_Pro gramme_Awards.P rogrammeID AS ProgrammeID,
    dbo.OFW_New_Pro gramme_Awards.P ointsValue AS PointsValue, dbo.OFW_New_Awa rds.Status AS Status, dbo.OFW_New_Awa rds.Category AS OFCategory,
    dbo.OFW_New_Awa rds.Title AS Title, dbo.OFW_New_Awa rds.UnitQuantit y AS UnitQuantity, dbo.OFW_New_Awa rds.MultiUnitQu antity AS MultiUnitQuanti ty,
    dbo.OFW_New_Awa rds.Description AS Description, dbo.OFW_New_Awa rds.SupplierID AS SupplierID, dbo.OFW_New_Awa rds.IsVoucher AS IsVoucher,
    dbo.OFW_New_Awa rds.HasSize AS HasSize, dbo.OFW_New_Cat egories.*
    FROM dbo.OFW_New_Awa rds INNER JOIN
    dbo.OFW_New_Pro gramme_Awards ON dbo.OFW_New_Awa rds.ID = dbo.OFW_New_Pro gramme_Awards.A wardID INNER JOIN
    dbo.OFW_New_Cat egories ON dbo.OFW_New_Awa rds.Category = dbo.OFW_New_Cat egories.Categor y

    Cheers
    Tags: None
    • OuTCasT
      Contributor
      • Jan 2008
      • 374
      #2
      Originally posted by adz1809
      Hi,

      How do I change this statement into a create view :

      SELECT dbo.OFW_New_Pro gramme_Awards.A wardID AS AwardID, dbo.OFW_New_Pro gramme_Awards.P rogrammeID AS ProgrammeID,
      dbo.OFW_New_Pro gramme_Awards.P ointsValue AS PointsValue, dbo.OFW_New_Awa rds.Status AS Status, dbo.OFW_New_Awa rds.Category AS OFCategory,
      dbo.OFW_New_Awa rds.Title AS Title, dbo.OFW_New_Awa rds.UnitQuantit y AS UnitQuantity, dbo.OFW_New_Awa rds.MultiUnitQu antity AS MultiUnitQuanti ty,
      dbo.OFW_New_Awa rds.Description AS Description, dbo.OFW_New_Awa rds.SupplierID AS SupplierID, dbo.OFW_New_Awa rds.IsVoucher AS IsVoucher,
      dbo.OFW_New_Awa rds.HasSize AS HasSize, dbo.OFW_New_Cat egories.*
      FROM dbo.OFW_New_Awa rds INNER JOIN
      dbo.OFW_New_Pro gramme_Awards ON dbo.OFW_New_Awa rds.ID = dbo.OFW_New_Pro gramme_Awards.A wardID INNER JOIN
      dbo.OFW_New_Cat egories ON dbo.OFW_New_Awa rds.Category = dbo.OFW_New_Cat egories.Categor y

      Cheers
      [CODE=sql]CREATE VIEW view_name AS
      SELECT column_name(s)
      FROM table_name
      WHERE condition
      [/CODE]

        Comment

        • adz1809
          New Member
          • Dec 2006
          • 27
          #3
          I have followed this syntax and it keeps failing.

          this is the syntax that I have used:

          CREATE VIEW VIEW_OFW_New_Pr og_Award_Detail _test
          AS
          SELECT
          OFW_New_Program me_Awards.Award ID AS AwardID,
          OFW_New_Program me_Awards.Progr ammeID AS ProgrammeID,
          OFW_New_Program me_Awards.Point sValue AS PointsValue,
          OFW_New_Awards. Status AS Status,
          OFW_New_Awards. Category AS OFCategory,
          OFW_New_Awards. Title AS Title,
          OFW_New_Awards. UnitQuantity AS UnitQuantity,
          OFW_New_Awards. MultiUnitQuanti ty AS MultiUnitQuanti ty,
          OFW_New_Awards. Description AS Description,
          OFW_New_Awards. SupplierID AS SupplierID,
          OFW_New_Awards. IsVoucher AS IsVoucher,
          OFW_New_Awards. HasSize AS HasSize,
          OFW_New_Categor ies.*
          FROM OFW_New_Awards INNER JOIN
          OFW_New_Program me_Awards ON OFW_New_Awards. ID = OFW_New_Program me_Awards.Award ID INNER JOIN
          OFW_New_Categor ies ON OFW_New_Awards. Category = OFW_New_Categor ies.Category

          It works fine throughSQL query Analyzar, but when but into this code:

          Code:
          OpenDatabaseOrder()
	
	cquery = "CREATE VIEW VIEW_OFW_New_Prog_Award_Detail" & vbcrlf
	cquery = cquery & "AS" & vbcrlf
	cquery = cquery & "SELECT " & vbcrlf
	cquery = cquery & "OFW_New_Programme_Awards.AwardID AS AwardID, " & vbcrlf
	cquery = cquery & "OFW_New_Programme_Awards.ProgrammeID AS ProgrammeID," & vbcrlf
	cquery = cquery & "OFW_New_Programme_Awards.PointsValue AS PointsValue,"  & vbcrlf
	cquery = cquery & "OFW_New_Awards.Status AS Status, " & vbcrlf
	cquery = cquery & "OFW_New_Awards.Category AS OFCategory," & vbcrlf
	cquery = cquery & "OFW_New_Awards.Title AS Title, " & vbcrlf
	cquery = cquery & "OFW_New_Awards.UnitQuantity AS UnitQuantity, " & vbcrlf
	cquery = cquery & "OFW_New_Awards.MultiUnitQuantity AS MultiUnitQuantity," & vbcrlf
	cquery = cquery & "OFW_New_Awards.Description AS Description, " & vbcrlf
	cquery = cquery & "OFW_New_Awards.SupplierID AS SupplierID," & vbcrlf
	cquery = cquery & "OFW_New_Awards.IsVoucher AS IsVoucher, " & vbcrlf
	cquery = cquery & "OFW_New_Awards.HasSize AS HasSize, " & vbcrlf
	cquery = cquery & "OFW_New_Categories.*" & vbcrlf
	cquery = cquery & "FROM OFW_New_Awards INNER JOIN" & vbcrlf
	cquery = cquery & "OFW_New_Programme_Awards ON OFW_New_Awards.ID = OFW_New_Programme_Awards.AwardID INNER JOIN" & vbcrlf
	cquery = cquery & "OFW_New_Categories ON OFW_New_Awards.Category = OFW_New_Categories.Categoryy" & vbcrlf
	
	sysDatabase.execute(cquery)

	CloseDatabaseOrder()
          It fails

            Comment

            • OuTCasT
              Contributor
              • Jan 2008
              • 374
              #4
              Originally posted by adz1809
              I have followed this syntax and it keeps failing.

              this is the syntax that I have used:

              CREATE VIEW VIEW_OFW_New_Pr og_Award_Detail _test
              AS
              SELECT
              OFW_New_Program me_Awards.Award ID AS AwardID,
              OFW_New_Program me_Awards.Progr ammeID AS ProgrammeID,
              OFW_New_Program me_Awards.Point sValue AS PointsValue,
              OFW_New_Awards. Status AS Status,
              OFW_New_Awards. Category AS OFCategory,
              OFW_New_Awards. Title AS Title,
              OFW_New_Awards. UnitQuantity AS UnitQuantity,
              OFW_New_Awards. MultiUnitQuanti ty AS MultiUnitQuanti ty,
              OFW_New_Awards. Description AS Description,
              OFW_New_Awards. SupplierID AS SupplierID,
              OFW_New_Awards. IsVoucher AS IsVoucher,
              OFW_New_Awards. HasSize AS HasSize,
              OFW_New_Categor ies.*
              FROM OFW_New_Awards INNER JOIN
              OFW_New_Program me_Awards ON OFW_New_Awards. ID = OFW_New_Program me_Awards.Award ID INNER JOIN
              OFW_New_Categor ies ON OFW_New_Awards. Category = OFW_New_Categor ies.Category

              It works fine throughSQL query Analyzar, but when but into this code:

              Code:
              OpenDatabaseOrder()
	
	cquery = "CREATE VIEW VIEW_OFW_New_Prog_Award_Detail" & vbcrlf
	cquery = cquery & "AS" & vbcrlf
	cquery = cquery & "SELECT " & vbcrlf
	cquery = cquery & "OFW_New_Programme_Awards.AwardID AS AwardID, " & vbcrlf
	cquery = cquery & "OFW_New_Programme_Awards.ProgrammeID AS ProgrammeID," & vbcrlf
	cquery = cquery & "OFW_New_Programme_Awards.PointsValue AS PointsValue,"  & vbcrlf
	cquery = cquery & "OFW_New_Awards.Status AS Status, " & vbcrlf
	cquery = cquery & "OFW_New_Awards.Category AS OFCategory," & vbcrlf
	cquery = cquery & "OFW_New_Awards.Title AS Title, " & vbcrlf
	cquery = cquery & "OFW_New_Awards.UnitQuantity AS UnitQuantity, " & vbcrlf
	cquery = cquery & "OFW_New_Awards.MultiUnitQuantity AS MultiUnitQuantity," & vbcrlf
	cquery = cquery & "OFW_New_Awards.Description AS Description, " & vbcrlf
	cquery = cquery & "OFW_New_Awards.SupplierID AS SupplierID," & vbcrlf
	cquery = cquery & "OFW_New_Awards.IsVoucher AS IsVoucher, " & vbcrlf
	cquery = cquery & "OFW_New_Awards.HasSize AS HasSize, " & vbcrlf
	cquery = cquery & "OFW_New_Categories.*" & vbcrlf
	cquery = cquery & "FROM OFW_New_Awards INNER JOIN" & vbcrlf
	cquery = cquery & "OFW_New_Programme_Awards ON OFW_New_Awards.ID = OFW_New_Programme_Awards.AwardID INNER JOIN" & vbcrlf
	cquery = cquery & "OFW_New_Categories ON OFW_New_Awards.Category = OFW_New_Categories.Categoryy" & vbcrlf
	
	sysDatabase.execute(cquery)

	CloseDatabaseOrder()
              It fails
              why do u use cquery ???

                Comment

                • adz1809
                  New Member
                  • Dec 2006
                  • 27
                  #5
                  It is used a variable to hold the SQL syntax, which is then past into:

                  sysDatabase.exe cute(cquery)

                    Comment

                    • OuTCasT
                      Contributor
                      • Jan 2008
                      • 374
                      #6
                      Originally posted by adz1809
                      It is used a variable to hold the SQL syntax, which is then past into:

                      sysDatabase.exe cute(cquery)
                      why do u want to do that ????

                      are u using vb?

                        Comment

                        • adz1809
                          New Member
                          • Dec 2006
                          • 27
                          #7
                          It is part of an vbscript page which requires a view to be dropped then re-created so that it is up-to-date with any changes.

                            Comment

                            • debasisdas
                              Recognized Expert Expert
                              • Dec 2006
                              • 8119
                              #8
                              Why are you using vbcrLf in every concatination.

                                Comment

                                • adz1809
                                  New Member
                                  • Dec 2006
                                  • 27
                                  #9
                                  Just so that the code is nice and tidy and arrives in the same format, rather than one long string, it's just a personal thing, it shouldn't affect it.

                                    Comment

                                    • adz1809
                                      New Member
                                      • Dec 2006
                                      • 27
                                      #10
                                      Just to let you know, this is all sorted, this is the final code:

                                      Code:
                                      dim cquery
	
	OpenDatabaseOrder()
	
	cquery = "CREATE VIEW VIEW_OFW_New_Prog_Award_Detail" & vbcrlf
	cquery = cquery & "AS" & vbcrlf
	cquery = cquery & "SELECT " & vbcrlf
	cquery = cquery & "OFW_New_Programme_Awards.AwardID AS AwardID, " & vbcrlf
	cquery = cquery & "OFW_New_Programme_Awards.ProgrammeID AS ProgrammeID," & vbcrlf
	cquery = cquery & "OFW_New_Programme_Awards.PointsValue AS PointsValue,"  & vbcrlf
	cquery = cquery & "OFW_New_Awards.Status AS Status, " & vbcrlf
	cquery = cquery & "OFW_New_Awards.Category AS OFCategory," & vbcrlf
	cquery = cquery & "OFW_New_Awards.Title AS Title, " & vbcrlf
	cquery = cquery & "OFW_New_Awards.UnitQuantity AS UnitQuantity, " & vbcrlf
	cquery = cquery & "OFW_New_Awards.MultiUnitQuantity AS MultiUnitQuantity," & vbcrlf
	cquery = cquery & "OFW_New_Awards.Description AS Description, " & vbcrlf
	cquery = cquery & "OFW_New_Awards.SupplierID AS SupplierID," & vbcrlf
	cquery = cquery & "OFW_New_Awards.IsVoucher AS IsVoucher, " & vbcrlf
	cquery = cquery & "OFW_New_Awards.HasSize AS HasSize, " & vbcrlf
	cquery = cquery & "OFW_New_Categories.*" & vbcrlf
	cquery = cquery & "FROM OFW_New_Awards INNER JOIN" & vbcrlf
	cquery = cquery & "OFW_New_Programme_Awards ON OFW_New_Awards.ID = OFW_New_Programme_Awards.AwardID INNER JOIN" & vbcrlf
	cquery = cquery & "OFW_New_Categories ON OFW_New_Awards.Category = OFW_New_Categories.Category" & vbcrlf
	
	sysDatabase.execute(cquery)

	CloseDatabaseOrder()

                                        Comment

                                        Previous template Next
                                        Working...