to create subrows - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

to create subrows

Author

Topic

mkgmahi
Starting Member

24 Posts

Posted - 2011-05-14 : 03:08:43

i am new to sql and i need to query a table and based on the condition, i need to display the output in two columns and also give me a sample for creating subrows

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-05-14 : 04:57:13

That was rather vague. You need to provide more information and elaborate on that.

please take a look at this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

KH
[spoiler]Time is always against us[/spoiler]

mkgmahi
Starting Member

24 Posts

Posted - 2011-05-15 : 01:50:40

i have to retrive company,qty from tabel x and company,casecnt from table y. Qty/casecnt will be the column total qty.If there is a quotient i need to create a column Unit which will display 'carton' against column total qty and if there is a remainder i need to display as 'each' in column Unit against the column total qty.
Therfore thare are two rows against the same record company .Could any one suggest me how should i do this.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-05-15 : 02:26:43

you will get faster response if you can refer to the link that i have provided and post your question and information accordingly.


select x.company, x.qty, y.casecnt, x.qty / y.casecnt as Unit, x.qty % y.casecnt as Each
from   table_x x
       inner join table_y y on x.company = y.company

KH
[spoiler]Time is always against us[/spoiler]

mkgmahi
Starting Member

24 Posts

Posted - 2011-05-15 : 03:02:35

Ok.
I tried the query but for this x.qty / y.casecnt it throws an error that datatype decimal and float are incompatible in the modulo operator

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-15 : 06:18:14

If you follow that link in KH's post about how to post the table DDL, you would get much better responses. In this case you are getting the error because either x.qty or y.casecnt is of data type float or decimal.

One way to hide the error is to replace x.qty % y.casecnt with

cast(x.qty as int)%cast(y.casecnt as int)

But you are really HIDING the error when you do that. I am saying that because normally one would expect QUANTITY and CASE_COUNT to be integers. But in your case, they are not. So there is more to it than what can be inferred from the information you have given.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources