Hi,
I want to calculate inventory with FIFO METHOD. my tabel is :
inventory(doc char(10),tgl date, qty numeric(15,2), price
numeric(15,2))
doc tgl PRICE QTY
---
FP123 02/02/06 180 10
ASD12 07/02/06 -9
FP23 10/02/06 150 2
ASD14 11/02/06 -2
I want to result is as below:
doc tgl price qty
---
FP123 02/02/06 180 10
ASD12 07/02/06 180 -9
FP23 10/02/06 150 2
ASD14 11/02/06 180 -1
ASD14 11/02/06 150 -1
how to create sintak SQL SERVER ?
thank you for your advanced.
best regards,
alimIf I understand your problem correctly , the sybtax is;
SELECT doc,tgl,qty,price ORDER BY tgl ASC
Jack Vamvas
___________________________________
Receive free SQL tips - http://www.ciquery.com/sqlserver.htm
"alim" <alfen_lim@.yahoo.com> wrote in message
news:1141293436.115783.286230@.i40g2000cwc.googlegroups.com...
> Hi,
> I want to calculate inventory with FIFO METHOD. my tabel is :
> inventory(doc char(10),tgl date, qty numeric(15,2), price
> numeric(15,2))
> doc tgl PRICE QTY
> ---
> FP123 02/02/06 180 10
> ASD12 07/02/06 -9
> FP23 10/02/06 150 2
> ASD14 11/02/06 -2
> I want to result is as below:
> doc tgl price qty
> ---
> FP123 02/02/06 180 10
> ASD12 07/02/06 180 -9
> FP23 10/02/06 150 2
> ASD14 11/02/06 180 -1
> ASD14 11/02/06 150 -1
> how to create sintak SQL SERVER ?
> thank you for your advanced.
> best regards,
> alim
>|||hi jack,
cannot used syntax like :SELECT doc,tgl,qty,price ORDER BY tgl ASC
because
in tabel : ASD14 11/02/06 -2
I want to :
ASD14 11/02/06 180 -1
ASD14 11/02/06 150 -1
best regards,
alim|||Have you been over to www.dbazine.com and looked for my article on
inventory?
Showing posts with label method. Show all posts
Showing posts with label method. Show all posts
Wednesday, March 28, 2012
Invalid Syntax in my sproc?!?!
Hi I have a gridview that is being populated from a method that gets it's data from a table view.
SELECT dbo.cis_AlumniContact.Street, dbo.cis_AlumniContact.City, dbo.cis_AlumniContact.State, dbo.cis_AlumniContact.Telephone,
dbo.cis_AlumniContact.Occupation, dbo.cis_AlumniContact.Description, dbo.cis_AlumniContact.Zip, dbo.cis_AlumniContact.FirstName,
dbo.cis_AlumniContact.LastName, dbo.cis_AlumniContact.YearGraduate, dbo.cis_AlumniContact.Email, dbo.cis_AlumniContact.Contact,
dbo.aspnet_Users.UserName, dbo.cis_StudentId.UaaStudentId
FROM dbo.aspnet_Users INNER JOIN
dbo.cis_AlumniContact ON dbo.aspnet_Users.UserId = dbo.cis_AlumniContact.UserId INNER JOIN
dbo.cis_StudentId ON dbo.aspnet_Users.UserId = dbo.cis_StudentId.UserId
No big deal, works great. Now when I click update I call this method
PublicSub UpdateAlumni(ByVal StreetAsString,ByVal CityAsString,ByVal StateAsString,ByVal TelephoneAsString,ByVal OccupationAsString,ByVal DescriptionAsString,ByVal ZipAsString,ByVal FirstNameAsString,ByVal LastNameAsString,ByVal YearGraduateAsString,ByVal EmailAsString,ByVal ContactAsBoolean,ByVal original_UserNameAsString,ByVal UaaStudentIdAsString)TryDim connxAsNew SqlConnection(getConnectionString)<DataObjectMethod(DataObjectMethodType.Update)>
connx.Open()
Dim sqlCmdAsNew SqlCommand("cis_UpdateAlumniContact", connx)sqlCmd.Parameters.Add(
New SqlParameter("@.UserName", SqlDbType.NVarChar))sqlCmd.Parameters(
"@.UserName").Value = original_UserNamesqlCmd.Parameters.Add(
New SqlParameter("@.FirstName", SqlDbType.NVarChar))sqlCmd.Parameters(
"@.FirstName").Value = FirstNamesqlCmd.Parameters.Add(
New SqlParameter("@.LastName", SqlDbType.NVarChar))sqlCmd.Parameters(
"@.LastName").Value = LastNamesqlCmd.Parameters.Add(
New SqlParameter("@.Email", SqlDbType.NVarChar))sqlCmd.Parameters(
"@.Email").Value = EmailsqlCmd.Parameters.Add(
New SqlParameter("@.Street", SqlDbType.NVarChar))sqlCmd.Parameters(
"@.Street").Value = StreetsqlCmd.Parameters.Add(
New SqlParameter("@.City", SqlDbType.NVarChar))sqlCmd.Parameters(
"@.City").Value = CitysqlCmd.Parameters.Add(
New SqlParameter("@.State", SqlDbType.NVarChar))sqlCmd.Parameters(
"@.State").Value = StatesqlCmd.Parameters.Add(
New SqlParameter("@.Occupation", SqlDbType.NVarChar))sqlCmd.Parameters(
"@.Occupation").Value = OccupationsqlCmd.Parameters.Add(
New SqlParameter("@.Description", SqlDbType.NVarChar))sqlCmd.Parameters(
"@.Description").Value = DescriptionsqlCmd.Parameters.Add(
New SqlParameter("@.Telephone", SqlDbType.NChar))sqlCmd.Parameters(
"@.Telephone").Value = TelephonesqlCmd.Parameters.Add(
New SqlParameter("@.Zip", SqlDbType.NChar))sqlCmd.Parameters(
"@.Zip").Value = ZipsqlCmd.Parameters.Add(
New SqlParameter("@.Contact", SqlDbType.Bit))sqlCmd.Parameters(
"@.Contact").Value = ContactsqlCmd.Parameters.Add(
New SqlParameter("@.YearGraduate", SqlDbType.NVarChar))sqlCmd.Parameters(
"@.YearGraduate").Value = YearGraduateDim cmdAs SqlDataReader = sqlCmd.ExecuteReaderCatch exAs ExceptionDim erAsNew cis_ODS_Errorer.InsertError(
"cis_ODS_Alumni - UpdateAlumni: " + ex.Message.ToString)EndTryEndSubThe sproc it calls is:
dbo.cis_UpdateAlumniContactALTER PROCEDURE
@.UserName
as nvarchar(50),@.Street
as nvarchar(50),@.City
as nvarchar(50),@.State
as nvarchar(2),@.Telephone
as nvarchar(50),@.Occupation
as nvarchar(50),@.Description
as nvarchar(50),@.Zip
as nvarchar(50),@.FirstName
as nvarchar(50),@.LastName
as nvarchar(50),@.YearGraduate
as nvarchar(4),@.Contact
as bitAS
UPDATEcis_AlumniContactSETStreet = @.Street, City = @.City, State = @.State, Telephone = @.Telephone, Occupation = @.Occupation, Description = @.Description, Zip = @.Zip, FirstName = @.FirstName, LastName = @.LastName, YearGraduate = @.YearGraduate, Email = @.Email, Contact = @.Contact
FROMaspnet_UsersINNER JOINcis_AlumniContact
ONcis_AlumniContact.UserId = aspnet_Users.UserId
WHERE@.UserName = aspnet_Users.UserName
RETURN
I get this vague error
cis_ODS_Alumni - UpdateAlumni: Incorrect syntax near 'cis_UpdateAlumniContact'
If I execute the SQL from the editor it works fine. The only thing different about this sproc and my other update sprocs is the inner join. Any idea? Thanks
I see two possible problems.
You need to set the command type to:StoredProcedure
You are calling ExecuteReader, I think you need to call ExecuteNonQuery.
|||Holy cow man. Thanks so much. I copied and pasted from another method and didn't include that line. Thanks again.
Monday, March 12, 2012
Invalid Column?
Two things I could use help with. First one is I know there is probbaly a
way better method to do this and I would love someone to show me what it is
having just begun in the SQL trans language. The 2nd is, I am curious why,
even though its clearly not the best method, why it doesn't work and returns
an error "invalid column name 'tod' " for the line containing the 'on'
statement for the join. It looks like it should work, but doesn't...:(
tnx
select datepart(hh,a.timearrived) as TOD, count(a.calldate) as total,
b.total
from tbldatapcr a
inner join
(
select datepart(hh,timearrived) as TOD, count(calldate) as Total
from tbldatapcr
where calldate between '09/01/04' and '12/01/04'
and timearrived is not null
group by datepart(hh,timearrived)
) b
on a.tod = b.tod
where calldate between '09/01/04' and '12/01/04'
and timearrived is not null
and cdisp = 40
group by datepart(hh,a.timearrived)
order by datepart(hh,a.timearrived)You need to specify for the join
"ON a.timearrived = b.tod"
"Dave S." <davidstedman@.colliergov.net> wrote in message
news:uIVaAEnHFHA.2744@.tk2msftngp13.phx.gbl...
> Two things I could use help with. First one is I know there is probbaly a
> way better method to do this and I would love someone to show me what it
is
> having just begun in the SQL trans language. The 2nd is, I am curious why,
> even though its clearly not the best method, why it doesn't work and
returns
> an error "invalid column name 'tod' " for the line containing the 'on'
> statement for the join. It looks like it should work, but doesn't...:(
> tnx
>
> select datepart(hh,a.timearrived) as TOD, count(a.calldate) as total,
> b.total
> from tbldatapcr a
> inner join
> (
> select datepart(hh,timearrived) as TOD, count(calldate) as Total
> from tbldatapcr
> where calldate between '09/01/04' and '12/01/04'
> and timearrived is not null
> group by datepart(hh,timearrived)
> ) b
> on a.tod = b.tod
> where calldate between '09/01/04' and '12/01/04'
> and timearrived is not null
> and cdisp = 40
> group by datepart(hh,a.timearrived)
> order by datepart(hh,a.timearrived)
>
>|||If TOD doesn't exist in the table then you can't reference it unless it
exists in a derived table query. It looks like you also need to add
B.total to the outer query's GROUP BY list.
Here's a shorter (and untested) version of the query:
SELECT DATEPART(HH,timearrived) AS tod,
COUNT(calldate) AS total,
COUNT(CASE WHEN cdisp=40 THEN calldate END) AS total
FROM tbldatapcr
WHERE calldate BETWEEN '20040901' and '20041201'
AND timearrived IS NOT NULL
GROUP BY DATEPART(HH,timearrived)
Use the format I have used for date literals. Your version isn't safe
under all connection settings and may cause errors in a production
environment.
David Portas
SQL Server MVP
--|||Dave,
> Two things I could use help with. First one is I know there is probbaly a
> way better method to do this and I would love someone to show me what it i
s
> having just begun in the SQL trans language.
It is always welcome when you post DDL, sample data and expected result. As
you can see there no one piece of info about your tables, constraints, etc.
What are you trying to accomplish?
Are you looking for a guess?
> even though its clearly not the best method, why it doesn't work and retur
ns
> an error "invalid column name 'tod' " for the line containing the 'on'
> statement for the join. It looks like it should work, but doesn't...:(
There is not a column named "tod" in table "tbldatapcr". You can not use a
column alias in the WHERE clause.
...
> on a.tod = b.tod
...
should be:
...
on datepart(hh,a.timearrived) = b.tod
...
The reason you can reference "b.tod" is because "b" is a derived table.
AMB
"Dave S." wrote:
> Two things I could use help with. First one is I know there is probbaly a
> way better method to do this and I would love someone to show me what it i
s
> having just begun in the SQL trans language. The 2nd is, I am curious why,
> even though its clearly not the best method, why it doesn't work and retur
ns
> an error "invalid column name 'tod' " for the line containing the 'on'
> statement for the join. It looks like it should work, but doesn't...:(
> tnx
>
> select datepart(hh,a.timearrived) as TOD, count(a.calldate) as total,
> b.total
> from tbldatapcr a
> inner join
> (
> select datepart(hh,timearrived) as TOD, count(calldate) as Total
> from tbldatapcr
> where calldate between '09/01/04' and '12/01/04'
> and timearrived is not null
> group by datepart(hh,timearrived)
> ) b
> on a.tod = b.tod
> where calldate between '09/01/04' and '12/01/04'
> and timearrived is not null
> and cdisp = 40
> group by datepart(hh,a.timearrived)
> order by datepart(hh,a.timearrived)
>
>
>|||Sorry, forgot this part
column_alias can be used in an ORDER BY clause. However, it cannot be used
in a WHERE, GROUP BY, or HAVING clause. If the query expression is part of a
DECLARE CURSOR statement, column_alias cannot be used in the FOR UPDATE
clause.
"SW" <simon.worth@.gmail.com> wrote in message
news:%23xexMMnHFHA.1476@.TK2MSFTNGP09.phx.gbl...
> You need to specify for the join
> "ON a.timearrived = b.tod"
> "Dave S." <davidstedman@.colliergov.net> wrote in message
> news:uIVaAEnHFHA.2744@.tk2msftngp13.phx.gbl...
a
> is
why,
> returns
>|||Here is the answer.
Dynamic Search Conditions in T-SQL
http://www.sommarskog.se/dyn-search.html
AMB
"Dave S." wrote:
> Two things I could use help with. First one is I know there is probbaly a
> way better method to do this and I would love someone to show me what it i
s
> having just begun in the SQL trans language. The 2nd is, I am curious why,
> even though its clearly not the best method, why it doesn't work and retur
ns
> an error "invalid column name 'tod' " for the line containing the 'on'
> statement for the join. It looks like it should work, but doesn't...:(
> tnx
>
> select datepart(hh,a.timearrived) as TOD, count(a.calldate) as total,
> b.total
> from tbldatapcr a
> inner join
> (
> select datepart(hh,timearrived) as TOD, count(calldate) as Total
> from tbldatapcr
> where calldate between '09/01/04' and '12/01/04'
> and timearrived is not null
> group by datepart(hh,timearrived)
> ) b
> on a.tod = b.tod
> where calldate between '09/01/04' and '12/01/04'
> and timearrived is not null
> and cdisp = 40
> group by datepart(hh,a.timearrived)
> order by datepart(hh,a.timearrived)
>
>
>|||Sorry, wrong place.
AMB
"Alejandro Mesa" wrote:
> Here is the answer.
> Dynamic Search Conditions in T-SQL
> http://www.sommarskog.se/dyn-search.html
>
> AMB
> "Dave S." wrote:
>
way better method to do this and I would love someone to show me what it is
having just begun in the SQL trans language. The 2nd is, I am curious why,
even though its clearly not the best method, why it doesn't work and returns
an error "invalid column name 'tod' " for the line containing the 'on'
statement for the join. It looks like it should work, but doesn't...:(
tnx
select datepart(hh,a.timearrived) as TOD, count(a.calldate) as total,
b.total
from tbldatapcr a
inner join
(
select datepart(hh,timearrived) as TOD, count(calldate) as Total
from tbldatapcr
where calldate between '09/01/04' and '12/01/04'
and timearrived is not null
group by datepart(hh,timearrived)
) b
on a.tod = b.tod
where calldate between '09/01/04' and '12/01/04'
and timearrived is not null
and cdisp = 40
group by datepart(hh,a.timearrived)
order by datepart(hh,a.timearrived)You need to specify for the join
"ON a.timearrived = b.tod"
"Dave S." <davidstedman@.colliergov.net> wrote in message
news:uIVaAEnHFHA.2744@.tk2msftngp13.phx.gbl...
> Two things I could use help with. First one is I know there is probbaly a
> way better method to do this and I would love someone to show me what it
is
> having just begun in the SQL trans language. The 2nd is, I am curious why,
> even though its clearly not the best method, why it doesn't work and
returns
> an error "invalid column name 'tod' " for the line containing the 'on'
> statement for the join. It looks like it should work, but doesn't...:(
> tnx
>
> select datepart(hh,a.timearrived) as TOD, count(a.calldate) as total,
> b.total
> from tbldatapcr a
> inner join
> (
> select datepart(hh,timearrived) as TOD, count(calldate) as Total
> from tbldatapcr
> where calldate between '09/01/04' and '12/01/04'
> and timearrived is not null
> group by datepart(hh,timearrived)
> ) b
> on a.tod = b.tod
> where calldate between '09/01/04' and '12/01/04'
> and timearrived is not null
> and cdisp = 40
> group by datepart(hh,a.timearrived)
> order by datepart(hh,a.timearrived)
>
>|||If TOD doesn't exist in the table then you can't reference it unless it
exists in a derived table query. It looks like you also need to add
B.total to the outer query's GROUP BY list.
Here's a shorter (and untested) version of the query:
SELECT DATEPART(HH,timearrived) AS tod,
COUNT(calldate) AS total,
COUNT(CASE WHEN cdisp=40 THEN calldate END) AS total
FROM tbldatapcr
WHERE calldate BETWEEN '20040901' and '20041201'
AND timearrived IS NOT NULL
GROUP BY DATEPART(HH,timearrived)
Use the format I have used for date literals. Your version isn't safe
under all connection settings and may cause errors in a production
environment.
David Portas
SQL Server MVP
--|||Dave,
> Two things I could use help with. First one is I know there is probbaly a
> way better method to do this and I would love someone to show me what it i
s
> having just begun in the SQL trans language.
It is always welcome when you post DDL, sample data and expected result. As
you can see there no one piece of info about your tables, constraints, etc.
What are you trying to accomplish?
Are you looking for a guess?
> even though its clearly not the best method, why it doesn't work and retur
ns
> an error "invalid column name 'tod' " for the line containing the 'on'
> statement for the join. It looks like it should work, but doesn't...:(
There is not a column named "tod" in table "tbldatapcr". You can not use a
column alias in the WHERE clause.
...
> on a.tod = b.tod
...
should be:
...
on datepart(hh,a.timearrived) = b.tod
...
The reason you can reference "b.tod" is because "b" is a derived table.
AMB
"Dave S." wrote:
> Two things I could use help with. First one is I know there is probbaly a
> way better method to do this and I would love someone to show me what it i
s
> having just begun in the SQL trans language. The 2nd is, I am curious why,
> even though its clearly not the best method, why it doesn't work and retur
ns
> an error "invalid column name 'tod' " for the line containing the 'on'
> statement for the join. It looks like it should work, but doesn't...:(
> tnx
>
> select datepart(hh,a.timearrived) as TOD, count(a.calldate) as total,
> b.total
> from tbldatapcr a
> inner join
> (
> select datepart(hh,timearrived) as TOD, count(calldate) as Total
> from tbldatapcr
> where calldate between '09/01/04' and '12/01/04'
> and timearrived is not null
> group by datepart(hh,timearrived)
> ) b
> on a.tod = b.tod
> where calldate between '09/01/04' and '12/01/04'
> and timearrived is not null
> and cdisp = 40
> group by datepart(hh,a.timearrived)
> order by datepart(hh,a.timearrived)
>
>
>|||Sorry, forgot this part
column_alias can be used in an ORDER BY clause. However, it cannot be used
in a WHERE, GROUP BY, or HAVING clause. If the query expression is part of a
DECLARE CURSOR statement, column_alias cannot be used in the FOR UPDATE
clause.
"SW" <simon.worth@.gmail.com> wrote in message
news:%23xexMMnHFHA.1476@.TK2MSFTNGP09.phx.gbl...
> You need to specify for the join
> "ON a.timearrived = b.tod"
> "Dave S." <davidstedman@.colliergov.net> wrote in message
> news:uIVaAEnHFHA.2744@.tk2msftngp13.phx.gbl...
a
> is
why,
> returns
>|||Here is the answer.
Dynamic Search Conditions in T-SQL
http://www.sommarskog.se/dyn-search.html
AMB
"Dave S." wrote:
> Two things I could use help with. First one is I know there is probbaly a
> way better method to do this and I would love someone to show me what it i
s
> having just begun in the SQL trans language. The 2nd is, I am curious why,
> even though its clearly not the best method, why it doesn't work and retur
ns
> an error "invalid column name 'tod' " for the line containing the 'on'
> statement for the join. It looks like it should work, but doesn't...:(
> tnx
>
> select datepart(hh,a.timearrived) as TOD, count(a.calldate) as total,
> b.total
> from tbldatapcr a
> inner join
> (
> select datepart(hh,timearrived) as TOD, count(calldate) as Total
> from tbldatapcr
> where calldate between '09/01/04' and '12/01/04'
> and timearrived is not null
> group by datepart(hh,timearrived)
> ) b
> on a.tod = b.tod
> where calldate between '09/01/04' and '12/01/04'
> and timearrived is not null
> and cdisp = 40
> group by datepart(hh,a.timearrived)
> order by datepart(hh,a.timearrived)
>
>
>|||Sorry, wrong place.
AMB
"Alejandro Mesa" wrote:
> Here is the answer.
> Dynamic Search Conditions in T-SQL
> http://www.sommarskog.se/dyn-search.html
>
> AMB
> "Dave S." wrote:
>
Subscribe to:
Comments (Atom)