Query problems - Group By and Latest date

Hi all,

hopefully someone can suggest the best way of implementing the problem i am trying to resolve. We have a table which contains rows relating to tests run on our product. This table is populated from an SSIS job which parses CSV files.

There are multiple rows per serial number relating to multiple tests. The only tests i am interested in are the ones with an ID of T120. Here is the query i have so far which should make it a little easier to explain:

SELECT [SerialNumber]
,Param1
,[TimeStamp]
FROM [Build Efficiency System].[dbo].[SSIS_SCANNERDATA_TBL]
WHERE Test = 'T120'
GROUP BY SerialNumber, Param1, [TimeStamp]
ORDER BY SerialNumber

What i have above is fine to a point. The problem i am encountering is that in test T120 it specifies a part which can be be one of about 6 in field Param1. If during testing there is a problem with the part then it is replaced and the test run a second time up until the whole product passes the test. The query above returns all instances of replacements so i may have the out put as follows:

SerialNumber Param1 TimeStamp
0 Part1 15/03/07
0 Part2 15/03/07
0 Part2 16/03/07
0 Part3 15/03/03

What i really need is to only list the last part that is installed, hence the one with the latest timestamp:

SerialNumber Param1 TimeStamp

0 Part1 15/03/07

0 Part2 16/03/07

0 Part3 15/03/03

Can someone please help me to alter the above query so that it will show only those Param1 fields that have the latest date for each part.

Many thanks in advance,

Grant

This should do the trick:

SELECT [SerialNumber]
,Param1
,MAX([TimeStamp])
FROM [Build Efficiency System].[dbo].[SSIS_SCANNERDATA_TBL]
WHERE Test = 'T120'
GROUP BY SerialNumber, Param1
ORDER BY SerialNumber

