Where Statement? - 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

General SQL Server Forums

New to SQL Server Programming

Where Statement?

Author

Topic

theHydra
Starting Member

42 Posts

Posted - 2011-08-12 : 16:04:11

Hi all,

I'm looking for a way to return results for a field that meets a certain criteria, but not sure how to code it.

So here goes...

I have a description field that contain all sorts of data but not following any particular logic.

For example,

30KVA 3PH 50/60HZ P:415D S:400Y/231
1PH 60HZ ISOLATION TRANS .5KVA
35KVA 3PH 60HZ AUTO TRANSFORME
3PH 50/60HZ 55KVA P:415D S:400Y/231
1PH 60HZ AUTO TRANSFORMER 1000KVA

So the report I'm trying to write, I'd like to only return anything that has a KVA rating of 50 or below.

Based off of the above example, I'd only want

30KVA 3PH 50/60HZ P:415D S:400Y/231
1PH 60HZ ISOLATION TRANS .5KVA
35KVA 3PH 60HZ AUTO TRANSFORME

returned.

Here's some code I've been playing with, but it hasn't worked.

select * from inmast
where fdescript like '__KVA%'
--and fdescript IS <= '50'
--where fdescript
--BETWEEN '1KVA%' AND '50KVA%'

Any help would be greatly appreciated.

Thanks!

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2011-08-12 : 17:19:15

The column that contains the "KVA" rating doesn't appear to be consistent. On rows 2, 4 and 5, the KVA rating appears in a different column than in rows 1 and 3. Is there a predictable way to know where the KVA ratings would be stored? If you have a few columns where it would be stored, then you'd have to reference those columns in the where clause. Since there are no names for your columns except for fdescript, I'm going to give an example with new names. This is fairly messy because I'm having to make some assumptions. The Where clause is probably not going to be all that efficient with the amount of OR's and functions embedded in it. But it does produce the results you requested.
declare @InMast table (ColA varchar(30), ColB varchar(30), ColC varchar(30), ColD varchar(30), ColE varchar(30))
insert into @InMast (ColA, ColB, ColC, ColD, ColE)
Values
('30KVA', '3PH', '50/60HZ', 'P:415D', 'S:400Y/231'),
('1PH', '60HZ', 'ISOLATION', 'TRANS' , '.5KVA'),
('35KVA', '3PH', '60HZ', 'AUTO', 'TRANSFORME'),
('3PH', '50/60HZ', '55KVA', 'P:415D', 'S:400Y/231'),
('1PH', '60HZ', 'AUTO', 'TRANSFORMER', '1000KVA')

SELECT *,

case when ColA like ('%KVA') then CONVERT(float, REPLACE(ColA,'KVA','')) else 0 end as NumPartColA,
case when ColB like ('%KVA') then CONVERT(float, REPLACE(ColB,'KVA','')) else 0 end as NumPartColA,
case when ColC like ('%KVA') then CONVERT(float, REPLACE(ColC,'KVA','')) else 0 end as NumPartColA,
case when ColD like ('%KVA') then CONVERT(float, REPLACE(ColD,'KVA','')) else 0 end as NumPartColD,
case when ColE like ('%KVA') then CONVERT(float, REPLACE(ColE,'KVA','')) else 0 end as NumPartColE

FROM @InMast
WHERE (
(ColA like ('%KVA') AND (case when ColA like ('%KVA') then CONVERT(float, REPLACE(ColA,'KVA','')) else 0 end)<=50) OR
(ColB like ('%KVA') AND (case when ColB like ('%KVA') then CONVERT(float, REPLACE(ColB,'KVA','')) else 0 end)<=50) OR
(ColC like ('%KVA') AND (case when ColC like ('%KVA') then CONVERT(float, REPLACE(ColC,'KVA','')) else 0 end)<=50) OR
(ColD like ('%KVA') AND (case when ColD like ('%KVA') then CONVERT(float, REPLACE(ColD,'KVA','')) else 0 end)<=50) OR
(ColE like ('%KVA') AND (case when ColE like ('%KVA') then CONVERT(float, REPLACE(ColE,'KVA','')) else 0 end)<=50)
)

theHydra
Starting Member

