MS SQL Server :: IF ELSE Statement In WHERE Clause

If/Case Statement In Having/Where Clause - Please Help Me.
Hi!I want to make search engine and I have problem with query for this search. User can write username to search or text to search or both. So at first I made query for each event individually: ALTER PROCEDURE [dbo].[Show_Search_Topics]
@username varchar(200),
@search_text varchar(200),
@days int
AS
DECLARE @date DATETIME
SET @date = DATEADD(day,@days,GETDATE())
IF @username IS NOT NULL AND @search_text IS NULL
BEGIN
SELECT COUNT(dbo.forum_topics.post_user_id) AS UserPosts, forum_topics_1.post_title, dbo.aspnet_Users.UserName, forum_topics_1.post_id,
forum_topics_1.post_current_date, forum_topics_1.post_stick, forum_topics_1.post_user_id, forum_topics_1.post_cat_id,
dbo.forum_kategorie.forum_kat_kolor, dbo.forum_kategorie.forum_kat_nazwa, COUNT(DISTINCT forum_topics_2.post_id) + 1 AS post_total
FROM dbo.forum_topics INNER JOIN
dbo.aspnet_Users ON dbo.forum_topics.post_user_id = dbo.aspnet_Users.uID INNER JOIN
dbo.forum_topics AS forum_topics_1 ON dbo.aspnet_Users.uID = forum_topics_1.post_user_id INNER JOIN
dbo.forum_kategorie ON forum_topics_1.post_cat_id = dbo.forum_kategorie.forum_kat_id LEFT OUTER JOIN
dbo.forum_topics AS forum_topics_2 ON forum_topics_1.post_id = forum_topics_2.post_parrent_id
GROUP BY forum_topics_1.post_title, dbo.aspnet_Users.UserName, forum_topics_1.post_parrent_id, forum_topics_1.post_id, forum_topics_1.post_current_date,
forum_topics_1.post_stick, forum_topics_1.post_user_id, forum_topics_1.post_cat_id, dbo.forum_kategorie.forum_kat_kolor,
dbo.forum_kategorie.forum_kat_nazwa
HAVING (forum_topics_1.post_parrent_id = 0) AND (dbo.aspnet_Users.UserName = @username) AND (forum_topics_1.post_current_date >= @date)
ORDER BY forum_topics_1.post_stick DESC, forum_topics_1.post_current_date DESC
END
ELSE
IF @username IS NULL AND @search_text IS NOT NULL
BEGIN
SELECT COUNT(dbo.forum_topics.post_user_id) AS UserPosts, forum_topics_1.post_title, dbo.aspnet_Users.UserName, forum_topics_1.post_id,
forum_topics_1.post_current_date, forum_topics_1.post_stick, forum_topics_1.post_user_id, forum_topics_1.post_cat_id,
dbo.forum_kategorie.forum_kat_kolor, dbo.forum_kategorie.forum_kat_nazwa, COUNT(DISTINCT forum_topics_2.post_id) + 1 AS post_total
FROM dbo.forum_topics INNER JOIN
dbo.aspnet_Users ON dbo.forum_topics.post_user_id = dbo.aspnet_Users.uID INNER JOIN
dbo.forum_topics AS forum_topics_1 ON dbo.aspnet_Users.uID = forum_topics_1.post_user_id INNER JOIN
dbo.forum_kategorie ON forum_topics_1.post_cat_id = dbo.forum_kategorie.forum_kat_id LEFT OUTER JOIN
dbo.forum_topics AS forum_topics_2 ON forum_topics_1.post_id = forum_topics_2.post_parrent_id
GROUP BY forum_topics_1.post_title, dbo.aspnet_Users.UserName, forum_topics_1.post_parrent_id, forum_topics_1.post_id, forum_topics_1.post_current_date,
forum_topics_1.post_stick, forum_topics_1.post_user_id, forum_topics_1.post_cat_id, dbo.forum_kategorie.forum_kat_kolor,
dbo.forum_kategorie.forum_kat_nazwa
HAVING (forum_topics_1.post_parrent_id = 0) AND (forum_topics_1.post_current_date >= @date) AND (forum_topics_1.post_title LIKE '%' + @search_text + '%')
ORDER BY forum_topics_1.post_stick DESC, forum_topics_1.post_current_date DESC
END
ELSE
IF @username IS NOT NULL AND @search_text IS NOT NULL
BEGIN
SELECT COUNT(dbo.forum_topics.post_user_id) AS UserPosts, forum_topics_1.post_title, dbo.aspnet_Users.UserName, forum_topics_1.post_id,
forum_topics_1.post_current_date, forum_topics_1.post_stick, forum_topics_1.post_user_id, forum_topics_1.post_cat_id,
dbo.forum_kategorie.forum_kat_kolor, dbo.forum_kategorie.forum_kat_nazwa, COUNT(DISTINCT forum_topics_2.post_id) + 1 AS post_total
FROM dbo.forum_topics INNER JOIN
dbo.aspnet_Users ON dbo.forum_topics.post_user_id = dbo.aspnet_Users.uID INNER JOIN
dbo.forum_topics AS forum_topics_1 ON dbo.aspnet_Users.uID = forum_topics_1.post_user_id INNER JOIN
dbo.forum_kategorie ON forum_topics_1.post_cat_id = dbo.forum_kategorie.forum_kat_id LEFT OUTER JOIN
dbo.forum_topics AS forum_topics_2 ON forum_topics_1.post_id = forum_topics_2.post_parrent_id
GROUP BY forum_topics_1.post_title, dbo.aspnet_Users.UserName, forum_topics_1.post_parrent_id, forum_topics_1.post_id, forum_topics_1.post_current_date,
forum_topics_1.post_stick, forum_topics_1.post_user_id, forum_topics_1.post_cat_id, dbo.forum_kategorie.forum_kat_kolor,
dbo.forum_kategorie.forum_kat_nazwa
HAVING (forum_topics_1.post_parrent_id = 0) AND (forum_topics_1.post_current_date >= @date) AND (forum_topics_1.post_title LIKE '%' + @search_text + '%') AND
(dbo.aspnet_Users.UserName = @username)
ORDER BY forum_topics_1.post_stick DESC, forum_topics_1.post_current_date DESC
END
RETURN This 3 queries are different only by Having clause. So I want to put If/Case in Having clause, but I have problem. Can anyone help me?Also I want to make paging from SQL level, so if anyone will be so helpful and make working this query with this: ALTER PROCEDURE [dbo].[Show_Search_Topics]
@username varchar(200),
@search_text varchar(200),
@days int,
@page int,
@page_size int
AS
WITH Results As
(
//QUERY)

)
SELECT * FROM Results
WHERE RowNumber BETWEEN (@page_size * @page + 1) AND (@page_size * (@page + 1))
ORDER BY forum_topics_1.post_stick DESC, forum_topics_1.post_current_date DESC
RETURN I will be grateful :-)