You only need to take the max of your timestamp field (and remove it from the group by). The group by all fields is a bit extreme in the previous query (you could use the distinct keyword instead if you had apparent duplicate rows (i.e. replaced the part 3 times in a day).

|||Thats sorted it.
I wasn't as far of the mark in the first place as i'd thought. Thank's very much for the assistance, its much appreciated.

Cheers,

Grant|||Hi, apologies but i need one more piece of advice on this subject.

If i want to include a column with a serial number of the part that has been replaced, how would i do that. As soon as i add it, it needs to be part of an aggregate function or the group by clause. When it becomes part of the group by clause it then duplicates the part again.

Any ideas?

Thanks,

Grant|||

Is the serial number of the part in the same table - if so presumably it is different for each time that part is replaced. You can use a nested query to get that - however it will run into a problem if there are multiple records with the same date. As it stands at the moment you could not distinuish between them.

If you had records:

SerialNumber Param1 TimeStamp Part_SN
0 Part1 15/03/07 1234
0 Part2 15/03/07 1235
0 Part2 16/03/07 1236
0 Part2 16/03/07 1237
0 Part3 15/03/03 1238

How would you know which of the two Part2 items fitted on 16 Mar to give the serial number of? If there are additional fields to determine this then we need to use them

If this does not arise then the query below should serve (substitute correct fieldname for Part_SN):

SELECT B.[SerialNumber]
,B.Param1
,B.[TimeStamp]
,B.Part_SN
FROM (
SELECT [SerialNumber]
,Param1
,MAX([TimeStamp]) AS TimeStamp
FROM [Build Efficiency System].[dbo].[SSIS_SCANNERDATA_TBL]
WHERE Test = 'T120'
GROUP BY SerialNumber, Param1
) A
INNER JOIN [Build Efficiency System].[dbo].[SSIS_SCANNERDATA_TBL] B
ON (A.[SerialNumber] = B.[SerialNumber]) AND
(A.Param1 = B.Param1) AND
(A.[TimeStamp] = B.[TimeStamp]) AND
(B.Test = 'T120')
ORDER BY B.[SerialNumber]

If this is a problem then you will get multiple records in that case - one for each serial number. If the TimeStamp is a datetime which includes the time of the replacement then this will not be an issue (as long as the required serial number is the last record.

|||Thanks,

That is exactly what i wanted to do. Works like a charm.

Grantsql

Query problem splitting field into two comumns

Hi I'm new to reporting services. I'm desinging a query for a report.
I have one filed "ship_yw" that contains year and week numbers, for example,
200701, 200702. I want to display the year in one column and the week in
another. I'm connected to a Progress database via ODBC. I have accomplished
this with no problem in Access using the following code:
SELECT LEFT([Ship_yw],4), RIGHT([Ship_yw],2)
This will not work in Query Builder or if I manually type it in the string
window.
I have also tried:
SELECT
LEFT(Ship__yw,4),
RIGHT(RTRIM(Ship__yw),2)
FROM
oe_head
SELECT
SUBSTRING(Ship__yw,1,4),
SUBSTRING(Ship__yw,5,2)
FROM
oe_head
Query Builder puts unwanted ' around the field name and causes the query to
return "ship" in every row in one column and "yw" in every row of the second.
Any suggestions?Try giving names for the fildes , Use AS
"Twaterman" wrote:
> Hi I'm new to reporting services. I'm desinging a query for a report.
> I have one filed "ship_yw" that contains year and week numbers, for example,
> 200701, 200702. I want to display the year in one column and the week in
> another. I'm connected to a Progress database via ODBC. I have accomplished
> this with no problem in Access using the following code:
> SELECT LEFT([Ship_yw],4), RIGHT([Ship_yw],2)
> This will not work in Query Builder or if I manually type it in the string
> window.
> I have also tried:
> SELECT
> LEFT(Ship__yw,4),
> RIGHT(RTRIM(Ship__yw),2)
> FROM
> oe_head
> SELECT
> SUBSTRING(Ship__yw,1,4),
> SUBSTRING(Ship__yw,5,2)
> FROM
> oe_head
> Query Builder puts unwanted ' around the field name and causes the query to
> return "ship" in every row in one column and "yw" in every row of the second.
> Any suggestions?
>
>

Query Problem

Hi
I'm sure there is simple answer to thsi trivial problem, but I am completely
stuck.
I have got a table which contains 1000's of transactions of Claims, however
I need to run a query of all closed claims.
Below is a sample for one claim.
Status Key is: 1=Opened, 2=Re-Opened, 4=Settled/Closed.
How do extract data for a given date range, of all Currently Settled/Closed
claims?
For e.g, Data range 01/JAN/2005 TO 01/DEC/2005.
Bearing in mind, you cannot just put in the WHERE clause, Status=4, since it
was re-opened several months later again.
CLAIM NO TRANS DATE CLAIMS STATUS
-- -- -
--
SOU/05/00007489 2005-01-13 00:00:00.000 1
SOU/05/00007489 2005-06-07 00:00:00.000 4
SOU/05/00007489 2005-11-07 00:00:00.000 2
Any ideas? I'm sure I need to use MAX dat somewhere, I have tried to use
MAX(Date Claims) and then Status = 4, but this doesn't work?
Kind Regards
RickyHi RIcky !
Try this one here:
SELECT
*
FROM SomeTable OuterTable
WHERE
STATUS = 4 AND
[DATE CLAIMS] BETWEEN '20050101' AND '20061231' AND
NOT EXISTS
(
SELECT
*
FROM SomeTable InnerTable
WHERE
Status = 1 AND
InnerTable.[DATE
CLAIMS] >= OuterTable.[DATE CLAIMS]
)
HTH, jens Suessmeyer.|||Hi Ricky,
this should work

>Status Key is: 1=Opened, 2=Re-Opened, 4=Settled/Closed.
SELECT * FROM dbo.foo
WHERE Status & 4 = 4
See more details about bitwise AND in BOL
HTH ;-)
Gru, Uwe Ricken
MCP for SQL Server 2000 Database Implementation
GNS GmbH, Frankfurt am Main
http://www.gns-online.de
http://www.memberadmin.de
http://www.conferenceadmin.de
________________________________________
____________
dbdev: http://www.dbdev.org
APP: http://www.AccessProfiPool.de
FAQ: http://www.donkarl.com/AccessFAQ.htm
"Ricky" <MSN.MSN.com> schrieb im Newsbeitrag
news:uPN06F3FGHA.208@.tk2msftngp13.phx.gbl...
> Hi
> I'm sure there is simple answer to thsi trivial problem, but I am
> completely
> stuck.
> I have got a table which contains 1000's of transactions of Claims,
> however
> I need to run a query of all closed claims.
> Below is a sample for one claim.
> Status Key is: 1=Opened, 2=Re-Opened, 4=Settled/Closed.
> How do extract data for a given date range, of all Currently
> Settled/Closed
> claims?
> For e.g, Data range 01/JAN/2005 TO 01/DEC/2005.
> Bearing in mind, you cannot just put in the WHERE clause, Status=4, since
> it
> was re-opened several months later again.
> CLAIM NO TRANS DATE CLAIMS STATUS
> -- --
> -
> --
> SOU/05/00007489 2005-01-13 00:00:00.000 1
> SOU/05/00007489 2005-06-07 00:00:00.000 4
> SOU/05/00007489 2005-11-07 00:00:00.000 2
>
> Any ideas? I'm sure I need to use MAX dat somewhere, I have tried to use
> MAX(Date Claims) and then Status = 4, but this doesn't work?
> Kind Regards
> Ricky
>
>|||Sorry, missed something:
SELECT
*
FROM SomeTable OuterTable
WHERE
STATUS = 4 AND
[DATE CLAIMS] BETWEEN '20050101' AND '20061231' AND
NOT EXISTS
(
SELECT
*
FROM SomeTable InnerTable
WHERE
Status = 1 AND
InnerTable.[DATE
CLAIMS] >= OuterTable.[DATE CLAIMS] AND
InnerTable.[CLAIM NO
TRANS] = OuterTable.[CLAIM NO TRANS]
)|||See if this helps you
CREATE TABLE #Test
(
rowid INT NOT NULL,
dt DATETIME NOT NULL,
status INT
)
INSERT INTO #Test VALUES (1,'20010101',1)
INSERT INTO #Test VALUES (1,'20010102',4)
INSERT INTO #Test VALUES (2,'20010103',1)
INSERT INTO #Test VALUES (2,'20010104',4)
INSERT INTO #Test VALUES (3,'20010105',1)
INSERT INTO #Test VALUES (3,'20010106',3)
INSERT INTO #Test VALUES (3,'20010107',4)
INSERT INTO #Test VALUES (4,'20010108',4)
INSERT INTO #Test VALUES (4,'20010109',2)
INSERT INTO #Test VALUES (4,'20010110',1)
INSERT INTO #Test VALUES (5,'20010108',4)
INSERT INTO #Test VALUES (5,'20010109',2)
INSERT INTO #Test VALUES (5,'20010110',1)
SELECT * FROM
(
SELECT * FROM #Test
WHERE dt=(SELECT MAX(dt) FROM #Test T WHERE
t.rowid=#Test.rowid)
) as Der WHERE status=4
"Ricky" <MSN.MSN.com> wrote in message
news:uPN06F3FGHA.208@.tk2msftngp13.phx.gbl...
> Hi
> I'm sure there is simple answer to thsi trivial problem, but I am
> completely
> stuck.
> I have got a table which contains 1000's of transactions of Claims,
> however
> I need to run a query of all closed claims.
> Below is a sample for one claim.
> Status Key is: 1=Opened, 2=Re-Opened, 4=Settled/Closed.
> How do extract data for a given date range, of all Currently
> Settled/Closed
> claims?
> For e.g, Data range 01/JAN/2005 TO 01/DEC/2005.
> Bearing in mind, you cannot just put in the WHERE clause, Status=4, since
> it
> was re-opened several months later again.
> CLAIM NO TRANS DATE CLAIMS STATUS
> -- --
> -
> --
> SOU/05/00007489 2005-01-13 00:00:00.000 1
> SOU/05/00007489 2005-06-07 00:00:00.000 4
> SOU/05/00007489 2005-11-07 00:00:00.000 2
>
> Any ideas? I'm sure I need to use MAX dat somewhere, I have tried to use
> MAX(Date Claims) and then Status = 4, but this doesn't work?
> Kind Regards
> Ricky
>
>|||Correction , range dates
SELECT *
FROM(
SELECT * FROM #Test
WHERE dt BETWEEN '20010101' AND '20010105'
) AS der WHERE dt=(SELECT MAX(dt)
FROM #Test T WHERE T.dt
BETWEEN '20010101' AND '20010105' AND t.rowid=der.rowid AND der.status=4)
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:u$T5vW3FGHA.2012@.TK2MSFTNGP14.phx.gbl...
> See if this helps you
> CREATE TABLE #Test
> (
> rowid INT NOT NULL,
> dt DATETIME NOT NULL,
> status INT
> )
> INSERT INTO #Test VALUES (1,'20010101',1)
> INSERT INTO #Test VALUES (1,'20010102',4)
> INSERT INTO #Test VALUES (2,'20010103',1)
> INSERT INTO #Test VALUES (2,'20010104',4)
> INSERT INTO #Test VALUES (3,'20010105',1)
> INSERT INTO #Test VALUES (3,'20010106',3)
> INSERT INTO #Test VALUES (3,'20010107',4)
> INSERT INTO #Test VALUES (4,'20010108',4)
> INSERT INTO #Test VALUES (4,'20010109',2)
> INSERT INTO #Test VALUES (4,'20010110',1)
> INSERT INTO #Test VALUES (5,'20010108',4)
> INSERT INTO #Test VALUES (5,'20010109',2)
> INSERT INTO #Test VALUES (5,'20010110',1)
>
> SELECT * FROM
> (
> SELECT * FROM #Test
> WHERE dt=(SELECT MAX(dt) FROM #Test T WHERE
> t.rowid=#Test.rowid)
> ) as Der WHERE status=4
>
>
>
> "Ricky" <MSN.MSN.com> wrote in message
> news:uPN06F3FGHA.208@.tk2msftngp13.phx.gbl...
>|||Thanks everyone, for your contributions, will go through these and compose
something, will let you guys know, how I get on!
Kind Regards
Ricky
"Ricky" <MSN.MSN.com> wrote in message
news:uPN06F3FGHA.208@.tk2msftngp13.phx.gbl...
> Hi
> I'm sure there is simple answer to thsi trivial problem, but I am
completely
> stuck.
> I have got a table which contains 1000's of transactions of Claims,
however
> I need to run a query of all closed claims.
> Below is a sample for one claim.
> Status Key is: 1=Opened, 2=Re-Opened, 4=Settled/Closed.
> How do extract data for a given date range, of all Currently
Settled/Closed
> claims?
> For e.g, Data range 01/JAN/2005 TO 01/DEC/2005.
> Bearing in mind, you cannot just put in the WHERE clause, Status=4, since
it
> was re-opened several months later again.
> CLAIM NO TRANS DATE CLAIMS STATUS
> -- --
-
> --
> SOU/05/00007489 2005-01-13 00:00:00.000 1
> SOU/05/00007489 2005-06-07 00:00:00.000 4
> SOU/05/00007489 2005-11-07 00:00:00.000 2
>
> Any ideas? I'm sure I need to use MAX dat somewhere, I have tried to use
> MAX(Date Claims) and then Status = 4, but this doesn't work?
> Kind Regards
> Ricky
>
>sql

Query Problem

Hi,
I have a table like this:
StringID,LanguageID and StringValue.
The table contains strings in different languages. Strings with the equal
meaning have the same StringID but a different LanguageID.
Here is a example:
LanguageID
1 - english
2 - german
StringTbl
1,1,home
1,2,Haus
2,1,tree
2,2,Baum
3,1,car
Now the problem: For some strings exists no translation, there is only an
english version available. How can I select a german sting and recive the
english version if no german version exist.
e.g. select StringValue from StringTbl where StringID = 1 and LanguageID = 2
' or if LanguageID = 2 not exist then LanguageID =1.
Is it possible to use one select to achieve this?
Thanks for any help.
WernerHi
Try something like:
SELECT ISNULL(g.[string], e.[string]) as [string]
FROM stringtbl e
LEFT JOIN stringtbl g on e.stringid = g.stringid
WHERE g.langid = 2
and e.landid = 1
John
"Werner" wrote:

> Hi,
> I have a table like this:
> StringID,LanguageID and StringValue.
> The table contains strings in different languages. Strings with the equal
> meaning have the same StringID but a different LanguageID.
> Here is a example:
> LanguageID
> 1 - english
> 2 - german
> StringTbl
> 1,1,home
> 1,2,Haus
> 2,1,tree
> 2,2,Baum
> 3,1,car
> Now the problem: For some strings exists no translation, there is only an
> english version available. How can I select a german sting and recive the
> english version if no german version exist.
> e.g. select StringValue from StringTbl where StringID = 1 and LanguageID =
2
> ' or if LanguageID = 2 not exist then LanguageID =1.
> Is it possible to use one select to achieve this?
> Thanks for any help.
> Werner
>
>

Query prob?

Hi,

I got table that contains prodcode, qty and Box, I want to display all product and their total QTY. like

Item

prodcode

qty

box

1

ADEN100550003006

2

000001

2

ADEN100550003006

3

000001

3

ADEN100550003006

6

000001

4

ADEN100550003006

12

000001

5

ADEN100550003006

15

000001

6

ADEN100550003006

18

000001

7

ADEN100550003006

21

000001

8

ADEN100550003006

24

000001

9

ADEN100550003006

36

000001

10

ADEN100550003006

48

000001

11

ADEN100550003006

72

000001

12

ADEN100550003006

144

000001

13

ADEN100550003006

157

000001

14

ADEN100550003006

360

000001

*** all thses product got same bar code so I want to sum all these QTY and display the total QTY.
I am executing this query right nw.

select WHBOXDET.prodcode,WHBOXDET.qty,WHBOXDET.box ,sum (WHBOXDET.qty) from WHBOXDET where prodcode = 'ADEN100550003006' GROUP BY prodcode,box,qty ;

But it Displays result like :

Created: 26/07/2007 12:55:03

Item

prodcode

qty

box

EXPR

1

ADEN100550003006

2

000001

20

2

ADEN100550003006

3

000001

15

3

ADEN100550003006

6

000001

54

4

ADEN100550003006

12

000001

60

5

ADEN100550003006

15

000001

15

6

ADEN100550003006

18

000001

18

7

ADEN100550003006

21

000001

21

8

ADEN100550003006

24

000001

24

9

ADEN100550003006

36

000001

36

10

ADEN100550003006

48

000001

48

11

ADEN100550003006

72

000001

72

12

ADEN100550003006

144

000001

144

13

ADEN100550003006

157

000001

157

14

ADEN100550003006

360

000001

360

Plz tell me how to do it.
Thx

You need to remove "WHBOXDET.qty" from your select list and "qty" from your group by, otherwise it is factored into the uniqueness of each row. It should look like this:

Code Snippet

SELECT WHBOXDET.prodcode, WHBOXDET.box, sum(WHBOXDET.qty) qty from WHBOXDET where prodcode = 'ADEN100550003006' GROUP BY prodcode, box

|||sorted

Query over many to many relationship

I have 2 tables with many to many relatioship

tblTitle and tblStyle

I have setup a connecting table tblTitleStyle which contains 2 forein keys TitleID and StyleID to connect the 2 tables.

I can query 2 tables with one to many relatioship using JOINT statement. How do I implement the JOINT statement across tblTitleStyle though? Or is it a wrong aproach altogether, should I use maybe a subquery?

Thanks, Jakub

Does the code below help you out?

Chris

SELECT <insert column names>

FROM dbo.tblTitleStyle ts

INNER JOIN dbo.tblTitle t ON t.TitleID = ts.TitleID

INNER JOIN dbo.tblStyle s ON s.StyleID = ts.StyleID

|||

Wow...that was quick and shure works like a charm!..greatly appreciated.

Now I have in adition 2 more tables (names are slightly different...I am in the process of changing them)

Composer and Category

Here is the complete DB scenario:

Title

with many to one relationship to

Composer

Category

and many to many relationship to

Genre (Style before)

I can use one query to SELECT data from Title, Composer and Category:

SELECT t.Title, c.Category, co.Composer

FROM Title t

INNER JOIN Composer co ON t.Index_Composer = co.Index_Composer

INNER JOIN Category c ON t.Index_Category = c.Index_Category

and one query (thanks to you) from the Genre table:

SELECT t.Title, g.Genre
FROM dbo.TitleGenre tg
INNER JOIN dbo.Title t ON t.Index_Title = tg.Index_Title
INNER JOIN dbo.Genre g ON g.Index_Genre = tg.Index_Genre

Can I combine those 2 queries into one to get the result in one form? The problem is that FROM calls for different tables in those 2 queries.

Thanks again...Jakub

I

|||

This should do the trick....

Chris

SELECT t.Title, c.Category, co.Composer, g.genre

FROM Title t

INNER JOIN Composer co ON t.Index_Composer = co.Index_Composer

INNER JOIN Category c ON t.Index_Category = c.Index_Category

INNER JOIN dbo.TitleGenre tg ON tg.TitleID = t.TitleID

INNER JOIN dbo.Genre g ON g.Index_Genre = tg.Index_Genre

|||

It did, thanks... the query builder in VS web dev produced this convoluted code which didn't work.

Check out www.concertantechamber players.com in few days to see the results...

Query on 2 bases

How to make a query which contains fields from two different bases ?

Thanks in advance.

ReNThis request was posted earlier today under the title "Inter-Database
References"

John
"TNR" <stage7@.tgs.fr> wrote in message
news:bg38re$u6c$1@.news-reader5.wanadoo.fr...
> How to make a query which contains fields from two different bases ?
> Thanks in advance.
> ReN|||"John Bell" <jbellnewsposts@.hotmail.com> wrote in message news:<3f252ad9$0$18494$ed9e5944@.reading.news.pipex.net>...
> This request was posted earlier today under the title "Inter-Database
> References"
> John
> "TNR" <stage7@.tgs.fr> wrote in message
> news:bg38re$u6c$1@.news-reader5.wanadoo.fr...
> > How to make a query which contains fields from two different bases ?
> > Thanks in advance.
> > ReN

If your databases are on the same server then it's as simple as:

select *
from database1..table1 inner join database2..table2

if they are on different servers, then you'll need to add a linked
server to the database that you are running your query on. This can
be done through the GUI or by running sp_addlinkedserver system stored
procedure. Once you've done it then, the command is:

select *
from database1..table1 inner join linkedservername.database2..table2

Hope this helps
Hodge

Query of query

Hi,

just a question: I created a simple query (it contains a UNION) which returns tree colomuns. Since, I have to use the results of this query on another query and I prefer to avoid to use dirty ways like scripting, I was wondering it is possible to perform a select "on the fly" on the results of previous select: a kinf of "select from select"

To give an example I have this query which returns:
Q1: colA,colB,colC

Soon I run Q1, I would like to run over Q2 which is:
select * from Q1

Note: Q1 is not a table and even a view: it's just a select

Thanks if you would like to give me a trick.

DomySELECT
t.col1,
t.col2,
s.col2,
s.col3
FROM
table t inner join
(SELECT
col1,
col2,
col3
FROM
another_table) s on t.col1 = s.col1|||To make the answer more straight:

Your query Q2 would be like

SELECT * FROM (<YourSelectStatement>) Q1|||jora's is the most efficient method of solving this in terms of code. If you actually need the results of Q1 twice ("Soon I run Q1, I would like to run over Q2") and the query is processor intensive, then run it once and store the results in a temporary table or table variable. Then you can quickly join it in further statements as often as you want.

blindman|||jora's code answers some other question that has not been asked, I believe, while DB's answer is exactly what the original posting was after.

I don't get bm's comments, - are you the judge of the forum? Or your word should always be last, even if it's wrong? Any point to that?

And speaking about efficiency, DB's suggestion is actually more efficient, hands down.|||If you use the results from the first query regularly, would it not make more sense to create a view and then query the view?|||I'd say it would, and I'd even go for a function, just in case :)|||You would just want to make sure that you add an index on the table for the rows you're selecting against in your view, and then you can index your view.|||it's normally not my thing to criticize other peoples posts, but while we're at it here it goes. Sure, my join was not asked, but i didn't read about views, temporary tables and functions as well. Moreover DomyFerraro asked for a select statement on the fly.|||"SELECT * FROM (<YourSelectStatement> ) Q1"???

What the heck does that contribute?! Hey, I can make it even more efficient, djabarov: how about just <YourSelectStatement>?

Or if (as I suspect) DB gets paid by the amount of code he writes, this would be more lucrative:
"SELECT * FROM (SELECT * FROM (SELECT * FROM (<YourSelectStatement> ) Q1) Q2) Q3"

DB's solution can't be more efficient than Jora's, because it is exactly the same as Jora's, just less informative.

Jora's is the best solution for on-the-fly processing. Mine is more efficient if the dataset has to be used multiple times. A view may be usefull if the join is used in multiple procedures, though it might be less efficient because it would not be pre-compiled.

djabarov, do you think about your posts, or does your head just fall onto the keyboard while you are napping?

blindman|||ROTFLMFAO!!

blindman, you da man!|||bm, have some bananas, you're coming up with severe case of verbal diarrhea :D|||Ouch!|||Blindman, did you understand DomyFerraro's problem? See his original question, and read loud and clear:

He has a query Q1, which is a union query. he wants to use this query on-the-fly, as it would be SELECT * FROM Q1. He does not want your view, or a (temporary) table.

I gave him just this solution. So, open up your eyes!|||Originally posted by Paul Young
ROTFLMFAO!!

blindman, you da man!

IDNUWYATA!|||Originally posted by DoktorBlue
He does not want your view, or a (temporary) table.


I was the one who made the comment regarding the view. I simply meant it as an alternative, but not necessarily a "solution", and something to research if curious.|||Originally posted by DoktorBlue
IDNUWYATA!

OISTUYJF!|||Originally posted by Paul Young
OISTUYJF!

Great, this guru stuff!|||Originally posted by Seppuku
I was the one who made the comment regarding the view. I simply meant it as an alternative, but not necessarily a "solution", and something to research if curious.

Apperently only the "correct" answer may be posted and differing views or opinions are not tollerated.|||That appears to be the rule that you and bm are imposing here.|||You crack me up, DoktorBlue!
Your "solution" is meaningless, and wouldn't solve any problem EVER posted on this forum. I'll try to make this simple for you:

There is NO syntactical difference between your solution...
SELECT * FROM (select from A union select from B) Q1
...and a straight union query...
select from A union select from B
NOTHING is gained by wrapping the union statement in a subquery.

If you can't see this, and can't figure out that DomyFerrao gains NOTHING from it, then there really is no hope for you. Go take a course in hardware maintenance or something. DUH.

As far as me not understanding the problem, didn't you make yourself look foolish enough on mohan1976's posted question (http://dbforums.com/t901820.html) when you told me my posting "must be mistaken from another thread", that I was "exposing yourself incapable understanding the problem", and to "Contribute to the thread, or be quiet!" AND THEN MOHAN USED MY SOLUTION INSTEAD OF YOURS!

It is not my goal to make you look foolish. That's not why I post here, I have better things to do with my time; but I will respond to your more ridiculous comments. You seem to think I'm gunning for you, but in reality it is you who keeps shooting yourself in the foot by attacking every comment I post.

Talking to you is like trying to teach a chimp how to tie shoelaces when you know it is never going to put on a pair of sneakers anyway.

blindman|||Cool off, bm, no need for Zoo analogies here, it's just a forum, not a battle field :)

I think that if you look at the original question which you quoted yourself, - you might see that an attemp to SELECT * FROM (select * from A union select * from B) Q1 may not necessarily be so straight forward. I don't believe that DomyFerraro just wanted to select from a sub-query for the heck of it. Don't you think? Try to leave some room for the possibility that DomyFerraro simply left out a presence of WHERE clause that may require the inner query to be left within the parenthesis rather than be join with the original table as jora suggested.

Other than that, why can't we all be more constructive while trying to help each other, rather than master our wit in insulting our "opponents"?

Can it be done? At all? Without recommending to increase the dosage of certain medication or referencing WWW.DICTIONARY.COM??

People are here not to see us being ugly, but to seek help. And if one happens to know more than the other, - at the end everybody wins.

Can we all try, just once?

Thanks in advance :)|||"I don't believe that DomyFerraro just wanted to select from a sub-query for the heck of it. Don't you think? "

I absolutely agree, and that is what Jora's solution explained; how to embed the results of the UNION query in a more complex statement.

Hey! We agree! Friends now?|||I am all for it! Don't exclude DoktorBlue! He is a good guy, I happened to get to know him a little more via private email, - he has A LOT to offer, I mean it, much more than I can dream of.|||Originally posted by rdjabarov
That appears to be the rule that you and bm are imposing here.

If you had taken the time to look over my prior posts, rather than the past few threads, you would realize how utterly stupid your comment was.

I am more than happy to drop all this, but don't expect me to sit back and take crap off people with out a response. I did not start this pissing contest.

Most problems posted here have many potential solutions. The ironic thing is that in all this debate DomyFerraro has not posted back asking for more help or suggested which solution best fit his problem.|||Originally posted by blindman
"I don't believe that DomyFerraro just wanted to select from a sub-query for the heck of it. Don't you think? "

I absolutely agree, and that is what Jora's solution explained; how to embed the results of the UNION query in a more complex statement.

Blindman: I'm glad that you finally got the point, thanks to rdjabarov. I assumed everybody, including you, te be familiar with the fact, that a SELECT statement may be more complex than just SELECT * FROM x. To make the principle more clear to DomyFerraro (where are you?), I gave a straight example without any extras. You called it with many words meaningless, however, it does show in its basic form how to select from a query. Regarding to Mohan's thread: yes, he is using your solution, which isn't giving him, what he has specified. As long as he's glad, I'm glad, too.

Paul: I guess, you already found out by yourself, that this isn't about the one-and-only path to Rome, but about blindman's almost structural misunderstandings.|||Hello buddies,

first let me thank you for so big support you gave me. I had the change to verify all the suggestions which I like and here the solution which fits better for my needs (already tested):

select * from
(
select * from T1
UNION
select * from T2
)

Believe, behind the two nested and root queries there is something more complexed, but the idea it's enough for me since, I have no problem about performance.

Open to any comments.

Thanks|||Sorry, I forgot to put the alias so:

select * from
(
select * from T1
UNION
select * from T2
) Q1

and here the draft of real query (just fyi):

Select * from
(
(
select
[Fatture Prodotti uscita].IDProdottiUscita,
cast (((cast((datediff(d, [Fatture Dati].Data, [Fatture Dati].Data_finito)) as decimal )) / (datediff(d, [Fatture Dati].Data, [Turni di lavorazione operazione].Data_turno))*100) as int) as ds1,
0 ds2
from
[Fatture Dati],
[Fatture Prodotti uscita],
[Elenco Disegni],
[Operazioni per disegno],
[Turni di lavorazione operazione]
where
[Fatture Dati].IDFattura = [Fatture Prodotti uscita].IDFattura
and [Fatture Prodotti uscita].IDProdottiUscita = [Elenco Disegni].IDProdottiUscita
and [Elenco Disegni].IDDisegno = [Operazioni per disegno].IDDisegno
and [Operazioni per disegno].IDOperazione = [Turni di lavorazione operazione].IDOperazione
and [Operazioni per disegno].cc_pln ='999'
and (left([Fatture Prodotti uscita].IDProdottiUscita,1) = '5')
and datediff (d, '01/01/2003' , [Fatture Dati].data ) > 0
)
UNION
(
select
[Fatture Prodotti uscita].IDProdottiUscita,
0 as ds1,
cast (((cast((datediff(d, [Fatture Dati].Data, [Fatture Dati].Data_grezzo)) as decimal )) / (datediff(d, [Fatture Dati].Data, [Turni di lavorazione operazione].Data_turno))*100) as int) as ds2
from
[Fatture Dati],
[Fatture Prodotti uscita],
[Elenco Disegni],
[Operazioni per disegno],
[Turni di lavorazione operazione]
where
[Fatture Dati].IDFattura = [Fatture Prodotti uscita].IDFattura
and [Fatture Prodotti uscita].IDProdottiUscita = [Elenco Disegni].IDProdottiUscita
and [Elenco Disegni].IDDisegno = [Operazioni per disegno].IDDisegno
and [Operazioni per disegno].IDOperazione = [Turni di lavorazione operazione].IDOperazione
and [Operazioni per disegno].cc_pln ='129'
and (left([Fatture Prodotti uscita].IDProdottiUscita,1) = '5')
and datediff (d, '01/01/2003' , [Fatture Dati].data ) > 0
)
) Q1|||Originally posted by DomyFerraro
select * from
(
select * from T1
UNION
select * from T2
) Q1


Your table and field names sound like italo music ...:)

Blindman's point was, that you don't need the outer SELECT frame in it's basic form, if you don't use any other SELECT options like a specific SELECT clause, GROUP BYs, ORDER BYs, or using your query Q1 as an entity of joining.|||I see Blindman's point, we should also consider I need to group the results of "nested" query, so the first solution, which is more pratical, for me is readble and easy to maintain.
At this time, because I have no performance problem all around, yours sounds good.

Thanks for sure to Blindman too.

ps: Yes: it's italian as well me

Query No1

Hi

I have a table Topics which contains Topics Id and Topic Name.

Now i have two strings 'strTopicId' & 'strTopicName'. I need to check whether strTopicId and strTopicName exist in the table or not.

Since TopicId is the Primary Key, so i think a better way to validate would be first to check for the TopicID and then check its corresponding TopicName and see whether it also matches.
You can show me the SP or Query the way it can be done. I think we can return a '1' if its present and a '0' if its not present. I would be very grateful for any help. Thanks a lot.

Regards,

Nab

Hmm, you are asking help to write a stored procedure. Dont you think its not a right place to ask about stored procedures.

Anyway,

CREATEORREPLACEPROCEDURE Sp_Chk_Topic

(

topicID IN"Table_Name"."TopicIDFieldName"%TYPE,

topicName IN"Table_Name"."TopicNameFieldName"%TYPE,

retValueOUTVARCHAR2

)

AS

c_counterNUMBER;

BEGIN

SELECT NVL(COUNT(*),0)INTO c_counterFROMTABLE_NAMEWHERE "topicIDFieldName" = UPPER(topicID) andtopicNameFieldName" = UPPER(topicName)

IF( c_counter<>0)THEN

retValue:='01';--Name matched';

RETURN;

ELSE

retValue:='02';--Name did NOT matched';

RETURN;

ENDIF;

EXCEPTION

WHENOTHERSTHEN

retValue:='03';--'Error Occured getting the data';

RETURN;

END Sp_Chk_Topic;

/

|||

Hi

Thanks a lot, Mr Kumar. I mistakenly posted it here. This should have been in the Sql Sectioin. If it can be moved, it should be.

However i modified ur SP and am still recieving some errors. Could u plz explain the reason of errors.

CREATE PROCEDURE CheckThread
(
@.TopicID IN Topics.TopicId int(4),

@.TopicName IN Topics.TopicName varchar(50),

@.retValue OUT VARCHAR

)

AS

c_counter NUMBER;

BEGIN

SELECT NVL(COUNT(*), 0) INTO c_counter FROM Topics WHERE TopicId = UPPER(TopicID) and TopicName = UPPER(TopicName)

IF( c_counter <> 0 ) THEN

retValue = '01'; --Name matched';

RETURN;

ELSE

retValue = '02'; --Name did NOT matched';

RETURN;

END IF;

EXCEPTION

WHEN OTHERS THEN

retValue = '03'; --'Error Occured getting the data';

RETURN;

END CheckThread;

1)Incorrect Syntax near IN "in line 3"

2)NVL is not a recognized f() name.

3)Incorrect syntax near the keyword 'Then'

4)Line 31: Incorrect syntax near ';'

The errors were much more in the original one. I had to add'@.' and also removed ':' as well. Could u plz inform me of the corrections required. Thanks a lot.

Regards,

NAB

|||You were provided Oracle PL/SQL coding. For SQL Server there are some differences.

1)Incorrect Syntax near IN "in line 3"

In SQL Server stored procedures there is no "IN" keyword. Parameters are assumed to be input parameters unless OUTPUT isspecified. So remove the IN keyword.

2)NVL is not a recognized f() name.

Use ISNULL instead of NVL. although it's not needed here because theCOUNT(*) will return a 0 if no records are found to match the criteria.

3)Incorrect syntax near the keyword 'Then'

In SQL Server stored procedures the THEN keyword is not used, nor isthe END IF keyword, so remove them. When conditional statementsextend for more than one line then enclose them in BEGIN...END blocks.

4)Line 31: Incorrect syntax near ';'

There are no line termination characters in SQL Server stored procedures, so remove them.

Additionally, all variables should be DECLARE'd and all variablesmuch start with an @. character. And SQL Server databases arenormally case-insensitive so the UPPER keyword is likely not needed.

You should be left with something that looks like this:

CREATE PROCEDURE CheckThread
(
@.TopicID int,

@.TopicName varchar(50),

@.retValue varchar(2) OUTPUT

)

