Dear all,
I am using SQL 2000 with vs2003.
I need to create a store procedure which get the LAST record which has been
written in the database.
What is teh way to get that record ?
thnaks for your help
regards
sergeHi
How many table does the database have?
What is at the same time users insert data in many tables?
One option is create a new columns as datetime (DEFAULT GETDATE()) for
inserting .For updating you will have to create a trigger for update to
track the changes.
"serge calderara" <sergecalderara@.discussions.microsoft.com> wrote in
message news:78D429C3-3519-45FA-ADD8-C952D199D47D@.microsoft.com...
> Dear all,
> I am using SQL 2000 with vs2003.
> I need to create a store procedure which get the LAST record which has
> been
> written in the database.
> What is teh way to get that record ?
> thnaks for your help
> regards
> serge|||I only need to return a single row from a database table which already
contains a DateTime field.
I have tried to use the TOP keyword as follow in a strore procedure:
SELECT TOP ID, LEN, START_TIME FROM REELHIST ORDER BY START_TIME DESC
But it return a syntax error message when checking the syntax
Any idea ?
regards
serge
"Uri Dimant" wrote:
> Hi
> How many table does the database have?
> What is at the same time users insert data in many tables?
> One option is create a new columns as datetime (DEFAULT GETDATE()) for
> inserting .For updating you will have to create a trigger for update to
> track the changes.
>
> "serge calderara" <sergecalderara@.discussions.microsoft.com> wrote in
> message news:78D429C3-3519-45FA-ADD8-C952D199D47D@.microsoft.com...
>
>|||SELECT TOP 1 ID, LEN, START_TIME FROM REELHIST ORDER BY START_TIME DESC
"serge calderara" <sergecalderara@.discussions.microsoft.com> wrote in
message news:486F2059-0EE0-4CF6-8687-3C5F707EE77F@.microsoft.com...
>I only need to return a single row from a database table which already
> contains a DateTime field.
> I have tried to use the TOP keyword as follow in a strore procedure:
> SELECT TOP ID, LEN, START_TIME FROM REELHIST ORDER BY START_TIME DESC
> But it return a syntax error message when checking the syntax
> Any idea ?
> regards
> serge
> "Uri Dimant" wrote:
>|||thnaks,
so simple sometimes :-)
"Uri Dimant" wrote:
> SELECT TOP 1 ID, LEN, START_TIME FROM REELHIST ORDER BY START_TIME DESC
> "serge calderara" <sergecalderara@.discussions.microsoft.com> wrote in
> message news:486F2059-0EE0-4CF6-8687-3C5F707EE77F@.microsoft.com...
>
>
Showing posts with label syntax. Show all posts
Showing posts with label syntax. Show all posts
Wednesday, March 28, 2012
help on SQL syntax ?
Dear all,
I am not so familar with SQL syntax.
Is there a good link where keywords and syntax are clearly explain ?
I am running SQL server 2000 Sp4 on server 2003.
regards
sergeHi serge,
read the "BOL"
http://www.aspfaq.com/show.asp?id=2229
HTH, Jens Suessmeyer,|||Hi
Books online is a good source for a lot of information and should be the
first thing to look at. If it is not in your SQL Server program group then
you can download it from
http://www.microsoft.com/downloads/...&displaylang=en
John
"serge calderara" wrote:
> Dear all,
> I am not so familar with SQL syntax.
> Is there a good link where keywords and syntax are clearly explain ?
> I am running SQL server 2000 Sp4 on server 2003.
> regards
> serge|||If BOL is not your thing you might try this free book:
http://www.managedtime.com/freesqlbook.php3
Msql
I am not so familar with SQL syntax.
Is there a good link where keywords and syntax are clearly explain ?
I am running SQL server 2000 Sp4 on server 2003.
regards
sergeHi serge,
read the "BOL"
http://www.aspfaq.com/show.asp?id=2229
HTH, Jens Suessmeyer,|||Hi
Books online is a good source for a lot of information and should be the
first thing to look at. If it is not in your SQL Server program group then
you can download it from
http://www.microsoft.com/downloads/...&displaylang=en
John
"serge calderara" wrote:
> Dear all,
> I am not so familar with SQL syntax.
> Is there a good link where keywords and syntax are clearly explain ?
> I am running SQL server 2000 Sp4 on server 2003.
> regards
> serge|||If BOL is not your thing you might try this free book:
http://www.managedtime.com/freesqlbook.php3
Msql
Help on SQL syntax
Dear Anyone,
I am no expert with SQL server but I am trying obtain
some data from this server using SQL syntax, I would like
to average out the data by correction_col_date below, can
anyone help please!
SELECT SAMPLE.PRODUCT_VERSION, PRODUCT.DESCRIPTION,
SAMPLE.PRODUCT, SAMPLE.PRODUCT_GRADE,
SAMPLE.CORRECTED_COL_DATE, SAMPLE.COLLECTION_TIME,
SAMPLE.LOCATION, PRODUCT_SPEC.NOMINAL_VALUE,
PRODUCT_SPEC.MAX_VALUE, PRODUCT_SPEC.MIN_VALUE,
RESULT.IN_SPEC, SAMPLE.LOGIN_BY, UNITS.DISPLAY_STRING,
RESULT.NAME, RESULT.ALIAS_NAME, RESULT.REPORTED_NAME,
RESULT.ENTRY FROM PRODUCT_SPEC, SAMPLE, RESULT, UNITS,
PRODUCT WHERE SAMPLE.PRODUCT = <<PROD_BRWSER>> AND
SAMPLE.CORRECTED_COL_DATE BETWEEN <<DATERANGE>> AND
SAMPLE.LOCATION= <<LOCATION_BROWSE>> AND
PRODUCT_SPEC.PRODUCT = SAMPLE.PRODUCT AND PRODUCT.NAME =
SAMPLE.PRODUCT AND PRODUCT_SPEC.VERSION =
SAMPLE.PRODUCT_VERSION AND PRODUCT_SPEC.GRADE =
SAMPLE.PRODUCT_GRADE AND
PRODUCT_SPEC.ANALYSIS = RESULT.ANALYSIS AND
PRODUCT_SPEC.COMPONENT = RESULT.NAME AND
RESULT.SAMPLE_NUMBER = SAMPLE.SAMPLE_NUMBER AND
((RESULT.ALIAS_NAME LIKE '%MEAN') OR (RESULT.ALIAS_NAME
LIKE '%SD')) AND RESULT.UNITS=UNITS.UNIT_CODE AND
RESULT.STATUS = 'A' AND SAMPLE.STATUS = 'A'ORDER BY
CORRECTED_COL_DATE
Thanks,
Annette
You can use AVG() aggregate function with GROUP BY clause, for example:
SELECT SAMPLE.CORRECTED_COL_DATE, AVG(SAMPLE.PRODUCT_GRADE)
FROM PRODUCT_SPEC, SAMPLE, RESULT, UNITS, PRODUCT
WHERE SAMPLE.PRODUCT = <<PROD_BRWSER>> AND
SAMPLE.CORRECTED_COL_DATE BETWEEN <<DATERANGE>> AND
SAMPLE.LOCATION= <<LOCATION_BROWSE>> AND
PRODUCT_SPEC.PRODUCT = SAMPLE.PRODUCT AND
PRODUCT.NAME = SAMPLE.PRODUCT AND
PRODUCT_SPEC.VERSION = SAMPLE.PRODUCT_VERSION AND
PRODUCT_SPEC.GRADE = SAMPLE.PRODUCT_GRADE AND
PRODUCT_SPEC.ANALYSIS = RESULT.ANALYSIS AND
PRODUCT_SPEC.COMPONENT = RESULT.NAME AND
RESULT.SAMPLE_NUMBER = SAMPLE.SAMPLE_NUMBER AND
((RESULT.ALIAS_NAME LIKE '%MEAN') OR (RESULT.ALIAS_NAME LIKE '%SD'))
AND
RESULT.UNITS=UNITS.UNIT_CODE AND
RESULT.STATUS = 'A' AND SAMPLE.STATUS = 'A'
GROUP BY SAMPLE.CORRECTED_COL_DATE
ORDER BY CORRECTED_COL_DATE
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Annette Malini" <anonymous@.discussions.microsoft.com> wrote in message
news:731d01c48423$63065e20$a501280a@.phx.gbl...
> Dear Anyone,
> I am no expert with SQL server but I am trying obtain
> some data from this server using SQL syntax, I would like
> to average out the data by correction_col_date below, can
> anyone help please!
> SELECT SAMPLE.PRODUCT_VERSION, PRODUCT.DESCRIPTION,
> SAMPLE.PRODUCT, SAMPLE.PRODUCT_GRADE,
> SAMPLE.CORRECTED_COL_DATE, SAMPLE.COLLECTION_TIME,
> SAMPLE.LOCATION, PRODUCT_SPEC.NOMINAL_VALUE,
> PRODUCT_SPEC.MAX_VALUE, PRODUCT_SPEC.MIN_VALUE,
> RESULT.IN_SPEC, SAMPLE.LOGIN_BY, UNITS.DISPLAY_STRING,
> RESULT.NAME, RESULT.ALIAS_NAME, RESULT.REPORTED_NAME,
> RESULT.ENTRY FROM PRODUCT_SPEC, SAMPLE, RESULT, UNITS,
> PRODUCT WHERE SAMPLE.PRODUCT = <<PROD_BRWSER>> AND
> SAMPLE.CORRECTED_COL_DATE BETWEEN <<DATERANGE>> AND
> SAMPLE.LOCATION= <<LOCATION_BROWSE>> AND
> PRODUCT_SPEC.PRODUCT = SAMPLE.PRODUCT AND PRODUCT.NAME =
> SAMPLE.PRODUCT AND PRODUCT_SPEC.VERSION =
> SAMPLE.PRODUCT_VERSION AND PRODUCT_SPEC.GRADE =
> SAMPLE.PRODUCT_GRADE AND
> PRODUCT_SPEC.ANALYSIS = RESULT.ANALYSIS AND
> PRODUCT_SPEC.COMPONENT = RESULT.NAME AND
> RESULT.SAMPLE_NUMBER = SAMPLE.SAMPLE_NUMBER AND
> ((RESULT.ALIAS_NAME LIKE '%MEAN') OR (RESULT.ALIAS_NAME
> LIKE '%SD')) AND RESULT.UNITS=UNITS.UNIT_CODE AND
> RESULT.STATUS = 'A' AND SAMPLE.STATUS = 'A'ORDER BY
> CORRECTED_COL_DATE
> Thanks,
> Annette
>
I am no expert with SQL server but I am trying obtain
some data from this server using SQL syntax, I would like
to average out the data by correction_col_date below, can
anyone help please!
SELECT SAMPLE.PRODUCT_VERSION, PRODUCT.DESCRIPTION,
SAMPLE.PRODUCT, SAMPLE.PRODUCT_GRADE,
SAMPLE.CORRECTED_COL_DATE, SAMPLE.COLLECTION_TIME,
SAMPLE.LOCATION, PRODUCT_SPEC.NOMINAL_VALUE,
PRODUCT_SPEC.MAX_VALUE, PRODUCT_SPEC.MIN_VALUE,
RESULT.IN_SPEC, SAMPLE.LOGIN_BY, UNITS.DISPLAY_STRING,
RESULT.NAME, RESULT.ALIAS_NAME, RESULT.REPORTED_NAME,
RESULT.ENTRY FROM PRODUCT_SPEC, SAMPLE, RESULT, UNITS,
PRODUCT WHERE SAMPLE.PRODUCT = <<PROD_BRWSER>> AND
SAMPLE.CORRECTED_COL_DATE BETWEEN <<DATERANGE>> AND
SAMPLE.LOCATION= <<LOCATION_BROWSE>> AND
PRODUCT_SPEC.PRODUCT = SAMPLE.PRODUCT AND PRODUCT.NAME =
SAMPLE.PRODUCT AND PRODUCT_SPEC.VERSION =
SAMPLE.PRODUCT_VERSION AND PRODUCT_SPEC.GRADE =
SAMPLE.PRODUCT_GRADE AND
PRODUCT_SPEC.ANALYSIS = RESULT.ANALYSIS AND
PRODUCT_SPEC.COMPONENT = RESULT.NAME AND
RESULT.SAMPLE_NUMBER = SAMPLE.SAMPLE_NUMBER AND
((RESULT.ALIAS_NAME LIKE '%MEAN') OR (RESULT.ALIAS_NAME
LIKE '%SD')) AND RESULT.UNITS=UNITS.UNIT_CODE AND
RESULT.STATUS = 'A' AND SAMPLE.STATUS = 'A'ORDER BY
CORRECTED_COL_DATE
Thanks,
Annette
You can use AVG() aggregate function with GROUP BY clause, for example:
SELECT SAMPLE.CORRECTED_COL_DATE, AVG(SAMPLE.PRODUCT_GRADE)
FROM PRODUCT_SPEC, SAMPLE, RESULT, UNITS, PRODUCT
WHERE SAMPLE.PRODUCT = <<PROD_BRWSER>> AND
SAMPLE.CORRECTED_COL_DATE BETWEEN <<DATERANGE>> AND
SAMPLE.LOCATION= <<LOCATION_BROWSE>> AND
PRODUCT_SPEC.PRODUCT = SAMPLE.PRODUCT AND
PRODUCT.NAME = SAMPLE.PRODUCT AND
PRODUCT_SPEC.VERSION = SAMPLE.PRODUCT_VERSION AND
PRODUCT_SPEC.GRADE = SAMPLE.PRODUCT_GRADE AND
PRODUCT_SPEC.ANALYSIS = RESULT.ANALYSIS AND
PRODUCT_SPEC.COMPONENT = RESULT.NAME AND
RESULT.SAMPLE_NUMBER = SAMPLE.SAMPLE_NUMBER AND
((RESULT.ALIAS_NAME LIKE '%MEAN') OR (RESULT.ALIAS_NAME LIKE '%SD'))
AND
RESULT.UNITS=UNITS.UNIT_CODE AND
RESULT.STATUS = 'A' AND SAMPLE.STATUS = 'A'
GROUP BY SAMPLE.CORRECTED_COL_DATE
ORDER BY CORRECTED_COL_DATE
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Annette Malini" <anonymous@.discussions.microsoft.com> wrote in message
news:731d01c48423$63065e20$a501280a@.phx.gbl...
> Dear Anyone,
> I am no expert with SQL server but I am trying obtain
> some data from this server using SQL syntax, I would like
> to average out the data by correction_col_date below, can
> anyone help please!
> SELECT SAMPLE.PRODUCT_VERSION, PRODUCT.DESCRIPTION,
> SAMPLE.PRODUCT, SAMPLE.PRODUCT_GRADE,
> SAMPLE.CORRECTED_COL_DATE, SAMPLE.COLLECTION_TIME,
> SAMPLE.LOCATION, PRODUCT_SPEC.NOMINAL_VALUE,
> PRODUCT_SPEC.MAX_VALUE, PRODUCT_SPEC.MIN_VALUE,
> RESULT.IN_SPEC, SAMPLE.LOGIN_BY, UNITS.DISPLAY_STRING,
> RESULT.NAME, RESULT.ALIAS_NAME, RESULT.REPORTED_NAME,
> RESULT.ENTRY FROM PRODUCT_SPEC, SAMPLE, RESULT, UNITS,
> PRODUCT WHERE SAMPLE.PRODUCT = <<PROD_BRWSER>> AND
> SAMPLE.CORRECTED_COL_DATE BETWEEN <<DATERANGE>> AND
> SAMPLE.LOCATION= <<LOCATION_BROWSE>> AND
> PRODUCT_SPEC.PRODUCT = SAMPLE.PRODUCT AND PRODUCT.NAME =
> SAMPLE.PRODUCT AND PRODUCT_SPEC.VERSION =
> SAMPLE.PRODUCT_VERSION AND PRODUCT_SPEC.GRADE =
> SAMPLE.PRODUCT_GRADE AND
> PRODUCT_SPEC.ANALYSIS = RESULT.ANALYSIS AND
> PRODUCT_SPEC.COMPONENT = RESULT.NAME AND
> RESULT.SAMPLE_NUMBER = SAMPLE.SAMPLE_NUMBER AND
> ((RESULT.ALIAS_NAME LIKE '%MEAN') OR (RESULT.ALIAS_NAME
> LIKE '%SD')) AND RESULT.UNITS=UNITS.UNIT_CODE AND
> RESULT.STATUS = 'A' AND SAMPLE.STATUS = 'A'ORDER BY
> CORRECTED_COL_DATE
> Thanks,
> Annette
>
Help on SQL syntax
Dear Anyone,
I am no expert with SQL server but I am trying obtain
some data from this server using SQL syntax, I would like
to average out the data by correction_col_date below, can
anyone help please!
SELECT SAMPLE.PRODUCT_VERSION, PRODUCT.DESCRIPTION,
SAMPLE.PRODUCT, SAMPLE.PRODUCT_GRADE,
SAMPLE.CORRECTED_COL_DATE, SAMPLE.COLLECTION_TIME,
SAMPLE.LOCATION, PRODUCT_SPEC.NOMINAL_VALUE,
PRODUCT_SPEC.MAX_VALUE, PRODUCT_SPEC.MIN_VALUE,
RESULT.IN_SPEC, SAMPLE.LOGIN_BY, UNITS.DISPLAY_STRING,
RESULT.NAME, RESULT.ALIAS_NAME, RESULT.REPORTED_NAME,
RESULT.ENTRY FROM PRODUCT_SPEC, SAMPLE, RESULT, UNITS,
PRODUCT WHERE SAMPLE.PRODUCT = <<PROD_BRWSER>> AND
SAMPLE.CORRECTED_COL_DATE BETWEEN <<DATERANGE>> AND
SAMPLE.LOCATION= <<LOCATION_BROWSE>> AND
PRODUCT_SPEC.PRODUCT = SAMPLE.PRODUCT AND PRODUCT.NAME = SAMPLE.PRODUCT AND PRODUCT_SPEC.VERSION = SAMPLE.PRODUCT_VERSION AND PRODUCT_SPEC.GRADE = SAMPLE.PRODUCT_GRADE AND
PRODUCT_SPEC.ANALYSIS = RESULT.ANALYSIS AND
PRODUCT_SPEC.COMPONENT = RESULT.NAME AND
RESULT.SAMPLE_NUMBER = SAMPLE.SAMPLE_NUMBER AND
((RESULT.ALIAS_NAME LIKE '%MEAN') OR (RESULT.ALIAS_NAME
LIKE '%SD')) AND RESULT.UNITS=UNITS.UNIT_CODE AND
RESULT.STATUS = 'A' AND SAMPLE.STATUS = 'A'ORDER BY
CORRECTED_COL_DATE
Thanks,
AnnetteYou can use AVG() aggregate function with GROUP BY clause, for example:
SELECT SAMPLE.CORRECTED_COL_DATE, AVG(SAMPLE.PRODUCT_GRADE)
FROM PRODUCT_SPEC, SAMPLE, RESULT, UNITS, PRODUCT
WHERE SAMPLE.PRODUCT = <<PROD_BRWSER>> AND
SAMPLE.CORRECTED_COL_DATE BETWEEN <<DATERANGE>> AND
SAMPLE.LOCATION= <<LOCATION_BROWSE>> AND
PRODUCT_SPEC.PRODUCT = SAMPLE.PRODUCT AND
PRODUCT.NAME = SAMPLE.PRODUCT AND
PRODUCT_SPEC.VERSION = SAMPLE.PRODUCT_VERSION AND
PRODUCT_SPEC.GRADE = SAMPLE.PRODUCT_GRADE AND
PRODUCT_SPEC.ANALYSIS = RESULT.ANALYSIS AND
PRODUCT_SPEC.COMPONENT = RESULT.NAME AND
RESULT.SAMPLE_NUMBER = SAMPLE.SAMPLE_NUMBER AND
((RESULT.ALIAS_NAME LIKE '%MEAN') OR (RESULT.ALIAS_NAME LIKE '%SD'))
AND
RESULT.UNITS=UNITS.UNIT_CODE AND
RESULT.STATUS = 'A' AND SAMPLE.STATUS = 'A'
GROUP BY SAMPLE.CORRECTED_COL_DATE
ORDER BY CORRECTED_COL_DATE
--
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Annette Malini" <anonymous@.discussions.microsoft.com> wrote in message
news:731d01c48423$63065e20$a501280a@.phx.gbl...
> Dear Anyone,
> I am no expert with SQL server but I am trying obtain
> some data from this server using SQL syntax, I would like
> to average out the data by correction_col_date below, can
> anyone help please!
> SELECT SAMPLE.PRODUCT_VERSION, PRODUCT.DESCRIPTION,
> SAMPLE.PRODUCT, SAMPLE.PRODUCT_GRADE,
> SAMPLE.CORRECTED_COL_DATE, SAMPLE.COLLECTION_TIME,
> SAMPLE.LOCATION, PRODUCT_SPEC.NOMINAL_VALUE,
> PRODUCT_SPEC.MAX_VALUE, PRODUCT_SPEC.MIN_VALUE,
> RESULT.IN_SPEC, SAMPLE.LOGIN_BY, UNITS.DISPLAY_STRING,
> RESULT.NAME, RESULT.ALIAS_NAME, RESULT.REPORTED_NAME,
> RESULT.ENTRY FROM PRODUCT_SPEC, SAMPLE, RESULT, UNITS,
> PRODUCT WHERE SAMPLE.PRODUCT = <<PROD_BRWSER>> AND
> SAMPLE.CORRECTED_COL_DATE BETWEEN <<DATERANGE>> AND
> SAMPLE.LOCATION= <<LOCATION_BROWSE>> AND
> PRODUCT_SPEC.PRODUCT = SAMPLE.PRODUCT AND PRODUCT.NAME => SAMPLE.PRODUCT AND PRODUCT_SPEC.VERSION => SAMPLE.PRODUCT_VERSION AND PRODUCT_SPEC.GRADE => SAMPLE.PRODUCT_GRADE AND
> PRODUCT_SPEC.ANALYSIS = RESULT.ANALYSIS AND
> PRODUCT_SPEC.COMPONENT = RESULT.NAME AND
> RESULT.SAMPLE_NUMBER = SAMPLE.SAMPLE_NUMBER AND
> ((RESULT.ALIAS_NAME LIKE '%MEAN') OR (RESULT.ALIAS_NAME
> LIKE '%SD')) AND RESULT.UNITS=UNITS.UNIT_CODE AND
> RESULT.STATUS = 'A' AND SAMPLE.STATUS = 'A'ORDER BY
> CORRECTED_COL_DATE
> Thanks,
> Annette
>
I am no expert with SQL server but I am trying obtain
some data from this server using SQL syntax, I would like
to average out the data by correction_col_date below, can
anyone help please!
SELECT SAMPLE.PRODUCT_VERSION, PRODUCT.DESCRIPTION,
SAMPLE.PRODUCT, SAMPLE.PRODUCT_GRADE,
SAMPLE.CORRECTED_COL_DATE, SAMPLE.COLLECTION_TIME,
SAMPLE.LOCATION, PRODUCT_SPEC.NOMINAL_VALUE,
PRODUCT_SPEC.MAX_VALUE, PRODUCT_SPEC.MIN_VALUE,
RESULT.IN_SPEC, SAMPLE.LOGIN_BY, UNITS.DISPLAY_STRING,
RESULT.NAME, RESULT.ALIAS_NAME, RESULT.REPORTED_NAME,
RESULT.ENTRY FROM PRODUCT_SPEC, SAMPLE, RESULT, UNITS,
PRODUCT WHERE SAMPLE.PRODUCT = <<PROD_BRWSER>> AND
SAMPLE.CORRECTED_COL_DATE BETWEEN <<DATERANGE>> AND
SAMPLE.LOCATION= <<LOCATION_BROWSE>> AND
PRODUCT_SPEC.PRODUCT = SAMPLE.PRODUCT AND PRODUCT.NAME = SAMPLE.PRODUCT AND PRODUCT_SPEC.VERSION = SAMPLE.PRODUCT_VERSION AND PRODUCT_SPEC.GRADE = SAMPLE.PRODUCT_GRADE AND
PRODUCT_SPEC.ANALYSIS = RESULT.ANALYSIS AND
PRODUCT_SPEC.COMPONENT = RESULT.NAME AND
RESULT.SAMPLE_NUMBER = SAMPLE.SAMPLE_NUMBER AND
((RESULT.ALIAS_NAME LIKE '%MEAN') OR (RESULT.ALIAS_NAME
LIKE '%SD')) AND RESULT.UNITS=UNITS.UNIT_CODE AND
RESULT.STATUS = 'A' AND SAMPLE.STATUS = 'A'ORDER BY
CORRECTED_COL_DATE
Thanks,
AnnetteYou can use AVG() aggregate function with GROUP BY clause, for example:
SELECT SAMPLE.CORRECTED_COL_DATE, AVG(SAMPLE.PRODUCT_GRADE)
FROM PRODUCT_SPEC, SAMPLE, RESULT, UNITS, PRODUCT
WHERE SAMPLE.PRODUCT = <<PROD_BRWSER>> AND
SAMPLE.CORRECTED_COL_DATE BETWEEN <<DATERANGE>> AND
SAMPLE.LOCATION= <<LOCATION_BROWSE>> AND
PRODUCT_SPEC.PRODUCT = SAMPLE.PRODUCT AND
PRODUCT.NAME = SAMPLE.PRODUCT AND
PRODUCT_SPEC.VERSION = SAMPLE.PRODUCT_VERSION AND
PRODUCT_SPEC.GRADE = SAMPLE.PRODUCT_GRADE AND
PRODUCT_SPEC.ANALYSIS = RESULT.ANALYSIS AND
PRODUCT_SPEC.COMPONENT = RESULT.NAME AND
RESULT.SAMPLE_NUMBER = SAMPLE.SAMPLE_NUMBER AND
((RESULT.ALIAS_NAME LIKE '%MEAN') OR (RESULT.ALIAS_NAME LIKE '%SD'))
AND
RESULT.UNITS=UNITS.UNIT_CODE AND
RESULT.STATUS = 'A' AND SAMPLE.STATUS = 'A'
GROUP BY SAMPLE.CORRECTED_COL_DATE
ORDER BY CORRECTED_COL_DATE
--
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Annette Malini" <anonymous@.discussions.microsoft.com> wrote in message
news:731d01c48423$63065e20$a501280a@.phx.gbl...
> Dear Anyone,
> I am no expert with SQL server but I am trying obtain
> some data from this server using SQL syntax, I would like
> to average out the data by correction_col_date below, can
> anyone help please!
> SELECT SAMPLE.PRODUCT_VERSION, PRODUCT.DESCRIPTION,
> SAMPLE.PRODUCT, SAMPLE.PRODUCT_GRADE,
> SAMPLE.CORRECTED_COL_DATE, SAMPLE.COLLECTION_TIME,
> SAMPLE.LOCATION, PRODUCT_SPEC.NOMINAL_VALUE,
> PRODUCT_SPEC.MAX_VALUE, PRODUCT_SPEC.MIN_VALUE,
> RESULT.IN_SPEC, SAMPLE.LOGIN_BY, UNITS.DISPLAY_STRING,
> RESULT.NAME, RESULT.ALIAS_NAME, RESULT.REPORTED_NAME,
> RESULT.ENTRY FROM PRODUCT_SPEC, SAMPLE, RESULT, UNITS,
> PRODUCT WHERE SAMPLE.PRODUCT = <<PROD_BRWSER>> AND
> SAMPLE.CORRECTED_COL_DATE BETWEEN <<DATERANGE>> AND
> SAMPLE.LOCATION= <<LOCATION_BROWSE>> AND
> PRODUCT_SPEC.PRODUCT = SAMPLE.PRODUCT AND PRODUCT.NAME => SAMPLE.PRODUCT AND PRODUCT_SPEC.VERSION => SAMPLE.PRODUCT_VERSION AND PRODUCT_SPEC.GRADE => SAMPLE.PRODUCT_GRADE AND
> PRODUCT_SPEC.ANALYSIS = RESULT.ANALYSIS AND
> PRODUCT_SPEC.COMPONENT = RESULT.NAME AND
> RESULT.SAMPLE_NUMBER = SAMPLE.SAMPLE_NUMBER AND
> ((RESULT.ALIAS_NAME LIKE '%MEAN') OR (RESULT.ALIAS_NAME
> LIKE '%SD')) AND RESULT.UNITS=UNITS.UNIT_CODE AND
> RESULT.STATUS = 'A' AND SAMPLE.STATUS = 'A'ORDER BY
> CORRECTED_COL_DATE
> Thanks,
> Annette
>
Help on SQL syntax
Dear Anyone,
I am no expert with SQL server but I am trying obtain
some data from this server using SQL syntax, I would like
to average out the data by correction_col_date below, can
anyone help please!
SELECT SAMPLE.PRODUCT_VERSION, PRODUCT.DESCRIPTION,
SAMPLE.PRODUCT, SAMPLE.PRODUCT_GRADE,
SAMPLE.CORRECTED_COL_DATE, SAMPLE.COLLECTION_TIME,
SAMPLE.LOCATION, PRODUCT_SPEC.NOMINAL_VALUE,
PRODUCT_SPEC.MAX_VALUE, PRODUCT_SPEC.MIN_VALUE,
RESULT.IN_SPEC, SAMPLE.LOGIN_BY, UNITS.DISPLAY_STRING,
RESULT.NAME, RESULT.ALIAS_NAME, RESULT.REPORTED_NAME,
RESULT.ENTRY FROM PRODUCT_SPEC, SAMPLE, RESULT, UNITS,
PRODUCT WHERE SAMPLE.PRODUCT = <<PROD_BRWSER>> AND
SAMPLE.CORRECTED_COL_DATE BETWEEN <<DATERANGE>> AND
SAMPLE.LOCATION= <<LOCATION_BROWSE>> AND
PRODUCT_SPEC.PRODUCT = SAMPLE.PRODUCT AND PRODUCT.NAME =
SAMPLE.PRODUCT AND PRODUCT_SPEC.VERSION =
SAMPLE.PRODUCT_VERSION AND PRODUCT_SPEC.GRADE =
SAMPLE.PRODUCT_GRADE AND
PRODUCT_SPEC.ANALYSIS = RESULT.ANALYSIS AND
PRODUCT_SPEC.COMPONENT = RESULT.NAME AND
RESULT.SAMPLE_NUMBER = SAMPLE.SAMPLE_NUMBER AND
((RESULT.ALIAS_NAME LIKE '%MEAN') OR (RESULT.ALIAS_NAME
LIKE '%SD')) AND RESULT.UNITS=UNITS.UNIT_CODE AND
RESULT.STATUS = 'A' AND SAMPLE.STATUS = 'A'ORDER BY
CORRECTED_COL_DATE
Thanks,
AnnetteYou can use AVG() aggregate function with GROUP BY clause, for example:
SELECT SAMPLE.CORRECTED_COL_DATE, AVG(SAMPLE.PRODUCT_GRADE)
FROM PRODUCT_SPEC, SAMPLE, RESULT, UNITS, PRODUCT
WHERE SAMPLE.PRODUCT = <<PROD_BRWSER>> AND
SAMPLE.CORRECTED_COL_DATE BETWEEN <<DATERANGE>> AND
SAMPLE.LOCATION= <<LOCATION_BROWSE>> AND
PRODUCT_SPEC.PRODUCT = SAMPLE.PRODUCT AND
PRODUCT.NAME = SAMPLE.PRODUCT AND
PRODUCT_SPEC.VERSION = SAMPLE.PRODUCT_VERSION AND
PRODUCT_SPEC.GRADE = SAMPLE.PRODUCT_GRADE AND
PRODUCT_SPEC.ANALYSIS = RESULT.ANALYSIS AND
PRODUCT_SPEC.COMPONENT = RESULT.NAME AND
RESULT.SAMPLE_NUMBER = SAMPLE.SAMPLE_NUMBER AND
((RESULT.ALIAS_NAME LIKE '%MEAN') OR (RESULT.ALIAS_NAME LIKE '%SD'))
AND
RESULT.UNITS=UNITS.UNIT_CODE AND
RESULT.STATUS = 'A' AND SAMPLE.STATUS = 'A'
GROUP BY SAMPLE.CORRECTED_COL_DATE
ORDER BY CORRECTED_COL_DATE
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Annette Malini" <anonymous@.discussions.microsoft.com> wrote in message
news:731d01c48423$63065e20$a501280a@.phx.gbl...
> Dear Anyone,
> I am no expert with SQL server but I am trying obtain
> some data from this server using SQL syntax, I would like
> to average out the data by correction_col_date below, can
> anyone help please!
> SELECT SAMPLE.PRODUCT_VERSION, PRODUCT.DESCRIPTION,
> SAMPLE.PRODUCT, SAMPLE.PRODUCT_GRADE,
> SAMPLE.CORRECTED_COL_DATE, SAMPLE.COLLECTION_TIME,
> SAMPLE.LOCATION, PRODUCT_SPEC.NOMINAL_VALUE,
> PRODUCT_SPEC.MAX_VALUE, PRODUCT_SPEC.MIN_VALUE,
> RESULT.IN_SPEC, SAMPLE.LOGIN_BY, UNITS.DISPLAY_STRING,
> RESULT.NAME, RESULT.ALIAS_NAME, RESULT.REPORTED_NAME,
> RESULT.ENTRY FROM PRODUCT_SPEC, SAMPLE, RESULT, UNITS,
> PRODUCT WHERE SAMPLE.PRODUCT = <<PROD_BRWSER>> AND
> SAMPLE.CORRECTED_COL_DATE BETWEEN <<DATERANGE>> AND
> SAMPLE.LOCATION= <<LOCATION_BROWSE>> AND
> PRODUCT_SPEC.PRODUCT = SAMPLE.PRODUCT AND PRODUCT.NAME =
> SAMPLE.PRODUCT AND PRODUCT_SPEC.VERSION =
> SAMPLE.PRODUCT_VERSION AND PRODUCT_SPEC.GRADE =
> SAMPLE.PRODUCT_GRADE AND
> PRODUCT_SPEC.ANALYSIS = RESULT.ANALYSIS AND
> PRODUCT_SPEC.COMPONENT = RESULT.NAME AND
> RESULT.SAMPLE_NUMBER = SAMPLE.SAMPLE_NUMBER AND
> ((RESULT.ALIAS_NAME LIKE '%MEAN') OR (RESULT.ALIAS_NAME
> LIKE '%SD')) AND RESULT.UNITS=UNITS.UNIT_CODE AND
> RESULT.STATUS = 'A' AND SAMPLE.STATUS = 'A'ORDER BY
> CORRECTED_COL_DATE
> Thanks,
> Annette
>
I am no expert with SQL server but I am trying obtain
some data from this server using SQL syntax, I would like
to average out the data by correction_col_date below, can
anyone help please!
SELECT SAMPLE.PRODUCT_VERSION, PRODUCT.DESCRIPTION,
SAMPLE.PRODUCT, SAMPLE.PRODUCT_GRADE,
SAMPLE.CORRECTED_COL_DATE, SAMPLE.COLLECTION_TIME,
SAMPLE.LOCATION, PRODUCT_SPEC.NOMINAL_VALUE,
PRODUCT_SPEC.MAX_VALUE, PRODUCT_SPEC.MIN_VALUE,
RESULT.IN_SPEC, SAMPLE.LOGIN_BY, UNITS.DISPLAY_STRING,
RESULT.NAME, RESULT.ALIAS_NAME, RESULT.REPORTED_NAME,
RESULT.ENTRY FROM PRODUCT_SPEC, SAMPLE, RESULT, UNITS,
PRODUCT WHERE SAMPLE.PRODUCT = <<PROD_BRWSER>> AND
SAMPLE.CORRECTED_COL_DATE BETWEEN <<DATERANGE>> AND
SAMPLE.LOCATION= <<LOCATION_BROWSE>> AND
PRODUCT_SPEC.PRODUCT = SAMPLE.PRODUCT AND PRODUCT.NAME =
SAMPLE.PRODUCT AND PRODUCT_SPEC.VERSION =
SAMPLE.PRODUCT_VERSION AND PRODUCT_SPEC.GRADE =
SAMPLE.PRODUCT_GRADE AND
PRODUCT_SPEC.ANALYSIS = RESULT.ANALYSIS AND
PRODUCT_SPEC.COMPONENT = RESULT.NAME AND
RESULT.SAMPLE_NUMBER = SAMPLE.SAMPLE_NUMBER AND
((RESULT.ALIAS_NAME LIKE '%MEAN') OR (RESULT.ALIAS_NAME
LIKE '%SD')) AND RESULT.UNITS=UNITS.UNIT_CODE AND
RESULT.STATUS = 'A' AND SAMPLE.STATUS = 'A'ORDER BY
CORRECTED_COL_DATE
Thanks,
AnnetteYou can use AVG() aggregate function with GROUP BY clause, for example:
SELECT SAMPLE.CORRECTED_COL_DATE, AVG(SAMPLE.PRODUCT_GRADE)
FROM PRODUCT_SPEC, SAMPLE, RESULT, UNITS, PRODUCT
WHERE SAMPLE.PRODUCT = <<PROD_BRWSER>> AND
SAMPLE.CORRECTED_COL_DATE BETWEEN <<DATERANGE>> AND
SAMPLE.LOCATION= <<LOCATION_BROWSE>> AND
PRODUCT_SPEC.PRODUCT = SAMPLE.PRODUCT AND
PRODUCT.NAME = SAMPLE.PRODUCT AND
PRODUCT_SPEC.VERSION = SAMPLE.PRODUCT_VERSION AND
PRODUCT_SPEC.GRADE = SAMPLE.PRODUCT_GRADE AND
PRODUCT_SPEC.ANALYSIS = RESULT.ANALYSIS AND
PRODUCT_SPEC.COMPONENT = RESULT.NAME AND
RESULT.SAMPLE_NUMBER = SAMPLE.SAMPLE_NUMBER AND
((RESULT.ALIAS_NAME LIKE '%MEAN') OR (RESULT.ALIAS_NAME LIKE '%SD'))
AND
RESULT.UNITS=UNITS.UNIT_CODE AND
RESULT.STATUS = 'A' AND SAMPLE.STATUS = 'A'
GROUP BY SAMPLE.CORRECTED_COL_DATE
ORDER BY CORRECTED_COL_DATE
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Annette Malini" <anonymous@.discussions.microsoft.com> wrote in message
news:731d01c48423$63065e20$a501280a@.phx.gbl...
> Dear Anyone,
> I am no expert with SQL server but I am trying obtain
> some data from this server using SQL syntax, I would like
> to average out the data by correction_col_date below, can
> anyone help please!
> SELECT SAMPLE.PRODUCT_VERSION, PRODUCT.DESCRIPTION,
> SAMPLE.PRODUCT, SAMPLE.PRODUCT_GRADE,
> SAMPLE.CORRECTED_COL_DATE, SAMPLE.COLLECTION_TIME,
> SAMPLE.LOCATION, PRODUCT_SPEC.NOMINAL_VALUE,
> PRODUCT_SPEC.MAX_VALUE, PRODUCT_SPEC.MIN_VALUE,
> RESULT.IN_SPEC, SAMPLE.LOGIN_BY, UNITS.DISPLAY_STRING,
> RESULT.NAME, RESULT.ALIAS_NAME, RESULT.REPORTED_NAME,
> RESULT.ENTRY FROM PRODUCT_SPEC, SAMPLE, RESULT, UNITS,
> PRODUCT WHERE SAMPLE.PRODUCT = <<PROD_BRWSER>> AND
> SAMPLE.CORRECTED_COL_DATE BETWEEN <<DATERANGE>> AND
> SAMPLE.LOCATION= <<LOCATION_BROWSE>> AND
> PRODUCT_SPEC.PRODUCT = SAMPLE.PRODUCT AND PRODUCT.NAME =
> SAMPLE.PRODUCT AND PRODUCT_SPEC.VERSION =
> SAMPLE.PRODUCT_VERSION AND PRODUCT_SPEC.GRADE =
> SAMPLE.PRODUCT_GRADE AND
> PRODUCT_SPEC.ANALYSIS = RESULT.ANALYSIS AND
> PRODUCT_SPEC.COMPONENT = RESULT.NAME AND
> RESULT.SAMPLE_NUMBER = SAMPLE.SAMPLE_NUMBER AND
> ((RESULT.ALIAS_NAME LIKE '%MEAN') OR (RESULT.ALIAS_NAME
> LIKE '%SD')) AND RESULT.UNITS=UNITS.UNIT_CODE AND
> RESULT.STATUS = 'A' AND SAMPLE.STATUS = 'A'ORDER BY
> CORRECTED_COL_DATE
> Thanks,
> Annette
>
Help on SQL Syantax
Dear Anyone,
I am no expert with SQL server but I am trying obtain
some data from this server using SQL syntax, I would like
to average out the data by correction_col_date below, can
anyone help please!
SELECT SAMPLE.PRODUCT_VERSION, PRODUCT.DESCRIPTION,
SAMPLE.PRODUCT, SAMPLE.PRODUCT_GRADE,
SAMPLE.CORRECTED_COL_DATE, SAMPLE.COLLECTION_TIME,
SAMPLE.LOCATION, PRODUCT_SPEC.NOMINAL_VALUE,
PRODUCT_SPEC.MAX_VALUE, PRODUCT_SPEC.MIN_VALUE,
RESULT.IN_SPEC, SAMPLE.LOGIN_BY, UNITS.DISPLAY_STRING,
RESULT.NAME, RESULT.ALIAS_NAME, RESULT.REPORTED_NAME,
RESULT.ENTRY FROM PRODUCT_SPEC, SAMPLE, RESULT, UNITS,
PRODUCT WHERE SAMPLE.PRODUCT = <<PROD_BRWSER>> AND
SAMPLE.CORRECTED_COL_DATE BETWEEN <<DATERANGE>> AND
SAMPLE.LOCATION= <<LOCATION_BROWSE>> AND
PRODUCT_SPEC.PRODUCT = SAMPLE.PRODUCT AND PRODUCT.NAME =
SAMPLE.PRODUCT AND PRODUCT_SPEC.VERSION =
SAMPLE.PRODUCT_VERSION AND PRODUCT_SPEC.GRADE =
SAMPLE.PRODUCT_GRADE AND
PRODUCT_SPEC.ANALYSIS = RESULT.ANALYSIS AND
PRODUCT_SPEC.COMPONENT = RESULT.NAME AND
RESULT.SAMPLE_NUMBER = SAMPLE.SAMPLE_NUMBER AND
((RESULT.ALIAS_NAME LIKE '%MEAN') OR (RESULT.ALIAS_NAME
LIKE '%SD')) AND RESULT.UNITS=UNITS.UNIT_CODE AND
RESULT.STATUS = 'A' AND SAMPLE.STATUS = 'A'ORDER BY
CORRECTED_COL_DATE
Thanks,
Annette
post relevent table structures, sample data and expected result set.
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com
I am no expert with SQL server but I am trying obtain
some data from this server using SQL syntax, I would like
to average out the data by correction_col_date below, can
anyone help please!
SELECT SAMPLE.PRODUCT_VERSION, PRODUCT.DESCRIPTION,
SAMPLE.PRODUCT, SAMPLE.PRODUCT_GRADE,
SAMPLE.CORRECTED_COL_DATE, SAMPLE.COLLECTION_TIME,
SAMPLE.LOCATION, PRODUCT_SPEC.NOMINAL_VALUE,
PRODUCT_SPEC.MAX_VALUE, PRODUCT_SPEC.MIN_VALUE,
RESULT.IN_SPEC, SAMPLE.LOGIN_BY, UNITS.DISPLAY_STRING,
RESULT.NAME, RESULT.ALIAS_NAME, RESULT.REPORTED_NAME,
RESULT.ENTRY FROM PRODUCT_SPEC, SAMPLE, RESULT, UNITS,
PRODUCT WHERE SAMPLE.PRODUCT = <<PROD_BRWSER>> AND
SAMPLE.CORRECTED_COL_DATE BETWEEN <<DATERANGE>> AND
SAMPLE.LOCATION= <<LOCATION_BROWSE>> AND
PRODUCT_SPEC.PRODUCT = SAMPLE.PRODUCT AND PRODUCT.NAME =
SAMPLE.PRODUCT AND PRODUCT_SPEC.VERSION =
SAMPLE.PRODUCT_VERSION AND PRODUCT_SPEC.GRADE =
SAMPLE.PRODUCT_GRADE AND
PRODUCT_SPEC.ANALYSIS = RESULT.ANALYSIS AND
PRODUCT_SPEC.COMPONENT = RESULT.NAME AND
RESULT.SAMPLE_NUMBER = SAMPLE.SAMPLE_NUMBER AND
((RESULT.ALIAS_NAME LIKE '%MEAN') OR (RESULT.ALIAS_NAME
LIKE '%SD')) AND RESULT.UNITS=UNITS.UNIT_CODE AND
RESULT.STATUS = 'A' AND SAMPLE.STATUS = 'A'ORDER BY
CORRECTED_COL_DATE
Thanks,
Annette
post relevent table structures, sample data and expected result set.
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com
Wednesday, March 21, 2012
Help on Calculated Members
Hi Gurus,
I am new to Microsoft Analysis Services. I wanted to create a new Average Calculation.
The Syntax is
Avg(?Set[, ?Numeric Expression]). I am using the Foodmart cube.
I have tried using many things but have not achieved any success.
These are the following things that I have tried.
- Avg([Product].[Product Family].&[Drink].&[Dairy].&[Dairy].&[Milk] , 'Avergare of Milk')
In this 'Avergare of Milk' is some sort of Calculation name I suppose.
- Avg([Product Family].&[Drink].&[Dairy].&[Dairy].&[Milk]
)
In this ‘6’ I have mentioned assuming there are only 6 members in the hierarchy.
Please tell me What shall I write in place of Set and Numeric Expression.
I also came across an article and found this
“If you want to create an average, what you need to do is create one
measure
called SUM, and another measure based on COUNT and then create a
calculated
member called AVG which would just be SUM/COUNT. I believe there are
examples of this in the Foodmart 2000 cubes.”
But to my Surprise I also found the Syntax of SUM and Count to be similar.
Please help me out in this.
I also have a request Could anyone give me some examples and any link from where I could learn all the available calculation with examples.
Thanks in advance
Here is an example that should help you:
with member [Measures].[Av1] as
'Avg({descendants([Product].[All Products].[Drink],[Product Name])}, [Measures].[Profit])'
member [Measures].[Av2] as 'Avg({descendants([Product].[All Products].[Drink],[Product Name])}, coalesceempty([Measures].[Profit],0))'
member [Measures].[Num] as 'count({descendants([Product].[All Products].[Drink],[Product Name])})'
member [Measures].[Sum] as 'sum({descendants([Product].[All Products].[Drink],[Product Name])}, [Measures].[Profit])'
member [Measures].[Av3] as '[Measures].[Sum]/[Measures].[Num]'
select
{[Measures].[Av1],[Measures].[Av2],[Measures].[Av3],[Measures].[Num],[Measures].[Sum]} on columns,
{[Store].[Store Name].members} on rows
from sales
As you can see, you Avg1 is different from Av2 because it doesn't consider empty cells, so the average is slightly bigger.
Hope this help,
Santiago
|||Hi,
Thanks for the help. But I think I was not clear what I wanted. I am using Analysis manager (Microsoft SQL Server -> Analysis Services -> Analysis Manger).
In that I am drilling down the Analysis Servers to list the Available servers and then drilling down the database present to get the list of the available cubes.Now I am right clicking on a cube and choosing edit to open the Cube Editor.In Cube editor I am trying to create a new Calculated Members. (In the Calculated member Builder I am unable to write the query for the Average.) There I am not able to make the calculation. And am unable to sort out the problem.
I hope this information help in giving my problem the perfect picture.
Please let me know if u need anything else
Thanks in advance
|||
In Calculated Member builder, select Measures as the Parent Dimension, write the name of the measure (eg. MyAverage) and type the appropriate mdx expression. For example:
Avg({descendants([Product].[All Products].[Drink],[Product Name])}, [Measures].[Profit])
You don′t need then to reprocess the cube. Just browse it and you should be able to see the new Calculated Member.
|||
HI,
Many many thanks for the help. It worked.
Can I get some link from where I can get some examples of the calculation.
Thanks
Sunday, February 19, 2012
Help me Insert/Update!
Can someone please Help,
I'm trying to write an insert/update query but it doesn't work.
My syntax is similar to the following
INSERT/UPDATE INTO GL_SCORE VALUES (1, 'andrew');
INSERT INTO GL_SCORE VALUES (2, 'gordon');
SELECT * FROM GL_SCORE
But it doesn't work, why?
Please help me insert/update becuase it would be good.RTFM
Look up the syntax for UPDATE & INSERT, I'm sure you can answer your question by yourself.|||Hi,
I found syntax on insert and update but nothing on insert/update, like doing them both at once. Why?|||Why do you think, that SQL Server has a Update/Insert command, like Oracle 9i has it's MERGE? Which SQL Server version you are talking about?|||Dear Tronboy
I have never encountered Insert/Update in SQL Server syntax
I think you will have to choose, either its INSERT or UPDATE
If its a type of functionality or logic which is often used you should consider putting it in a user defined function or sproc.|||Tronboy,
Insert and Update are two different command in SQL Server (although updates actually involve deleting and reinserting rows, this all happens behind the scenes).
You may be getting confused because triggers can be set to execute and update.
blindman|||Originally posted by blindman
You may be getting confused because triggers can be set to execute and update.
blindman
Good point, blindman; this is the way to confuse TronBoy completely. I'm proud of you!
I'm trying to write an insert/update query but it doesn't work.
My syntax is similar to the following
INSERT/UPDATE INTO GL_SCORE VALUES (1, 'andrew');
INSERT INTO GL_SCORE VALUES (2, 'gordon');
SELECT * FROM GL_SCORE
But it doesn't work, why?
Please help me insert/update becuase it would be good.RTFM
Look up the syntax for UPDATE & INSERT, I'm sure you can answer your question by yourself.|||Hi,
I found syntax on insert and update but nothing on insert/update, like doing them both at once. Why?|||Why do you think, that SQL Server has a Update/Insert command, like Oracle 9i has it's MERGE? Which SQL Server version you are talking about?|||Dear Tronboy
I have never encountered Insert/Update in SQL Server syntax
I think you will have to choose, either its INSERT or UPDATE
If its a type of functionality or logic which is often used you should consider putting it in a user defined function or sproc.|||Tronboy,
Insert and Update are two different command in SQL Server (although updates actually involve deleting and reinserting rows, this all happens behind the scenes).
You may be getting confused because triggers can be set to execute and update.
blindman|||Originally posted by blindman
You may be getting confused because triggers can be set to execute and update.
blindman
Good point, blindman; this is the way to confuse TronBoy completely. I'm proud of you!
Subscribe to:
Posts (Atom)