Database

31-Mar-08 1:13

hi there,
i m trying to retrieve data from one table with 2 condition in 2 queries, here is an example

select id, count(Number) from myTable where Number < 10 group by id order by id
select id, count(Number) from myTable where Number >= 77 and Number <=100 group by id order by id

the result is coming what expected but i want to run only single query for single result
for example
the result is coming :

QUERY 1
ID----(Column Name)
10----66
14----3
19----44
30----22

QUERY 2
ID----(Column Name)
30----45
40----74
55----43
60----44

what i want :

ID----(Column Name)----(Column Name)
10------66--------------0
14------3---------------0
19------44--------------0
30------22--------------45
40------0---------------74
55------0---------------43
60------0---------------44

i used join but didnt got it work
any idea?

thanks

TVMU^P[[IGIOQHG^JSH`A#@`RFJ\c^JPL>;"[,*/|+&WLEZGc`AFXc!L
%^]*IRXD#@GKCQ`R\^SF_WcHbORY87??6?N8?BcRAV\Z^&SU~%CSWQ@#2
W_AD`EPABIKRDFVS)EVLQK)JKSQXUFYK[M`UKs*$GwU#(QDXBER@CBN%
Rs0~53%eYrd8mt^7Z6]iTF+(EWfJ9zaK-i?TV.C\y<p?jxsg-b$f4ia>
--------------------------------------------------------
128 bit encrypted signature, crack if you can

Rob Philpott31-Mar-08 1:20

Rob Philpott

31-Mar-08 1:20

Are you using Sql Server 2005?

Regards,
Rob Philpott.

Xmen Real 31-Mar-08 1:22

31-Mar-08 1:22

yupp!!!

Rob Philpott31-Mar-08 1:26

Rob Philpott

31-Mar-08 1:26

Cool.

There may be a better way to do this, but here's what I'd do. In the old world you could create a view to represent each query and then join them together, but you can use Common Table Expressions in 2005 (http://msdn2.microsoft.com/en-us/library/ms190766.aspx[^]) which are similar.

So, create the two queries as you have them with the with keyword. Then to combine the two results set you want a full outer join with an isnull to check for null ids resulting from the join and to set nulls to zeros on the count.

Does that make sense?

Regards,
Rob Philpott.

Xmen Real 31-Mar-08 1:30

31-Mar-08 1:30

yeah, i think i have to pick my old books, i will try and thanks Smile | :)

current date query

Pankaj Garg30-Mar-08 19:41

Pankaj Garg

30-Mar-08 19:41

SELECT Dat
FROM dbo.tblkmreading
WHERE DAT = GETDATE()
i have records pertaining to current date , the above query is not fetching the records.Please rectify it

If you have an apple & I have an apple and we exchange our apples, then each of us will still have only one apple but if you have an idea & I have an idea and we exchange our ideas, then each of us will have two ideas!

Andy_L_J30-Mar-08 20:14

Andy_L_J

30-Mar-08 20:14

Thie GETDATE() Function returns the time now ie: 3/30/2008 12.35.05...

You will need to parse the GETDATE() result to return just MM/DD/YYYY and use his in your WHERE clause.

I don't speak Idiot - please talk slowly and clearly

Pankaj Garg30-Mar-08 20:36

Pankaj Garg

30-Mar-08 20:36

select dat from tblkmreading where dat = convert(varchar(10) , getdate() , 103)

still getting the following error
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

dat is a datetime type
this is the another way i did , but no record is comming

select dat from tblkmreading where dat = convert(datetime , getdate() , 103)

Andy_L_J30-Mar-08 21:43

Andy_L_J

30-Mar-08 21:43

Is the data stored as DateTime dataType in the database? You cant expect that your query will run at the exact time that a record is written.

In this case you may have to change your Where clause to BETWEEN and look from 12:00:00am to 11:59:00pm. Or Between 3/31/2002 12:00:00 And 4/1/2008 12:00:00

Sorry, I dont have a query editor handy to check this out Frown | :(

DECLARE @Start DateTime
SET @Start = '3/31/2008'

DECLARE @End DateTime
SET @End = '4/1/2008'

SELECT [Date] FROM TestData
Where [Date] Between @Start and @End

I don't speak Idiot - please talk slowly and clearly

Ashfield30-Mar-08 21:17

Ashfield

30-Mar-08 21:17

Try this

declare @Now datetime

select @Now = convert(varchar,getdate(),103)

SELECT Dat
FROM dbo.tblkmreading
WHERE DAT >= @Now

I suspect your Dat column contains a time as well as a date. The above code trims off the time from getdate.

Bob
Ashfield Consultants Ltd

Pankaj Garg30-Mar-08 21:27

Pankaj Garg

30-Mar-08 21:27

dat is of datetime type
still occuring the error
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

Ashfield30-Mar-08 23:20

Ashfield

30-Mar-08 23:20

What version of SQL Server are you using? My code works fine with 2000 and 2005.

Bob
Ashfield Consultants Ltd

ORA-12528: TNS:listener: all appropriate instances are blocking new connections how could i solve this probelm

Sankar Komma 2-Apr-08 11:10

Sankar Komma

2-Apr-08 11:10

If Dat is date time format in your table then use following query

SELECT Dat
FROM dbo.tblkmreading
WHERE convert(varchar,Dat,103) = convert(varchar,GETDATE(),103)

If Dat is varchar in your table then use following query

SELECT Dat
FROM dbo.tblkmreading
WHERE convert(varchar,convert(datetime,Dat),103) = convert(varchar,GETDATE(),103)

smile

lakshmichawala30-Mar-08 19:17

lakshmichawala

30-Mar-08 19:17

iam using oracle 10g and iam getting this problem.plz tell me steps that how could i solve this problem

ORA-12528: TNS:listener: all appropriate instances are blocking new connections

Restoring database file problem

hari4ur30-Mar-08 18:57

hari4ur

30-Mar-08 18:57

Hai,

I am hari,

I have a .MDF file, i want to restore this file into sqlserver 2005. How to restore this file give me clear process.....urgent please
thanks
pinna

Re: Restoring database file problem

A Wong31-Mar-08 4:58

A Wong

31-Mar-08 4:58

Right click on "Databases" and select "Attach". Follow instructions from there on.

Re: Restoring database file problem

hari4ur31-Mar-08 5:46

hari4ur

31-Mar-08 5:46

Hai,

Thanks for your reply its working

Thanks
pinna

data bace problem

asha_s30-Mar-08 7:45

asha_s

30-Mar-08 7:45

please i need help WTF | :WTF:

i have teble withe the flowing colums (slotid,sectionid)
the primery key is bothe i have the sectionid only
and i want to update the slotid withe diffrent values for the same section
i use vb.net and i do not now how i spend 2 week to fand slotion please helpe if you can i use diffrent ways D'Oh! | :doh:

datatable expression column

saltcode29-Mar-08 11:19

saltcode

29-Mar-08 11:19

Hi,

I have a datatable with 3 expression column bind to datagridview with a bindingsource but the column are not displayed in the datagridview.
when i debug, it was throwing a StronglyTypedException and the innerexception is InvalidCastException "can not convert dbnull to decimal".
Please help.

Thanks in Advance

Lookman

Re: datatable expression column

Xmen Real 31-Mar-08 1:26