42 Posts

Posted - 2011-08-12 : 17:27:29

You are correct, it is not consistent, which is one reason I'm having such a hard time with it.

And no, there is no predictable way it would be stored. Again, adding to the difficulty of this task.

With that in mind, is what I'm trying to do still doable?

Thanks for the reply.

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2011-08-12 : 17:31:56

It's doable...if you run my query, you should get the results you requested. However, it's probably not practical if there are a lot of possible columns where the KVA could be stored. How many possible columns could the data be stored in? If it's 5-10 columns and not a huge amount of rows, then it's probably not a big deal, but if you have 30-40 columns where the KVA could be placed, then you'd have to write something like I did above for each possible column.

theHydra
Starting Member

42 Posts

Posted - 2011-08-12 : 17:45:47

Help me understand what exactly the column is.

Is the column the amount of characters?

For example:

1PH 60HZ ISOLATION TRANS .5KVA

contains 30 columns? Basically the amount of placeholders for text or numbers?

Sorry if it's a dumb question.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-08-12 : 17:47:36

Here's an alternative:

DECLARE @inmast TABLE(ID INT NOT NULL IDENTITY(1,1), fdescript VARCHAR(255) NOT NULL)
INSERT @inmast(fdescript) SELECT '30KVA 3PH 50/60HZ P:415D S:400Y/231' UNION ALL SELECT 
'1PH 60HZ ISOLATION TRANS .5KVA' UNION ALL SELECT 
'35KVA 3PH 60HZ AUTO TRANSFORME' UNION ALL SELECT 
'3PH 50/60HZ 55KVA P:415D S:400Y/231' UNION ALL SELECT 
'1PH 60HZ AUTO TRANSFORMER 1000KVA'

;WITH KVA1(ID, fdescript, KVA) AS (
	SELECT ID, fdescript, REVERSE(fdescript) + ' ' FROM @inmast WHERE fdescript LIKE '%[0-9]KVA%'),
KVA2(ID, fdescript, KVA) AS (
	SELECT ID, fdescript, SUBSTRING(KVA, CHARINDEX('AVK',KVA), CHARINDEX(' ',KVA,CHARINDEX('AVK',KVA))-CHARINDEX('AVK',KVA))
	FROM KVA1),
KVA3 (ID, fdescript, KVA) AS (
	SELECT ID, fdescript, CAST(REVERSE(REPLACE(kva,'AVK','')) AS FLOAT) FROM KVA2)
SELECT * FROM KVA3 where KVA<=50

I use multiple CTEs to avoid nesting and repeating certain expressions.

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2011-08-12 : 17:52:15

It's not dumb, just probably need to take a look at some online tutorials on SQL to get familiar with the syntax and vocabulary.

There are tons of sites with tutorials, I like this one: http://www.w3schools.com/sql/

With that, a column is a just one of the objects that makes up a table. So the data you provided are all stored neatly in what's called a table in a database. Within a table, you have columns. If you use spreadsheets, imagine a spreadsheet as being a table. And the columns (vertical cells with headers at the top) are within a table.

Example:

TABLE: Clients
Columns:
Lastname
Firstname
DateofBirth

That data would be stored like this:

Lastname Firstname DateofBirth
Jones Harry 2000-01-01
Williams Sarah 1999-04-05

Etc

I tried to reproduce a sample of your database based on the information you provided. If you look at the declare @InMast statement, that's basically a table (variable) that I created to temporarily store your dataset.

theHydra
Starting Member

42 Posts

Posted - 2011-08-15 : 16:39:52

flameblaster:

After studying the code I think I have a better understanding of what you're doing, but I may have not been clear int he beginning.

The table is inMast, which has over 120 columns or fields.

But the data I want to filter on, the fdescript (a field) has all the information (3PH 50/60HZ 55KVA P:415D S:400Y/231) in one field, not multiple fields or columns.

So I guess how would I take the code you already gave me, and have that run against the whole inmast table, whitout having to define the values?
('30KVA', '3PH', '50/60HZ', 'P:415D', 'S:400Y/231'),
('1PH', '60HZ', 'ISOLATION', 'TRANS' , '.5KVA'),
('35KVA', '3PH', '60HZ', 'AUTO', 'TRANSFORME'),
('3PH', '50/60HZ', '55KVA', 'P:415D', 'S:400Y/231'),
('1PH', '60HZ', 'AUTO', 'TRANSFORMER', '1000KVA')

