|
I do not understand what you want. However keep in mind that if you concatenate a null value the result will be null.
'John' + NULL + 'Doe' = NULL
Try using the ISNULL function.
Also you cannot mix types
'John' + 1.0
will give an error. You need to make all compatible types.
Good Luck
djj
|
|
|
|
|
Thanks so much for the reply.
1.all the data types are verified to be strings(text)
2. i want to concatenate the strings by grouping them on a fieldid.
when i pick an id i want to concat all values corresponding to the id.
(this is the structure.
i have table A which contains list of towns.
table B contains list of regions.
table C contains list of attributes of the towns
table D contains the values of the attributes
the task is to provide a summary for towns under a region. grouped
under the various attributes.
for Region A i have Attribute A: then the concatenated values for
all attributes A for each town.
thanks once again.
|
|
|
|
|
Do you really want to concatenate columns or just create a table?
-- Table
SELECT A.Town, B.Region, C.Attribute AS AttribCode, D.Attribute
FROM TableA A
INNER JOIN TableB B
ON A.ID = B.ID
INNER JOIN TableC C ........
--or
-- Concatenate
SELECT A.Town + ISNULL(B.Region, '') + ISNULL(C.Attribute, '') + ISNULL(D.Attribute, '') AS TownInfo
FROM TableA A
INNER JOIN TableB B
ON A.ID = B.ID
INNER JOIN TableC C ........
If you have a one to many relationship and want the say multiple attributes for a given town you will need to Google that as it has been covered in various places.
I hope this helps,
djj
|
|
|
|
|
Hi! Is there an ORM that provides streaming support? What I mean by streaming is that the users can see the rows/objects being loaded one-by-one/partially (Think of SQL Management Studio when the query result is thousands of record). I can't implement paging since our data changes very fast.
Background:
- DB contains >250 million of data (but I will limit the result set to 10K max)
- Query can take minutes even hours
- Can't change DB architecture
Thanks
|
|
|
|
|
pebrian27 wrote: but I will limit the result set to 10K max
Why, how useful is 10k records in a UI, why not limit to 100 or 50 or something the user can work with.
pebrian27 wrote: Query can take minutes even hours
This does not make sense when servicing a UI. A select statement that takes hours, ridiculous, your design is screwed.
If the query takes hours the user has got bored and gone home. Change you design to data dump, output the data via a job/SSIS package and make it available in a shared folder. We extended SSRS to do just this sort of work, anything more than 1k rows is a lousy design for a UI and a report.
[edit] Checked your profile, web developer, so use an FTP/download scenario[/edit]Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Our users are also technical people and they need ~10k records.
Also, the users get to create the queries (ad-hoc queries). My current ORM is Nhibernate so they use HQL which is very similar to SQL.
Mycroft Holmes wrote: pebrian27 wrote:
Query can take minutes even hours
This does not make sense when servicing a UI. A select statement that takes hours, ridiculous, your design is screwed.
Oops... Queries will only take hours if I don't do the 10k limit. Currently, querying 500 records takes 2 mins. Thats why I want streaming support so that if they get bored, they can press cancel but still get partial result.
Our systems will be upgraded after 8 months.
|
|
|
|
|
Nhibernate isn't the cause of the slow query and there are no joins in the queries
|
|
|
|
|
500 records in 2 mins with no joins is very very slow. It would appear that either your server is useless or the query optimizer is unable to use indexes and is doing a table scan of all 250million records. Have you looked at the query execution plan to see if there are indexes that you can add?
|
|
|
|
|
This is probably easy...I am just brain dead...I am using Ms Sql 2008, and given the following two tables:
Table1
---------------
Field1 Field2
3 55
3 55
4 55
5 55
3 66
3 66
3 77
4 77
3 88
Table2
---------------
Field1 Field2
3 9
4 10
5 11
Table1 and Table2 are linked together by the Field1's...I get a parameter passed in like "9" for Table2's Field2...how do I return all of the records from Table1 of just the 66's and the one record of 88?
|
|
|
|
|
SELECT Field2 FROM TABLE1<br />
WHERE Field1 = (SELECT Field1 FROM Table2 WHERE Field2 = 9)<br />
AND Field2 IN(66,88)
I know the language. I've read a book. - _Madmatt
|
|
|
|
|
Thank you, but I don't want to hard code the 66 and 88 as those values I constantly changing. I just wanted those records as they don't have other linking fields of the 4 and 5 for example.
|
|
|
|
|
Of course you don't want them hard coded, this was just an example. 
I know the language. I've read a book. - _Madmatt
|
|
|
|
|
Start with:
select t1.*
from t1 inner join t2 on t1.field1 = t2.field1
where t2.field2 = @passedvalue
But, why is the (3,55) pair excluded?
Tim
|
|
|
|
|
55 is excluded because it has a link of 4 and 5...and 77 is excluded because it has a link of 4 in it.
Table1
---------------
Field1 -Field2
3 -55
3 -55
4 -55
5 -55
3 -66
3 -66
3 -77
4 -77
3 -88
Table2
---------------
Field1 -Field2
3 -9
4 -10
5 -11
|
|
|
|
|
Mike654321 wrote: 55 is excluded because it has a link of 4 and 5...and 77 is excluded because it has a link of 4 in it.
That was useful info.
Is it something like this you need?
SELECT Table1.field2
FROM Table1 join Table2
ON Table1.field1 = Table2.field1
WHERE Table2.field2 = 9
AND Table1.field2 NOT IN
(
SELECT DISTINCT Table1.field2
FROM Table1 join Table2
ON Table1.field1 = Table2.field1
WHERE Table2.field2 <> 9
)
;
This query might be very slow if the tables are big.
which can be cured with composite indexes on both tables.My postings are a natural product. The slight variations in spelling and grammar enhance their individual character and beauty and are in no way to be considered flaws or defects.
|
|
|
|
|
Hy,
Supose we have this:
SELECT SUM(ProdGood) as TotalProduced
From ProductionData
Now if ProdGood is defined as int(eger) will SUM(ProdGood) be also integer
or will it go to BigInt if the SUM is lager than the largest int?
Thanks in advance.
Oh, I tried google but nothig relevant came out.modified on Sunday, February 28, 2010 2:14 AM
|
|
|
|
|
That's an excellent question!
I did some searching and couldn't find a definitive answer, either. Have you tried forcing a too large result using test data yet? It would be worthy of an article to explore this further."A Journey of a Thousand Rest Stops Begins with a Single Movement"
|
|
|
|
|
I didn't have enough but i created a dummy test table and the output is
"Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type int."
So no it does not modify the underling field automatically.
|
|
|
|
|
As you found out the data types do not change to meet the needs of the content. If you think about it this would be a disaster in any system that used even minimal data typing. VB could probably handle it 
Another interesting one is trying to work out the result type when you multiple an integer by a decimal and divide it by a float and add a numeric  Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I've done it!
The Result is of type System.Terror 
|
|
|
|
|
I have the following Table Structure in a SQL 2008 database:
ProductCategory
ProdCat_ID Int PK
Name VarChar(30)
Product
Product_ID Int PK
ProdCat_ID Int FK
...
WorkOrder
WO_ID Int PK
Product_ID Int FK
...
Production
Prodn_ID Int PK
WO_ID Int FK
...
MaterialTransaction
MatTrans_ID Int PK,
TransDate Date,
Prodn_ID FK,
Qty Decimal(18,7)
...
I want to retrieve Qty data for specified Category Names on each date in the query.
If I do the following:
SELECT Distinct(mt.TransDate) As [Date],
CASE WHEN pc.Name IN('Hadware','Custom')
THEN SUM(mt.Qty)
ELSE 0 END AS 'Hardware',
CASE WHEN pc.Name LIKE 'DS %'
THEN SUM(mt.Qty)
ELSE 0 End As 'DataStrip'
From MaterialTransaction mt
JOIN Production pr
ON mt.Prodn_ID = pr.Prodn_ID
JOIN WorkOrder wo
ON pr.WO_ID = wo.WO_ID
JOIN Product p
ON wo.Product_ID = p.ID
JOIN ProductCategory pc
ON p.ProdCat_ID = pc.ID
WHERE Date Between '11/24/2009' And '11/25/2009'
GROUP BY mt.TransDate, pc.Name
I end up with results similar to:
OUTPUT:
Date Hardware DataStrip
2009-11-24 00:00:00 0.0000000000 560.4080000
2009-11-24 00:00:00 2786.3100000 0.0000000
2009-11-25 00:00:00 0.0000000 125.5415000
What I require however is:
Date Hardware DataStrip
2009-11-24 00:00:00 2786.310000000 560.4080000
2009-11-25 00:00:00 0.0000000 125.4150000
Any pointers are most appreciated.I don't speak Idiot - please talk slowly and clearly
'This space for rent'
Driven to the arms of Heineken by the wife
|
|
|
|
|
SELECT mt.TransDate, CASE ISNULL(h.Hardware,0)
WHEN 0 Then 0
ELSE h.Hardware
End As Hardware,
CASE ISNULL(d.Datastrip, 0)
WHEN 0 Then 0
ELSE d.Datastrip
END AS Datastrip
FROM MaterialTransaction mt
LEFT JOIN (SELECT mtt.TransDate,SUM(mtt.Qty) As Hardware
FROM MaterialTransaction mtt
JOIN Production prr
ON mtt.Prodn_ID = prr.Prodn_ID
JOIN WorkOrder woo
ON prr.WO_ID = woo.WO_ID
JOIN Product pp
ON woo.Product_ID = pp.ID
JOIN ProductCategory pcc
ON pp.ProdCat_ID = pcc.ID
WHERE pcc.Name In ('Hardware','Custom', 'Cellular')
GROUP BY mtt.TransDate) h
ON mt.TransDate = h.TransDate
LEFT JOIN (SELECT mtt.TransDate, SUM(mtt.Qty) As Datastrip
FROM MaterialTransaction mtt
JOIN Production prr
ON mtt.Prodn_ID = prr.Prodn_ID
JOIN WorkOrder woo
ON prr.WO_ID = woo.WO_ID
JOIN Product pp
ON woo.Product_ID = pp.ID
JOIN ProductCategory pcc
ON pp.ProdCat_ID = pcc.ID
WHERE pcc.Name In ('DS PVC','DS PETG')
GROUP BY mtt.TransDate) d
ON h.TransDate = d.TransDate
WHERE mt.TransDate BETWEEN '11/24/2009' And '11/25/2009'
GROUP BY mt.TransDate, h.Hardware, d.Datastrip
It works as required, but boy is it ugly. Can this be optimized further?
<edit> The final query (with 8 sub-queries) was running rather slowly - ~8 secs to retreive 30 rows from around 4000. An nonclustered index on MaterialTransaction.TransDate sped this up to ~1 sec <edit>I don't speak Idiot - please talk slowly and clearly
'This space for rent'
Driven to the arms of Heineken by the wife
modified on Saturday, February 27, 2010 11:33 PM
|
|
|
|
|
AFAIK joins don't need explicit "ON" clauses if the corresponding fields carry the same names, so ON prr.WO_ID = woo.WO_ID could be omitted, and others after a field name change too.
 Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]
I only read code that is properly formatted, adding PRE tags is the easiest way to obtain that. All Toronto weekends should be extremely wet until we get it automated in regular forums, not just QA.
|
|
|
|
|
Luc Pattyn wrote: AFAIK joins don't need explicit "ON" clauses if the corresponding fields carry the same names
Erk! what happens if you have fields of the same name and you don't want them joined? Or am I misunderstanding your point.Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Andy_L_J wrote: Can this be optimized further?
Probably but not by the query structure, I have these regularly, as long as it is a transactional type system (as opposed to a batch system)and you are not dealing with 1000s of results there should not be too much pain.
Ugly, Pfft, its less than 40 line, has only 2 sub selects, doesn't qualify for ugly.Never underestimate the power of human stupidity
RAH
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