View Replies !

Case Statement In Where Clause
I need a SQL statement that selects a specific year (@yr type int) in the "createddate" column...if this @yr is equal to 0 then I want to select ALL columns regardless of the year...This is what I have so far, but it doesnt work...SELECT * FROM tblUsersWHERE year(CreatedDate)=CASEWHEN @yr<>'0' THEN @yrELSE NOT NULLEND

View Replies !

Need Help With Query Statement 'IN' Clause.
I have a column in the database that stored moduleId that are seperated by '|' (pipes). For Example: '527|343|454'

I need to add a where clause to a query that pulls the data based on a
ModuleId. For Example: select * from table where 527 in [column above]

Does anyone know how I can do this in a query? Normally I could
use an IN statement, ex: select * from table where 527 in (527,343,454)

How can I get the column in that format?

Thanks for the help in advance,

KM

View Replies !

Case Statement In Where Clause
If you could help me with my syntax i would really appreciateit. I'm trying to change the where clause on the fly, but it'sgiving me a syntax error.Hopefully from what I have below you can tell what i'm afterthe first part should evaluate a boolean expression, then if true,search using one field, otherwisesearch using a different fieldWHERECase WHEN @myCompany = 933THEN tblClient.companycode = 933 --problem lineELSEtblCase.clientcode = @myClient --problem lineENDAnd tblCase.status = 'Active'thank you!!

View Replies !

CASE Statement In Where Clause?
Can anyone tell me if it's possible to use a Case statement in a Whereclause, and if so, the proper syntax?J.R.Largo SQL ToolsThe Finest Collection of SQL Tools Availablehttp://www.largosqltools.com

View Replies !

Case Statement In Where Clause
Hello

I want to put a case statement into a where clause but it's not working. Can anybody help, or tell me a better way of doing this

Thanks very much

declare @param varchar (100)
select @param = 'mytext

select
colA
,colB
,colC
from
mytable
where
(case
when @param is null then colA = 'group'
else colA = 'single'
end)

View Replies !

HAVING Clause Is A Case Statement???
i have wrote a query which compares two tables, returning anywhere the qty is not the same in each table:

(simple ex)

Select order_no
from table1
inner join table2
on table1.order_no = table2.order_no
group by order_no
having table1.Qty<> table2.Qty

BUT... I need to add a table3, where there maybe (or may not be enters - thus a left join). If there is an entry in table3 then use qty in table3 and not table1... so having becomes:

CASE WHEN table1.Qty<> table3.Qty
THEN table3.Qty<> table2.Qty
ELSE table1.Qty<> table2.Qty END

but how do i actually write this?

View Replies !

Case Statement In Where Clause
can i use case statement in where clause.
The scenario is as follow

declare @param int
select
*
from
table1
where
column1 = 'asdf'
column2= @param

In the above sql, if @param is '' then i don't want to include it in the where clause.I can use "like" statement for that, but i want exact value not partial value.

can i use case in where clause so that if @param is '' then i will not include in the where clause

View Replies !

Case Statement In Where Clause?
Can I do the following? Keep getting an error stopping at the first < of the where clause.

declare @mon as int, @yr as int, @myDate as varChar(20)
set @yr=2006
set @mon=1
set @mydate='01/31/2006 23:59:59'

select 0 as DTAP, 0 as DT, 0 as TD, 0 as HIB, 0 as IPV, 0 as MMR, 0 as HEPB, 0 as _VAR, count(v.procedureKey) as FLU, 0 as PPV23, 0 as PCV7, v.chartID, max(rs1.dateService) as dateService from dbo.tbl1 v,
(select distinct dateService, chartID, procedureKey from fhc.dbo.tbl1 where (datePart(year,dateService)=@yr and datePart(month,dateService)=@mon) and (procedureKey='90657' or procedureKey='90658')) as rs1
where (v.chartID=rs1.chartID) and (v.procedureKey=rs1.procedureKey) and
(case when @mon=1 then dateDiff(month,dateService,@myDate)< 216
when @mon=2 then dateDiff(month,dateService,@myDate)<244
when @mon=3 then dateDiff(month,dateService,@myDate)<275
when @mon=4 then dateDiff(month,dateService,@myDate)<305
when @mon=5 then dateDiff(month,dateService,@myDate)<336
when @mon=6 then dateDiff(month,dateService,@myDate)<366
when @mon=7 then dateDiff(month,dateService,@myDate)<32
when @mon=8 then dateDiff(month,dateService,@myDate)<63
when @mon=9 then dateDiff(month,dateService,@myDate)<93
when @mon=10 then dateDiff(month,dateService,@myDate)<124
when @mon=11 then dateDiff(month,dateService,@myDate)<154
when @mon=12 then dateDiff(month,dateService,@myDate)<185
end) group by v.chartID, rs1.procedureKey

View Replies !

Using CASE Statement In A WHERE Clause
Is it possible to use CASE within a WHERE?

I have a query which is something like this, but it returns an error:

SELECT * FROM tablex

WHERE

CASE WHEN 'sexec' IS NOT NULL THEN

dm_marque = 'foo' AND year(dm_date) LIKE 'pyear' AND dm_month LIKE 'pmonth' AND dm_weekno LIKE 'pweek' and dm_sexec LIKE 'sexec' ELSE

dm_marque = 'foo' AND year(dm_date) LIKE 'pyear' AND dm_month LIKE 'pmonth' AND dm_weekno LIKE 'pweek' END

GROUP BY dm_marque

In this case sexec is a form parameter, if something is passed then I need to include it in the where statement, if it isn't I need to include something else. I am using CASE because there are three of these parameters and I want it to stop evaluating as soon as it matches as more than one may match but I only want to apply one.

Many thanks

Karen

View Replies !

WHERE Clause Of An INSERT INTO Statement

Hi,

I have the following bit of SQL:

