| Author |
Topic |
|
Bellus
Starting Member
29 Posts |
 Posted - 2007-09-26 : 03:28:02
|
| HeyI want my output to exlude the rows where some columns is missing theire values..how can I do that? |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2007-09-26 : 04:00:28
|
| select columns from tablewhere (col1 is not null and col2 is not null and...... colN is not null)MadhivananFailing to plan is Planning to fail |
 |
|
|
Bellus
Starting Member
29 Posts |
Posted - 2007-09-26 : 04:45:41
|
| $query="SELECT id,ref_id,name FROM customers ORDER BY name where cellphone is not null"; Hey, i tried this, on different others combinations, but just got the error:Fatal error: Call to undefined method MDB2_Error::fetchRow() in /home/isabel/public_html/gui-config-0.1/classes/smarteye.class.php on line 40any ideas why?thanks a lot!:) |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-09-26 : 05:59:41
|
| Are you sure that that $query=... is what is actually on line 40 of that PHP file? |
|
|
|
Bellus
Starting Member
29 Posts |
Posted - 2007-09-26 : 06:38:14
|
| while ($row=$result->fetchRow()){is what is on line 40... |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2007-09-26 : 06:38:33
|
quote:
Originally posted by Bellus
$query="SELECT id,ref_id,name FROM customers ORDER BY name where cellphone is not null"; Hey, i tried this, on different others combinations, but just got the error:Fatal error: Call to undefined method MDB2_Error::fetchRow() in /home/isabel/public_html/gui-config-0.1/classes/smarteye.class.php on line 40any ideas why?thanks a lot!:)
Run that query in mysql and see if you get an errorMadhivananFailing to plan is Planning to fail |
|
|
|
elancaster
A very urgent SQL Yakette
1208 Posts |
Posted - 2007-09-26 : 06:43:13
|
| the where clause comes before the order by |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-09-26 : 07:30:55
|
| Good catch! (says this blind man!) |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2007-09-26 : 08:06:10
|
quote:
Originally posted by Kristen
Good catch! (says this blind man!)
But the query doesnt seem to match the requirements to select row where none of columns is nullMadhivananFailing to plan is Planning to fail |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-09-26 : 08:16:29
|
That will just be moving-problem-itis  |
|
|
|
Bellus
Starting Member
29 Posts |
Posted - 2007-09-26 : 08:41:18
|
| it worked, thanks! |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2007-09-26 : 09:18:36
|
quote:
Originally posted by Bellus
it worked, thanks!
Which worked?MadhivananFailing to plan is Planning to fail |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-09-26 : 09:47:43
|
| I expect: Moving WHERE to be in front of ORDER BY |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2007-09-26 : 10:05:25
|
quote:
Originally posted by Kristen
I expect: Moving WHERE to be in front of ORDER BY
Then I would say OP posted wrong question  MadhivananFailing to plan is Planning to fail |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-09-26 : 10:24:36
|
| Nah, I think you are being a little hard mate.OP's question:"I want my output to exlude the rows where some columns is missing theire values.."Your answer:"select columns from tablewhere (col1 is not null and col2 is not null and...... colN is not null)"What the OP tried:"SELECT id,ref_id,name FROM customers ORDER BY name where cellphone is not null"and what the OP then fixed that to (I assume):"SELECT id,ref_id,name FROM customers where cellphone is not null ORDER BY name "which is basically what you said, except in this particular instantiation there is only one column in the WHERE.Kristen |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2007-09-27 : 01:50:25
|
quote:
Originally posted by Kristen
Nah, I think you are being a little hard mate.OP's question:"I want my output to exlude the rows where some columns is missing theire values.."Your answer:"select columns from tablewhere (col1 is not null and col2 is not null and...... colN is not null)"What the OP tried:"SELECT id,ref_id,name FROM customers ORDER BY name where cellphone is not null"and what the OP then fixed that to (I assume):"SELECT id,ref_id,name FROM customers where cellphone is not null ORDER BY name "which is basically what you said, except in this particular instantiation there is only one column in the WHERE.Kristen
I agree with you MadhivananFailing to plan is Planning to fail |
|
|
|
jcelko
Esteemed SQL Purist
547 Posts |
Posted - 2007-09-28 : 21:22:33
|
| Why are you formatting data in the back end? The basic principle of a tiered architecture is that display is done in the front end and never in the back end. This is a more basic programming principle than just SQL and RDBMS. To find out if a column is all NULLs (I guess that is what you are trying to say in that vague spec) then use: SELECT MAX(col1), MAX(col2), .. , MAX(coln) FROM Foobar; the aggregate will return a NULL on those columns and your front end can do the display formatting. --CELKO--Joe Celko, SQL Guru |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-09-29 : 13:49:31
|
| Think the question is about filtering not formatting.Anyway I wouldn't agree about formatting. Put it where it sits best for the system and resources available.Usually better to try to get it in a client but then it depends how you define the client (or front end). There's usually some formatting going on in each isolated layer so why not utilise it.The art is to control the layers and know what you are aiming for and how any changes will affect it.Sometimes you don't have any control over the front end so don't have a choice.Sometimes you just want the front end to be dumb (for portability maybe) so do as much formatting as possible as early as possible.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
Exlude row with empty columns