AS

DECLARE @.c_counter int

SELECT @.c_counter = COUNT(*) FROM Topics WHERE TopicId = @.TopicID and TopicName = @.TopicName

IF @.@.ERROR <> 0

BEGIN
SELECT @.retValue = '03' --'Error Occured getting the data'
RETURN
END

IF(@.c_counter <> 0 )

SELECT @.retValue = '01' --Name matched'

ELSE

SELECT @.retValue = '02' --Name did NOT matched'

RETURN

That could probably be simplified even further using an EXISTS keyword:

CREATE PROCEDURE CheckThread
(
@.TopicID int,

@.TopicName varchar(50),

@.retValue varchar(2) OUTPUT

)

AS
SELECT @.retValue = '03'

IF EXISTS(SELECT TopicID FROM Topics WHERE TopicId = @.TopicID and TopicName = @.TopicName)

SELECT @.retValue = '01' --Name matched'

ELSE

SELECT @.retValue = '02' --Name did NOT matched'

RETURN

|||

Thanks a lot Tmorton. I think Mr. Kumar never knew what he was posting . I have found a very easier way of doin this and its working perfectly well for my purpose

CREATE PROCEDURE VerifyTopic(@.strTopicId int, @.strTopicName varchar(50))
AS
DECLARE @.Verified int

