query works fine outside union, but doesnt work .. .

hi all

I have the following query which works fine when it's executed as a single query. but when i union the result of this query with other queries, it returns a different set of data.

any one know why that might be the case??

select top 100 max(contact._id) "_id", max(old_trans.date) "callback_date", 7 "priority", max(old_trans.date) "recency", count(*) "frequency" --contact._id, contact.callback_date
from topcat.class_contact contact inner join topcat.MMTRANS$ old_trans on contact.phone_num = old_trans.phone
where contact.phone_num is not null
and contact.status = 'New Contact'
group by contact._id
order by "recency" desc, "frequency" desc

i've included the union query here for completeness of the question

begin
declare @.current_date datetime
set @.current_date = GETDATE()

select top 100 _id, callback_date, priority, recency, frequency from
(
(
select top 10 _id, callback_date, 10 priority, @.current_date recency, 1 frequency --, DATEPART(hour, callback_date) "hour", DATEPART(minute, callback_date) "min"
from topcat.class_contact
where status ='callback'
and (DATEPART(year, callback_date) <= DATEPART(year, @.current_date))
and (DATEPART(dayofyear, callback_date) <= DATEPART(dayofyear, @.current_date)) -- all call backs within that hour will be returned
and (DATEPART(hour, callback_date) <= DATEPART(hour, @.current_date))
and (DATEPART(hour, callback_date) <> 0)
order by callback_date asc
--order by priority desc, DATEPART(hour, callback_date) asc, DATEPART(minute, callback_date) asc, callback_date asc
)
union
(
select top 10 _id, callback_date, 9 priority, @.current_date recency, 1 frequency
from topcat.class_contact
where status = 'callback'
and callback_date is not null
and (DATEPART(year, callback_date) <= DATEPART(year, @.current_date))
and (DATEPART(dayofyear, callback_date) <= DATEPART(dayofyear, @.current_date))
and (DATEPART(hour, callback_date) <= DATEPART(hour, @.current_date))
and (DATEPART(hour, callback_date) = 0)
order by callback_date asc
)
union
(
select top 10 _id, callback_date, 8 priority, @.current_date recency, 1 frequency
from topcat.class_contact
where status = 'No Connect'
and callback_date is not null
and (DATEPART(year, callback_date) <= DATEPART(year, @.current_date))
and (DATEPART(dayofyear, callback_date) <= DATEPART(dayofyear, @.current_date))
and (DATEPART(hour, callback_date) <= DATEPART(hour, @.current_date))
order by callback_date asc
)
union
(
select top 100 max(contact._id) "_id", max(old_trans.date) "callback_date", 7 "priority", max(old_trans.date) "recency", count(*) "frequency" --contact._id, contact.callback_date
from topcat.class_contact contact inner join topcat.MMTRANS$ old_trans on contact.phone_num = old_trans.phone
where contact.phone_num is not null
and contact.status = 'New Contact'
group by contact._id
order by "recency" desc, "frequency" desc
)
) contact_queue
order by priority desc, recency desc, callback_date asc, frequency desc

endTry using UNION ALL.

-PatP

Query word starting with....from string caught in web app

Hi all
This should be easy for one of you pros out there...
I take a single string(one letter) from a textbox and want to query my
database for a name that starts with that letter.
I know that the correct synthax when you know the letter is:

WHERE FirstName LIKE 'O%'

but when I have this:
*****************
Dim lastname As String
lastname = txtlastname.Text

Dim strSQL As String = "SELECT * FROM [" & PubName & "] WHERE Last_Name
like ' txtlastname % ' "
**********************************************
It doesn't seem to work since my datagrid is empty afterwards. I also
tried:
***************
Dim strSQL As String = "SELECT * FROM [" & PubName & "] WHERE Last_Name
like '" & txtlastname & " % ' "
****************
without succes...
It's probably just a synthax error on my part but I don'T know were..
THanks guys!!
JMTI got it to work with my variable 'lastname'
Got the answer form another forum, here's how:

And Last_Name like '" & lastname & "%'"

If it can help someone!!
JMT|||It may just be a posting error, but it looks as if you have a couple of
spaces in your string construction; the second looks closer to correct:

Dim strSQL As String = "SELECT * FROM [" & PubName & "] WHERE
Last_Name
like '" & txtlastname & "%'

Assuming that PUBNAME is a table named TABLE, and txtLastName = A, then
the final SQL String should look like:

SELECT * FROM [TABLE] WHERE Last_Name LIKE 'A%',

Stu|||I got it to work with my variable 'lastname'
Got the answer form another forum, here's how:

And Last_Name like '" & lastname & "%'"

If it can help someone!!
JMT

Query word starting with....from string caught in web app

Hi all
This should be easy for one of you pros out there...
I take a single string(one letter) from a textbox and want to query my
database for a name that starts with that letter.
I know that the correct synthax when you know the letter is:

WHERE FirstName LIKE 'O%'

but when I have this:
*****************
Dim lastname As String
lastname = txtlastname.Text

Dim strSQL As String = "SELECT * FROM [" & PubName & "] WHERE Last_Name
like ' txtlastname % ' "
**********************************************
It doesn't seem to work since my datagrid is empty afterwards. I also
tried:
***************
Dim strSQL As String = "SELECT * FROM [" & PubName & "] WHERE Last_Name
like '" & txtlastname & " % ' "
****************
without succes...
It's probably just a synthax error on my part but I don'T know were..
THanks guys!!
JMTIt may just be a posting error, but it looks as if you have a couple of
spaces in your string construction; the second looks closer to correct:

Dim strSQL As String = "SELECT * FROM [" & PubName & "] WHERE
Last_Name
like '" & txtlastname & "%'

Assuming that PUBNAME is a table named TABLE, and txtLastName = A, then
the final SQL String should look like:

SELECT * FROM [TABLE] WHERE Last_Name LIKE 'A%',

Stu

Query with single quotes using OPENROWSET

I'm trying to pass through a SQL statement to an Oracle database using
OPENROWSET. My problem is that I'm not sure of the exact syntax I need
to use when the SQL statement itself contains single quotes.
Unfortunately, OPENROWSET doesn't allow me to use parameters so I can't
get around the problem by assigning the SQL statement to a parameter of
type varchar or nvarchar as in

SELECT *
FROM OPENROWSET('MSDAORA','myconnection';'myusername';' mypassword',
@.chvSQL)

I tried doubling the single quotes as in

SELECT *
FROM OPENROWSET('MSDAORA','myconnection';'myusername';' mypassword',
'SELECT *
FROM A
WHERE DateCol > To_Date(''2002-12-01'', ''yyyy-mm-dd'')'
)

But that didn't work. Is there a way out of this?

Thanks,

Bill E.
Hollywood, FLI take it back--doubling the single quotes is working fine now. I
don't know why I had trouble before.

Bill

query to find a sum of char values present in different rows

the table is
col1
---
M
S
S
Q
L

The expected Result is

Name
----
MSSQL

I need a single query which solves the above problem. please help me out?

Quote:

Originally Posted by Anu139

the table is
col1
---
M
S
S
Q
L

The expected Result is

Name
----
MSSQL

I need a single query which solves the above problem. please help me out?

I got the answer

declare @.name varchar(50)

select @.name = coalesce( @.name,'' )+ col1 from tab_name

select @.name 'Name'

Query to create a countable list using a single value from table

Hi,
from a single numeric value, say 'n', in a field in a table, i'd like to create a SELECT query that produces a list from 1 to n, e.g
if the value was 6, I would like selected back:
1
2
3
4
5
6
Any pointers?
GregWhy?|||I'm developing a system using an Enterprise product, Metastorm e-work, that sits above SQL server. Given a Quantity value, I want to make a drop down list of the values from 1 to Quantity using the form designer that comes with the product, to allow a user to change this value.

I'd like to do something more sophisticated than my posted request, but if there is SQL code available to do this, it will get me on the way.

g.|||CREATE a numbers TABLE...

SELECT Col1 from myTable99
WHERE Col1 <= @.value

You'll use it for more than just this...|||Cheers.|||Originally posted by Brett Kaiser
You'll use it for more than just this...

so true