Code Block
INSERT INTO [INTRANETSQLEXPRESS].Trapped.dbo.TBL_Debtsolv (Debtsolv_ID, Payment_Amount, Payment_Status, Client_Status)
SELECT Client.ID AS ClientID,
InitialInstallment.OffsetAmountExpected AS FirstExpectedPayment
,
CASE WHEN Payment.TotalPaid <= 0 OR LatestPaymentDate IS NULL
THEN 'No Money Paid'
WHEN Payment.TotalPaid < InitialInstallment.OffsetAmountExpected
AND Payment.TotalPaid >0
AND InitialInstallment.DateExpected + ISNULL(Schedule.DayOffset, 0) >GETDATE()
THEN 'Still Awaiting - Part Paid'
WHEN Payment.TotalPaid >= InitialInstallment.OffsetAmountExpected
THEN '1st Payment Made'
WHEN Payment.TotalPaid < InitialInstallment.OffsetAmountExpected
AND InitialInstallment.DateExpected + ISNULL(Schedule.DayOffset, 0) <GETDATE()
THEN 'Late - First Payment Not Made'
ELSE 'Status Unclear'
END
AS Status
, CASE WHEN
Client.Status IN(0,1,2,3,4,5,6,7)
THEN 'In Preparation'
WHEN Client.Status IN(8,9)
THEN 'Active'
ELSE 'Inactive'
END AS ClientStatus
FROM SALEEN.Debtsolv.dbo.Client_Contact as Client
LEFT OUTER JOIN SALEEN.Debtsolv.dbo.Payment_Schedule AS Schedule
ON Client.ID = Schedule.ClientID
INNER JOIN SALEEN.Debtsolv.dbo.Client_LeadData CLD
ON Client.ID = CLD.Client_ID
INNER JOIN SALEEN.Debtsolv.dbo.Type_Client_Status TCS
ON Client.Status = TCS.ID
LEFT OUTER JOIN SALEEN.Debtsolv.dbo.Type_Lead_Source TLS
ON CLD.SourceID = TLS.ID
LEFT OUTER JOIN SALEEN.Debtsolv.dbo.Type_Lead_Source_Group AS LeadGroup
ON TLS.[Group] = LeadGroup.ID
LEFT OUTER JOIN SALEEN.Debtsolv.dbo.Users [User]
ON CLD.Counsellor = [User].ID
LEFT OUTER JOIN (SELECT ClientID
, SUM(CAST((Amount + ISNULL(AmountOffset, 0))AS Money) / 100) AS OffsetAmountExpected
, MAX(DateExpected) AS DateExpected
FROM SALEEN.Debtsolv.dbo.Payment_Schedule AS PS
WHERE (SequenceID <=(SELECT
CASE WHEN NumInitialInstalments = 0 THEN 1
ELSE IsNull(NumInitialInstalments, 1)
END
AS NumInitialInstalments
FROM SALEEN.Debtsolv.dbo.Client_LeadData AS LD
WHERE (Client_ID = PS.ClientID)))
GROUP BY ClientID
)
AS InitialInstallment
ON Client.ID = InitialInstallment.ClientID
LEFT OUTER JOIN (SELECT Receipt.ClientID
, SUM(CAST(Receipt.Amount AS Money)) / 100 AS TotalPaid
FROM SALEEN.Debtsolv.dbo.Payment_Receipt Receipt
INNER JOIN SALEEN.Debtsolv.dbo.Type_Payment_Status Type
ON Receipt.Status = Type.ID
WHERE (Receipt.Status = 5)
GROUP BY ClientID)
AS Payment
ON Client.ID = Payment.ClientID
LEFT OUTER JOIN (SELECT ClientID
, MAX(ID) AS MaxPSID
, MAX(LastPayment) AS LatestPaymentDate
FROM SALEEN.Debtsolv.dbo.Payment_Schedule Schedule
WHERE (NOT (LastPayment IS NULL)
AND LastPayment > CONVERT(DATETIME, '1980-01-01 00:00:00', 102))
GROUP BY ClientID)
AS LatestPaymentDate
ON Client.ID = LatestPaymentDate.ClientID
INNER JOIN (SELECT ClientID
, MAX(SequenceID) AS LFPDSequenceID
FROM SALEEN.Debtsolv.dbo.Payment_Schedule PS
WHERE (PaymentType = 1)
GROUP BY ClientID)
AS LastPayment
ON Client.ID = LastPayment.ClientID
AND Schedule.ClientID = LastPayment.ClientID
AND Schedule.SequenceID = LastPayment.LFPDSequenceID
WHERE Client.ID = TBL_Debtsolv.Debtsolv_ID

Executing this statement gives the following error:

Code Block

Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "TBL_Debtsolv.Debtsolv_ID" could not be bound.

I think thsi is soemthing to do with referencing another table in the INSERT into command as if I put:

Code Block
WHERE Client.ID = '12065'

The statement executes with no problem. I think I have read in some other threads that you cannot reference another table in the WHERE part of an INSERT INTO clause but cannot find out how I can make this work.

Most of the statement comes from a pre-made report from one of our systems and I have slighlty changed it to insert the data into another database that we have.

Any suggestions would be greatly appreciated

Thanks

View Replies !

Where Clause In Insert Statement
Hello all,

I've got a question. I have an array that I am inserting into a table. The code is in classic asp and looks like this:

SQL1 = "INSERT INTO transactionz (classid, userid) values (" & listOfAnswers(i) & ",8)

The problem is that I want to only insert values that have the value "1". listofAnsers is an array that returns values of "1" and "2". If there a way to limit the inserts to values of "1". Another words, I want to just insert the values of "1" into the table. I know that you experts would say I should control this on the code side and thats true, however, I can't (because I don't control the code side) so is there a way to do this via SQL? Am I making any sense? I was thinking that I needed to add a "where" clause at the end of the code, but I don't know where to go from here.

View Replies !

CASE Statement On A WHERE Clause?
I am trying to create a condition if the value of a parameter is null then pass a certain WHERE condition to my query. I keep on getting this error:

Line 15: Incorrect syntax near '='.

this is my query that lies on a PROC:

SELECT count(a.pEngr_Item_ID) as assembly_count FROM Engr_Item a
INNER JOIN Engr_BOM_Control b
ON a.pEngr_Item_ID=b.fEngr_Item_ID
WHERE
b.Bill_Type=@v_Bill_Type and
a.Item_No=@v_Item_No and
case when @v_Rev_Lett is not null then a.Item_No=@v_Rev_Lett else a.Item_No=@v_Rev_Lett end