SELECT @.Verified = 1
FROM Topics
Where TopicId = @.strTopicId AND TopicName = @.strTopicName

If @.Verified = 1
Return 1
else
Return 0
GO

Regards,

NAB

|||

TheNAB wrote:

Thanks a lot Tmorton. I think Mr. Kumar never knew what he was posting . I have found a very easier way of doin this and its working perfectly well for my purpose

lol. You try to help people and this is what you get. Way to go Mr.NAB

By the way, you never mentioned in your original post that you are using SQL server and you posted in Getting Started forum. You simply asked for a damn SP and that too in a wrong forum

Oh yeah, by the way, what you posted is simply a sql server version of my proc, if you know what you are doing

|||

TheNAB wrote:

Thanks a lot Tmorton. I think Mr. Kumar never knew what he was posting .

Kumar knew what he was posting -- he was posting an Oracle storedprocedure. It's not good form to insult those who are trying tohelp you.
I am glad what you have now serves your purpose. I stillrecommend the coding I suggested at the end of my first post, but ifwhat you now have works for you then I guess you have what youneed. Good luck!

Query needs to loop?

Is this possible?
A char variable length field contains this data: (for example)
ABC,XYZ,GEF,CAB
I need to query another table on the contents of each of these names that
are separated by comma.
I need to read ABC and then query another table for ABC in field1 and pick
up some fields.
Then, I need to do the same for XYZ, etc, reading across.
How can I parse the field that's comma delim, and in one continuous select
statment pick all the data from another table? Put a looping statement into
the Select statement to read through the comma delim field until done?
Thanks,
Don
On Thu, 25 Aug 2005 14:55:34 -0700, DonSQL2222 wrote:

>Is this possible?
>A char variable length field contains this data: (for example)
>ABC,XYZ,GEF,CAB
>I need to query another table on the contents of each of these names that
>are separated by comma.
>I need to read ABC and then query another table for ABC in field1 and pick
>up some fields.
>Then, I need to do the same for XYZ, etc, reading across.
>How can I parse the field that's comma delim, and in one continuous select
>statment pick all the data from another table? Put a looping statement into
>the Select statement to read through the comma delim field until done?
>Thanks,
>Don
Hi Don,
http://www.sommarskog.se/arrays-in-sql.html
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

Query multiple fields at once??

I have a table with 10 fields. I want to select any row that contains
the word "bob" in any one of the 10 fields. I've been writing this
query by checking each field individually connected with an OR
statement. Such as...
Select * from tablename where (column1 like '%bob%') or (column2 like
'%bob%') or ... and so on.
Is there an easier way to search all fields/columns in a more simple
SQL query?
The concept of ... Select * from tablename where (* like '%bob%')
?
Thanks,
- SteveNope. Your method of using the [OR] is the only way.
That you have to search multiple columns hints of denormalized data. Perhaps
this type of operation would be simpler if the table schema was
reconsidered.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"kennedystephen" <skennedy@.oaconsulting.com> wrote in message
news:1162572395.398641.35870@.h48g2000cwc.googlegroups.com...
>I have a table with 10 fields. I want to select any row that contains
> the word "bob" in any one of the 10 fields. I've been writing this
> query by checking each field individually connected with an OR
> statement. Such as...
> Select * from tablename where (column1 like '%bob%') or (column2 like
> '%bob%') or ... and so on.
> Is there an easier way to search all fields/columns in a more simple
> SQL query?
> The concept of ... Select * from tablename where (* like '%bob%')
> ?
> Thanks,
> - Steve
>|||Hi Stephen
This sounds like your design may want further normalisation?
I guess you could do something like
Select column1, column2, column3, column4, column5, column6, column7,
column8, column9, column10
from tablename
where ISNULL(column1,'') + '|' + ISNULL(column2,'') + '|' +
ISNULL(column3,'') + '|' + ISNULL(column4,'') + '|' + ISNULL(column5,'') +
'|' + ISNULL(column6,'') + '|' + ISNULL(column7,'') + '|' +
ISNULL(column8,'') + '|'+ ISNULL(column9,'') + '|' + ISNULL(column10,'')
like '%bob%'
so long as you are not looking for something containing the delimiter! I am
not sure how that would perform. Other possible options might be:
Select column1, column2, column3, column4, column5, column6, column7,
column8, column9, column10
from tablename
where column1 like '%bob%'
UNION
Select column1, column2, column3, column4, column5, column6, column7,
column8, column9, column10
from tablename
where column2 like '%bob%'
UNION
Select column1, column2, column3, column4, column5, column6, column7,
column8, column9, column10
from tablename
where column3 like '%bob%'
UNION
Select column1, column2, column3, column4, column5, column6, column7,
column8, column9, column10
from tablename
where column4 like '%bob%'
etc ...
If you know that only one column contained the search string then you could
use UNION ALL in the above method.
or
Select column1, column2, column3, column4, column5, column6, column7,
column8, column9, column10
FROM ( Select column1 AS Searchcolumn, column1, column2, column3, column4,
column5, column6, column7, column8, column9, column10
from tablename
UNION ALL
Select column2 AS Searchcolumn, column1, column2, column3, column4, column5,
column6, column7, column8, column9, column10
from tablename
UNION ALL
Select column3 AS Searchcolumn, column1, column2, column3, column4, column5,
column6, column7, column8, column9, column10
from tablename
UNION ALL
Select column4 AS Searchcolumn, column1, column2, column3, column4, column5,
column6, column7, column8, column9, column10
from tablename
UNION ALL
Select column5 AS Searchcolumn, column1, column2, column3, column4, column5,
column6, column7, column8, column9, column10
from tablename ) A
where Searchcolumn like '%bob%'
HTH
John
"kennedystephen" wrote:
> I have a table with 10 fields. I want to select any row that contains
> the word "bob" in any one of the 10 fields. I've been writing this
> query by checking each field individually connected with an OR
> statement. Such as...
> Select * from tablename where (column1 like '%bob%') or (column2 like
> '%bob%') or ... and so on.
> Is there an easier way to search all fields/columns in a more simple
> SQL query?
> The concept of ... Select * from tablename where (* like '%bob%')
> ?
> Thanks,
> - Steve
>|||And if it were equality instead of like, you could reverse the clause, e.g.
WHERE 'bob' IN (column1, column2, column3, ...)
But I agree that the design does not seem fundamentally sound to me.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:0256D861-C01E-4766-BE20-09ED5790337B@.microsoft.com...
> Hi Stephen
> This sounds like your design may want further normalisation?
> I guess you could do something like
> Select column1, column2, column3, column4, column5, column6, column7,
> column8, column9, column10
> from tablename
> where ISNULL(column1,'') + '|' + ISNULL(column2,'') + '|' +
> ISNULL(column3,'') + '|' + ISNULL(column4,'') + '|' + ISNULL(column5,'') +
> '|' + ISNULL(column6,'') + '|' + ISNULL(column7,'') + '|' +
> ISNULL(column8,'') + '|'+ ISNULL(column9,'') + '|' + ISNULL(column10,'')
> like '%bob%'
> so long as you are not looking for something containing the delimiter! I
> am
> not sure how that would perform.|||stephen,
If this is a common requirement for your applications, then you might want
to consider full text indexing and searches. BOL can get you started.
-- Bill
"kennedystephen" <skennedy@.oaconsulting.com> wrote in message
news:1162572395.398641.35870@.h48g2000cwc.googlegroups.com...
>I have a table with 10 fields. I want to select any row that contains
> the word "bob" in any one of the 10 fields. I've been writing this
> query by checking each field individually connected with an OR
> statement. Such as...
> Select * from tablename where (column1 like '%bob%') or (column2 like
> '%bob%') or ... and so on.
> Is there an easier way to search all fields/columns in a more simple
> SQL query?
> The concept of ... Select * from tablename where (* like '%bob%')
> ?
> Thanks,
> - Steve
>