Trouble forming SQL Queries - 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

SQL Server 2000 Forums

SQL Server Development (2000)

Trouble forming SQL Queries

Author

Topic

arty
Starting Member

13 Posts

Posted - 2005-09-28 : 00:41:42

Hi there,

Probably my problem is very simple with a pretty straightforward answer...but sometimes you get stuck around simple problems and never seem to get out!

I have database with 5 fields : 1.RecNo(Pkey,auto) 2.AssetName(not null ) 3.AssetNo(null allowed) 4.Description(null allowed) 5.MDAprroval? (null allowed-checkbox)
The database already has 500 records

I need to frame three different SQL statements to generate results in a datagrid...

1. First Query to generate all records which DO NOT have ASSETNO
2. Second query to generate all records which DO HAVE ASSETNO
3. Third query to generate all records which HAVE ASSETNO and MDAPPROVAL is checked(or true)

Aartee.
I could get a workaoundin the second query but somehow my 'Null' check for Asset No and where statement in third query is failing.

Can someone please post some help with these query formations?

Thankyou in advance for all your precious time spent in reading & replying to this post.

Kristen
Test

22859 Posts

Posted - 2005-09-28 : 00:51:28

Hi arty, Welcome to SQL Team!

Something like these perhaps?


SELECT *
FROM MyTable
WHERE NullIf(AssetNo, '') IS NULL

SELECT *
FROM MyTable
WHERE NullIf(AssetNo, '') IS NOT NULL

SELECT *
FROM MyTable
WHERE     NullIf(AssetNo, '') IS NOT NULL
      AND MDAprroval = 1

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-28 : 00:52:02

1 Select * from yourTable where AssetNo is Null
2 Select * from yourTable where AssetNo is not Null
3 Select * from yourTable where AssetNo is not Null and MDAPPROVAL = 1

You need to take some classes on how to write SQL queries

Refer these
www.sqlcourse2.com
www.sqlzoo.com

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-28 : 00:52:57

Madhivanan

Failing to plan is Planning to fail

arty
Starting Member

13 Posts

Posted - 2005-09-28 : 01:00:20

Thanks for the warm welcome to the forum madhivanan :).
Thankyou kristen for your help,

Well thats the problems, I tried it with the null, it just doesn't show up any records, the datagrid comes up blank!! It is working fine for records with asset no i.e.
"...where assetno = " &assetno
It shows heaps of records here

but fails for

"....where assetno= 'NULL'"

I tried it in several ways , I even tried it with
......where assetno="" (empty string)

but doesn't work, the datagrid comes up blank, no errors nothing, just doesn't retrieve any records.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-28 : 01:02:19

>>"....where assetno= 'NULL'"

That should be "....where assetno is NULL"

Madhivanan

Failing to plan is Planning to fail

arty
Starting Member

13 Posts

Posted - 2005-09-28 : 01:17:40

Thankyou once again for the reply....it still does not work

don't know what's going wrong, I have used null & not null hundreds of times but this time it is just not working..can you think of what the problem might be?

but hey firends, sincerely thankyou for involving your precious time in helping me out....I have another problem coming up in next post

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-28 : 01:27:54

Probably you may need this

where assetno is NULL or assetno = ''

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2005-09-28 : 01:31:48

That's why I put

WHERE NullIf(AssetNo, '') IS NULL

Try your query in some SQL tool outside of the grid to make sure you get some data, then you will know which part of the process to debug.

You have removed the quotes from NULL, right?

And you are using "IS NULL" and not "= NULL", right?

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-28 : 01:38:10

>>WHERE NullIf(AssetNo, '') IS NULL

You are Clever Kris

It seems that arty didnt execute your query

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2005-09-28 : 01:41:12

"It seems that arty didnt execute your query"

Or AssetNo is numeric or somesuch.

Need DDL and some sample data ...

Kristen

arty
Starting Member

13 Posts

Posted - 2005-09-28 : 01:43:32

Thankyou Kris,

Yes i did take off the quotes from Null and I am using Is Null but hey your solution worked just fine with 'Nullif'!

thankyou kris,thankyou madhivanan..keep looking for more from me :) I will offer help too wheever I can.

Cheers,
Aartee.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-28 : 01:50:21

>>I will offer help too wheever I can.

You are welcome Aartee

Madhivanan

Failing to plan is Planning to fail

arty
Starting Member

13 Posts

Posted - 2005-09-28 : 02:23:06

>>You are welcome Aartee :)

>>You need to take some classes on how to write SQL queries :)

dear friend

Mark twain once said...

There is no sadder sight than a young pessimist!
-Mark Twain

Please do not pass any snide remarks and discourage people to participate in the forums...sometimes it takes ages to solve simplest of problems !!

We Live More Than Once!!!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-28 : 02:30:22

>>Please do not pass any snide remarks and discourage people to participate in the forums...

Did I discourage you?
I dont think you are correct

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources