Solved

Or operator does not bring back data as expected in SQL Server 2000.

Posted on 2007-03-28
18
212 Views
Last Modified: 2008-02-01
The query I have is supposed to return data greater than 30 characters in all fields queried against.
The first field displays the data greater than 30 characters, but the other fields display data greater than 30 or less than 30.  The query does not work.
Here is the query:

select regline1,regline2,regline3,regline4,regline5,regline6
from tblAmp
where (Len(regline1)> 30 or (Len(regline2)> 30) and (Len(regline3)> 30)) or (Len(regline4)> 30) or (Len(regline5)>30) or (Len(regline6)>30)))


Please help.
0
Comment
Question by:donnie919101
  • 7
  • 5
  • 2
  • +3
18 Comments
 
LVL 35

Expert Comment

by:Raynard7
ID: 18813758
hi,

your brackets to not look like they are correctly organised.

the middle and statement does not look consistent, you may want to check what exactly you want to compare, I'm not sure exactly what you want from the description given.
0
 

Author Comment

by:donnie919101
ID: 18813953
For regline1 I want to display the string data in the field greater than 30 characters.
Then I want to do the same for the rest of the other fields.
If the data in regline1 is greater than 30 characters then I need to display the data in the field regline1.
If the data in regline2 is greater than 30 characters then I need to display the data in the field regline2.
If the data in regline3 is greater than 30 characters then I need to display the data in the field regline3.
If the data in regline4 is greater than 30 characters then I need to display the data in the field regline4.
If the data in regline5 is greater than 30 characters then I need to display the data in the field regline5.
If the data in regline6 is greater than 30 characters then I need to display the data in the field regline6.

Each column (field) should have it's own set of data.



where (Len(regline1)> 30 or (Len(regline2)> 30) and (Len(regline3)> 30)) or (Len(regline4)> 30) or (Len(regline5)>30) or (Len(regline6)>30)))
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 18813985
I count 12 opening brackets and 14 closing brackets in that WHERE clause so I don't know how it could actually be running.

If the data in regline1 is greater than 30 characters in length but the rest aren't, do you just want to see a single record with regline1 populated but with NULLS in the other columns?

If every regline is < 30 characters, do you want to see no record or do you want to see an empty record?

The where clause effects whether a record is returned. It resolves to True or False - the record is shown or it isn't. That record will have all fields in it.
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 6

Assisted Solution

by:psv1973
psv1973 earned 25 total points
ID: 18814129
You description of what you want to show is different form what you're query says it should do Try formatting you're query like this and then you should be able to figure it out.

select regline1,regline2,regline3,regline4,regline5,regline6
from tblAmp
where  ( Len(regline1)> 30)  or (Len(regline2)> 30) )
             and (        (Len(regline3)> 30)
                         or (Len(regline4)> 30)
                         or (Len(regline5)>30)
                         or (Len(regline6)>30)
                     )
             )

If I look at your description, then I can not fgure out what you really wan(data greater than 30 or less than 30, what do you mean?):

The query I have is supposed to return data greater than 30 characters in all fields queried against.
The first field displays the data greater than 30 characters, but the other fields display data greater than 30 or less than 30.  The query does not work.
Here is the query:

0
 

Author Comment

by:donnie919101
ID: 18814186
     Regline1                         Regline2                           Regline3                         Regline4                   
characters >30            characters >30                      characters >30            characters >30
If not then blank          If not then blank                       If not then blank        If not then blank
0
 
LVL 30

Accepted Solution

by:
nmcdermaid earned 75 total points
ID: 18814268
Then you dont actually want a where clause. You want a case statement.


select
CASE WHEN LEN(regline1) > 30 THEN regline1 ELSE '' END As regline1
CASE WHEN LEN(regline2 > 30 THEN regline2 ELSE '' END As regline2
CASE WHEN LEN(regline3) > 30 THEN regline3 ELSE '' END As regline3
...

..


from tblAmp
0
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 25 total points
ID: 18814961
I think mmcdermaid has the correct solution.
I would add the where clause to remove rows where all the 6 fields are shorter than 30:


select
CASE WHEN LEN(regline1) > 30 THEN regline1 ELSE '' END As regline1
CASE WHEN LEN(regline2 > 30 THEN regline2 ELSE '' END As regline2
CASE WHEN LEN(regline3) > 30 THEN regline3 ELSE '' END As regline3
CASE WHEN LEN(regline4) > 30 THEN regline4 ELSE '' END As regline4
CASE WHEN LEN(regline5) > 30 THEN regline5 ELSE '' END As regline5
CASE WHEN LEN(regline6) > 30 THEN regline6 ELSE '' END As regline6
from tblAmp
where (
     Len(regline1) > 30
or  Len(regline2)> 30
or  Len(regline3)> 30
or  Len(regline4)> 30
or  Len(regline5)> 30
or  Len(regline6)> 30
)
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 18816182
<<I think mmcdermaid has the correct solution.
I would add the where clause to remove rows where all the 6 fields are shorter than 30:>>
I believe an important information is missing to correctly answer the question: What should be returned in case the length of the columns are below 30...

Just my two cents...
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18817113
Racimo: if you look at the last comment from the author above, you should find that information...
just my 2c
0
 

Author Comment

by:donnie919101
ID: 18820070
Server: Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'CASE'.

select regline1,regline2,regline3,regline4,regline5,regline6
CASE WHEN LEN(regline1) > 30 THEN regline1 ELSE '' END As regline1
CASE WHEN LEN(regline2 > 30 THEN regline2 ELSE '' END As regline2
CASE WHEN LEN(regline3) > 30 THEN regline3 ELSE '' END As regline3
CASE WHEN LEN(regline4) > 30 THEN regline4 ELSE '' END As regline4
CASE WHEN LEN(regline5) > 30 THEN regline5 ELSE '' END As regline5
CASE WHEN LEN(regline6) > 30 THEN regline6 ELSE '' END As regline6
from tblAmp
where (
     Len(regline1) > 30
or  Len(regline2)> 30
or  Len(regline3)> 30
or  Len(regline4)> 30
or  Len(regline5)> 30
or  Len(regline6)> 30
)

0
 

Author Comment

by:donnie919101
ID: 18820450
Server: Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'CASE'.

select regline1,regline2,regline3,regline4,regline5,regline6
CASE WHEN LEN(regline1) > 30 THEN regline1 ELSE '' END As regline1
CASE WHEN LEN(regline2 > 30 THEN regline2 ELSE '' END As regline2
CASE WHEN LEN(regline3) > 30 THEN regline3 ELSE '' END As regline3
CASE WHEN LEN(regline4) > 30 THEN regline4 ELSE '' END As regline4
CASE WHEN LEN(regline5) > 30 THEN regline5 ELSE '' END As regline5
CASE WHEN LEN(regline6) > 30 THEN regline6 ELSE '' END As regline6
from tblAmp
where (
     Len(regline1) > 30
or  Len(regline2)> 30
or  Len(regline3)> 30
or  Len(regline4)> 30
or  Len(regline5)> 30
or  Len(regline6)> 30
)

Help please.
0
 

Author Comment

by:donnie919101
ID: 18820715
select
CASE
WHEN LEN(regline1) > 30
      THEN regline1
   ELSE ''
END As regline1,
CASE
WHEN LEN(regline2) > 30
      THEN regline2
   ELSE ''
END As regline2,
CASE
WHEN LEN(regline3) > 30
      THEN regline3
   ELSE ''
END As regline3,
CASE
WHEN LEN(regline4) > 30
      THEN regline4
   ELSE ''
END As regline4,
CASE
WHEN LEN(regline5) > 30
      THEN regline5
   ELSE ''
END As regline5,
CASE
WHEN LEN(regline6) > 30
      THEN regline6
   ELSE ''
END As regline6
from tblAmp
where (
     Len(regline1) > 30
or  Len(regline2)> 30
or  Len(regline3)> 30
or  Len(regline4)> 30
or  Len(regline5)> 30
or  Len(regline6)> 30)

This works.  The only problem is that I there are blanks where the record is not greater than 30 characters.
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 18821802
angelIII
<<Racimo: if you look at the last comment from the author above, you should find that information...
just my 2c>>
Apologies I have overlooked that part...;)

<< The only problem is that I there are blanks where the record is not greater than 30 characters.>>
Was'nt that what you demanded?


0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18822029
> The only problem is that I there are blanks where the record is not greater than 30 characters.
what do you want instead ? null?
then replace '' by NULL in the code.
0
 

Author Comment

by:donnie919101
ID: 18824312
I would like all the columns to shift up where there is a blank.
 Is this possible?
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18824913
>I would like all the columns to shift up where there is a blank.
are you aware that that would mean a variable number of columns returned (which is not possible by database means).
you should do that in the front-end code.
in sql, this would be much too complicated code, although possible (assuming that you put nulls in the trailing columns instead)
0
 

Author Comment

by:donnie919101
ID: 18826328
I was't sure about lthe variable number part.  Thanks for clearing that up for me.
Thanks for all your help.

So who gets the points.

0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18828252
>So who gets the points.
those that helped to solve your problem.
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

791 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question