SQL Queries
in [Visual Objects]
|
From: Kevin on 3 Jun 2010 14:09 I have an MS SQL Server Express table with a Primary key on the ClaimNo column. What I want to do is open a selection of records where the State column is not a specific value. The State column is a CHAR field, 1 character long. I have tried the following statements, but they all return empty record sets, even though only the first 19 rows in the table have the value R, the rest are empty. SELECT ClaimNo, PurchaseID, State FROM FUEL WHERE NOT State = 'R' SELECT ClaimNo, PurchaseID, State FROM FUEL WHERE State != 'R' SELECT ClaimNo, PurchaseID, State FROM FUEL WHERE State <> 'R' If I change the statements to show just the rows where the State = 'R', it works. What am I missing or doing wrong? Thanks in advance. Kevin
From: Roger Lawton on 3 Jun 2010 14:42 Kevin: Are the other values NULL or a blank space. If they are NULL then you have to do something like: SELECT ClaimNo, PurchaseID, State FROM FUEL WHERE State in null or State <> 'R' HTH -- Roger Lawton Product Manager SOMAX, Inc. "Kevin" <kdmurphy(a)eircom.net> wrote in message news:odSNn.122$K4.100(a)news.indigo.ie... >I have an MS SQL Server Express table with a Primary key on the ClaimNo >column. > > What I want to do is open a selection of records where the State column is > not a specific value. The State column is a CHAR field, 1 character long. > > I have tried the following statements, but they all return empty record > sets, even though only the first 19 rows in the table have the value R, > the rest are empty. > > SELECT ClaimNo, PurchaseID, State FROM FUEL WHERE NOT State = 'R' > > SELECT ClaimNo, PurchaseID, State FROM FUEL WHERE State != 'R' > > SELECT ClaimNo, PurchaseID, State FROM FUEL WHERE State <> 'R' > > If I change the statements to show just the rows where the State = 'R', it > works. > > What am I missing or doing wrong? > > Thanks in advance. > > Kevin >
From: Kevin on 3 Jun 2010 16:53 Roger, I had tried the '<>' already, thinking it would be like a DBF. Using the 'null' test was enough. No entry had been added to the fields. Thanks for that. Kevin "Roger Lawton" <nsproger(a)nspsomax.com> wrote in message news:hu8t39$d3o$1(a)speranza.aioe.org: > Kevin: > > Are the other values NULL or a blank space. If they are NULL then you have > to do something like: > > SELECT ClaimNo, PurchaseID, State FROM FUEL WHERE State in null or State <> > 'R' > > HTH > > > -- > Roger Lawton > Product Manager > SOMAX, Inc. > > > > "Kevin" <kdmurphy(a)eircom.net> wrote in message > news:odSNn.122$K4.100(a)news.indigo.ie... > >I have an MS SQL Server Express table with a Primary key on the ClaimNo > >column. > > > > What I want to do is open a selection of records where the State column is > > not a specific value. The State column is a CHAR field, 1 character long. > > > > I have tried the following statements, but they all return empty record > > sets, even though only the first 19 rows in the table have the value R, > > the rest are empty. > > > > SELECT ClaimNo, PurchaseID, State FROM FUEL WHERE NOT State = 'R' > > > > SELECT ClaimNo, PurchaseID, State FROM FUEL WHERE State != 'R' > > > > SELECT ClaimNo, PurchaseID, State FROM FUEL WHERE State <> 'R' > > > > If I change the statements to show just the rows where the State = 'R', it > > works. > > > > What am I missing or doing wrong? > > > > Thanks in advance. > > > > Kevin > >
From: Roger Lawton on 3 Jun 2010 18:22 Kevin Glad it worked. The reason that you may want to include the <> part is to get those items that have a value other than 'R' but not NULL. Roger "Kevin" <kdmurphy(a)eircom.net> wrote in message news:CDUNn.123$K4.153(a)news.indigo.ie... > Roger, > > I had tried the '<>' already, thinking it would be like a DBF. Using the > 'null' test was enough. No entry had been added to the fields. > > Thanks for that. > > Kevin > > "Roger Lawton" <nsproger(a)nspsomax.com> wrote in message > news:hu8t39$d3o$1(a)speranza.aioe.org: > >> Kevin: >> >> Are the other values NULL or a blank space. If they are NULL then you >> have >> to do something like: >> >> SELECT ClaimNo, PurchaseID, State FROM FUEL WHERE State in null or State >> <> >> 'R' >> >> HTH >> >> >> -- >> Roger Lawton >> Product Manager >> SOMAX, Inc. >> >> >> >> "Kevin" <kdmurphy(a)eircom.net> wrote in message >> news:odSNn.122$K4.100(a)news.indigo.ie... >> >I have an MS SQL Server Express table with a Primary key on the ClaimNo >> >column. >> > >> > What I want to do is open a selection of records where the State column >> > is >> > not a specific value. The State column is a CHAR field, 1 character >> > long. >> > >> > I have tried the following statements, but they all return empty record >> > sets, even though only the first 19 rows in the table have the value >> > R, >> > the rest are empty. >> > >> > SELECT ClaimNo, PurchaseID, State FROM FUEL WHERE NOT State = 'R' >> > >> > SELECT ClaimNo, PurchaseID, State FROM FUEL WHERE State != 'R' >> > >> > SELECT ClaimNo, PurchaseID, State FROM FUEL WHERE State <> 'R' >> > >> > If I change the statements to show just the rows where the State = 'R', >> > it >> > works. >> > >> > What am I missing or doing wrong? >> > >> > Thanks in advance. >> > >> > Kevin >> > >
From: Geoff Schaller on 3 Jun 2010 18:39 Kevin, You need to pay attention to your database design. There are two things you can do to 'create' DBF like behaviour: 1. declare each of your columns (except dates) in the create statement as NOT NULL 2. provide a default, especially for numerics (of zero) and characters (empty string) and 0 for logics etc. Dates are problematic and the only ones for which you should check for <= some value and not null. But you can also create your own SQL function Empty() which could do all this. Geoff "Kevin" <kdmurphy(a)eircom.net> wrote in message news:odSNn.122$K4.100(a)news.indigo.ie: > I have an MS SQL Server Express table with a Primary key on the ClaimNo > column. > > What I want to do is open a selection of records where the State column > is not a specific value. The State column is a CHAR field, 1 character > long. > > I have tried the following statements, but they all return empty record > sets, even though only the first 19 rows in the table have the value R, > the rest are empty. > > SELECT ClaimNo, PurchaseID, State FROM FUEL WHERE NOT State = 'R' > > SELECT ClaimNo, PurchaseID, State FROM FUEL WHERE State != 'R' > > SELECT ClaimNo, PurchaseID, State FROM FUEL WHERE State <> 'R' > > If I change the statements to show just the rows where the State = 'R', > it works. > > What am I missing or doing wrong? > > Thanks in advance. > > Kevin
|
Pages: 1 Prev: OwnerDraw StatusBar Next: v2obase.dll for VO 2.8 (VO to Oracle) |