View Replies !

CASE Statement On A WHERE Clause?
I need to have a CASE statement inside a WHERE clause. Is this possible? Here is my WEHRE clause. Any suggestions are appreciated:

WHERE
--r.pBOM_ID=d.fEngr_BOM_ID and
r.fItem_ID=a.pEngr_Item_ID and
r.level<=@v_level+1 and
case when @v_showrootlevel=1 then r.level>1 else r.level>1 end
order by r.pID

View Replies !

IF Statement In SELECT WHERE Clause
Hi Everyone,

I have the following stored procedure, I would like to use
IF statement or something of the sort in the where clause i.e.
The last line in the SP is: AND (category.categoryID = @categoryID),
I only want to check this, if @categoryID is not = 12.
So can I do something like this:

IF @categoryID <> 12
AND (category.categoryID = @categoryID)

STORED PROCEDURE:

CREATE PROCEDURE sp_get_total_risk_patients
@categoryID int
AS

SELECT COUNT(DISTINCT patient.patientID) AS total_patients
FROM patient
INNER JOIN patient_record ON patient.patientID = patient_record.patientID
INNER JOIN sub_category ON sub_category.sub_categoryID = patient.sub_categoryID
INNER JOIN category ON category.categoryID = sub_category.categoryID
WHERE risk = 6
AND (completed_date = '' OR completed_date IS NULL)
AND (category.categoryID = @categoryID)

View Replies !

Returning An IN Clause From A CASE Statement
Hi all,

I am passing in a variable into a CASE statement. Based on the value, I want to return a set of values in an IN clause. Here is an example:

(The where clause will use a field called 'Location')

DECLARE @strTest as Varchar(50)
SET @strTest = 'HI'

SELECT * FROM [SomeTable]
WHERE
CASE @strTest
WHEN 'HI' THEN Location IN('1', '2', '3')
END
ORDER BY Location

Is this possible to do?

Sanctos

View Replies !

'Case' Statement Inside 'Where' Clause
Hi

I've been trying to put a simple case statement into my 'where' clause but having no luck, is there another way to do the following?

DECLARE @searchCriteria Int
SET @searchCriteria = 2

SELECT column1, column2 FROM TABLE WHERE
CASE @searchCriteria
WHEN 1 THEN (column3 = 1000100)
WHEN 2 THEN (column3 = 1000101)
END CASE

...cheers

View Replies !

Can We Have An Inner Join Clause In An Update Statement
Hi,
I'm trying to inner join an update statement.
Something like this:

update #point_connection_temp AS a inner join #point_connection_temp_two as b on a.key_fld = b.key_fld set a.next_point = b.next_point
where #point_connection_temp.next_point is null
order by a.key_fld

I'm getting an error message:Incorrect syntax error near AS
Any help will be greatly appreciated.Thank you!!!!!!!!!1

View Replies !

Use A Variable Along With The FROM Clause In SELECT Statement
I have a table 'table_list' which contains two columns, table_name and a record_count. This table stores a list of tables and their corresponding record counts.

What I am trying to do is, to be able to write a select statement, that can read each table name in the 'table_name' column, execute a select count(*) for the same, and update its record_count with the result of select count(*).

This is the code in my procedure..

DECLARE @tab_list CURSOR
set @tab_list = CURSOR FOR select * from table_list
OPEN @tab_list

DECLARE @tab_name varchar(256)
DECLARE @rec_cnt int
FETCH NEXT FROM @tab_list INTO @tab_name, @rec_cnt

select count(*) from @tab_name

This select is looping around along with FETCH till all the table names are exhausted and their counts are updated from the cursor back into the table.

Problem is that, I am not able to use select count(*) from @tab_name, and its not accepting a variable there.

Please help me to construct the select statement that is similiar to

x=<table name>
select * from x
where x is a variable and the table name gets substituted.

what is the syntax for it ?

View Replies !

Conditional Where Clause W/ Case Statement Possible?
Greetings,

After many hours search many forums and many failed experiments, I figure it's time to turn to the experts.

I need to execute a query that changes the returned data based upon a parameter's value. In my example below, the lob field contains both text values and nulls.

SELECT uniqueID, lob, xdate
FROM mytable
WHERE

CASE WHEN @myparam = 'ALL'

THEN

xdate >= '2007-09-01'
ELSE

xdate >= '2007-09-01' or
lob = @myparm
END

I've experimented with various forms of the LIKE function, checking for null/not null and keep coming up blank.

I thought about using an IF statement and creating different versions of the entire statement, however, in real-life I need to do this with four fields using four parameters (one for each field). The permutations are a little too much.

Any ideas?

Rob

View Replies !

Case Statement On Where Clause If Parameter =NULL
I am working on a Function that takes multiple parameters. I have a query that populates a temporary table, and then it processes some logic. My question is, if the parameter is passed as null, I dont want the query to be affected by this null value. Rather, I would like to not pass it at all to the query. So if the parameter is NULL, dont pass it through the query. I have the following but its not compiling right:

SELECT bom.pEngr_BOM_ID , bom.fEngr_Item_ID, det.pEngr_BOM_Detail_ID, 1, bom.Bill_Type, bom.Rev_Ltr, bom.Series_Ltr
FROM dbo.Engr_BOM_Control bom WITH (nolock)
INNER JOIN dbo.Engr_BOM_Detail det WITH (nolock)
ON det.fEngr_BOM_ID=bom.pEngr_BOM_ID
WHERE bom.pEngr_BOM_ID=@v_pEngr_BOM_ID
AND det.fEngr_BOM_ID=@v_pEngr_BOM_ID
CASE WHEN @v_Bill_Type IS NOT NULL THEN
AND bom.Bill_Type=@v_Bill_Type
END

View Replies !

Gridview Select Statement With User.identity.name In The Where Clause
Hello everyone,
I have a view, NAS_vPosition that has a coloumn vLogin_Acting and I want to use the user.identity.name to select the row from this table that matches.
So far i have tried:
SelectCommand = "Select * FROM NAS_vPosition WHERE vLogin_Acting = ' <%=User.Identity.Name %> ' "
with no success.
Any help is appreciated

View Replies !