Hope that makes sense.

Thanks!

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-08-15 : 17:06:51

Does my solution work?

theHydra
Starting Member

42 Posts

Posted - 2011-08-15 : 17:39:32

Hi robvolk,

It does, but I think i'd have the same issue, the description is pre-defined, and I don't want that, I just want it to look at the fdescript field from within the inmast table.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-08-15 : 17:53:07

quote:
It does, but I think i'd have the same issue, the description is pre-defined, and I don't want that, I just want it to look at the fdescript field from within the inmast table.

I don't understand. I wrote it specifically to look at that column. You'll have to change the table name is all, in the first CTE (KVA1).

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2011-08-15 : 23:30:08

Wow, I totally missed that Hydra. I wasn't getting that it was all in one column...I need to read closer! Rob's solution (and I won't presume to explain it) is a much better approach for what you're trying to do. The substring/charindex routine is going to find the "KVA" information regardless of where it resides within the fdescript column...so I'd say it is NOT predefined, but Rob will be better suited to explain his work.

theHydra
Starting Member

42 Posts

Posted - 2011-08-16 : 09:16:55

quote:
Originally posted by robvolk

I don't understand. I wrote it specifically to look at that column. You'll have to change the table name is all, in the first CTE (KVA1).

This is the part that is confusing me:

SELECT '30KVA 3PH 50/60HZ P:415D S:400Y/231' UNION ALL SELECT
'1PH 60HZ ISOLATION TRANS .5KVA' UNION ALL SELECT
'35KVA 3PH 60HZ AUTO TRANSFORME' UNION ALL SELECT
'3PH 50/60HZ 55KVA P:415D S:400Y/231' UNION ALL SELECT
'1PH 60HZ AUTO TRANSFORMER 1000KVA'

so what should I do with that?

And when you say change the table name in the first CTE (KVA1). Where exactly do you mean? I don't totally understand.

Thanks again, your patience is much appreciated. :)

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-08-16 : 09:46:04

Sorry, I didn't explain this at all. The first part was just to set up test data in my environment, you can skip it. All you need is this:

;WITH KVA1(ID, fdescript, KVA) AS (
	SELECT ID, fdescript, REVERSE(fdescript) + ' ' FROM inmast WHERE fdescript LIKE '%[0-9]KVA%'),
KVA2(ID, fdescript, KVA) AS (
	SELECT ID, fdescript, SUBSTRING(KVA, CHARINDEX('AVK',KVA), CHARINDEX(' ',KVA,CHARINDEX('AVK',KVA))-CHARINDEX('AVK',KVA))
	FROM KVA1),
KVA3 (ID, fdescript, KVA) AS (
	SELECT ID, fdescript, CAST(REVERSE(REPLACE(kva,'AVK','')) AS FLOAT) FROM KVA2)
SELECT * FROM KVA3 where KVA<=50

I changed the red portion to match your table name (inmast) from my original (@inmast).

The process is probably over-complicated because I was concerned about matching digits preceding the KVA string. If I have time I might try a simpler solution, but from what I tested this should work.

theHydra
Starting Member

42 Posts

Posted - 2011-08-16 : 10:01:46

Okay, this is making alittle more sense, but I'm not sure what to do about my select statement.

I've played with something like this:

select * from inmast

;WITH KVA1(ID, fdescript, KVA) AS (
SELECT ID, fdescript, REVERSE(fdescript) + ' ' FROM inmast WHERE fdescript LIKE '%[0-9]KVA%'),
KVA2(ID, fdescript, KVA) AS (
SELECT ID, fdescript, SUBSTRING(KVA, CHARINDEX('AVK',KVA), CHARINDEX(' ',KVA,CHARINDEX('AVK',KVA))-CHARINDEX('AVK',KVA))
FROM KVA1),
KVA3 (ID, fdescript, KVA) AS (
SELECT ID, fdescript, CAST(REVERSE(REPLACE(kva,'AVK','')) AS FLOAT) FROM KVA2)
SELECT * FROM KVA3 where KVA<=50

but nada.

Thanks!

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-08-16 : 10:45:20

When I run my original solution, including the sample data, I get the following results:

ID   fdescript                            KVA
---- ------------------------------------ ----
1    30KVA 3PH 50/60HZ P:415D S:400Y/231  30
2    1PH 60HZ ISOLATION TRANS .5KVA       0.5
3    35KVA 3PH 60HZ AUTO TRANSFORME       35

If you run the same code, do you get these results? Just run the code I posted, don't add any other statements.

theHydra
Starting Member

42 Posts

Posted - 2011-08-16 : 11:30:05

Yes, when I run the first code you posted, it runs, but that's where I'm confused, the first set of code is 'pre-defining' what to look against, right. I'm trying to figure out how to get it to run against the whole INMAST table but filter for that <50KVA against anything in the FDESCRIPT field.

Hope that makes sense.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-08-16 : 12:24:21

Then just run the code in my 2nd post:

;WITH KVA1(ID, fdescript, KVA) AS (
	SELECT ID, fdescript, REVERSE(fdescript) + ' ' FROM inmast WHERE fdescript LIKE '%[0-9]KVA%'),
KVA2(ID, fdescript, KVA) AS (
	SELECT ID, fdescript, SUBSTRING(KVA, CHARINDEX('AVK',KVA), CHARINDEX(' ',KVA,CHARINDEX('AVK',KVA))-CHARINDEX('AVK',KVA))
	FROM KVA1),
KVA3 (ID, fdescript, KVA) AS (
	SELECT ID, fdescript, CAST(REVERSE(REPLACE(kva,'AVK','')) AS FLOAT) FROM KVA2)
SELECT * FROM KVA3 where KVA<=50

You'll get results for KVA values less than 50, assuming there are any.

theHydra
Starting Member

42 Posts

Posted - 2011-08-16 : 12:32:10

I can't, I get a Server Database Error: Invalid column name 'ID'

Don't I have to start with a SELECT statement or something, not just ;WITH KVA1......

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-08-16 : 12:41:42

quote:
I can't, I get a Server Database Error: Invalid column name 'ID'

Ahhh, OK, you'll need to remove that:

;WITH KVA1(fdescript, KVA) AS (
	SELECT fdescript, REVERSE(fdescript) + ' ' FROM inmast WHERE fdescript LIKE '%[0-9]KVA%'),
KVA2(fdescript, KVA) AS (
	SELECT fdescript, SUBSTRING(KVA, CHARINDEX('AVK',KVA), CHARINDEX(' ',KVA,CHARINDEX('AVK',KVA))-CHARINDEX('AVK',KVA))
	FROM KVA1),
KVA3 (fdescript, KVA) AS (
	SELECT fdescript, CAST(REVERSE(REPLACE(kva,'AVK','')) AS FLOAT) FROM KVA2)
SELECT * FROM KVA3 where KVA<=50

quote:
Don't I have to start with a SELECT statement or something, not just ;WITH KVA1......

Not with a CTE, the WITH indicates the start of the expression. The SELECT comes after the last CTE is defined (see last line).

theHydra
Starting Member

42 Posts

Posted - 2011-08-16 : 12:54:09

Sorry, still not following, what needs to be removed? When I remove the ; and just run:

WITH KVA1(fdescript, KVA) AS (
SELECT fdescript, REVERSE(fdescript) + ' ' FROM inmast WHERE fdescript LIKE '%[0-9]KVA%'),
KVA2(fdescript, KVA) AS (
SELECT fdescript, SUBSTRING(KVA, CHARINDEX('AVK',KVA), CHARINDEX(' ',KVA,CHARINDEX('AVK',KVA))-CHARINDEX('AVK',KVA))
FROM KVA1),
KVA3 (fdescript, KVA) AS (
SELECT fdescript, CAST(REVERSE(REPLACE(kva,'AVK','')) AS FLOAT) FROM KVA2)
SELECT * FROM KVA3 where KVA<=50

i get a Error SQL Server Database Error: Error converting data type varchar to float.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources