Newbie Question

How do I get the unique fields from a select query without iterating. Is
this possible? My current statement is below. Thanks in advance.
"SELECT DealNumber, [User] FROM DealingFinal WHERE ([User] = 'dbo')"Hi
SELECT DISTINCT DealNumber, [User] FROM DealingFinal
"xfd" <xfd@.xfd.com> wrote in message
news:u9rL%23udkFHA.3756@.TK2MSFTNGP15.phx.gbl...
> How do I get the unique fields from a select query without iterating. Is
> this possible? My current statement is below. Thanks in advance.
> "SELECT DealNumber, [User] FROM DealingFinal WHERE ([User] = 'dbo')"
>

Newbie Q: Executing StoredProc?

hi all,
i just wonder if below storedproc will update record correctly if executed
concurrently by multiple user using
varying parameter value or it containe logic error.
CREATE PROCEDURE UpdateQTY @.QTY int
AS
UPDATE PRODUCT SET Quantity = Quantity + @.QTY
GO
Hi,
The Syntax of the Storeprocedure (SP) shows that it would update all the
records in the Product table.
Is this what you want to achieve ?
If not, then add a where clause in the Update statement, where it would
contain one or more columns, that would together select a distinct row.
for example
CREATE PROCEDURE UpdateQTY @.QTY int,@.ProductID int
AS
UPDATE PRODUCT SET Quantity = Quantity + @.QTY
where productid = @.productid
GO
The above example would find a row with the matching Productid and update
the Qty field for it.
HTH
Ashish
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Hi,
I missed one point in your question.
There will be no problems if multiple users call the SP simultaneously to
update the Product Table.
One more thing, is your QTY as integer or a numeric column. If it requires
to store decimal, then setting the parameter as int would make it to loose
its accuracy. So just make sure that the datatype of QTY matches that in
the table definition.
HTH
Ashish
This posting is provided "AS IS" with no warranties, and confers no rights.
|||thanks for the response

Newbie needs help with simple query :-)

Hi All,
I have been trying to learn and use SQL, and have learned quite a bit
(relatively speaking!!). Still, cannot get the below query to work...
if anyone could help out, I would be very grateful (and have learn't
something new!!!) =)
SELECT T0.ItemCode, T0.ItemName,T1.ItmsGrpNam, SUM(T3.OnHand),
SUM(T3.IsCommited), SUM(T3.OnOrder), (SUM(T3.OnHand) + SUM(T3.OnOrder)
- SUM(T3.IsCommited)) AS 'Available', T2.Currency, T2.Price
FROM OITM T0 INNER JOIN OITB T1 ON T0.ItmsGrpCod = T1.ItmsGrpCod INNER
JOIN ITM1 T2 ON T0.ItemCode = T2.ItemCode INNER JOIN OITW T3 ON
T0.ItemCode = T3.ItemCode
WHERE T0.PrchseItem = 'Y' AND T2.PriceList = '[%1]' AND T1.ItmsGrpNam
= '[%2]'
The summing bits are because in OITW there are around 30 warehouses, so
need the "total" on hand etc. Can get it to work with just OITW, but
need the other info in the same query as well :-(
Thanks,
RajivYou'll need to append a GROUP BY clause to the end of the query and any
specify any columns not aggregated in the SELECT list.
HTH
Jerry
"abd08" <dejaonly@.hotmail.com> wrote in message
news:1130260327.349774.100720@.o13g2000cwo.googlegroups.com...
> Hi All,
> I have been trying to learn and use SQL, and have learned quite a bit
> (relatively speaking!!). Still, cannot get the below query to work...
> if anyone could help out, I would be very grateful (and have learn't
> something new!!!) =)
> SELECT T0.ItemCode, T0.ItemName,T1.ItmsGrpNam, SUM(T3.OnHand),
> SUM(T3.IsCommited), SUM(T3.OnOrder), (SUM(T3.OnHand) + SUM(T3.OnOrder)
> - SUM(T3.IsCommited)) AS 'Available', T2.Currency, T2.Price
> FROM OITM T0 INNER JOIN OITB T1 ON T0.ItmsGrpCod = T1.ItmsGrpCod INNER
> JOIN ITM1 T2 ON T0.ItemCode = T2.ItemCode INNER JOIN OITW T3 ON
> T0.ItemCode = T3.ItemCode
> WHERE T0.PrchseItem = 'Y' AND T2.PriceList = '[%1]' AND T1.ItmsGrpNam
> = '[%2]'
> The summing bits are because in OITW there are around 30 warehouses, so
> need the "total" on hand etc. Can get it to work with just OITW, but
> need the other info in the same query as well :-(
> Thanks,
> Rajiv
>|||"abd08" <dejaonly@.hotmail.com> wrote in message
news:1130260327.349774.100720@.o13g2000cwo.googlegroups.com...
> Hi All,
> I have been trying to learn and use SQL, and have learned quite a bit
> (relatively speaking!!). Still, cannot get the below query to work...
> if anyone could help out, I would be very grateful (and have learn't
> something new!!!) =)
> SELECT T0.ItemCode, T0.ItemName,T1.ItmsGrpNam, SUM(T3.OnHand),
> SUM(T3.IsCommited), SUM(T3.OnOrder), (SUM(T3.OnHand) + SUM(T3.OnOrder)
> - SUM(T3.IsCommited)) AS 'Available', T2.Currency, T2.Price
> FROM OITM T0 INNER JOIN OITB T1 ON T0.ItmsGrpCod = T1.ItmsGrpCod INNER
> JOIN ITM1 T2 ON T0.ItemCode = T2.ItemCode INNER JOIN OITW T3 ON
> T0.ItemCode = T3.ItemCode
> WHERE T0.PrchseItem = 'Y' AND T2.PriceList = '[%1]' AND T1.ItmsGrpNam
> = '[%2]'
> The summing bits are because in OITW there are around 30 warehouses, so
> need the "total" on hand etc. Can get it to work with just OITW, but
> need the other info in the same query as well :-(
> Thanks,
> Rajiv
>
Rajiv,
All non-aggregate columns in the SELECT list (i.e. all columns that don't
have a SUM tied to them) must be included in a GROUP BY clause.
Try the following... NOTE: untested
SELECT T0.ItemCode,
T0.ItemName,
T1.ItmsGrpNam,
SUM(T3.OnHand),
SUM(T3.IsCommited),
SUM(T3.OnOrder),
(SUM(T3.OnHand) + SUM(T3.OnOrder) - SUM(T3.IsCommited))
AS 'Available',
T2.Currency,
T2.Price
FROM OITM T0
JOIN OITB T1 ON T0.ItmsGrpCod = T1.ItmsGrpCod
JOIN ITM1 T2 ON T0.ItemCode = T2.ItemCode
JOIN OITW T3 ON T0.ItemCode = T3.ItemCode
WHERE T0.PrchseItem = 'Y'
AND T2.PriceList = '[%1]'
AND T1.ItmsGrpNam = '[%2]'
GROUP BY
T0.ItemCode,
T0.ItemName,
T1.ItmsGrpNam,
T2.Currency,
T2.Price
Rick Sawtell
MCT, MCSD, MCDBA|||Wow... that seemed to work. Hmm... so have to group all non "aggregated
columns". Ok, something new to play with!!!
THANK YOU GUYS!!!! =)
Rajiv

Newbie Need help with tsql query

I am tring to use this tsql query in a sql 2005 report it keeps giving me the error below. This query works in my ms sql 2000 ok but I keep getting this error in the 2005. Can some one look at this and tell me what I am doing wrong. Thank you.

There is an error in the query. Incorrect syntax near '('.

SELECT
DISTINCT
JOB.JOBID,
JOB.*,
PAYER.PAY_COMPANY,
PATIENT.SSN,
PATIENT.LASTNAME,
PATIENT.FIRSTNAME,
PATIENT.MIDDLENAME,
PATIENT.AM_PHONE,
PATIENT.EMAIL,
JOB_OUTCOME.DESCRIPTION AS 'JOB_OUTCOME_DESC'

FROM
(((((JOB LEFT JOIN PATIENT ON JOB.PATIENTID = PATIENT.PATIENTID)
( LEFT JOIN PAYER ON JOB.PAYERID = PAYER.PAYERID)
( LEFT JOIN REFERRAL_SOURCE AS ADJUSTER ON JOB.ADJUSTER.REFERRAL_ID)
( LEFT JOIN REFERRAL_SOURCE AS CM ON JOB.CASEMANAGERID = CM.REFERRAL_ID)
( LEFT JOIN JOB_OUTCOME ON JOB.JOBOUTCOMEID = JOB_OUTCOME_ID)

WHERE
(JOB.APPT_DATE BETWEEN @.STARTDATE AND @.ENDDATE)

AND (JOB.TRANSPORTATION = '1')

AND (AREACOORDINATOR = '138')

ORDER BY
JOB.APPT_DATE DESC;

In this line

FROM
(((((JOB LEFT JOIN PATIENT ON JOB.PATIENTID = PATIENT.PATIENTID)

there are five (5) opening parentheses, but ONLY one of them is closed.

This could NOT have worked in SQL 2000 either.

Please post the ENTIRE query so that we may better assist you.

|||

Sorry I pasted the wrong one The 5 ( are becuase the opens before the left look at it again. This works just fine in sql 2000 in a cold fusion application I am using this in sql 2005 with a asp.net application.

There is an error in the query. An expression of non-boolean type specified in a context where a condition is expected, near ')'.

SELECT
DISTINCT
JOB.JOBID,
JOB.*,
PAYER.PAY_COMPANY,
PATIENT.SSN,
PATIENT.LASTNAME,
PATIENT.FIRSTNAME,
PATIENT.MIDDLENAME,
PATIENT.AM_PHONE,
PATIENT.EMAIL,
JOB_OUTCOME.DESCRIPTION AS 'JOB_OUTCOME_DESC'

FROM
(((((JOB LEFT JOIN PATIENT ON JOB.PATIENTID = PATIENT.PATIENTID)
LEFT JOIN PAYER ON JOB.PAYERID = PAYER.PAYERID)
LEFT JOIN REFERRAL_SOURCE AS ADJUSTER ON JOB.ADJUSTER.REFERRAL_ID)
LEFT JOIN REFERRAL_SOURCE AS CM ON JOB.CASEMANAGERID = CM.REFERRAL_ID)
LEFT JOIN JOB_OUTCOME ON JOB.JOBOUTCOMEID = JOB_OUTCOME_ID)

WHERE
((JOB.APPT_DATE >= '02/19/2007' AND ( JOB.APPT_DATE <= '02/19/2007'))

AND (JOB.TRANSPORTATION = '1')

AND (AREACOORDINATOR = '138')

ORDER BY
JOB.APPT_DATE DESC;

|||

Your parentheses are still not correct. This is missing a closing parenthesis.

((JOB.APPT_DATE >= '02/19/2007' AND ( JOB.APPT_DATE <= '02/19/2007'))

AND (JOB.TRANSPORTATION = '1')

AND (AREACOORDINATOR = '138')

|||The problem (once you get your parenthesis straighten out is here:

(((((JOB LEFT JOIN PATIENT ON JOB.PATIENTID = PATIENT.PATIENTID)
LEFT JOIN PAYER ON JOB.PAYERID = PAYER.PAYERID)
LEFT JOIN REFERRAL_SOURCE AS ADJUSTER ON JOB.ADJUSTER.REFERRAL_ID)
LEFT JOIN REFERRAL_SOURCE AS CM ON JOB.CASEMANAGERID = CM.REFERRAL_ID)
LEFT JOIN JOB_OUTCOME ON JOB.JOBOUTCOMEID = JOB_OUTCOME_ID)

You need to have JOB.ADJUSTER.REFERRAL_ID = REFERRAL_SOURCE..

Or something along these lines.

I would just get rid of most of these parentheses. None of them are actually needed in your query in the JOIN or WHERE clause.
|||

Thanks Louis.

That was to be my next suggestion.

But first, I wanted to help the OP understand that when he/she posts

This query works in my ms sql 2000

, or,

This works just fine in sql 2000 in a cold fusion application

and the query can't possible execute, he/she needs to be a bit more 'critical' when looking over the code and finding the obvious mistakes.

I've been feeling a bit pedantic today...

|||I know the feeling

Newbie mystery

Try running the code below in QA

USE Northwind
--The quesy below produces the correct numbers.
SELECT CategoryID,(100*((COUNT(*)+.0)/(SELECT COUNT(*) AS TotalCount FROM Products))) AS PERCENT_CAT FROM Products GROUP BY CategoryID

--The query below produces 0 values and are wrong.
SELECT CategoryID,(100*((COUNT(*))/(SELECT COUNT(*) AS TotalCount FROM Products))) AS PERCENT_CAT FROM Products GROUP BY CategoryID

--This is the total
SELECT COUNT(*) AS TotalCount FROM Products
--The totals of the groupings
SELECT CategoryID,(COUNT(*)) AS Category_Total FROM Products GROUP BY CategoryID

I think I understand what happens with the above, but what I really want to know is there a good coding habit to prevent it. Some of our reports are very complex and an error could be missed.Dear,

In MSSQL, this is normal behaviour. When you divide an integer (count) by another integer (count), the result is an integer.

Example :

select 1/3 returns 0

select 1/cast(3 as numeric) returns .33333

That is just the way it is, and it is documented in BOL.

Regards,

CVM.

newbie - defining dimensions.

I'm new to datawarehouse and needed some assistance in designing the cubes. Below is the scenario.

I have two tables.

tblbook with columns (bookid, author)

tblauthor with columns (authorid, authorname)

Now, my report should include the no. of books authored by an author and also include details of co-authors.

i.e. tblbook has the following values

1 mike

1 joe

2 mike.

3 joe

4 richard

my result should display

mike 2, joe 1

joe 2

richard 1

I would really appreciate any help.

You may have over simplified your example a little bit. In order to properly model this sort of situation I would have thought you would have needed at least 3 tables.

tblBook(bookid, book_name, publishing_date, ...)

tblAuthor(authorid,author_name, ...)

tblBookAuthors(bookid, authorid)

Basically it sounds like you need to set up a many to many relationship (one book can have many authors and one author can have many books)

There is an excellent whitepaper on some of the uses of many to many relationships here: http://www.sqlbi.eu/Home/tabid/36/ctl/Details/mid/374/ItemID/7/Default.aspx

And you should be able to find information in Books Online.

|||

Thanks Darren, i will dig more into the article and update you for any issues.