Select Statement Using Multi-list Box Values For WHERE IN SQL Clause
I have a gridview that is based on the selection(s) in a listbox. The gridview renders fine if I only select one value from the listbox. I recive this error though when I select more that one value from the listbox:
Syntax error converting the nvarchar value '4,1' to a column of data type int. If, however, I hard code 4,1 in place of @ListSelection (see below selectCommand WHERE and IN Clauses) the gridview renders perfectly.
<asp:SqlDataSource ID="SqlDataSourceAll" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
SelectCommand="SELECT DISTINCT dbo.Contacts.Title, dbo.Contacts.FirstName, dbo.Contacts.MI, dbo.Contacts.LastName, dbo.Contacts.Suffix, dbo.Contacts.Dear, dbo.Contacts.Honorific, dbo.Contacts.Address, dbo.Contacts.Address2, dbo.Contacts.City, dbo.Contacts.StateOrProvince, dbo.Contacts.PostalCode FROM dbo.Contacts INNER JOIN dbo.tblListSelection ON dbo.Contacts.ContactID = dbo.tblListSelection.contactID INNER JOIN dbo.ListDescriptions ON dbo.tblListSelection.selListID = dbo.ListDescriptions.ID WHERE (dbo.tblListSelection.selListID IN (@ListSelection)) AND (dbo.Contacts.StateOrProvince LIKE '%') ORDER BY dbo.Contacts.LastName">
<SelectParameters>
<asp:Parameter Name="ListSelection" DefaultValue="1"/>
</SelectParameters>
</asp:SqlDataSource>
The selListID column is type integer in the database.
I'm using the ListBox1_selectedIndexChanged in the code behind like this where I've tried using setting my selectparameter using the label1.text value and the Requst.From(ListBox1.UniqueID) value with the same result:

Protected Sub ListBox1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ListBox1.SelectedIndexChanged
Dim Item As ListItem
For Each Item In ListBox1.Items
If Item.Selected Then
If Label1.Text <> "" Then
Label1.Text = Label1.Text + Item.Value + ","
Else
Label1.Text = Item.Value + ","
End If
End If
Next
Label1.Text = Label1.Text.TrimEnd(",")
SqlDataSourceAll.SelectParameters("ListSelection").DefaultValue = Request.Form(ListBox1.UniqueID)
End Sub
What am I doing wrong here? Thanks!

View Replies !

Using A Field Alias For A CASE Statement In A GROUP BY Clause
When I created a CASE statement (This is at work, Pat:)) it is about 30-40 lines long. I gave it a name and set the name = to the case statement:

ie,

SELECT fieldname1 =
CASE
WHEN condition THEN 'blah blah'
WHEN condition THEN 'blah blah'
WHEN condition THEN 'blah blah'
ELSE thisandthat
END
, fieldname2
, fieldname3
FROM tablename1
GROUP BY CASE
WHEN condition THEN 'blah blah'
WHEN condition THEN 'blah blah'
WHEN condition THEN 'blah blah'
ELSE thisandthat
END, , fieldname2, fieldname3

etc.

The long CASE statement in my GROUP BY is awkward to me. Is this the only way to do it? I tried using the fieldname1 but it comes back as an invalid field name and asks for the "expression".

Regards,

Dave

View Replies !

Escape Clause Causes Error In Lookup Modified Sql Statement
I need to use a modified SQL statement for a lookup component. It has an escape clause in it and this causes error:

select * from dbo.typecustomer where ? like '%'+type_subtype +'%'
ESCAPE '_'

Is this is a bug? Any help will be greatly appreciated.

Thanks

Akin

View Replies !

Trying To Use The Results Of A Case Statement In My Select List In My WHERE Clause?
I am fairly new with SQL and still learning. I have used a case statemtent for a column in my select list and want to use the results of that statement's field in my WHERE clause but it is not working for me. Here is the code I have so far:

SELECT
l.loanid,
p.investorid,
l.duedate,
case when pc.duedate >= l.duedate then pc.duedate end as RateDueDate,
pc.interestrate
FROM loan l
inner join participation p on p.loanid = l.loanid
inner join paymentchange pc on pc.loanid = l.loanid
where p.investorid = '12345' and RateDueDate is not null
order by l.loanid, pc.duedate

I want to put the results of this case statment in my where clause like highlighted above but it is not working because RateDueDate is not an actual column in the table. Any help would be greatly appreciated.

Thanks!

View Replies !

SQL Statement: Group By And Where Clause Do Not Display Null Rows

The following is a simplified version of my SQL statement. I am attempting to do a simple count(*) with two groupings and a where clause. This is Select command for a GridView. However, I am unable to display zeros. The rows are completely missing and I would greatly appreciate someone's help. I have already tried Group By All, but that, unfortunately, does not work. Here is my SQL statement:

"SELECT [GENDER], [RACE], COUNT(*) FROM [TABLE] WHERE ([COLUMNNAME] ='SOMETHING') GROUP BY [GENDER], [RACE]"

Thanks for the help in advance!

View Replies !

Combing In A Cursor, A Select Statement With The WHERE Clause Stored In A Variable
Hi
I am ramesh here from go-events.com
I am using sql mail to send out emails to my mailing list

I have difficulty combining a select statement with a where clause stored in a variable inside a cursor

The users select the mail content and frequency of delivery and i deliver the mail

I use lots of queries and a stored procedure to retrieve thier preferences. In the end i use a cursor to send out mails to each of them.

Because my query is dynamic, the where clause of my select statement is stored in a variable. I have the following code
that does not work

For example

DECLARE overdue3 CURSOR
LOCAL FORWARD_ONLY
FOR SELECT DISTINCT Events.E_Name, Events.E_SDate, Events.E_City, Events.E_ID FROM Events, IndustryEvents + @sqlquery2
OPEN overdue3

I get an error message at the '+' sign
which says, cannot use empty object or column names, use a single
space if necessary

How do I combine the select statement with the where clause?

Help me...I need help urgently

View Replies !

Using The ORDER BY Clause When The Ordered Column Is Not Needed In The SELECT Statement
Greetings,

I have a C# application that calls a stored procedure to query the database (MSSQL 2005). I only have one field/column returned from the query but I need that column ordered.

How do I use the ORDER BY clause without returning the index column which does the sorting? The first example is NOT what I want. I want something that works like the second example which only returns the 'Name' column.

ALTER PROCEDURE [dbo].[MyProcedure]

AS

BEGIN

SELECT DISTINCT A.Name, A.index

FROM
...
...
ORDER BY A.[Index], A.Name ASC

END

ALTER PROCEDURE [dbo].[MyProcedure]

AS

BEGIN

SELECT DISTINCT A.Name
FROM
...
...
ORDER BY A.[Index]

END

Thanks

View Replies !

Unable To Pass Queue Name In A Parameter For The FROM Clause Of RECEIVE Statement
I'm writing some generalized stored procedures for use form managed code. The following stored proc works great:

CREATE PROCEDURE fnd_Send (
        @Message VARCHAR(5000),
        @Contract SYSNAME,
        @MessageType SYSNAME,
        @FromService SYSNAME,
        @ToService VARCHAR(100),
        @Encrypted Bit
)
AS
        BEGIN
                DECLARE @Handle UNIQUEIDENTIFIER;

                BEGIN DIALOG CONVERSATION @Handle
                        FROM SERVICE @FromService
                        TO SERVICE @ToService
                        ON CONTRACT @Contract
                        WITH ENCRYPTION = OFF;

                SEND ON CONVERSATION @Handle
                        MESSAGE TYPE @MessageType(@Message);

Notice the use of SYSNAME parameters €“ translating the parameter into an object name - allows it to work in the BEGIN DIALOG statement when an system object is needed. Works create and cues up generic messages.

The following will not compile:

CREATE PROCEDURE fnd_Receive (
        @Queue SYSNAME,
        @Message VARCHAR(5000) out,
        @MessageType SYSNAME out
)
AS
        BEGIN
                DECLARE @Handle UNIQUEIDENTIFIER;

                RECEIVE TOP (1)
                        @Handle = conversation_handle,
                        @MessageType = message_type_name,
                        @Message = message_body
                FROM @Queue;
        END
GO

It won€™t let me use the SYSNAME field @Queue in the FROM clause. I€™m getting an unfriendly:

Msg 102, Level 15, State 1, Procedure fnd_Receive, Line 15
Incorrect syntax near '@Queue'.

It looks like maybe I can€™t bind a variable in the FROM clause €“ perhaps like you cannot in a SELECT statement. I can fall back to dynamic SQL in the stored proc or in the managed code; I was hoping not to.

Can anyone shed any light on this? Also, are there any other techniques I'm missing to avoid falling back to a dynamic SQL statement.

Thank you in advance -

Jeff Odell
Catapult Systems

View Replies !

Order By Clause In DECLARE CURSOR Select Statement Won't Compile
The stored procedure, below, results in this error when I try to compile...

Msg 156, Level 15, State 1, Procedure InsertImportedReportData, Line 69
Incorrect syntax near the keyword 'ORDER'.

However the select statement itself runs perfectly well as a query, no errors.

The T-SQL manual says you can't use the keywords COMPUTE, COMPUTE BY, FOR BROWSE, and INTO in a cursor select statement, but nothing about plain old ORDER BYs.

What gives with this?

Thanks in advance
R.

The code:

Code Snippet

-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF object_id('InsertImportedReportData ') IS NOT NULL
    DROP PROCEDURE InsertImportedReportData
GO
-- =============================================
-- Author:        -----
-- Create date:
-- Description:    inserts imported records, marking as duplicates if possible
-- =============================================
CREATE PROCEDURE InsertImportedReportData
    -- Add the parameters for the stored procedure here
    @importedReportID int,
    @authCode varchar(12)
AS
BEGIN
    DECLARE @errmsg VARCHAR(80);

--    SET NOCOUNT ON added to prevent extra result sets from
--    interfering with SELECT statements.
    SET NOCOUNT ON;

    --IF (@authCode <> 'TX-TEC')
    --BEGIN
     -- SET @errmsg = 'Unsupported reporting format:' + @authCode
    -- RAISERROR(@errmsg, 11, 1);
    --END

    DECLARE srcRecsCursor CURSOR LOCAL
    FOR    (SELECT
           ImportedRecordID
        ,ImportedReportID
        ,AuthorityCode
        ,[ID]
        ,[Field1] AS RecordType
        ,[Field2] AS FormType
        ,[Field3] AS ItemID
        ,[Field4] AS EntityCode
        ,[Field5] AS LastName
        ,[Field6] AS FirstMiddleNames
        ,[Field7] AS Title
        ,[Field8] AS Suffix
        ,[Field9] AS AddressLine1
        ,[Field10] AS AddressLine2
        ,[Field11] AS City
        ,[Field12] AS [State]
        ,[Field13] AS ZipFull
        ,[Field14] AS OutOfStatePAC
        ,[Field15] AS FecID
        ,[Field16] AS Date
        ,[Field17] AS Amount
        ,[Field18] AS [Description]
        ,[Field19] AS Employer
        ,[Field20] AS Occupation
        ,[Field21] AS AttorneyJob
        ,[Field22] AS SpouseEmployer
        ,[Field23] As ChildParentEmployer1
        ,[Field24] AS ChildParentEmployer2
        ,[Field25] AS InKindTravel
        ,[Field26] AS TravellerLastName
        ,[Field27] AS TravellerFirstMiddleNames
        ,[Field28] AS TravellerTitle
        ,[Field29] AS TravellerSuffix
        ,[Field30] AS TravelMode
        ,[Field31] As DptCity
        ,[Field32] AS DptDate
        ,[Field33] AS ArvCity
        ,[Field34] AS ArvDate
        ,[Field35] AS TravelPurpose
        ,[Field36] AS TravelRecordBackReference
    FROM ImportedNativeRecords
    WHERE ImportedReportID IS NOT NULL
    AND ReportType IN ('RCPT','PLDG')
    ORDER BY ImportedRecordID -- this should work but gives syntax error!
    );

END

View Replies !

Using OPTION Clause Within CREATE FUNCTION Statement For Inline Table Functions
Hi!

I need to expand resursion level for resursive CTE expression within CREATE FUNCTION statement for inline table function to a value greater than default. It turns out that OPTION clause for MAXRECURSION hint perfectly works if I use it outside CREATE FUNCTION (as well as CREATE VIEW for non-parametrized queries), but it does not within CREATE FUNCTION statement - I'm getting error:

Msg 156, Level 15, State 1, Procedure ExpandedCTE, Line 34

Incorrect syntax near the keyword 'option'.

Here is the function:

create FUNCTION [dbo].[ExpandedCTE]

(

@p_id int

)

RETURNS TABLE

AS

RETURN

(

with tbl_cte (id, tbl_id, lvl)

as

(

select

id, tbl_id, 0 lvl

from

tbl

where

id = @p_id

union all

select

t.id, t.tbl_id, lvl + 1
from

tbl_cte
inner join tbl t

on rnr.tbl_id = tbl_cte.id

)

select

id, tbl_id, lvl

from

tbl_cte

option (maxrecursion 0)

)

Please help!

Alexander.

P.S.
I'm really sorry if it is about syntax, but I could not find it in the documentation.

View Replies !

CTE Error: Incorrect Syntax Near The Keyword 'with'. If This Statement Is A Common Table Expression Or An Xmlnamespaces Clause,
I am having this error when using execute query for CTE

Help will be appriciated

View Replies !

Speed Cost For Using &"or&" Clause And Functions On Join Statement
Select memberfrom NameListInner join Memberson (Left(Namelist.NameID,5) = Members.IDOR (left(namelist.SSN,9) = Members.ssnOR (Left(namelist.CustID,9) + '*01' = Members.CustID)wherenamelist.name <> ''How do I speed up a process like this? Can I create indexes on themembers table based on a functionLike an index based on the left(members.id,5)or should these statements go into the where clause?

View Replies !

Interaction Between &&"instead Of Insert&&" Trigger And Output Clause Of Insert Statement

This problem is being seen on SQL 2005 SP2 + cumulative update 4

I am currently successfully using the output clause of an insert statement to return the identity values for inserted rows into a table variable

I now need to add an "instead of insert" trigger to the table that is the subject of the insert.

As soon as I add the "instead of insert" trigger, the output clause on the insert statement does not return any data - although the insert completes successfully. As a result I am not able to obtain the identities of the inserted rows

Note that @@identity would return the correct value in the test repro below - but this is not a viable option as the table in question will be merge replicated and @@identity will return the identity value of a replication metadata table rather than the identity of the row inserted into my_table

Note also that in the test repro, the "instead of insert" trigger actually does nothing apart from the default insert, but the real world trigger has additional code.

To run the repro below - select each of the sections below in turn and execute them
1) Create the table
2) Create the trigger
3) Do the insert - note that table variable contains a row with column value zero - it should contain the @@identity value
4) Drop the trigger
5) Re-run the insert from 3) - note that table variable is now correctly populated with the @@identity value in the row

I need the behaviour to be correct when the trigger is present

Any thoughts would be much appreciated

aero1

/************************************************
1) - Create the table
************************************************/
CREATE TABLE [dbo].[my_table](
      [my_table_id] [bigint] IDENTITY(1,1) NOT NULL,
      [forename] [varchar](100) NULL,
      [surname] [varchar](50) NULL,
CONSTRAINT [pk_my_table] PRIMARY KEY NONCLUSTERED
(
      [my_table_id] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, FILLFACTOR = 70) ON [PRIMARY]
)

GO
/************************************************
2) - Create the trigger
************************************************/
CREATE TRIGGER [dbo].[trig_my_table__instead_insert] ON [dbo].[my_table]
INSTEAD OF INSERT
AS
BEGIN

      INSERT INTO my_table
            (
            forename,
            surname)
      SELECT
            forename,
            surname
      FROM inserted

END

/************************************************
3) - Do the insert
************************************************/

DECLARE @my_insert TABLE( my_table_id     bigint )

declare                 @forename                     VARCHAR(100)
declare                 @surname                      VARCHAR(50)

set         @forename = N'john'
set         @surname = N'smith'

INSERT INTO my_table (
                                    forename
                                    , surname
                                    )
OUTPUT inserted.my_table_id INTO @my_insert
VALUES(     @forename
            , @surname
            )

select @@identity -- expect this value in @my_insert table
select * from @my_insert -- OK value without trigger - zero with trigger

/************************************************
4) - Drop the trigger
************************************************/

drop trigger [dbo].[trig_my_table__instead_insert]
go

/************************************************
5) - Re-run insert from 3)
************************************************/
-- @my_insert now contains row expected with identity of inserted row
-- i.e. OK

View Replies !

GROUP By Clause Or DISTINCT Clause
Hi, can anyone shed some light on this issue?SELECT Status from lupStatuswith a normal query it returns the correct recordcountSELECT Status from lupStatus GROUP BY Statusbut with a GROUP By clause or DISTINCT clause it return the recordcount= -1

View Replies !

Filtering Results In The Where Clause Vs A Having Clause
I am working with a vendor on upgrading their application from SQL2K to SQL2K5 and am running into the following.

When on SQL Server 2000 the following statement ran without issue:

UPDATE dbo.Track_ID

SET dbo.Track_ID.Processed = 4 --Regular 1 leg call thats been completed

WHERE Processed = 0 AND LegNum = 1

AND TrackID IN

(

SELECT TrackID

FROM dbo.Track_ID

GROUP BY TrackID

HAVING MAX(LegNum) = 1 AND

TrackID + 'x1' IN

(

SELECT

dbo.Track_ID.TrackID + 'x' + CONVERT(NVARCHAR(2), COUNT(dbo.Track_ID.TrackID))

FROM dbo.Track_ID INNER JOIN dbo.transactions

ON dbo.Track_ID.SM_ID = dbo.transactions.sm_session_id

GROUP BY dbo.Track_ID.TrackID

)

)
Once moved to SQL Server 2005 the statement would not return and showed SOS_SCHEDULER_YIELD to be the waittype when executed. This machine is SP1 and needs to be upgraded to SP2, something that is not going to happen near time.

I changed the SQL to the following, SQL Server now runs it in under a second, but now the app is not functioning correctly. Are the above and the following semantically the same?

UPDATE dbo.Track_ID

SET dbo.Track_ID.Processed = 4 --Regular 1 leg call thats been completed

WHERE Processed = 0 AND LegNum = 1

AND TrackID IN
(

SELECT TrackID

FROM dbo.Track_ID

WHERE TrackID + 'x1' IN

(

SELECT dbo.Track_ID.TrackID + 'x' + CONVERT(NVARCHAR(2), COUNT(dbo.Track_ID.TrackID))

FROM dbo.Track_ID INNER JOIN dbo.transactions

ON dbo.Track_ID.SM_ID = dbo.transactions.sm_session_id

GROUP BY dbo.Track_ID.TrackID

)
GROUP BY TrackID

HAVING MAX(LegNum) = 1

)

View Replies !

Expression Defined In SELECT Clause Overwrites Column Defined In FROM Clause
2 examples:

1) Rows ordered using textual id rather than numeric id

Code Snippet
select
cast(v.id as nvarchar(2)) id
from
(
  select 1 id
  union select 2 id
  union select 11 id
) v
order by
v.id

Result set is ordered as: 1, 11, 2
I expect: 1,2,11

if renamed or removed alias for "cast(v.id as nvarchar(2))" expression then all works fine.

2) SQL server reject query below with next message

Server: Msg 169, Level 15, State 3, Line 16
A column has been specified more than once in the order by list. Columns in the order by list must be unique.

Code Snippet
select
cast(v.id as nvarchar(2)) id
from
(
  select 1 id
  union select 2 id
  union select 11 id
) v
cross join (
  select 1 id
  union select 2 id
  union select 11 id
) u
order by
v.id
    ,u.id

Again, if renamed or removed alias for "cast(v.id as nvarchar(2))" expression then all works fine.

It reproducible on

Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)   May 3 2005 23:18:38   Copyright (c) 1988-2003 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

and

Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86)   Feb 9 2007 22:47:07   Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

In both cases database collation is SQL_Latin1_General_CP1251_CS_AS

If I check quieries above on database with SQL_Latin1_General_CP1_CI_AS collation then it works fine again.

Could someone clarify - is it bug or expected behaviour?

View Replies !

ERROR [42000] [Lotus][ODBC Lotus Notes]Table Reference Has To Be A Table Name Or An Outer Join Escape Clause In A FROM Clause
I am using web developer 2008, while connecting to I wanted to fetch data from Lotus notes database file, for this i used notesql connector, while connectiong to notes database i am fetting error

ERROR [42000] [Lotus][ODBC Lotus Notes]Table reference has to be a table name or an outer join escape clause in a FROM clause

I have already checked that database & table name are correct, please help me out
How i can fetch the lotus notes data in my asp.net pages.

View Replies !

Having Clause Without GROUP BY Clause?
Hi,

What is HAVING clause equivalent in the following oracle query, without the combination of "GROUP BY" clause ?

eg :

SELECT SUM(col1) from test HAVING col2 < 5

SELECT SUM(col1) from test WHERE x=y AND HAVING col2 < 5

I want the equivalent query in MSSQLServer for the above Oracle query.

Also, does the aggregate function in Select column(here the SUM(col1)) affect in anyway the presence of HAVING clause?.

Thanks,
Gopi.

View Replies !

SQL Inner Join Clause And The Where Clause
Hi everyone,
I saw some queries where SQL inner join clause and the where clause is used at the same time. I knew that "on" is used instead of the "where" clause. Would anyone please exaplin me why both "where" and "on" clause is used in some sql Select queries ?

Thanks

View Replies !

Diff In On Clause And Where Clause?????
hi..
i have basic question like

what is differance between conditions put in ON clause and in WHERE clause in JOINS????

see conditions that shown in brown color

select d1.SourceID, d1.PID, d1.SummaryID, d1.EffectiveDate,
d1.Audit, d1.ExpirationDate, d1.Indicator
from[DB1].[dbo].[Implicit] d1 inner join [DB2].[dbo].[Implicit] d2
on d1.SummaryID=d2.SummaryID
AND d1.ListType = d2.ListType
AND (d1.EffectiveDate <= d2.ExpirationDate or d2.ExpirationDate is null)
AND (d1.ExpirationDate >= d2.EffectiveDate or d1.ExpirationDate is null)
whered1.ImplicitID >= d2.ImplicitID AND
(d1.SourceID<>d2.SourceID
OR (d1.SourceID IS NULL AND d2.SourceID IS NOT NULL)
OR (d1.SourceID IS NOT NULL AND d2.SourceID IS NULL)
)

select d1.SourceID, d1.PID, d1.SummaryID, d1.EffectiveDate,
d1.Audit, d1.ExpirationDate, d1.Indicator
from[DB1].[dbo].[Implicit] d1 inner join [DB2].[dbo].[Implicit] d2
on d1.SummaryID=d2.SummaryID
AND d1.ImplicitID = d1.ImplicitIDAND d1.ListType = d2.ListType
AND (d1.EffectiveDate <= d2.ExpirationDate or d2.ExpirationDate is null)
AND (d1.ExpirationDate >= d2.EffectiveDate or d1.ExpirationDate is null)
whered1.ImplicitID >= d2.ImplicitID AND
(d1.SourceID<>d2.SourceID
OR (d1.SourceID IS NULL AND d2.SourceID IS NOT NULL)
OR (d1.SourceID IS NOT NULL AND d2.SourceID IS NULL)
)

another thing...

if we put AND d1.ImplicitID = d1.ImplicitID condition in second query then shall we remove
d1.ImplicitID >= d2.ImplicitID from WHERE clause????

View Replies !

Top Clause With GROUP BY Clause
How Can I use Top Clause with GROUP BY clause?

Here is my simple problem.

I have two tables

Categories
Products

I want to know Top 5 Products in CategoryID 1,2,3,4,5

Resultset should contain 25 Rows ( 5 top products from each category )

I hope someone will help me soon.
Its urngent

thanks in advance

regards
Waqas

View Replies !

Is It Possible To Re-reference A Column Alias From A Select Clause In Another Column Of The Same Select Clause?
Example, suppose you have these 2 tables(NOTE: My example is totally different, but I'm simply trying to setupthe a simpler version, so excuse the bad design; not the point here)CarsSold {CarsSoldID int (primary key)MonthID intDealershipID intNumberCarsSold int}Dealership {DealershipID int, (primary key)SalesTax decimal}so you may have many delearships selling cars the same month, and youwanted a report to sum up totals of all dealerships per month.select cs.MonthID,sum(cs.NumberCarsSold) as 'TotalCarsSoldInMonth',sum(cs.NumberCarsSold) * d.SalesTax as 'TotalRevenue'from CarsSold csjoin Dealership d on d.DealershipID = cs.DealershipIDgroup by cs.MonthIDMy question is, is there a way to achieve something like this:select cs.MonthID,sum(cs.NumberCarsSold) as 'TotalCarsSoldInMonth',TotalCarsSoldInMonth * d.SalesTax as 'TotalRevenue'from CarsSold csjoin Dealership d on d.DealershipID = cs.DealershipIDgroup by cs.MonthIDNotice the only difference is the 3rd column in the select. Myparticular query is performing some crazy math and the only way I knowof how to get it to work is to copy and past the logic which isgetting out way out of hand...Thanks,Dave

View Replies !