MDX Query (was Syntax Error)

Mar 2, 2005

" ORDER ( [Accounting Date].[Account Week].Members , DESC ) ON ROWS "

what is wrong with this line???

View 9 Replies


ADVERTISEMENT

Execute SQL Task: Executing The Query Exec (?) Failed With The Following Error: Syntax Error Or Access Violation. Possible F

Jan 23, 2008

Hi,
I'm having an SSIS package which gives the following error when executed :

Error: 0xC002F210 at Create Linked Server, Execute SQL Task: Executing the query "exec (?)" failed with the following error: "Syntax error or access violation". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.Task failed: Create Linked Server

The package has a single Execute SQL task with the properties listed below :

General Properties
Result Set : None

ConnectionType : OLEDB
Connection : Connected to a Local Database (DB1)
SQLSourceType : Direct Input
SQL Statement : exec(?)
IsQueryStorePro : False
BypassPrepare : False

Parameter Mapping Properties

variableName Direction DataType ParameterName

User::AddLinkSql Input Varchar 0


'AddLinkSql' is a global variable of package scope of type string with the value
Exec sp_AddLinkedServer 'Srv1','','SQLOLEDB.1',@DataSrc='localhost',@catalog ='DB1'

When I try to execute the Query task, it fails with the above error. Also, the above the sql statement cannot be parsed and gives error "The query failed to parse. Syntax or access violation"

I would like to add that the above package was migrated from DTS, where it runs without any error, eventhough
it gives the same parse error message.

I would appreciate if anybody can help me out of this issue by suggeting where the problem is.

Thanks in Advance.

View 12 Replies View Related

Query Has A Syntax Error

Mar 15, 2008

