MS SQL Server :: Multiple ORDER BY Query Syntax

Query Multiple Tables - Union/Order By

Oct 25, 2007

Hi!

I'm trying to get the results from three different tables, where they have some of the same results. I'm only interested in where they match and then trying to order by date (that's in three columns - M, D, Y). I read previous post in 9/07 but the result doesn't seem to order correctly. It does not have any rhyme or reason to the outputed results as it bounces back and forth through Oct, Nov and Dec posting and throughout all three tables. Here's my query below. Any ideas how I can get my ordering correct for all three tables to display all Oct, all Nov and all Dec?

Thanks so much

select date3, date2, date1, who, what
from
(
select date3, date2, date1, who, what from shows
union
select date3, date2, date1, who, what from shares
union
select date3, date2, date1, who, what from soiree
)
a order by date3, date2, date1

View 4 Replies View Related

Display Multiple Items On One Line Per Order (was Query)

Jun 27, 2005

I have table1 with orderID and demographic info.
Table2 with orderID and items.
I would like to have a results display like this:
OrderIDDemographicInfo Item1Item2Item3....ect
One line per order. When I do a join I displaying all items in different rows.

View 1 Replies View Related

XML Data To SQL Server With Multiple Orders An Multiple Order Details??

Aug 5, 2004

Hi all!

I'm trying to get some XML data into SQL Server but i ran into problem when inserting the data (multiple orders with multiple order details) using a single sproc. Is it possible, or do I have to do in some other way? :confused:

I simplified my example to this:
-----------------------------
--CREATE PROCEDURE sp_InsertOrders AS

DECLARE @docHandle INT, @xmlDoc VARCHAR(4000), @orderID INT

--DROP TABLE #Orders
CREATE TABLE #Orders
(
OrderId SMALLINT IDENTITY(1,1),
FkCustomerID SMALLINT NOT NULL,
OrderDate DATETIME NOT NULL
)

--DROP TABLE #OrderDetails
CREATE TABLE #OrderDetails
(
OrderDetailsId SMALLINT IDENTITY(1,1),
FkOrderID SMALLINT NOT NULL,
ProductID SMALLINT NOT NULL,
UnitPrice SMALLINT NOT NULL
)

Set @xmlDoc = '
<Orders>
<Order CustomerID="1" OrderDate="2004-04-01">
<OrderDetails ProductID="6" UnitPrice="19"/>
<OrderDetails ProductID="3" UnitPrice="11"/>
<OrderDetails ProductID="9" UnitPrice="7"/>
</Order>
<Order CustomerID="2" OrderDate="2004-04-12">
<OrderDetails ProductID="2" UnitPrice="24"/>
<OrderDetails ProductID="4" UnitPrice="13"/>
</Order>
</Orders>'

EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmlDoc

INSERT INTO #Orders (FkCustomerID, OrderDate)
SELECT CustomerID, OrderDate
FROM OpenXML(@docHandle, 'Orders/Order', 3)
WITH (
CustomerID INTEGER,
OrderDate DATETIME
)

SET @OrderID = @@IDENTITY;

--INSERT INTO #OrderDetails (@OrderID, ProductID, UnitPrice)
SELECT @OrderID AS OrderID, ProductID, UnitPrice
FROM OpenXML(@docHandle, 'Orders/Order/OrderDetails', 3)
WITH (
ProductID INTEGER,
UnitPrice INTEGER
)
-----------------------------

All orders are inserted first which makes the use of @@IDENTITY incorrect (it works fine if you insert a single order with multiple order details). Since it was quite some time since I last worked with SQL I am not sure if am doing it the right way... :confused: :confused: Anybody out there who knows how to solve the problem?

Cheers,
Christian

View 2 Replies View Related

Transact SQL :: Incorrect Syntax Near Order

Sep 18, 2015

select
row_number() over (partition by merrickid order by recorddate asc) as rn,
merrickid,
recorddate,
allocestgasvolmcf,
sum(allocestgasvolmcf) over (partition by merrickid order by recorddate asc) as xxxxxxxx
from dbo.completiondailytb
where merrickid=1965

I get the following message after I insert the sum() over function:

Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'order'.

View 4 Replies View Related

Multiple ORDER BY

May 15, 2007

I am pretty new to SQL coding, but I assume this can be done and I just don't know the syntax.

I have an application which pulls my data using "... ORDER BY #date#"

Can I have it do something like "... ORDER BY #date# AND name" so that it sorts things first by date, and then by name?

Thanks in advance!

View 2 Replies View Related

Multiple Order Bys In One Request

Oct 7, 2005

Greetings
This probably is a silly question but I can't get it to work.
I need to pull data from a table and Order the information by 2 requests.

Something like ...

Select * from MyTable
where pid = @pid
Order by DATE and Order by Product

Can someone help me in getting my ORDER statements to work?

Thx,
Edb

View 2 Replies View Related

ORDER BY, CASE, With Multiple Columns

Nov 7, 2003

I'm unable to specify multiple columns in my order by statement if i use a case statement.
Does anyone know why this is, or what syntax would make this work?

Thanks

SELECT ....
ORDER BY (CASE Lower(@SortExpression)
WHEN 'prodname' THEN prodname, prodprice
WHEN 'prodsize' THEN prodsize, prodname
WHEN 'prodprice' THEN prodprice, prodname
Else prodcompany, prodname
END)

View 5 Replies View Related

ORDER BY Clause With Multiple Tables

Aug 25, 2005

Hi AllI am having a problem with an ORDER BY clause when selecting information from multiple tables. EgSELECT i.InvoiceId, pd.PayDescription, u.UserNameFROM Invoice i LEFT OUTER JOIN tblPay ON i.PayId = pd.PayId LEFT OUTER JOIN tblUsers ON i.UserId = u.UserIdORDER BY pd.PayDescriptionthis is just an example my query is a lot more complex. Is there any simply way you can do an order by in this way?I am writing this for MSSQL Server 2000ThanksBraiden

View 6 Replies View Related

Run Multiple SSIS Packages In Order

Sep 21, 2006

I built 3 diffrent packages and i want to be executed in order. The first one is exporting some tables to another database and the other two packages are based on the database built in the first package.

Any suggestions?

Thank you in advance:)

View 6 Replies View Related

Multiple Row Delete Syntax Error

Feb 2, 2007

Hello
I am trying to delete multiple rows in my sql 2000 database, I have used the following sysntax but I keep getting errors:
DELETE From NameList
WHERE LName = 'Smith';and LName = 'Jones';and LName = 'Peters';and LName = 'Adams';and LName = 'Conner';and LName = 'Simon';
I have tried editing the syntax in a variety of ways, but I just can't find the correct solution.
I have Googled and so far not found another syntax format. What am I doing wrong.
Thanks.
Lynn

View 7 Replies View Related

Multiple Order By Statements In Union Select

Jan 20, 2005

I'm new to SQL stored procedures, but I'm looking to be able to select order by statement.

declare @OrderBy
@OrderBy = 'first_name'

Select first_name,last_name from table1
Union
Select first_name,last_name from table2

if @OrderBy = 'first_name' begin
Order By first_name
else
Order By last_name
end

You'll have to excuse my if statement if the syntax is incorrect (used to only writing asp ifs :P). Thanks for any help

View 6 Replies View Related

Show Multiple Order Records In Single Row

Mar 13, 2006

I have two tables CompanyTab and OrderTab .CompanyTab table contain one record for each client while OrderTab table contain multiple orders for clients.

I have data in both table like

CompanyTable
ID Name
1 name1
2 name2

OrderTable

OrderId CompanyTabID
1 1
2 1
3 1
4 1

In my query I want to show all orders in single row.

ID Name Orders
1 name1 1,2,3,4
2 name2 null

Is anybody can help on it.

Thanks
Arvind

View 5 Replies View Related

SQLCE Syntax For Multiple Word Columns

Feb 2, 2005

What is the syntax when creating a SQL statement and using a column that has a multiple word name?

I know that in query analyzer it should be delimited? by double quotation marks like such:
SELECT Item, "Item Description" FROM ComponentList WHERE Item = 'CPU'
The above works great in Query Analyzer on the device and in the device emulator.

but I'm attempting to build a SQL string in code...VB.Net using CF and PocketPC SDK, SQLCE on the PocketPC device....

Here's the snippet that does not work:
sql = "SELECT Item, 'Item Description' FROM ComponentList WHERE Item = '" & selectedItem & "' "

Try
cnITSComponents.Open()
cmdItems = New SqlCeCommand(sql, cnITSComponents)
drItems = cmdItems.ExecuteReader()
fieldCount = drItems.FieldCount

While drItems.Read()
.....More code here not copied

I'm trying to fill a listview control and when I use the above syntax one of the columns is filled with the text Item Description instead of pulling the actual Item Description from the table...So what is the correct way to construct the SQL string?

Thanks
Greg

View 5 Replies View Related

Sql Syntax For Multiple Likes Phrases In Where Clause

Nov 16, 2007

You know how you can go:

Code Block
where control_id in (11111,22222,33333,44444)

or

Code Block
where TextName in ('11111','22222','33333','44444')

and you can do this:

Code Block
where TextName like '11%' or TextName like '22%'

well how do you do this? Or can you... or can we right a function to do it... or are we just hosed writing like after like.

where TextName like in ('11%','22%',... and so on)? is anything like that possible?

or better yet
where TextName like in ( select substring(columnName, 1, 2) + '%' from whatever )

can you imagine the dynamics if that syntax actually worked?

Can a function be written to mimic this functionality? so I can do something like this:
where TextName = function('11%,22%,33%')

View 13 Replies View Related

Multiple Versions Of SQL Server 2005 - What Order To Install?

Jun 15, 2007

I currently have three SQL Server instances installed on my notebook. One is v8, which I believe is used by an accounting application. The other two are as follows:-

SQL Server MSSMLBIZ

SQL Server SQLEXPRESS

The second one (SQLEXPRESS) fails to start generating the following error:-

The SQL Server (SQLEXPRESS) service terminated with service-specific error 17058 (0x42A2).

Other services listed in SQL Server Configuration Manager are:-

SQL Server Integration Services - which runs apparently normally.

SQL Server Analysis Services (MSSQLSERVER)

SQL Server Reporting Services (MSSQLSERVER)

SQL Server Browser

The last thre of these are run under LocalSystem.

I have been trying to install the SQLServer SP2 to the SQLEXPRESS instance for some number of days now, but each time it runs, it fails to install.

I am beginning to wonder if there is a sequence of installation for each of the above instances that should be followed. Can anyone give me any suggestions, or answers to the above problems?

Thanks,

View 3 Replies View Related

Outer Join Syntax Problems (Multiple Tables)

Sep 13, 2005

Hello all--

I'm trying to run a SELECT on 3 tables: Class, Enrolled, Waiting.
I want to select the name of the class, the count of the students enrolled, and the count of the students waiting to enroll.

My current query...

SELECT     Class.Name, COUNT(Enrolled.StudentID) AS EnrolledCount, COUNT(Waiting.StudentID) AS WaitingCount
FROM         Class LEFT OUTER JOIN

Enrolled ON Class.ClassID = Enrolled.ClassID LEFT OUTER JOIN

Waiting ON Class.ClassID = Waiting.ClassID
GROUP BY Class.Name

...results in identical counts for enrolled and waiting, which I know
to be incorrect. Furthermore, it appears that the counts are being
multiplied together (in one instance, enrolled should be 14, waiting
should be 2, but both numbers come back as 28).

If I run this query without one of the joined tables, the counts are
accurate. The problem only occurs when I try to pull counts from both
the tables.

Can anyone find the problem with my query? Should I be using something other than a LEFT OUTER JOIN?

Thanks very much for your time,
--Jeremy

View 2 Replies View Related

Multiple CASE Statements - Three Fields To Be True In Order To Return Y

Feb 19, 2014

How do I properly write a CASE statement for the following business logic:

I want three of the following fields to be true in order to return 'Y'.

For example:

If field name 'Accepted' = 1 AND field 'StepNo' = '1' and field 'Visit' = 'V1'

Otherwise, I want it to return 'N'.

I have tried the following code below and it is not working.

, CASE WHEN Accepted = '1' AND StepNo ='1' AND Visit ='V1'
THEN 'Y' ELSE 'N' END AS 'StatusQ (Col N)'

View 2 Replies View Related

Syntax For Returning And Accessing Multiple Result Sets In MSSQL 2k

Dec 29, 2006

Seasons greetings to everyone,A simple question. Could someone show me the syntax to produce multiple (2 or 3) result sets in a stored proc and how you access those sets from a c# program (ASP.NET)..Couldn't find a reference on Google, maybe I was asking the wrong question! Thanks for any help regardsDavej

View 3 Replies View Related

Stored Procudure With Multiple Select... Incorrect Syntax Near 'storedProcedure'

Mar 8, 2004

Hi,

Im fairly new to writing stored procudures so I thought you lot might be able to help with this problem.

I have a stored procudure which looks like this:

CREATE PROCEDURE usrCienet.spAdminAgencyActivate_Select
(
@strAgencyId CHAR(6)
)

AS

DECLARE @idAgency INT

SELECT @idAgency = idAgency FROM tblAgencies WHERE strAgencyId = @strAgencyId;

SELECT strName, strAddress1, strAddress2, strCounty, strCountry, strPostcode, strTelephone, strFax, bitActive, bitHeadOffice, bitFinanceBranch
FROM tblBranches
WHERE fk_idAgency = @idAgency

SELECT strFirstName, strSurname, strUsername, bitActive
FROM tblUsers
WHERE fk_idAgency = @idAgency

GO

It basically first declare's and sets @idAgency using the first small select statment, then uses that parameter to run two more selects queries which I want sending to a dataset. Now within the Query Analyzer this works fine. But in my asp.net page it trows up this error:

Exception Details: System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near 'spAdminAgencyActivate_Select'

Now the code im using in the asp.net page is as follows:

Dim objSqlConnection as New SqlConnection(ConfigurationSettings.AppSettings("strCon"))
Dim objSqlCommand_Select as New SqlCommand("spAdminAgencyActivate_Select", objSqlConnection)

objSqlCommand_Select.Parameters.Add(New SqlParameter("@strAgencyId", SqlDbType.Char, 6))
objSqlCommand_Select.Parameters("@strAgencyId").Value = "tes001"

Dim objSqlDataAdapter as New SqlDataAdapter(objSqlCommand_Select)

Dim dsActivateAgency as New DataSet()

objSqlConnection.Open()
objSqlDataAdapter.TableMappings.Add("Table", "tblBranches")
objSqlDataAdapter.TableMappings.Add("Table1", "tblUsers")
objSqlDataAdapter.Fill(dsActivateAgency)
objSqlConnection.Close()

Can anyone help? I believe the error is in the stored procedure somewhere, because if I change the stored procedure so no parameters are being passed to it then it starts working. This is what I comment out and change for it to to get it working, but obviously this is not satisfactory as a final result because the parameter is hard coded in the stored procedure. Im just showing this to see if it gives anyone a clue!!

CREATE PROCEDURE usrCienet.spAdminAgencyActivate_Select
--(
--@strAgencyId CHAR(6)
--)

AS

--DECLARE @idAgency INT

--SELECT @idAgency = idAgency FROM tblAgencies WHERE strAgencyId = @strAgencyId;

SELECT strName, strAddress1, strAddress2, strCounty, strCountry, strPostcode, strTelephone, strFax, bitActive, bitHeadOffice, bitFinanceBranch
FROM tblBranches
WHERE fk_idAgency = 1

SELECT strFirstName, strSurname, strUsername, bitActive
FROM tblUsers
WHERE fk_idAgency = 1

GO

Thanks in advance for any help!!

- Carl S

View 1 Replies View Related

Transact SQL :: Query To Convert Single Row Multiple Columns To Multiple Rows

Apr 21, 2015

View 6 Replies View Related

One Receipt Number, Query Multiple Tables Gives Multiple Data.

Sep 8, 2006

I have just taken over the job of sorting out a rather poorly designed database. It looks like it was 'upsized' from an access database to the SQL server. The SQL server is the 2000 version.

Now I am trying to generate a report of what the students in the database are owing by referencing the Receipt table and then all the available payment methods and allocations. I was wondering if there was anyway to work out data being displayed twice (Let me demonstrate)

Note1: All the tables are linked by a key of ReceiptNo. From what I can see there is a table for every payment type and allocation but no link between the two other then the receipt number.

Using the query:
SELECT T_Receipt.ReceiptNo, T_cheque.Amount AS Chq_Amount, T_credit.Amount AS Cre_Amount, StandingOrder.Amount AS Stn_Amount,
T_BankTransfer.amount AS Bnk_Amount, T_cash.TotalAmount AS Cas_Amount, T_RentPayment.AmountPayed AS Ren_Paid,
T_AdminPayment.AmountPaid AS Adm_Paid, T_InternetBilling.Total AS Int_Paid, T_Utilities.AmountPaid AS Util_Amount,
T_InvoicePayment.amountPaid AS Inv_Paid, T_OtherPayments.paymentAmount AS Oth_Paid, T_parkingBill.paymentAmount AS Prk_Paid,
T_TelephoneBills.TelephoneCredit AS Tel_Paid, T_DepositPayment.[Deposit payment] AS Dep_Amount, T_Receipt.cancelled AS Canceled,
T_Receipt.RemittanceReceiptNo AS Rec_Ref, T_Receipt.Student
FROM T_Receipt INNER JOIN
T_DepositPayment ON T_Receipt.ReceiptNo = T_DepositPayment.receiptNo LEFT OUTER JOIN
T_RentPayment ON T_Receipt.ReceiptNo = T_RentPayment.RentPaymentNo LEFT OUTER JOIN
StandingOrder ON T_Receipt.ReceiptNo = StandingOrder.ReceiptNo LEFT OUTER JOIN
T_TelephoneBills ON T_Receipt.ReceiptNo = T_TelephoneBills.ReceiptNo LEFT OUTER JOIN
T_parkingBill ON T_Receipt.ReceiptNo = T_parkingBill.ReceiptNo LEFT OUTER JOIN
T_OtherPayments ON T_Receipt.ReceiptNo = T_OtherPayments.ReceiptNo LEFT OUTER JOIN
T_InvoicePayment ON T_Receipt.ReceiptNo = T_InvoicePayment.receiptNo LEFT OUTER JOIN
T_cash ON T_Receipt.ReceiptNo = T_cash.ReceiptNo LEFT OUTER JOIN
T_AdminPayment ON T_Receipt.ReceiptNo = T_AdminPayment.ReceiptNo LEFT OUTER JOIN
T_BankTransfer ON T_Receipt.ReceiptNo = T_BankTransfer.receiptNo LEFT OUTER JOIN
T_Utilities ON T_Receipt.ReceiptNo = T_Utilities.receiptNo LEFT OUTER JOIN
T_credit ON T_Receipt.ReceiptNo = T_credit.ReceiptNo LEFT OUTER JOIN
T_cheque ON T_Receipt.ReceiptNo = T_cheque.ReceiptNo LEFT OUTER JOIN
T_InternetBilling ON T_Receipt.ReceiptNo = T_InternetBilling.ReceiptNo
GROUP BY T_Receipt.Student, T_Receipt.ReceiptNo, T_cheque.Amount, T_credit.Amount, StandingOrder.Amount, T_BankTransfer.amount, T_cash.TotalAmount,
T_AdminPayment.AmountPaid, T_InternetBilling.Total, T_Utilities.AmountPaid, T_InvoicePayment.amountPaid, T_OtherPayments.paymentAmount,
T_parkingBill.paymentAmount, T_TelephoneBills.TelephoneCredit, T_Receipt.cancelled, T_Receipt.RemittanceReceiptNo,
T_DepositPayment.[Deposit payment], T_RentPayment.AmountPayed, T_Receipt.Student
HAVING (T_Receipt.Student LIKE N'06%')

Which gives a result of:

RecNo.
30429
Cheque
250
Deposit
250

30429
679.98
250

This is fine but when I do analysis on this it appears as though the student has paid two deposit payments. I was wondering with out querying each table independently from an application if there was a criteria to specify that I only get one deposit result.
So as such say, give me all the payments but I only want one result from the other tables. I though about discrete but that wouldn't work here.

View 3 Replies View Related

Order By Query

Jun 14, 2007

Hi, I am using below query:
SELECT tbh_Articles.ArticleID, tbh_Articles.AddedDate, tbh_Articles.AddedBy, tbh_Articles.CategoryID, tbh_Articles.Title, tbh_Articles.Abstract, tbh_Articles.Body, tbh_Articles.Country, tbh_Articles.State, tbh_Articles.City, tbh_Articles.ReleaseDate, tbh_Articles.ExpireDate, tbh_Articles.Approved, tbh_Articles.Listed, tbh_Articles.CommentsEnabled, tbh_Articles.OnlyForMembers, tbh_Articles.ViewCount, tbh_Articles.Votes, tbh_Articles.TotalRating, tbh_Articles.ImageURL, tbh_Articles.specialFROM tbh_Lang CROSS JOIN tbh_ArticlesWHERE (tbh_Lang.LangID = @LanguageID) AND (tbh_Articles.ArticleID = tbh_Lang.ArticleMain OR tbh_Articles.ArticleID = tbh_Lang.ArticleSecond1 OR tbh_Articles.ArticleID = tbh_Lang.ArticleSecond2 OR tbh_Articles.ArticleID = tbh_Lang.ArticleSecond3 OR tbh_Articles.ArticleID = tbh_Lang.ArticleSecond4 OR tbh_Articles.ArticleID = tbh_Lang.ArticleSecond5)
Problem is that I want to sort in a manner which the results returns as ArticleMain as the first column, ArticleSecond1 as the second and so on...
Tables structure: tbh_Articles(id, title, body...) ; tbh_Lang(id,ArticleMain,ArticleSecond1 ,ArticleSecond2.... )
Any suggestions?

View 9 Replies View Related

Order By Query

Sep 29, 2007

I have a Comment Table where a comment can have a reply, if the comment is replied to I want the reply to appear under the comment.

Based on the Fields CommentID and Parent ID the parentID is the Comment and the Comment with a ParentID set too that comment is the answer.

How do I build this Query?

View 7 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