Creating SQL View - Syntax Is Wrong
Apr 14, 2004
We are creating a view in SQL to format data to DTS to another database. Here is the section of the view which does not work -- eliminating this section from the query allows the view to be created:
CASE APTran.LineNbr
WHEN < '0' THEN 'O'
--Offsetting Document (or transaction line as we have (AP Liab. AP Cash Entry).
WHEN > '0' THEN 'D'
--Regular Document (or transaction line as we have (This is either a Debit or a Credit line not AP Liab. AP Cash Entry).
ELSE 'E'
END AS JED_DIST_OR_OFF,
I am attaching the whole query stmt here.
Thanks very much for all assistance.
View 6 Replies
ADVERTISEMENT
May 21, 2006
Hello
I am trying to insert a hyperlink to display on my web site, via the database, for example:
http://www.tressleworks.ca/Modules/SQLGridSelectedView/LiveExamples/Example4Links/tabid/91/Default.aspx
Name of my table is: Biz_Names
Cols:
ID_numberFNameLNameBusiness
I know that this works:
SELECT FName, LName, BusinessFROM BizNamesWHERE FName = 'John'
ORDER BY LName ASC
I want to add a hyperlink and I have since included extra columns:
ID_numberFNameLNameBusinessurltitleLink
I want to add the hyperlink from the url column and use the title from the title column to fill the Link column, I don't want to include the url and the title column on the site.
I only want to display Link data on the site, but so far my syntax is not working:
SELECT FName, LName, Business, '<a href='" + url '"> + Title +'</a>' as LinkFROM BizNamesWHERE FName = 'John'
And url like 'http:%'
ORDER BY LName ASC
What am I doing wrong?
Thanks
Regards
Lynn
View 2 Replies
View Related
Jul 28, 2007
It works in ASP-Access, but not on the Unix sql:
SELECT SUM(result.pt) as SumOfpt FROM result,team GROUP BY sref,tref,team.id HAVING sref=2 and team.id=tref;
it says - and only on the unix server:
SQLState: 4 Native Error Code: 1054 [TCX][MyODBC]Unknown column 'sref' in 'having clause'
/test/teamstandings.asp, line 20
View 11 Replies
View Related
May 24, 2007
"*** " + @[System::MachineName] + " - SSIS Package Error - [ " + @[System:ackageName] + " ] @ " + (DT_STR, @[System::ContainerStartTime]) + " ***"
Get this error:
TITLE: Expression Builder
------------------------------
Expression cannot be evaluated.
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%u00ae+Visual+Studio%u00ae+2005&ProdVer=8.0.50727.42&EvtSrc=Microsoft.DataTransformationServices.Controls.TaskUIFramework.TaskUIFrameworkSR&EvtID=FailToEvaluateExpression&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
Attempt to parse the expression ""*** " + @[System::MachineName] + " - SSIS Package Error - [ " + @[System:ackageName] + " ] @ " + (DT_STR, @[System::ContainerStartTime]) + " ***"" failed. The expression might contain an invalid token, an incomplete token, or an invalid element. It might not be well-formed, or might be missing part of a required element such as a parenthesis.
(Microsoft.DataTransformationServices.Controls)
------------------------------
BUTTONS:
OK
------------------------------
Thanks
View 3 Replies
View Related
Oct 2, 2007
CASE field_val
WHEN 'M5' THEN EXEC ( @query)
ELSE THEN print ''
END
I have a SP , and i state a query .. but i only
want this query to be executed based on the value
of field_val , otherwise i print a blank ..
but when i check the syntax (ms sql server 2000)
i get "invalid syntax near CASE ..
View 5 Replies
View Related
Nov 23, 2007
Hi
I have a table called DiaryDate2 and a query called DiaryDateOver8 and want to have a sql query to return the records from the table that are not in the query based on two linked fields username and Diarydate1
The SQL I have written however returns all records
SelectCommand="SELECT DiaryDate2.Username, DiaryDate2.DiaryDate1 FROM DiaryDate2
WHERE(DiaryDate2.Username = ?)AND NOT EXISTS (SELECT 1 from DiaryDateover8 where DiaryDateover8.Username=DiaryDate2.Username AND DiaryDateover8.DiaryDate1=DiaryDate2.DiaryDate1)ORDER BY DiaryDate2.DiaryDate1">
Please could someone help
Many thanks Colin
View 5 Replies
View Related
Mar 21, 2007
Hi heres my script, why does it not work?
CREATE PROC [dbo].[sp_append_CemexSQLDB_to_EQuIS5_V2]
@DBINnvarchar(50),
@DBOUTnvarchar(50)
AS
DECLARE @SQLStrVarchar(1000)
/* Start Transaction, incase of error */
BEGIN TRAN
/* Appending Ref tables */
SET @SQLStr = 'INSERT INTO ' + QUOTENAME(@DBOUT) '.dbo.rt_action_level_type'
SET @SQLStr = 'SELECT *'
SET @SQLStr = 'FROM ' + QUOTENAME(@DBIN) '.dbo.rt_action_level_type;' + ' OPTION (KEEP PLAN)'
EXEC (@SQLStr)
IF @@ERROR <> 0 GOTO ERRORHANDLER
COMMIT TRAN
RETURN 0
ERRORHANDLER:
PRINT "Unexpected Error Ocurred!"
ROLLBACK TRAN
RETURN 1
GO
The error is 'Server: Msg 170, Level 15, State 1, Procedure sp_append_SQLtables_to_NewDB_V2, Line 13
Line 13: Incorrect syntax near '.dbo.rt_action_level_type'.'
can anybody help
View 5 Replies
View Related
Jul 23, 2005
Hello all,I belive, my problem is probably very easy to solve, but still, Icannot find solution:declare @iintdeclare @zintcreate table bubusilala ([bubu] [int] NOT NULL ,[gogo] [int] NOT NULL ,[lala] [varchar] (3) NOT NULL )insert into bubusilala (bubu,gogo,lala) values (1,2,'ala')insert into bubusilala (bubu,gogo,lala) values (10,20,'aca')insert into bubusilala (bubu,gogo,lala) values (100,200,'bbb')insert into bubusilala (bubu,gogo,lala) values (11,21,'ccc')insert into bubusilala (bubu,gogo,lala) values (12,22,'abc')insert into bubusilala (bubu,gogo,lala) values (13,23,'cbd')set @i = 10set @z = 2select * from bubusilalawhere bubu in (case when @i > @z then (1,2)when @i < @z then (10,13) end)and gogo like '%a%'I get error, that statement is wrong in case near ','.I supose, it is not possible, to get from case a group of values.But why then, this works:select * from bubusilalawhere bubu in (case when @i > @z then (1)when @i < @z then (select gogo from bubusilala) end)and gogo like '%a%'This data are totaly simplified.agrh ... any ideas??Thank You in advance,Mateusz
View 3 Replies
View Related
Jul 23, 2005
I am looking to create a constraint on a table that allows multiplenulls but all non-nulls must be unique.I found the following scripthttp://www.windowsitpro.com/Files/0.../Listing_01.txtthat works fine, but the following lineCREATE UNIQUE CLUSTERED INDEX idx1 ON v_multinulls(a)appears to use indexed views. I have run this on a version of SQLStandard edition and this line works fine. I was of the understandingthat you could only create indexed views on SQL Enterprise Edition?
View 3 Replies
View Related
Aug 24, 2007
Hi guys 'n gals,
I created a query, which makes use of a temp table, and I need the results to be displayed in a View. Unfortunately, Views do not support temp tables, as far as I know, so I put my code in a stored procedure, with the hope I could call it from a View....
I tried:
CREATE VIEW [qryMyView]
AS
EXEC pr_MyProc
and unfortunately, it does not let this run.
Anybody able to help me out please?
Cheers!
View 3 Replies
View Related
Apr 11, 2004
************* Edited by moderator Adec ***************
Inserted missing < code></ code> tags. Always include such
tags when including code in your postings. Many readers
disregard postings without the code tags.
**************************************************
Hello,
I have a big problem with some piece of code. I am new to C# and ado.net.
My MSDE 2000 is set up and running. But the following code produces the error
(translated from german) like "wrong syntax near the user keyword"
The connection to the database works fine. But trying to create a table or to drop
a table alwas leads to the error. Whats wrong with it?
try
{
SqlConnection myConnection = new SqlConnection( "server=(local);" +
"Trusted_Connection=yes;" +
"database=ergofun; " +
"connection timeout=10");
SqlCommand sqlCommand = new SqlCommand();
sqlCommand.Connection = myConnection;
sqlCommand.CommandText = "CREATE TABLE user ( userid INT," +
"nickname CHAR(20),"+
"password CHAR(20),"+
"birthday DATETIME,"+
"weight INT,"+
"size INT,"+
"puls INT,"+
"name CHAR(20),"+
"surname CHAR(20))";
sqlCommand.Connection.Open();
sqlCommand.ExecuteNonQuery();
sqlCommand.Connection.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
View 1 Replies
View Related
Aug 3, 2006
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION DetailedView
AS
BEGIN
Declare @fieldname varchar(10)
Declare @stmt varchar(4000)
Declare Fields Cursor For Select Amounttype From Amounttypes
Set @stmt = 'Select pono, myid, billtype'
Open Fields
Fetch Next From Fields Into @fieldname
While @@Fetch_Status = 0 Begin
Set @stmt = @stmt + ', sum(amountfc * Case When amounttype = ''' + @fieldname + ''' Then 1 Else 0 End) As ' + @fieldname
Fetch Next From Fields Into @fieldname
End
Close Fields
Deallocate Fields
Set @stmt = @stmt + ' From multiplebillsviewall Group By pono, myId,billtype '
return Exec(@stmt)
END
View 3 Replies
View Related
Jul 23, 2005
When I add this code in a view and try to save . . .SELECT TOP 610 *FROM dbo.Master INNER JOINdbo.TypeByCase ON dbo.TypeByCase.CaseNum = dbo.Master.CaseNumIt gives the error:ODBC error: [Microsoft][ODBC SQL Server Driver]Column names in eachview or function must be unique. Column name 'CaseNum' in view orfunction 'dbo.BobView1' is specified more than once.Any idea why?Thanks,RBollinger
View 1 Replies
View Related
Oct 16, 2007
SQL 2005 9.00.3402.00 (x64) As Above really when I select * OR select a single column from the view the wrong column data is returned. in SQL Management Studio when I expand the Columns of the view it reflects the old table structure not the new table structure. I can easily fix by compiling the view again but this would mean I would have to recompile all referencing views when I make a change to table structures. I've tried various DBCC Clean Buffers & drop cache with no effect. Is there a command to recompile all views & poss stored procs in a database. Any help or explanation would be appreciated GW
View 12 Replies
View Related
Apr 15, 2004
Hi there,
Completely new to the world of databases. I'm a designer who works primarily in Flash. In any case, I'm trying to manage an application that uses MS SQL and learn about the wonderful world of databases.
Ok, I modified a table (e.g. I added a column called "Rate") that had associated views (created by another developer). Noticed that my application went a little wonky as some of my variables within my app took on the value of the data in the "Rate" column. I checked one of the views and noticed that a column within the view (e.g. TutorID) was assuming the values in the "Rate" column. Note: The column TutorID had been blank before the change to the table. I'm completely lost as to why this is happening. Do I need to rebuild the view? Can I just reset the original view?
Thanks.
Oh yeah, I'm using SQL4X Manager J from Mac Guru (if that helps).
View 7 Replies
View Related
Aug 3, 2007
Why can't one use a three part naming convention when creating a view?
example:
CREATE VIEW databasename.schemaname.anyview
AS
SELECT * FROM anytable
The following error is returned:
Msg 166, Level 15, State 1, Line 1
'CREATE/ALTER VIEW' does not allow specifying the database name as a prefix to the object name.
View 1 Replies
View Related
Jul 3, 2007
Hi, Im trying to create a xml schema like CREATE XML SCHEMA COLLECTION BooksSchemaCollection ASN'<?xml version="1.0" encoding="UTF-16"?><xsd:schema elementFormDefault="unqualified" attributeFormDefault="unqualified" xmlns:xsd="http://www.w3.org/2001/XMLSchema" > <xsd:element name="book"> <xsd:complexType mixed="false"> <xsd:sequence> <xsd:element name="name" type="xsd:string"/> <xsd:element name="author" type="xsd:string"/> <xsd:element name="publisher" type="xsd:string"/> <xsd:element name="cost" type="xsd:integer"/> <xsd:element name="comments" type="xsd:string"/> </xsd:sequence> </xsd:complexType> </xsd:element></xsd:schema>';
But when i execute , im getting a error like Incorrect syntax near 'XML' .any one know why its comming?????? Thanks
View 1 Replies
View Related
Dec 30, 2007
I'm trying to create a SQL job in SQL Server and am a little unclear about the formatting.Here's a snippet from the stored procedure that creates the job:CREATE PROCEDURE [dbo].[spArchive] @DB varchar(30), @Date DateTimeAS EXEC msdb.dbo.sp_add_jobstep @job_name = 'ArchiveIncentives' , @step_id = 1 , @step_name = 'ArchiveAHD' , @subsystem = 'TSQL' , @command = 'spArchiveAHD ''@Date''' , @on_success_action = 3 , @on_fail_action = 2 , @database_name = '@DB' , @retry_attempts = 1 In this case, the job will be calling this stored procedure:CREATE PROCEDURE [dbo].[spArchiveAHD] ( @dtArchiveBefore DateTime)AS I'm unclear about these lines: @command = 'spArchiveAHD ''@Date''' @database_name = '@DB' Do they look correct to you or should I drop some/all of the apostrophes?Robert W.
View 5 Replies
View Related
Nov 29, 2000
Hi
Is there a syntax similar to the oracle in SQL Server?
Oracle statement: create table table2 as select * from table1
which will create table2 with exactly the same structure
and records as table 1
thanks
Liju
View 2 Replies
View Related
May 27, 2014
Have been given this code to create a table
CREATE TABLE 'emaillist' (
'id' INT(11) NOT NULL AUTO_INCREMENT,
'clientname' VARCHAR(200) NOT NULL DEFAULT '0',
'email' VARCHAR(200) NOT NULL DEFAULT '0',
PRIMARY KEY ('id')
);
when I execute it says
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'emaillist'.
What the correct code should be?
View 3 Replies
View Related
Aug 6, 2007
i have a table which has 2 columns 1 'report' 2 'part'
now in my 'report' cloumn i have # with 6 digits ex. '111111' and 'part' has '1, 2, 3, 4,..to 50'
i want to create a view that will put them together in format like this:
1111110001
1111110002
1111110003 .. and on
it needs to be in 10 digits.
is there anyway i can create a View or may be a column in the table which can create the #'s in this format.
View 2 Replies
View Related
Dec 5, 2005
What are some possible purposes of creating a view and how can it be used to reinforce data security. What description of circumstances can be used for a view to save re-programming?
View 1 Replies
View Related
Apr 16, 2008
Hi All,
I have a sql command with temporary tables, but whenever I am trying to create a view with temporary tables
I am getting an error as below:
"Views or functions are not allowed on temporary tables. Table names that begin with ‘#’ denote temporary tables."
Please anybody let me know is it possible to create a view with temporary tables in SQL Server 2005.If not, then is their any way how I can create a view with temporary tables.
Thank You
View 2 Replies
View Related
Apr 16, 2008
Hi All,
I have a sql command with temporary tables, but whenever I am trying to create a view with temporary tables
I am getting an error as below:
"Views or functions are not allowed on temporary tables. Table names that begin with ‘#’ denote temporary tables."
Please anybody let me know is it possible to create a view with temporary tables in SQL Server 2005.If not, then is their any way how I can create a view with temporary tables.
Thank You
View 2 Replies
View Related
Jul 23, 2005
I need to create a view that scores a research assessment. So I havequestions 1 through 10, but the problem is that Q3 and Q5-10 have to berecoded so that if the response is 3, it becomes 0, 2=1, 1=2 and 0=3.So this is what I have. I keep getting an error message that there is"incorrect syntax near the keyword THEN". I don't know which "THEN" itis referring to (or all of them)?? HELP! I am new to this.CREATE VIEW name ASSELECT ID, DATE, TOTAL=Q1+Q2+CASE WHEN (Q3=0 THEN 3 WHEN Q3=1 THEN 2 WHEN Q3=2 THEN 1 WHEN Q3=3 THEN0)+Q4+CASE WHEN (Q5=0 THEN 3 WHEN Q5=1 THEN 2 WHEN Q5=2 THEN 1 WHEN Q5=3 THEN0)+CASE WHEN (Q6=0 THEN 3 WHEN Q6=1 THEN 2 WHEN Q6=2 THEN 1 WHEN Q6=3 THEN0)+CASE WHEN (Q7=0 THEN 3 WHEN Q7=1 THEN 2 WHEN Q7=2 THEN 1 WHEN Q7=3 THEN0)+CASE WHEN (Q8=0 THEN 3 WHEN Q8=1 THEN 2 WHEN Q8=2 THEN 1 WHEN Q8=3 THEN0)+CASE WHEN (Q9=0 THEN 3 WHEN Q9=1 THEN 2 WHEN Q9=2 THEN 1 WHEN Q9=3 THEN0)+CASE WHEN (Q10=0 THEN 3 WHEN Q10=1 THEN 2 WHEN Q10=2 THEN 1 WHEN Q10=3THEN 0) ENDFROM tablename
View 2 Replies
View Related
Apr 16, 2008
Hi All,
I have a sql command with temporary tables, but whenever I am trying to create a view with temporary tables
I am getting an error as below:
"Views or functions are not allowed on temporary tables. Table names that begin with €˜#€™ denote temporary tables."
Please anybody let me know is it possible to create a view with temporary tables in SQL Server 2005.If not, then is their any way how I can create a view with temporary tables.
Thank You
View 3 Replies
View Related
May 26, 2000
hai guys,
i have written a stored procedure which creates a table ex:
USE PUBS
GO
IF EXISTS (SELECT * FROM SYSOBJECTS WHERE NAME = 'RC_STRPROC')
DROP PROCEDURE RC_STRPROC
GO
USE PUBS
GO
CREATE PROCEDURE RC_STRPROC
(@TBLNAME VARCHAR(35), @COLVAL1 VARCHAR(35), @COLVAL2 VARCHAR(35))
AS
IF EXISTS (SELECT * FROM SYSOBJECTS WHERE NAME = '@TBLNAME')
DROP TABLE @TBLNAME
CREATE TABLE @TBLNAME
(@COLVAL1, @COLVAL2)
GO
it gives an syntax error at '@tblname'
can u guys tell me the problem
thanks
hiss
View 2 Replies
View Related
Apr 19, 2015
I am getting error when I am trying to create table on runtime
Declare @FileName varchar(100)
Declare @File varchar(100)
set @FileName='brkrte_121227102828'
SET @File = SUBSTRING(@FileName,1,CHARINDEX('_',@FileName)-1)
--=select @File
[Code] ....
Error massage:-
Msg 203, Level 16, State 2, Line 16
The name 'CREATE TABLE DataStaging.dbo.Staging_brkrte ( [COL001] VARCHAR (4000) NOT NULL, [Id] Int Identity(1,1), [LoadDate] datetime default getdate() )' is not a valid identifier.
How to resolve above error....
View 4 Replies
View Related
Feb 17, 2008
Hi all,
I copied the the following code from a book to the query editor of my SQL Server Management Studio Express (SSMSE):
///--MuCh14spInvTotal3.sql--///
USE AP --AP Database is installed in the SSMSE--
GO
CREATE PROC spInvTotal3
@InvTotal money OUTPUT,
@DateVar smalldatetime = NULL,
@VendorVar varchar(40) = '%'
AS
IF @DateVar IS NULL
SELECT @DateVar = MIN(InvoiceDate)
SELECT @InvTotal = SUM(InvoiceTotal)
FROM Invoices JOIN Vendors
WHERE (InvoiceDate >= @DateVar) AND
(VendorName LIKE @VendorVar)
GO
///////////////////////////////////////////////////////////////
Then I executed it and I got the following error:
Msg 156, Level 15, State 1, Procedure spInvTotal3, Line 12
Incorrect syntax near the keyword 'WHERE'.
I do not know what wrong with it and how to correct this problem.
Please help and advise.
Thanks,
Scott Chang
View 18 Replies
View Related
May 19, 2007
I currently have a website with a page that displays the flags/ keys of the entire roster of guilded characters. Some more background is that I run a website for my Everquest guild, users can log in , create characters , and update their flags / keys.
There are 4 associated tables that are used in displaying the flags.
the Characters , Flags, Expansion, and Char_Flags tables. The char_flags table consists of 2 foreign keys and a bit field for enabled / disabled.
Char_flags = char_fk, flag_fk, enabled (bit)
The select statement I'm currently using to get information is as follows.
SELECT Expansion.ExpansionName, Flags.Flag_Name, Characters.Char_Name, char_flags.enabled FROM char_flags INNER JOIN Flags ON char_flags.Flag_FK = Flags.Flag_PK INNER JOIN Expansion ON Flags.Expansion_FK = Expansion.ExpansionPK INNER JOIN Characters ON char_flags.Char_FK = Characters.Char_PK WHERE (Expansion.ExpansionPK = @expansion)
That displays the information in a format like
Expansion Name, FlagName, CharacterName, Enabled. And there are usually 10 -15 flags per expansion so this lists the expansion 10 times, each flag name, then the character name 10-15 times, and wether or not the flag is enabled.
I would like to display the information a little differently and I'm not sure how to. I would like to display the inormation like this
Flag Name 1 Flag Name 2, Flag name 3, Flag Name 4, etc...
Char 1 X X X
Char2 X X X
Char 3 X X X X
Char 4
Char 5
etc
where the characters make up the first column, the flag names make up the first row(headers) and wether or not the flag is enabled is in the column under the corresponding flag.
This way the name of the flag, and the character name are only displayed one time instead of the flags and character names being repeated over and over.
If anyone can help me on how to display the data I would appreciate it. Here is a link to the page to show how it looks now if it helps www.shattereddestinies.net/flagstest.aspx
Thanks
Brad Fortner
View 3 Replies
View Related
Jan 19, 2005
I have a query on one of my pages that is just too large to keep in the page, so I need to reference a stored view in sql. I'm kind of new to this, so I'm having trouble getting the syntax right. The query is just a simple select statement using the results of several textboxes as parameters. I know how to do the query inside an asp.net page, but when I move it to sql, I don't know how to reference the textbox value i.e. @textbox. Here's what I have so far:
USE [Maindb]
GO
CREATE VIEW [tblMain_view] (@textbox nvarchar(30)) ????
AS SELECT dbo.tblMain.Field1, ....
FROM dbo.tblMain
WHERE dbo.tblMain.Field1 = @textbox and ....
First of all, I know that where I declare @textbox is wrong, so where is the right place to declare it? Also, how do I reference the view from the webpage and do I still use:
cmd.SelectCommand.Parameters.Add . . .
in the page to establish the value. Anyone know a good tutorial on this. All the ones I've found were either in C# or didn't really apply. I need to know how to do this in VB. Thanks
View 1 Replies
View Related
May 23, 2006
Hi I have two SQL server 2000 machine in my corporateHow to create a SQL View if the database table is located on another SQL server?Example :I am currently working at SQL Server "S01", but need to create a sql view to table customer on "Marketing" database located on SQL Server "S02"if the table is reside on the same machine then it's simple for me, but this customer table is not located on "S01" but located on different machine "S02"how to do this? any tutorial?thanks a lotYonscun
View 2 Replies
View Related
Jun 15, 2001
Hi guys,
I am trying to create a view through SP
as follows, but I am getting as error as Invalid syntax near view.
Please let me know where I am doing wrong.
Thanks,Rau
CREATE PROCEDURE PurgeReport
@intJob as int
AS
if @intjob=1
begin
CREATE VIEW Purge AS SELECT Btch_id AS Batch_ID, DLN, Process,End_DLN, Job,
Tcode
FROM Batch_HDR
end
View 2 Replies
View Related