I have modelled this query after another query which works fine and retrieves a result set. It accepts a search parameter and brings up all the websites in the database by searching on various columns for the search text.  The article search query works the same way.  I just changed the tables and parameters to match the tables.  It uses the "view" and I had to go into the query and check a few columns in the query designer to add the column to the view.
Here's the query with the syntax error: 1 set ANSI_NULLS ON
2
3 set QUOTED_IDENTIFIER ON
4
5 GO
6
7 ALTER PROCEDURE [dbo].[_spArticleSearch]
8
9 @search varchar (100),
10
11 @orderBy varchar(200)='ActiveMemberShip DESC, PageRank DESC, ArticleTitle, DateAdded DESC'
12
13 AS
14
15 EXEC('SELECT * from vArticle
16
17 where Active=1 AND ShowInDirectory=1 AND
18
19 (
20
21 Articletitle like ''%' + @search + '%''
22
23 OR Articletext LIKE ''%' + @search + '%''
24
25 OR ShortDesc LIKE ''%' + @search + '%''
26
27 OR Keywords LIKE ''%' + @search + '%''
28
29 IN (SELECT ACategoryID FROM tblArticleCategory WHERE AActive=1 AND (ACategoryName LIKE ''%' + @search + '%'' OR AParentID IN(SELECT ACategoryID FROM tblArticleCategory WHERE AActive=1 AND ACategoryName LIKE ''%' + @search + '%'')))
30
31 )
32
33 ORDER BY ' + @OrderBy )
34
35
36

 When I run the stored procedure and input a word into the search box, and it gives me this:
Msg 156, Level 15, State 1, Line 8
Incorrect syntax near the keyword 'IN'.
Msg 102, Level 15, State 1, Line 9
Incorrect syntax near ')'.
(1 row(s) affected)
This is the query it was modelled after which works fine and retrieves all the websites.
1    set ANSI_NULLS ON2    set QUOTED_IDENTIFIER ON3    GO4    5    ALTER PROCEDURE [dbo].[_spWebSiteSearch]6    @search varchar (100),7    @orderBy varchar(200)='ActiveMemberShip DESC, PageRank DESC, TotalExchangedLinks Desc, SiteTitle, SiteURL, DateAdded DESC'8    9    AS10   11   EXEC('SELECT * from vWebSite12   where Active=1 AND ShowInDirectory=1 AND13   (14   sitetitle like ''%' + @search + '%''15   OR SiteURL LIKE ''%' + @search + '%''16   OR SiteDescription LIKE ''%' + @search + '%''17   OR CategoryID IN18   (SELECT CategoryID FROM tblCategory WHERE Active=1 AND (CategoryName LIKE ''%' + @search + '%'' OR ParentID IN(SELECT CategoryID FROM tblCategory WHERE Active=1 AND CategoryName LIKE ''%' + @search + '%'')))19   )20   ORDER BY ' + @OrderBy )21  
Does anyone know where the problem may be? Do the columns in the view have to be in order? When I added my columns, it adds them to the very far right side of the view. Does the columns to be in order in the same way it searches in the query?

View 6 Replies View Related

Query Syntax Error

Feb 23, 2004

This thing is giving me 'Incorrect syntax near the keyword declare'. What's the correct form?



declare @Query varchar(8000)

set @Query = 'insert into PortfolioStock (PortfolioID, StockSymbol) select ' + cast(@Portfolio as varchar) + ', StockSymbol from PortfolioStock where StockSymbol in (''' + replace(ltrim(rtrim(@Textbox)), ' ', ''', ''') + ''')'

exec @Query

View 1 Replies View Related

Sql Query Syntax Error

Jun 15, 2004

Ok I can run the query below in SQL Query Analyzer with no problems. However when I place the SQL query in my asp.net page I get a syntax error. It looks like there is some issue I am not seeing can someone help me. The error i get is "Incorrect syntax near 'pb_sub_recipes_1'. " I have narrowed it down to the area in bold as to where the syntax error appears to be occuring.


SELECT DISTINCT pb_customers.customer_name FROM pb_sub_recipes AS pb_sub_recipes_1 INNER JOIN ((((((pb_jobs INNER JOIN pb_jobs_lots ON pb_jobs.job_id = pb_jobs_lots.job_id) INNER JOIN pb_recipes ON pb_jobs.recipe_id = pb_recipes.recipe_id) INNER JOIN pb_recipes_sub_recipes ON pb_recipes.recipe_id = pb_recipes_sub_recipes.recipe_id) INNER JOIN pb_customers ON pb_jobs.customer_id = pb_customers.customer_id) INNER JOIN pb_sub_recipes ON pb_recipes_sub_recipes.sub_recipe_id = pb_sub_recipes.sub_recipe_id) LEFT JOIN pb_report_shippers ON pb_jobs.job_id = pb_report_shippers.job_id) ON pb_sub_recipes_1.sub_recipe_id = pb_recipes_sub_recipes.sub_recipe_id WHERE (((pb_jobs.date_time)> '5/30/2004') AND pb_customers.customer_id ='228' AND ((pb_report_shippers.shipper_date_time) Is Null) AND ((pb_jobs.job_deleted)=0)) GROUP BY pb_customers.customer_name, pb_jobs.date_time, pb_sub_recipes.energy,pb_sub_recipes.dose,pb_jobs.job_id,pb_sub_recipes.specie,pb_sub_recipes_1.cost_per_wafer, pb_sub_recipes_1.setup_cost pb_sub_recipes_1.wafers_in_batch"

View 2 Replies View Related

Query Syntax Error - Help

Apr 28, 2005

Hi,

I am using SQL query analyzer and typed the following,

Update xupaddress as A1, xupaddress as B1
SET A1.address1 = upper(B1.address1)
set A1.address2 = upper(B1.address2)
set A1.city = upper(B1.city)
set A1.state = upper(B1.state)
where A1.ProfileID = B1.ProfileID

I am getting followin error :
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'as'.

Please point out what is wrong
Thank you

View 1 Replies View Related

Can {oj Be Used In Query? Gives Syntax Error

Mar 4, 2004

Hi all
i am using a query
SELECT DISTINCT lcactivityT.activitycategory_id, code, sort_order, description, lccategoryT.code_alias, lccategoryT.description_alias FROM
{oj ActivityCategory AS categoryT INNER JOIN LicensedClientActivities AS lcactivityT ON lcactivityT.activitycategory_id = categoryT.activitycategory_id LEFT OUTER JOIN LicensedClientCategories AS lccategoryT ON categoryT.activitycategory_id = lccategoryT.activitycategory_id AND lccategoryT.licensedclient_id = '1'}
WHERE lcactivityT.licensedclient_id = '1'

This works fine in mssql query analyser but when i use it in code, using mssql jdbc driver, i am getting following error

java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]Syntax error a
t token ON , line 0 offset 84.
at com.microsoft.jdbc.base.BaseExceptions.createExcep tion(Unknown Source
.......

i removed the white spaces and i also have the correct driver, what can i do?

Thanku

View 4 Replies View Related

Insert Query Syntax Error

Dec 14, 2006

I cannot identify where the syntax error is, please help.

INSERT INTO [Attendence/Activity Log] (ID Number, Date, Activity, Duration) VALUES ('39', '12/14/06', 'Health & Nutrition', '2')

View 1 Replies View Related

XML Query Error Incorrect Syntax Near '&&<'

Feb 6, 2007

Hi all,

I use

="<Query><XmlData>" & Parameters!XMLData.Value & "</XmlData><ElementPath>Product {@}</ElementPath></Query>"



and I enter XMLData with value "<Products><Product>Chair</Product><Product>Table</Product></Products>"

for the query expression of the dataset, and I always got error:

Query failed for dataset "Dataset1"

Incorrect syntax near '<'



What's wrong with it?



Thanks,

Jone

View 1 Replies View Related

OPENROWSET Query Will Give Syntax Error - Please Help Me

Feb 28, 2008

I am not able to use WHERE Clause in my query. What am I doing wrong?


Here my query that will generate error:
SELECT * INTO LN_S
FROM OPENROWSET('MSDASQL',
'DSN=SHADOW',
'SELECT * FROM LN_ACCT WHERE trn_dt > '2007-03-08' '

I am getting this error:
Server: Msg 170, Level 15, State 1, Line 4
Line 4: Incorrect syntax near '2007'.


Here is my query which doesn't generate error:
SELECT * INTO LN_S
FROM OPENROWSET('MSDASQL',
'DSN=SHADOW',
'SELECT * FROM LN_ACCT'

Using SQL Server 2000
DSN to a CACHE database on local network

Thanks in advance,

Sam

View 3 Replies View Related

Syntax Error - Parameters In A Derieved Query

Jun 1, 2006

I am geting syntax error with the following query. I created a connection with SQL Native Client as Provider. I looks there is bug when we try to parameterise the derieved query



Select [EvidentiaryDataDefinitionHistoryID]
, EvidentiaryDataDefinitionID, Name, ModifiedUTCDate
From HistoryEvidentiaryDataDefinition HEDD (nolock)
Inner Join
(Select max([EvidentiaryDataDefinitionHistoryID]) AS EDDHistoryID
from HistoryEvidentiaryDataDefinition (nolock)
where ModifiedUTCDate >= ? and ModifiedUTCDate < ?
group by EvidentiaryDataDefinitionID,ModifiedUTCDate) T1
On T1.EDDHistoryID = HEDD.EvidentiaryDataDefinitionHistoryID




Regards

Koya

View 3 Replies View Related

Syntax Error (missing Operator) In Query Expression

Jan 22, 2008

Hi, Please could someone assist - the above error occurs.  This is my code: Protected Sub btnReport_Click(ByVal sender As Object, ByVal e As System.EventArgs)        ' Response.Redirect("CrystalreportTEST_Print.aspx?C_id=" & ddlCompany.SelectedValue)        Dim myConnection As New OleDbConnection(connString)        Dim Str As String = "SELECT clientid,company FROM Client WHERE company =" & ddlCompany.SelectedItem.Text        Dim cmd As New OleDbCommand(Str, myConnection)        Dim ds As New DataSet        Dim da As New OleDbDataAdapter(cmd)        da.Fill(ds)        Label7.Text = ds.Tables(0).Rows(0).Item("clientid")        ' Response.Redirect("CrystalreportTEST_Print.aspx?C_id=" & ds.Tables(0).Rows(0).Item("clientid"))    End Sub  Thank you in advance 

View 1 Replies View Related

Syntax Error (missing Operator) In Query Expression '?

Jan 31, 2008

I'm getting the error listed above.  To create this I used the wizard in VS2005 for the datagrid.  The delete works but not the updating.  I can't seem to find what's wrong.  In the update parameters I've removed eventID and added it, neither works.  Delete does work.  Here's the code:
<asp:GridView ID="GridView1" runat="server" AllowPaging="True" AllowSorting="True" AutoGenerateColumns="False" DataSourceID="ds1" DataKeyNames="eventID"><asp:CommandField ButtonType="Button" ShowEditButton="True" ShowDeleteButton="True" /><asp:BoundField DataField="eventID" HeaderText="ID" SortExpression="eventID" ReadOnly="True" /><asp:BoundField DataField="trainer" HeaderText="trainer" SortExpression="trainer" /><asp:BoundField DataField="employeeNumber" HeaderText="Emp#" SortExpression="employeeNumber" /><asp:BoundField DataField="area" HeaderText="Area" SortExpression="area" /><asp:TemplateField HeaderText="Training Date" SortExpression="trainingDate"><EditItemTemplate><asp:TextBox ID="TextBox1" runat="server" Text='<%# Eval("trainingDate", "{0:M/dd/yy}") %>'></asp:TextBox></EditItemTemplate><ItemTemplate><asp:Label ID="Label2" runat="server" Text='<%# Bind("trainingDate", "{0:M/dd/yy}") %>'></asp:Label></ItemTemplate></asp:TemplateField><asp:CheckBoxField DataField="additionalTrainerExpected" HeaderText="Addl Trainer Expected" SortExpression="additionalTrainerExpected" ><asp:TemplateField HeaderText="Ext Trainer" SortExpression="extendedTrainer"><EditItemTemplate><asp:CheckBox ID="CheckBox1" runat="server" Checked='<%# Bind("extendedTrainer") %>' /></EditItemTemplate><ItemTemplate><asp:CheckBox ID="CheckBox1" runat="server" Checked='<%# Bind("extendedTrainer") %>' Enabled="false" /></ItemTemplate><HeaderStyle Font-Names="Verdana" Font-Size="Small" /><ItemStyle Font-Bold="False" HorizontalAlign="Center" /></asp:TemplateField><asp:TemplateField HeaderText="NonExt Trainer" SortExpression="nonextendedTrainer"><EditItemTemplate><asp:CheckBox ID="CheckBox2" runat="server" Checked='<%# Bind("nonextendedTrainer") %>' /></EditItemTemplate><ItemTemplate><asp:CheckBox ID="CheckBox2" runat="server" Checked='<%# Bind("nonextendedTrainer") %>' Enabled="false" /></ItemTemplate><HeaderStyle Font-Names="Verdana" Font-Size="Small" /><ItemStyle HorizontalAlign="Center" /></asp:TemplateField><asp:SqlDataSource ID="ds1" runat="server" ConnectionString="<%$ ConnectionStrings:TrainingClassTrackingConnectionString %>"ProviderName="<%$ ConnectionStrings:TrainingClassTrackingConnectionString.ProviderName %>"
UpdateCommand="UPDATE [trainingLog] SET [trainer] = ?, [employeeNumber] = ?, [additionalTrainerExpected] = ?, [extendedTrainer] = ?, [nonextendedTrainer] = ?, [area] = ?, [trainingDate] = ? FROM [trainingLog] WHERE [eventID] = ?">
<UpdateParameters><asp:Parameter Name="eventID" Type="Int32" /><asp:Parameter Name="trainer" Type="String" /><asp:Parameter Name="employeeNumber" Type="String" /><asp:Parameter Name="additionalTrainerExpected" Type="Boolean" /><asp:Parameter Name="extendedTrainer" Type="Boolean" /><asp:Parameter Name="nonextendedTrainer" Type="Boolean" /><asp:Parameter Name="area" Type="String" /><asp:Parameter Name="trainingDate" Type="DateTime" /></UpdateParameters>
<DeleteParameters><asp:Parameter Name="eventID" Type="Int32" /></DeleteParameters>

View 4 Replies View Related

Report Bulider - Semantic Query Syntax Error

Apr 27, 2008

Hi,

I am having trouble getting the following query to execute in the report builder:


OPEN Symmetric KEY DemoKey Decryption BY Certificate DemoCertificate;
SELECT

PIN,
EffDate,
Cast(DecryptByKey(Address1) AS varchar(200)) AS Address1,
Cast(DecryptByKey(Address2) AS varchar(200)) AS Address2,
Cast(DecryptByKey(Address3) AS varchar(200)) AS Address3,
Cast(DecryptByKey(City) AS varchar(125)) AS City,
Cast(DecryptByKey(State) AS varchar(125)) AS State,
Cast(DecryptByKey(ZipCode) AS varchar(90)) AS ZipCode,
Cast(DecryptByKey(Country) AS varchar(125)) AS Country
FROM Address
CLOSE Symmetric KEY DemoKey;

I am getting the following error when I try to generate a report in the report builder:

Semantic query execution failed. Incorrect syntax near the keyword 'Open'.
Incorrect syntax near ')'.
----------------------------
Query execution failed for data set 'dataSet'.
----------------------------
An error has occurred during report processing.

I was getting a similar error when I tried to enter the query using the Named Query window of the dsv file. However, I was able to overcome this obstacle by entering the query directly into the code of the dsv file and it works perfectly. That is, when I click on "Explore Data" in the dsv design view the data is retrieved from the database and is decrypted properly.

It appears that the report builder's semantic query engine does not accept transact-sql statements that involve data decryption. Does anybody know if this is true? Or is there some workaround for this situation? Any help would be greatly appreciated.

Thanks,

Mark

View 5 Replies View Related

Nested Select Query Generating Syntax Error

Jan 23, 2008

I hope I'm posting this in the correct forum. If not I apologize. I have a nested select query that I imported from Oracle:

Oracle Version:



Code Snippetselect avg(days) as days from (
select dm_number, max(dm_closedate) - max(comment_closed_date) as days from dm_data
where
dm_type = 'prime' and
dm_closedate <= '31-dec-2007' and
dm_closedate >= '1-dec-2007' and
program = 'aads'
group by dm_number)





SQL Version:



select round(abs(avg(days)), 0) as days from
(select dm.dm_number, abs(datediff(DAY,max(dm.dm_closedate), max(dm.comment_closed_date))) as days
from dm_data dm, ProgramXref px
where
px.Program_Name = 'aads'
and dm.Program_Id = px.Program_Id
and dm.dm_type = 'prime'
and dm.dm_closedate <= '31-dec-2007'
and dm.dm_closedate >= '1-dec-2007'
group by dm.dm_number)





In Oracle the query runs fine. In SQL I am getting a "Line 10: Incorrect syntax near ')'." error. If I run just the nested portion of the query, there are no errors. It only happens when the first query tries to query the nested query. Can anyone help me get the syntax correct?

Thanks,
Lee

View 4 Replies View Related

ERROR:Syntax Error Converting Datetime From Character String. With Stored Procedure

Jul 12, 2007

Hi All,





i have migrated a DTS package wherein it consists of SQL task.

this has been migrated succesfully. but when i execute the package, i am getting the error with Excute SQL task which consists of Store Procedure excution.



But the SP can executed in the client server. can any body help in this regard.





Thanks in advance,

Anand

View 4 Replies View Related

Getting Server Error Syntax Error Converting The Nvarchar Value 'Sonoma' To A Column Of Data Type Int.

Apr 20, 2007

Hi, all
I'm getting this error at runtime when my page tries to populate a datagrid. Here's the relevant code.
First, the user selects his choice from a dropdownlist, populated with a sqldatasource control on the aspx side:<asp:SqlDataSource ID="sqlDataSourceCompany" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
SelectCommand="SELECT [PayrollCompanyID], [DisplayName] FROM [rsrc_PayrollCompany] ORDER BY [DisplayName]">
</asp:SqlDataSource>
 And the dropdown list's code:<asp:DropDownList ID="ddlPayrollCompany" runat="server" AutoPostBack="True" DataSourceID="sqlDataSourcePayrollCompany"
DataTextField="DisplayName" DataValueField="PayrollCompanyID">
</asp:DropDownList>
Then, I use the selectedindexchanged event to bind the data to the datagrid. Here's that code:
 1 Sub BindData()
2
3 Dim ds As New DataSet
4 Dim sda As SqlClient.SqlDataAdapter
5 Dim strSQL As String
6 Dim strCon As String
7
8 strSQL = "SELECT [SocialSecurityNumber], [Prefix], [FirstName], [LastName], [HireDate], [PayrollCostPercent], " & _
9 "[Phone], [BadgeNumber], [IsSupervisor], [SupervisorID], [IsUser], [IsScout] FROM [rsrc_Personnel] " & _
10 "WHERE ([PayrollCompanyID] = @PayrollCompanyID)"
11
12 strCon = "Data Source=DATASOURCE;Initial Catalog=DATABASE;User ID=USERID;Password=PASSWORD"
13
14 sda = New SqlClient.SqlDataAdapter(strSQL, strCon)
15
16 sda.SelectCommand.Parameters.Add(New SqlClient.SqlParameter("@PayrollCompanyID", Me.ddlPayrollCompany.SelectedItem.ToString()))
17
18 sda.Fill(ds, "rsrc_Personnel")
19
20 dgPersonnel.DataSource = ds.Tables("rsrc_Personnel")
21 dgPersonnel.DataBind()
22
23 End Sub
24

 
I'm assuming my problem lies in line 16 of the above code. I've tried SelectedItemIndex, SelectedItemValue too and get errors for those, as well.
What am I missing?
Thanks for anyone's help!
Cappela07

View 2 Replies View Related

Error 241: Syntax Error Converting Datetime From Character String

Jan 7, 2004

Hi All, can someone help me,
i've created a stored procedure to make a report by calling it from a website.
I get the message error "241: Syntax error converting datetime from character string" all the time, i tryed some converting things but nothig works, probably it is me that isn't working but i hope someone can help me.
The code i use is:


CREATE proc CP_Cashbox @mID varchar,@startdate datetime,@enddate datetime
as
set dateformat dmy
go
declare @startdate as varchar
declare @enddate as varchar

--print "query aan het uitvoeren"

select sum(moneyout) / sum(moneyin)*100 as cashbox
from dbo.total
where machineID = '@mID' and njdate between '@startdate' and '@enddate'
GO



Thanx in front
Cya

View 14 Replies View Related

Snapshot Agent Gives Error: Line38: Syntax Error Near 'tabl'

Oct 1, 2005

Hi all,

View 4 Replies View Related

Alter Table Syntax Error - Anyone Sees The Error?

Nov 24, 2004

Hello,

the following alter table statement:

ALTER TABLE [dbo].[CalCalendar]
ALTER COLUMN [OID] uniqueidentifier NOT NULL PRIMARY KEY NONCLUSTERED

is answered with:

Server: Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'PRIMARY'.

which I consider to be interesting. Anyone has an idea why? I checked documentation but I do not see an error.

Note that:

ALTER TABLE [dbo].[CalCalendar]
ALTER COLUMN [OID] uniqueidentifier NOT NULL

DOES get executed, and

ALTER TABLE [dbo].[CalCalendar]
ALTER COLUMN [OID] uniqueidentifier NOT NULL PRIMARY KEY

produces the same error.

Now, in my understanding this has nothing to do with an index may already exist etc. - the eror indicates a SYNTAX error, before any checking. Makes no sense to me, though, reading the documentation.

So - anyone an idea?

View 4 Replies View Related

Error 0: Syntax Error Or Access Violation

Aug 1, 2004

Hi,
I'm writing a stored procedure and when I click on the Check Syntax button its giving me the error in the subject. I'm not really sure whats wrong with this. Here is my Stored Procedure code. Any help wud be appreciated.


CREATE PROC CabsSchedule_Insert
{
@JulianDatesmallint,
@SiteCodesmallint,
@CalendarDaysmallint,
@BillPeriodsmallint,
@WorkDaysmallint,
@CalDayBillRcvd varchar(30),
@Remarksvarchar(50)
}
AS
INSERT INTO CabsSchedule
(JulianDate, SiteCode, CalendarDay, BillPeriod, WorkDay, CalDayBillRcvd, Remarks)
VALUES
(@JulianDate, @SiteCode, @CalendarDay, @BillPeriod, @WorkDay, @CalDayBillRcvd, @Remarks)


Thanks,

View 2 Replies View Related

SQL Query Syntax

Nov 30, 1998

I recently wrote a query similar to this:

Select e.name, e.employee_id, d.department_name
from employees e,departments d
where e.department_id = d.department_id

An associate recommended that I change this query to add the words INNER JOIN like this:

Select e.name, e.employee_id, d.department_name
from employees e inner join departments d
on e.department_id = d.department_id

I was of the understanding that the first query was Microsoft SQL Syntax, and that the second example was ANSI SQL syntax, but that they executed on the server the same way. My associate however thinks that the second example will execute faster.

Would anyone like to confirm this?

View 2 Replies View Related

Please Help Me With The Syntax For This Sql Query.

Aug 29, 2007

Hi.. I want to join this query


Code:

select Count(*) from iCalls_Events where Call_ID = " & Session("Call_ID") & "
select Count(*) from iCalls_Events where Call_ID = "& Session("Call_ID") & " and Events_Flag <> 0


in this query


Code:

select iCalls_Calls.Call_ID,iCalls_Calls.Requestor,Type,Scope,iCalls_Calls.Status_ID,iCalls_Status.Status_I D,
iCalls_Status.Status_Label from
((iCalls_Calls inner join iCalls_Status on iCalls_Calls.Status_ID=iCalls_Status.Status_ID ) inner join iCalls_Users on
iCalls_Calls.Requestor=iCalls_Users.User_ID) left outer join iCalls_Messages on iCalls_Calls.Call_ID=iCalls_Messages.Call_ID where Requestor='" & Session("User_ID") & "' AND iCalls_Calls.Status_ID <> 6 order by iCalls_Calls.Call_ID


The Place where i need to Join is after
Code:

iCalls_Status.Status_ID,iCalls_Status.Status_Label

and before
Code:

((iCalls_Calls inner join iCalls_Status on iCalls_Calls.Status_ID=iCalls_Status.Status_ID )


I want to add ( / ) in between these 2 queries. The reason is for example first query will return '5' and second '10' , so the output i need is 5 / 10. And i need to put this query in a variable (Countrec) like

Code:

select Count(*) from iCalls_Events where Call_ID = " & Session("Call_ID") & " ( / )
select Count(*) from iCalls_Events where Call_ID = "& Session("Call_ID") & " and Events_Flag <> 0 as Countrec


The Final Query would be something like this

Code:

select iCalls_Calls.Call_ID,iCalls_Calls.Requestor,Type,Scope,iCalls_Calls.Status_ID,iCalls_Status.Status_I D,
iCalls_Status.Status_Label, select Count(*) from iCalls_Events where Call_ID = " & Session("Call_ID") & " ( / )
select Count(*) from iCalls_Events where Call_ID = "& Session("Call_ID") & " and Events_Flag <> 0 as Countrec from
((iCalls_Calls inner join iCalls_Status on iCalls_Calls.Status_ID=iCalls_Status.Status_ID ) inner join iCalls_Users on
iCalls_Calls.Requestor=iCalls_Users.User_ID) left outer join iCalls_Messages on iCalls_Calls.Call_ID=iCalls_Messages.Call_ID where Requestor='" & Session("User_ID") & "' AND iCalls_Calls.Status_ID <> 6 order by iCalls_Calls.Call_ID

but this syntax is not correct..Please can U get me the Correct Syntax.

View 7 Replies View Related

SQL Query Syntax Help

Jan 13, 2005

I have a query that I am running out of sql server 2000 that is pulling duplicate records. I can probably figure it out but I am wondering if someone could look at it and point out errors in my syntax and/or structure.

Thanks!


code:--------------------------------------------------------------------------------
SELECT p.ParticipantID, pr.RaceID, p.FirstName, p.LastName, pr.Bib, p.Gender, pr.Age,
pr.AgeGrp, p.DOB, p.Address, p.City, p.St, p.Zip, pr.Clyde, pr.WhlChr, pr.RcWlk,
p.Phone, p.Email, reg.ShrtSize, reg.ShrtStyle, reg.WhereReg, reg.DateReg, reg.AmtPd
FROM Participant p INNER JOIN PartReg reg ON p.ParticipantID = reg.ParticipantID JOIN PartRace pr
ON pr.ParticipantID = p.ParticipantID JOIN RaceData rd ON pr.RaceID = rd.RaceID
WHERE (rd.EventID = 45 AND pr.RaceID = reg.RaceID) ORDER BY p.LastName
--------------------------------------------------------------------------------

View 1 Replies View Related

Query Syntax Help

Jan 13, 2005

I have a query that I am running out of sql server 2000 that is pulling duplicate records. I can probably figure it out but I am wondering if someone could look at it and point out errors in my syntax and/or structure.

Thanks!


code:--------------------------------------------------------------------------------
SELECT p.ParticipantID, pr.RaceID, p.FirstName, p.LastName, pr.Bib, p.Gender, pr.Age,
pr.AgeGrp, p.DOB, p.Address, p.City, p.St, p.Zip, pr.Clyde, pr.WhlChr, pr.RcWlk,
p.Phone, p.Email, reg.ShrtSize, reg.ShrtStyle, reg.WhereReg, reg.DateReg, reg.AmtPd
FROM Participant p INNER JOIN PartReg reg ON p.ParticipantID = reg.ParticipantID JOIN PartRace pr
ON pr.ParticipantID = p.ParticipantID JOIN RaceData rd ON pr.RaceID = rd.RaceID
WHERE (rd.EventID = 45 AND pr.RaceID = reg.RaceID) ORDER BY p.LastName
--------------------------------------------------------------------------------

View 7 Replies View Related

Query Syntax Plz

Feb 6, 2008

am looking to make a select query that has some condition where it should look like:
select if(SomeField is null) then 'Not Set' else SomeField
from SomeTable

how it can be done ?????

View 9 Replies View Related

Query Syntax

Nov 15, 2007

Hi guys<


I wan to know what command can I use to view the latest date of posting of interest in my database given the following tables

Table: DepositInterest
Fields: code1, code2, interestdate

code 1 and 2 are primary keys.
Then I tried to run a query using where clause but then it happened that all interestdate are viewed.

What I want is to just view the latest interestdate.

View 4 Replies View Related

Sql Query Syntax

Jul 23, 2005

Hi all,I have the following tables:T1==ID Name-- ----1 Name12 Name2T2==ID Color-- ----1 Color11 Color22 Color22 Color3and I would like to get the following output (without duplicates):ID Colors-- ------1 Color1, Color22 Color2, Color3Is it possible?

View 4 Replies View Related

Query Syntax Help

Sep 17, 2005

Hello All,I have the following table:CREATE TABLE [dbo].[TBL_NAME] ([NAME] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[STANDARD_NAME] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_ASNULL) ON [PRIMARY]GOWith values:insert into tbl_namevalues('DAN', 'DANIEL')insert into tbl_namevalues('DANNY', 'DANIEL')insert into tbl_namevalues('DANYY', 'DANIEL')Question is:I need want to construct a query which returns all names for a standardname plus the standard name itself.e.g.if name = 'DAN' then return 'DAN', 'DANNY', 'DANYY', 'DANIEL'ff name = 'DANIEL', then return 'DAN', 'DANNY', 'DANYY', 'DANIEL'i have the following sql:declare @name varchar(50)select @name = 'DANIEL'select standard_name from tbl_name where name = @nameunionselect name from tbl_name where standard_name = (select standard_namefrom tbl_name where name = @name)unionselect name from tbl_name where standard_name = @nameunionselect standard_name from tbl_name where standard_name = @name----declare @name varchar(50)select @name = 'DANNY'select standard_name from tbl_name where name = @nameunionselect name from tbl_name where standard_name = (select standard_namefrom tbl_name where name = @name)unionselect name from tbl_name where standard_name = @nameunionselect standard_name from tbl_name where standard_name = @name----Both appear to work fine..can anyone see a fault or suggest a cleanerway to achieve the above ?Suggestions/pointers appreciatedThanks in advance

View 3 Replies View Related

SQL Query Syntax

Jan 20, 2007

sql query syntax?i write this query but error get : column not foundhow can i write ?SELECT c.stock_cat_name, a.stock_code, c.description, a.date,IIF(MID(a.type,2,1)="I",SUM(a.quantity),0) AS iqty,IIF(MID(a.type,2,1)="O",SUM(a.quantity),0) AS oqtyFROM stock_tran AS a LEFT JOIN stock AS c ON c.stock_code=a.stock_codeWHERE a.date>=[from_date] And a.date<=[to_date]GROUP BY c.stock_cat_name, a.stock_code, c.description, a.date,MID(a.type,2,1)ORDER BY c.stock_cat_name, a.stock_code, a.date, MID(a.type,2,1);this database driver sage line 50 odbci write a simpale query of this database, successful run but when iwrite in condition (IF) is not run error comes : column not found.rashesh

View 2 Replies View Related

Query Syntax

Sep 11, 2006

Hi all! I am new here and I'm also new to SQL.. I hope somebody could help me regarding my problem.

I don't know if this is possible but I would like to have a code that
can integrate data from 3 tables. The names of my tables are Savings,
Loans and Insurances. Their common field is the MemberID. One member
could have zero or more Savings Accounts. At the same time, a member
could also have one or more accounts on Loans or Insurances.

How can I get the data that would appear like this:



MemberID - Savings Account - Loan Account - Insurance Account

0001
-
S0001
- L0002

- I0001


-
S0003
-
L0005
-

0002
-
S0012
-
-

0003
-
S0004
-
- I0002


-
-
- I0003







I'm using MS SQL Server 2005.

Hope you guys could help me with this. Thanks a lot, in advance!

View 6 Replies View Related

Help With Query Syntax

Aug 2, 2006

Hi all, I have been fighting with this query and would like some advice. Please consider the following tables;

prod_table
widget_number
shift
date
production_time (in minutes)

down_table
rec_id (ident key)
down_shift
down_date
down_minutes

Prod_table (data)

widget_number shift date production_time
0001 1 08/02/06 5.00
0002 1 08/02/06 10.00
0003 1 08/02/06 7.00
0004 2 08/02/06 5.00
0005 2 07/31/06 3.00

Down_table (data)
rec_id down_shift down_date down_minutes
1 1 08/02/06 3.00
2 1 08/02/06 20.00
3 2 07/31/06 10.00

I would like to combine the production times and down times into one summary where the down time is in the same date and shift as the production time.
As you can see in my results below, I can group them correctly, but the down totals obviously repeat for each match. Is there any way of getting to the "Desired results"?

My Results

widget_number shift date production_time down_time
0001 1 08/02/06 5.00 23.00
0002 1 08/02/06 10.00 23.00
0003 1 08/02/06 7.00 23.00
0004 2 08/02/06 5.00 0.00
0005 2 07/31/06 3.00 10.00


Desired results

Widget_number shift date production_time down_time
0001 1 08/02/06 5.00 23.00
0002 1 08/02/06 10.00 NULL
0003 1 08/02/06 7.00 NULL
0004 2 08/02/06 5.00 0.00
0005 2 07/31/06 3.00 10.00

thank you in advance.

View 10 Replies View Related

SQL Query Incorrect Syntax Help

Mar 4, 2007

I am getting a SQLExcepetion error near , in this query string...so obvicously my query string is wrong...
could someone help me get this query string right please...
 
Thanks
"Select OrgID, OrgName From aspnet_OrgNames Where UserID = @UserID, OrgID = @OrgID"

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved