Row Count SQL Syntax

Feb 13, 2006

IN the following query i have to get the sum of 3 columnsfrom the case statement(Builders ,Associates ,Affiliates ) in the last column.I tried sum(Productid) but it is multiplying the count by 22,23,24 respectively.
I know i have been posting this again just to get the attention of the gurus.I have come
very close to my results but this is the last hurdle i have to get thru.
Thanks for your help

select
isnull(replace(Ltrim(Rtrim(P.Firstname)),',',''),' ') Firstname
, isnull(replace(Ltrim(Rtrim(P.Lastname)),',',''),'' ) Lastname
, RecordType = isnull(replace(Ltrim(Rtrim(S.Product)),',',''),'')
, case ProductID
WHEN 22 then count(S.Product)
WHEN 23 then 0
WHEN 24 then 0
END AS Builders
, case ProductID
WHEN 23 then count(S.Product)
WHEN 22 then 0
WHEN 24 then 0
END AS Associates
, case ProductID
WHEN 24 then count(S.Product)
WHEN 23 then 0
WHEN 22 then 0
END AS Affiliates
, Total = sum (????)
FROM vwpersons p with (nolock)
join vwSubscriptions S with (nolock)
on S.RecipientID = P.ID
where (
p.firstname in (select Ltrim(Rtrim(H.name)) from externaldata.dbo.Hispanicnames H)
or
P.Lastname in (select Ltrim(Rtrim(H.name)) from externaldata.dbo.Hispanicnames H)
)
andS.ProductID in (22,23,24)
AND S.StatusID in(1,2,3,11,12)
and ((len(p.firstname)>2 or len(p.Lastname)>2))
and ((len(p.Lastname)>2 or len(p.firstname)>2))
group by P.Lastname, S.Product,S.ProductID,S.ProductID,P.Firstname
having count(P.LastName)>=1
order by 2

View 2 Replies


ADVERTISEMENT

SQL Syntax Help (Count, Sum, Etc.)

Jul 18, 2007

Hoping someone does this 50 times a day and will know the answer.I need to build a query that groups and summarizes my data. Let's say table like this:int IDint clientType (keyed to a table with two columns [int a_ID, varchar a_clientType)]int requestType (keyed to a table with two columns [int b_ID, varchar  b_requestType)]The result of the query should spit out by Client type, and with a count of how many rows of each Request Type:Client Type A  [Request Type 1, # rows with Request Type 1]  [Request Type 2, # rows with Request Type 2]Client Type B  [Request Type 1, # rows with Request Type 1]  [Request Type 2, # rows with Request Type 2]I'm still reading up on grouping and sum (though sum seems to need a field to total numbers - I need a count of row of Request Type X/Y/ZAny tips would be appreciatedThanks in advanceDave

View 14 Replies View Related

Syntax Error Near '@Count'

Mar 19, 2004

I need your help:

I want to get the total number of expired messages of two tables.

But if gives me this error:

Line 17: Syntax error near '@Count'

What did I do wrong? Am I not allowed to add two Integers??


CREATE PROCEDURE Portal_GetExpired

(
@Count int OUTPUT
)

AS

SELECT Count(*) as x
FROM Portal_Announcements
WHERE ExpireDate < GetDate()

SELECT Count(*) as y
FROM Portal_Events
WHERE ExpireDate < GetDate()

@Count=x+y

GO

View 3 Replies View Related

SQL Syntax - Group By And Having Count

Jul 23, 2005

Does anyone have any recommendations on how to solve the following?I would like to have a query that selects ALL columns from a database,however only records that have a count of a certain column which isgreater than 1 when the results are grouped by a few columns. I knowthe following query doesnt work (because it contains items in theSELECT that arent in the GROUP BY), but its the jist of what I need todo.select a,b,c,d,e,f,g,hfrom table1group by a,b,c,dhaving count(e) > 1Can anyone help me out with this?

View 3 Replies View Related

Count And Update Syntax Help

Jul 20, 2005

Field Names: NOs Code Code1a UniqueID61 10 888 1062 10 888 1163 10 888 12Logic: If Count(code >1) & Count (Code1a >1)Update the (Nos) to EQUAL the same Value.ALL the Nos for the above examble should be the same value forall three records whether it's 61 for all three records of anyof the other two numbers, it doesn't matter as long as the equal the same value.How can this be done via sql?

View 5 Replies View Related

COUNT Field Incorrect Or Syntax Error.

Sep 21, 2004

I'm stuck. This is in C#.
I am making the following query:
string query = INSERT INTO region_info(prefix, region, last_update) VALUES ('" + RegionInfo.Prefix + "', '" + region + "', ???TIMESTAMP???);

and then executing
query = query.Replace("???TIMESTAMP???", "'" + DateTime.Now.ToString("yyyyMMdd") + "'");

Thus, an example query is:
INSERT INTO region_info(prefix, region, last_update) VALUES ('907209', 'Alaska-Juneau', '20040921');
When I execute this query through my program(uses ADO.net), I get a "COUNT field incorrect or syntax error" exception, but if I run this same query through the query analyzer, it works fine.

Any ideas on what'z going wrong?
Thanks

View 5 Replies View Related

Transaction Count After EXECUTE Indicates That A COMMIT Or ROLLBACK TRANSACTION Statement Is Missing. Previous Count = 1, Current Count = 0.

Aug 6, 2006

With the function below, I receive this error:Error:Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 0.Function:Public Shared Function DeleteMesssages(ByVal UserID As String, ByVal MessageIDs As List(Of String)) As Boolean        Dim bSuccess As Boolean        Dim MyConnection As SqlConnection = GetConnection()        Dim cmd As New SqlCommand("", MyConnection)        Dim i As Integer        Dim fBeginTransCalled As Boolean = False
        'messagetype 1 =internal messages        Try            '            ' Start transaction            '            MyConnection.Open()            cmd.CommandText = "BEGIN TRANSACTION"            cmd.ExecuteNonQuery()            fBeginTransCalled = True            Dim obj As Object            For i = 0 To MessageIDs.Count - 1                bSuccess = False                'delete userid-message reference                cmd.CommandText = "DELETE FROM tblUsersAndMessages WHERE MessageID=@MessageID AND UserID=@UserID"                cmd.Parameters.Add(New SqlParameter("@UserID", UserID))                cmd.Parameters.Add(New SqlParameter("@MessageID", MessageIDs(i).ToString))                cmd.ExecuteNonQuery()                'then delete the message itself if no other user has a reference                cmd.CommandText = "SELECT COUNT(*) FROM tblUsersAndMessages WHERE MessageID=@MessageID1"                cmd.Parameters.Add(New SqlParameter("@MessageID1", MessageIDs(i).ToString))                obj = cmd.ExecuteScalar                If ((Not (obj) Is Nothing) _                AndAlso ((TypeOf (obj) Is Integer) _                AndAlso (CType(obj, Integer) > 0))) Then                    'more references exist so do not delete message                Else                    'this is the only reference to the message so delete it permanently                    cmd.CommandText = "DELETE FROM tblMessages WHERE MessageID=@MessageID2"                    cmd.Parameters.Add(New SqlParameter("@MessageID2", MessageIDs(i).ToString))                    cmd.ExecuteNonQuery()                End If            Next i
            '            ' End transaction            '            cmd.CommandText = "COMMIT TRANSACTION"            cmd.ExecuteNonQuery()            bSuccess = True            fBeginTransCalled = False        Catch ex As Exception            'LOG ERROR            GlobalFunctions.ReportError("MessageDAL:DeleteMessages", ex.Message)        Finally            If fBeginTransCalled Then                Try                    cmd = New SqlCommand("ROLLBACK TRANSACTION", MyConnection)                    cmd.ExecuteNonQuery()                Catch e As System.Exception                End Try            End If            MyConnection.Close()        End Try        Return bSuccess    End Function

View 5 Replies View Related

Incorrect Syntax Near The Keyword CONVERT When The Syntax Is Correct - Why?

May 20, 2008

Why does the following call to a stored procedure get me this error:


Msg 156, Level 15, State 1, Line 1

Incorrect syntax near the keyword 'CONVERT'.




Code Snippet

EXECUTE OpenInvoiceItemSP_RAM CONVERT(DATETIME,'01-01-2008'), CONVERT(DATETIME,'04/30/2008') , 1,'81350'




The stored procedure accepts two datetime parameters, followed by an INT and a varchar(10) in that order.

I can't find anything wrong in the syntax for CONVERT or any nearby items.


Help me please. Thank you.

View 7 Replies View Related

Analysis :: Count Function Taking More Time To Get Count From Parent Child Dimension?

May 25, 2015

below data,

Countery
parentid
CustomerSkId
sales

A
29097
29097
10

A
29465
29465
30

A
30492
30492
40

[code]....
 
Output

Countery
parentCount

A
8

B
3

c
3

in my count function,my code look like,

 set buyerset as exists(dimcustomer.leval02.allmembers,custoertypeisRetailers,"Sales")
set saleset(buyerset)
set custdimensionfilter as {custdimensionmemb1,custdimensionmemb2,custdimensionmemb3,custdimensionmemb4}
set finalset as exists(salest,custdimensionfilter,"Sales")
Set ProdIP as dimproduct.dimproduct.prod1
set Othersset as (cyears,ProdIP)
(exists(([FINALSET],Othersset,dimension2.dimension2.item3),[DimCustomerBuyer].[ParentPostalCode].currentmember, "factsales")).count

it will take 12 to 15 min to execute.

View 3 Replies View Related

Incorrect Syntax When There Appears To Be No Syntax Errors.

Dec 14, 2003

I keep receiving the following error whenever I try and call this function to update my database.

The code was working before, all I added was an extra field to update.

Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near the keyword 'WHERE'


Public Sub MasterList_Update(sender As Object, e As DataListCommandEventArgs)

Dim strProjectName, txtProjectDescription, intProjectID, strProjectState as String
Dim intEstDuration, dtmCreationDate, strCreatedBy, strProjectLead, dtmEstCompletionDate as String

strProjectName = CType(e.Item.FindControl("txtProjectName"), TextBox).Text
txtProjectDescription = CType(e.Item.FindControl("txtProjDesc"), TextBox).Text
strProjectState = CType(e.Item.FindControl("txtStatus"), TextBox).Text
intEstDuration = CType(e.Item.FindControl("txtDuration"), TextBox).Text
dtmCreationDate = CType(e.Item.FindControl("txtCreation"),TextBox).Text
strCreatedBy = CType(e.Item.FindControl("txtCreatedBy"),TextBox).Text
strProjectLead = CType(e.Item.FindControl("txtLead"),TextBox).Text
dtmEstCompletionDate = CType(e.Item.FindControl("txtComDate"),TextBox).Text
intProjectID = CType(e.Item.FindControl("lblProjectID"), Label).Text

Dim strSQL As String
strSQL = "Update tblProject " _
& "Set strProjectName = @strProjectName, " _
& "txtProjectDescription = @txtProjectDescription, " _
& "strProjectState = @strProjectState, " _
& "intEstDuration = @intEstDuration, " _
& "dtmCreationDate = @dtmCreationDate, " _
& "strCreatedBy = @strCreatedBy, " _
& "strProjectLead = @strProjectLead, " _
& "dtmEstCompletionDate = @dtmEstCompletionDate, " _
& "WHERE intProjectID = @intProjectID"

Dim myConnection As New SqlConnection(System.Configuration.ConfigurationSettings.AppSettings("connectionstring"))
Dim cmdSQL As New SqlCommand(strSQL, myConnection)

cmdSQL.Parameters.Add(new SqlParameter("@strProjectName", SqlDbType.NVarChar, 40))
cmdSQL.Parameters("@strProjectName").Value = strProjectName
cmdSQL.Parameters.Add(new SqlParameter("@txtProjectDescription", SqlDbType.NVarChar, 30))
cmdSQL.Parameters("@txtProjectDescription").Value = txtProjectDescription
cmdSQL.Parameters.Add(new SqlParameter("@strProjectState", SqlDbType.NVarChar, 30))
cmdSQL.Parameters("@strProjectState").Value = strProjectState
cmdSQL.Parameters.Add(new SqlParameter("@intEstDuration", SqlDbType.NVarChar, 60))
cmdSQL.Parameters("@intEstDuration").Value = intEstDuration
cmdSQL.Parameters.Add(new SqlParameter("@dtmCreationDate", SqlDbType.NVarChar, 15))
cmdSQL.Parameters("@dtmCreationDate").Value = dtmCreationDate
cmdSQL.Parameters.Add(new SqlParameter("@strCreatedBy", SqlDbType.NVarChar, 10))
cmdSQL.Parameters("@strCreatedBy").Value = strCreatedBy
cmdSQL.Parameters.Add(new SqlParameter("@strProjectLead", SqlDbType.NVarChar, 15))
cmdSQL.Parameters("@strProjectLead").Value = strProjectLead
cmdSQL.Parameters.Add(new SqlParameter("@dtmEstCompletionDate", SqlDbType.NVarChar, 24))
cmdSQL.Parameters("@dtmEstCompletionDate").Value = dtmEstCompletionDate
cmdSQL.Parameters.Add(new SqlParameter("@intProjectID", SqlDbType.NChar, 5))
cmdSQL.Parameters("@intProjectID").Value = intProjectID

myConnection.Open()
cmdSQL.ExecuteNonQuery
myConnection.Close()

MasterList.EditItemIndex = -1
BindMasterList()


End Sub

Thankyou in advance.

View 3 Replies View Related

Which Is Faster? Conditional Within JOIN Syntax Or WHERE Syntax?

Mar 31, 2008

Forgive the noob question, but i'm still learning SQL everyday and was wondering which of the following is faster? I'm just gonna post parts of the SELECT statement that i've made changes to:

INNER JOIN Facilities f ON e.Facility = f.FacilityID AND f.Name = @FacilityName

OR

WHERE f.Name = @FacilityName


My question is whether or not the query runs faster if i put the condition within the JOIN line as opposed to putting in the WHERE line? Both ways seems to return the same results but the time difference between methods is staggering? Putting the condition within the JOIN line makes the query run about 3 times faster?

Again, forgive my lack of understanding, but could someone agree or disagree and give me the cliff-notes version of why or why not?

Thanks!

View 4 Replies View Related

Converting Rrom Access Syntax To Sql Syntax

Sep 23, 2007


Ok I am tying to convert access syntax to Sql syntax to put it in a stored procedure or view..
Here is the part that I need to convert:

SELECT [2007_hours].proj_name, [2007_hours].task_name, [2007_hours].Employee,
IIf(Mid([task_name],1,3)='PTO','PTO_Holiday',
IIf(Mid([task_name],1,7)='Holiday','PTO_Holiday',
IIf(Mid([proj_name],1,9) In ('9900-2831','9900-2788'),'II Internal',
IIf(Mid([proj_name],1,9)='9900-2787','Sales',
IIf(Mid([proj_name],1,9)='9910-2799','Sales',
IIf(Mid([proj_name],1,9)='9920-2791','Sales',

)
)
)
)
) AS timeType, Sum([2007_hours].Hours) AS SumOfHours
from................

how can you convert it to sql syntax

I need to have a nested If statment which I can't do in sql (in sql I have to have select and from Together for example ( I can't do this in sql):
select ID, FName, LName
if(SUBSTRING(FirstName, 1, 4)= 'Mike')
Begin
Replace(FirstNam,'Mike','MikeTest')
if(SUBSTRING(LastName, 1, 4)= 'Kong')
Begin
Replace(LastNam,'Kong,'KongTest')
if(SUBSTRING(Address, 1, 4)= '1245')
Begin
.........
End
End

end




Case Statement might be the solution but i could not do it.






Your input will be appreciated

Thank you

View 5 Replies View Related

Count For Varchar Field - How To Get Distinct Count

Jul 3, 2013

I am trying to get count on a varchar field, but it is not giving me distinct count. How can I do that? This is what I have....

Select Distinct
sum(isnull(cast([Total Count] as float),0))

from T_Status_Report
where Type = 'LastMonth' and OrderVal = '1'

View 9 Replies View Related

In SQL 2000 Can I Use Count() To Count A Column?

Nov 26, 2007

I use SQL 2000
I have a Column named Bool , the value in this Column is  0ã€?0ã€?1ã€?1ã€?1
I no I can use Count() to count this column ,the result would be "5"
but what I need is  "2" and "3" and then I will show "2" and "3" in my DataGrid
as the True is  2 and False is 3
the Query will have some limited by a Where Query.. but first i need to know .. how to have 2 result count
could it be done by Count()? please help.  
thank you very much
 

View 5 Replies View Related

Table Row Count + Index Row Count

Jul 23, 2005

SQL 2000I have a table with 5,100,000 rows.The table has three indices.The PK is a clustered index and has 5,000,000 rows - no otherconstraints.The second index has a unique constraint and has 4,950,000 rows.The third index has no constraints and has 4,950,000 rows.Why the row count difference ?Thanks,Me.

View 5 Replies View Related

Obtain Unit Percent With Unit Count Divided By Total Count In Query

Aug 21, 2007

The following query returns a value of 0 for the unit percent when I do a count/subquery count. Is there a way to get the percent count using a subquery? Another section of the query using the sum() works.

Here is a test code snippet:


--Test Count/Count subquery

declare @Date datetime

set @date = '8/15/2007'


select
-- count returns unit data
Count(substring(m.PTNumber,3,3)) as PTCnt,
-- count returns total for all units

(select Count(substring(m1.PTNumber,3,3))

from tblVGD1_Master m1

left join tblVGD1_ClassIII v1 on m1.SlotNum_ID = v1.SlotNum_ID

Where left(m1.PTNumber,2) = 'PT' and m1.Denom_ID <> 9

and v1.Act = 1 and m1.Active = 1 and v1.MnyPlyd <> 0

and not (v1.MnyPlyd = v1.MnyWon and v1.ActWin = 0)

and v1.[Date] between DateAdd(dd,-90,@Date) and @Date) as TotalCnt,
-- attempting to calculate the percent by PTCnt/TotalCnt returns 0
(Count(substring(m.PTNumber,3,3)) /

(select Count(substring(m1.PTNumber,3,3))

from tblVGD1_Master m1

left join tblVGD1_ClassIII v1 on m1.SlotNum_ID = v1.SlotNum_ID

Where left(m1.PTNumber,2) = 'PT' and m1.Denom_ID <> 9

and v1.Act = 1 and m1.Active = 1 and v1.MnyPlyd <> 0

and not (v1.MnyPlyd = v1.MnyWon and v1.ActWin = 0)

and v1.[Date] between DateAdd(dd,-90,@Date) and @Date)) as AUPct
-- main select

from tblVGD1_Master m

left join tblVGD1_ClassIII v on m.SlotNum_ID = v.SlotNum_ID

Where left(m.PTNumber,2) = 'PT' and m.Denom_ID <> 9

and v.Act = 1 and m.Active = 1 and v.MnyPlyd <> 0

and not (v.MnyPlyd = v.MnyWon and v.ActWin = 0)

and v.[Date] between DateAdd(dd,-90,@Date) and @Date

group by substring(m.PTNumber, 3,3)

order by AUPct Desc


Thanks. Dan

View 1 Replies View Related

Inserted Rows Count From SSIS Not Like Table Rows Count

Jun 25, 2007

Hi all



i using lookup error output to insert rows into table

Rows count rows has been inserted on the table 59,123,019 mill

table rows count 6,878,110 mill ............................



any ideas

View 6 Replies View Related

Incorrect Syntax Near The Keyword 'from'. Line 1: Incorrect Syntax Near ')'.

May 27, 2008

This is the error it gives me for my code and then it calls out line 102.  Line 102 is my  buildDD(sql, ddlPernames)  When I comment out this line the error goes away, but what I don't get is this is the same way I build all of my dropdown boxes and they all work but this one.  Could it not like something in my sql select statement.  thanksPrivate Sub DDLUIC_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles DDLUIC.SelectedIndexChanged
Dim taskforceID As Byte = ddlTaskForce.SelectedValueDim uic As String = DDLUIC.SelectedValue
sql = "select sidstrNAME_IND from CMS.dbo.tblSIDPERS where sidstrSSN_SM in (Select Case u.strSSN from tblAssignedPersonnel as u " _
& "where u.bitPresent = 1 and u.intUICID in (select intUICID from tblUIC where intTaskForceID = " & taskforceID & " and strUIC = '" & uic & "'))"ddlPerNames.Items.Add(New ListItem("", "0"))
buildDD(sql, ddlPerNames)
 
End Sub

View 2 Replies View Related

Count(*) Vs Count(columnname)

Aug 28, 2007

 Is there a difference in performance when using count(*) or count(columnname)?

View 10 Replies View Related

SQL Query Automatically Count Month Events B4 Today; Count Events Today + Balance Of Month

Mar 20, 2004

I would like to AUTOMATICALLY count the event for the month BEFORE today

and

count the events remaining in the month (including those for today).

I can count the events remaining in the month manually with this query (today being March 20):

SELECT Count(EventID) AS [Left for Month],
FROM RECalendar
WHERE
(EventTimeBegin >= DATEADD(DAY, 1, (CONVERT(char(10), GETDATE(), 101)))
AND EventTimeBegin < DATEADD(DAY, 12, (CONVERT(char(10), GETDATE(), 101))))

Could anyone provide me with the correct syntax to count the events for the current month before today

and

to count the events remaining in the month, including today.

Thank you for your assistance in advance.

Joel

View 1 Replies View Related

Incorrect Syntax Near The Keyword 'SELECT'.Incorrect Syntax Near The Keyword 'else'.

May 22, 2008

What I am trying to create a query to check, If recDT is not value or null, then will use value from SELECT top 1 recDtim FROM Serv. Otherwise, will use the value from recDT. I have tried the below query but it doesn't work. The error says, Incorrect syntax near the keyword 'SELECT'.Incorrect syntax near the keyword 'else'.1 SELECT
2 case when recDT='' then SELECT top 1 recDtim FROM Serv else recDT end
3 FROM abc
4
Anyone can help? Thanks a lot.

View 5 Replies View Related

SQL Syntax

Apr 2, 2007

Hi, i am trying to write a mulitple sql statement. basically i have 5 fields to search:

User ID
FirstName
LastName
Department
Site
i would like to search the records of the database with any of the fields above, so a user can specify a last name of "smith" and a department of "finance" which would return all the smiths in the finance department. or if a user enters "john" all the johns from any department or site would appear. How would the sql statement go like for this? and could i bind a tableadapter to a datagrid to view the results? 
 Any help would be appreciated. Thank you.

View 7 Replies View Related

Syntax

Aug 8, 2007

cmd.CommandText="Insert INTO personaldet(Firstname,Lastname,Username,password,dob,) values '('"+txtfname.Text.Replace("'", "''").ToString()+"','"+txtlname.Text+"','"+txtusername.Text+"','"+txtpassword.Text+"','"+txtdob.Text+"')'";
 
always says syntax error ')'.
check this plz
 

View 4 Replies View Related

Help With C# Syntax

Nov 11, 2007

Hello, I have at sp that return a value:set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[sp_getLastActivityDate]
(
@userid nvarchar(256)
 
)
AS
DECLARE @ret nvarchar(256)
SET @ret = (SELECT CAST(LastActivityDate AS nvarchar)
FROM aspnet_Users
WHERE UserName = @userid)
RETURN @ret
 The sp returns a nvarchar. How can i write the syntax in C# to grab the value in @ret?
// Tomas

View 2 Replies View Related

Bad Syntax

Feb 24, 2004

I just cannot get the syntax right for this: (The problem is in my TSart and TEnd)

Dim SQL As String = "Select DateEntered FROM tblTasks Where [DateEntered] Between " '" & TStart & "'" And "'" & TEnd & "'" And [ID] = " & _
IDSent

Thank you for any help,

View 1 Replies View Related

Help With LIKE Syntax...

May 17, 2004

HI All,

I am writing some code where i want to get a list of all the company names that start with say, M...

here is what i have:



Dim M_company, companyID_M

Set M_company = adoCon.Execute("SELECT company FROM tbl_exib WHERE company LIKE 'm%'")
companyID_M = M_company( 0 )



but this is only returning the first company in the list that starts with M. Can anybody help with getting it to return more than one value???

thanks,
Leissa

View 4 Replies View Related

SQL Syntax Help

Jan 6, 2005

I'm a 3GL guy not familiar with SQL that much. What I try to add a record to shopping cart and assign a line number for each record. If the same item already exist, instead of adding a new line I'd like to add to existing line. I use to following code and I got two lines created even I was only adding the very first first. The following are the code I wrote, would anyone please give some hints. Thx.

ALTER PROCEDURE dbo.AddItemToCart
(
@Cart nvarchar(50),
@ItmID int,
@Qty int,
@ItmTyp char
)
As

Declare @AllItmCount Int
SELECT
@AllItmCount = Count(ItmID)
FROM
ShoppingCart
WHERE
Cart = @Cart

IF @AllItmCount = 0 /*new shoppong cart*/
INSERT INTO ShoppingCart
( Cart, Qty, ItmID, ItmTyp, Line )
VALUES
( @Cart, @Qty, @ItmID, @ItmTyp, 1)

ELSE /*some item entered */

DECLARE @CountItems int

SELECT
@CountItems = Count(ItmID)
FROM
ShoppingCart
WHERE
ItmID = @ItmID AND Cart = @Cart AND ItmTyp='R'

IF @CountItems > 0 /* same item entered before then add together */

UPDATE ShoppingCart
SET Qty = (@Qty + ShoppingCart.Qty)
WHERE ItmID = @ItmID AND Cart = @Cart AND ItmTyp='R'

ELSE /* Find the last line number */

Declare @LastLine Int
SELECT @LastLine = Max(Line)
FROM ShoppingCart
WHERE Cart = @Cart

/*Add a new line */

INSERT INTO ShoppingCart
(Cart, Qty, ItmID, ItmTyp, Line)
VALUES
(@Cart, @Qty, @ItmID, @ItmTyp, @LastLine + 1)

View 6 Replies View Related

Help With SQL Syntax

Apr 23, 2005

Hi all,
I'm trying to build a simple forum. I want to display a list containing the forum names and last post date and last post author. I can view the date, but can't figure out a way to see the author. Here's the query i use:
SELECT     ForumID, ForumTitle, ForumDesc, ForumTopics, ForumReplies,                          (SELECT     MAX(AddedDate)                             FROM          (SELECT     ForumID, AddedDate, Author                                                    FROM          dbo.Forums_Topic                                                    UNION ALL                                                    SELECT     ForumId, AddedDate, Author                                                    FROM         dbo.Forum_Reply) AS dates                            WHERE      (ForumID = dbo.Forums.ForumID)) AS ForumLastPostDateFROM         dbo.Forums
Any suggestion on how to show the author of the last message as well? Thanks in advance.

View 27 Replies View Related

Sql Syntax?

Dec 6, 2005

i want to find some repeatable data in a column,is there a sql syntax here?Thanks in advance.

View 1 Replies View Related

Syntax ?

Jan 7, 2002

Hello,

I am trying to create a view, but I am having trouble with the syntax (go figure!). It seems that as I reference more and more tables, my result set becomes smaller. For example, I want to return the state for all my customers then everything is fine, but when I want the state plus the site type (home, office, etc.) then I return fewer results. What is would be the correct syntax to use to return all the rows even if some of the columns have NULL?

SELECT TimeZone.LongZone, SiteMain.SiteNumber,
SiteType.SiteTypeDescription
FROM SiteType INNER JOIN
TimeZone ON SiteType.id = TimeZone.id INNER JOIN
SiteMain ON TimeZone.id = SiteMain.TimeZone AND
SiteType.id = SiteMain.SiteType

View 1 Replies View Related

If Else Syntax

Feb 5, 2002

Can someone help me with the syntax for an if else statement such as:

select (if column(a) > 1) then print '+' else print '-')
,column(b)
,column(c)
,etc.
from table...

BOL doesn't seem to be much help for this
Thanks in advance!
CP

View 1 Replies View Related

SQL Syntax

May 15, 2002

I have some problem for construction Sql statement to execute in asp pages.
In the table the data is stored in such a way that
Worknote Id,IncidentId, SeqNo , Desc1,Desc2.
1111 2004 1 - Test1 Test2
2222 3000 1 - Test1 Test2
2222 3000 2 - Test3 Test3

I want to take the maximum of IncidentId (3000) and concatinate the description (Test1 TEst2 Test3 Test4)

I dono how to construct SQl to fetch records . I tried some way , it gives only the last row.

SELECT iWorkNoteID, SeqNum, vchWorkNote1, vchWorkNote2 from incidentworknote where iWorkNoteID = (Select max(iWorknoteID) from incidentworknote where iIncidentID = " & iIncidentId & ")"

Can anyone helop me to find out.

View 1 Replies View Related

SQL DBF Syntax

Jun 5, 2000

I have a sql database which is not a backed up copy.....just a copy of the dbf file. I have tried everything in the enterprise manager to get the file re-introduced to no avail. I was told that you could execute the DB in the query analyser with the following

execute sp_attach_db @filename

but it does not like the syntax....can anyone please help.....

View 1 Replies View Related







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