similar example: generating sequential dates
see The integers table (http://searchdatabase.techtarget.com/ateQuestionNResponse/0,289625,sid13_cid569539_tax285649,00.html)
(site registration may be required, but it's free)

Query suddenly becomes very slow

Hi. This is a bit of an open ended question which I don't think there is a
single answer to but I woud appreciate suggestions on though. For the last
few months a stored procedure has been taking between 2-12 seconds to
complete. It runs several times a day. Suddenly today in the space of a
minute the stored procedure went from 2 seconds to complete to 35 minutes!
It was inexplicable. There was nothing else running on the machine (that I
know of) at the time. Further invocations continued to take at least 10
minutes to run. Only after bouncing the SQL Server instance did the stored
procedure go back to its normal timeframe of 2-12 seconds.
Nothing seemed out of the ordinary - it was just a regular day's processing
as had been many times in the past. How can there be such a huge decrease in
performance so rapidly? What could have caused it?
--
McGeeky
http://mcgeeky.blogspot.comhave you updated you table statistics?
and defrag your indexes
"McGeeky" <anon@.anon.com> wrote in message
news:OnY8TKUSGHA.5656@.TK2MSFTNGP11.phx.gbl...
> Hi. This is a bit of an open ended question which I don't think there is a
> single answer to but I woud appreciate suggestions on though. For the last
> few months a stored procedure has been taking between 2-12 seconds to
> complete. It runs several times a day. Suddenly today in the space of a
> minute the stored procedure went from 2 seconds to complete to 35 minutes!
> It was inexplicable. There was nothing else running on the machine (that I
> know of) at the time. Further invocations continued to take at least 10
> minutes to run. Only after bouncing the SQL Server instance did the stored
> procedure go back to its normal timeframe of 2-12 seconds.
> Nothing seemed out of the ordinary - it was just a regular day's
> processing
> as had been many times in the past. How can there be such a huge decrease
> in
> performance so rapidly? What could have caused it?
> --
> McGeeky
> http://mcgeeky.blogspot.com
>
>|||The database is configured to auto update stats.
Would not updating stats cause performance to drop off a cliff all of a
sudden?
--
McGeeky
http://mcgeeky.blogspot.com
"Jéjé" <willgart@.BBBhotmailAAA.com> wrote in message
news:#v41#IVSGHA.3192@.TK2MSFTNGP09.phx.gbl...
> have you updated you table statistics?
> and defrag your indexes
> "McGeeky" <anon@.anon.com> wrote in message
> news:OnY8TKUSGHA.5656@.TK2MSFTNGP11.phx.gbl...
> > Hi. This is a bit of an open ended question which I don't think there is
a
> > single answer to but I woud appreciate suggestions on though. For the
last
> > few months a stored procedure has been taking between 2-12 seconds to
> > complete. It runs several times a day. Suddenly today in the space of a
> > minute the stored procedure went from 2 seconds to complete to 35
minutes!
> > It was inexplicable. There was nothing else running on the machine (that
I
> > know of) at the time. Further invocations continued to take at least 10
> > minutes to run. Only after bouncing the SQL Server instance did the
stored
> > procedure go back to its normal timeframe of 2-12 seconds.
> >
> > Nothing seemed out of the ordinary - it was just a regular day's
> > processing
> > as had been many times in the past. How can there be such a huge
decrease
> > in
> > performance so rapidly? What could have caused it?
> >
> > --
> > McGeeky
> > http://mcgeeky.blogspot.com
> >
> >
> >
>|||auto update is not enough.
a good and planned cleansing is always better.
1 client delete and reload some tables at regular basis, if we don't update
the statistics a query against these table drop from seconds to minutes.
so updating stats and defrag indexes is really important.
regarding which way you use to defrag your indexes, the system will also
update the stats at the same time.
see the BOL and search the web for more info
"McGeeky" <anon@.anon.com> wrote in message
news:utYEAPSTGHA.5924@.TK2MSFTNGP09.phx.gbl...
> The database is configured to auto update stats.
> Would not updating stats cause performance to drop off a cliff all of a
> sudden?
> --
> McGeeky
> http://mcgeeky.blogspot.com
>
> "Jéjé" <willgart@.BBBhotmailAAA.com> wrote in message
> news:#v41#IVSGHA.3192@.TK2MSFTNGP09.phx.gbl...
>> have you updated you table statistics?
>> and defrag your indexes
>> "McGeeky" <anon@.anon.com> wrote in message
>> news:OnY8TKUSGHA.5656@.TK2MSFTNGP11.phx.gbl...
>> > Hi. This is a bit of an open ended question which I don't think there
>> > is
> a
>> > single answer to but I woud appreciate suggestions on though. For the
> last
>> > few months a stored procedure has been taking between 2-12 seconds to
>> > complete. It runs several times a day. Suddenly today in the space of a
>> > minute the stored procedure went from 2 seconds to complete to 35
> minutes!
>> > It was inexplicable. There was nothing else running on the machine
>> > (that
> I
>> > know of) at the time. Further invocations continued to take at least 10
>> > minutes to run. Only after bouncing the SQL Server instance did the
> stored
>> > procedure go back to its normal timeframe of 2-12 seconds.
>> >
>> > Nothing seemed out of the ordinary - it was just a regular day's
>> > processing
>> > as had been many times in the past. How can there be such a huge
> decrease
>> > in
>> > performance so rapidly? What could have caused it?
>> >
>> > --
>> > McGeeky
>> > http://mcgeeky.blogspot.com
>> >
>> >
>> >
>>
>