Workaround for rendering of a view

I have a view to get table and column names, with an extra field added to extract only the number portion of the column name and make it an integer in another column.

SELECT T.[name] AS [SITE_NAME] ,
AC.[name] AS [LOGGER_FIELD] ,
CONVERT(INT, RIGHT(AC.name, 6)) AS SITE_ID
FROM sys.[tables] AS T
INNER JOIN sys.[all_columns] AC ON T.[object_id] = AC.[object_id]
INNER JOIN sys.[types] TY ON AC.[system_type_id] = TY.[system_type_id]
AND AC.[user_type_id] = TY.[user_type_id]
WHERE AC.[system_type_id] = 59
AND AC.name NOT LIKE '%%'
AND ( AC.name LIKE 'X0%'
OR AC.name LIKE 'A0%'
OR AC.name LIKE 'G0%' );

This view does what I want, and returns like so:
image

Later on, when the query is used with a WHERE clause on the converted column as an integer, it is using the entire data set, not paired down to the columns I think I have, so it tries to convert the non-numeric columns.

SELECT * FROM dbo.LN_TABLES_COLUMNS_V WHERE SITE_ID= 17588;
Conversion failed when converting the nvarchar value 'IN_Tot' to data type int.

My thought about using a view to do the conversion and eliminate unwanted columns is that the end user would already have a paired down set of data to work on. A query without a where on the converted column works fine.

Is there another way around this?

that might mean one of the columns you are attempting to convert is not being fully converted to numeric value

CONVERT(INT, RIGHT(AC.name, 6)) AS SITE_ID

there could be trailing or leading spaces in there.

The where clause in the create view removes those names that can't be converted. If I query against the view without a WHERE clause, or on any other column than the one being converted, I get the correct values back, and they are all INT.

If in the WHERE clause I query the converted column, it's not using the paired down set of only the INT data, it's using the full data set and trying to do the conversion on all of them.

My thought in creating the view is that I'd be working with an already rendered data set without those alpha column names. For example, the complete view returns column names that being with X,A or G without an '_'. If a WHERE is added to that query, it's trying to convert a column that begins with R, it's not working with the paired down set.

is this the view?

LN_TABLES_COLUMNS_V

Yes.

CREATE VIEW dbo.ln_tc_test_v AS
WITH colvals AS
(SELECT AC.object_id,
AC.[name] AS [LOGGER_FIELD] ,
CONVERT(INT,RIGHT(AC.name,6)) AS SITE_ID
FROM sys.all_columns AC
WHERE AC.[system_type_id] = 59
AND AC.name NOT LIKE '%[_]%'
AND ( AC.name LIKE 'X0%'
OR AC.name LIKE 'A0%'
OR AC.name LIKE 'G0%'
))
SELECT T.[name] AS [SITE_NAME] ,
cv.LOGGER_FIELD ,
cv.SITE_ID
FROM sys.[tables] AS T
INNER JOIN colvals cv ON T.[object_id] = cv.[object_id] ;

so creating a view is one thing, SQL wont complain, but using the view is another thing hence sql is complaining. I think the issue is when you are trying to convert a primary key column?

No, it's just a column name on a user-defined table, which comes from the application.
In the view, they convert properly and are good.
As you say, when using the view it's not using the proper data. It's like in the execution plan the query execution is first, and then where clause that eliminates the bad data comes after, so it's not done yet.

Maybe I need to look at another kind of object rather than a view. I'm coming from the Oracle environment, which renders differently, and sometimes it's hard to change the thinking.

ok I sere what the issue is, you are trying to convert to int varchar values of the column names .cant do that

It's controlled as I know the application column names are going to be something like 'X0017588' where I know the first digit is the numeric, either an A, G or X. I get rid of those and make it a numeric and the leading zeros fall off. It works great until a query is executed on the column that I'm converting. Without that where clause, I get back what I want.

time for you to do some forensic work. as warned previously this is what happens when the process is not standard, it is what it is. it is what you have inherited. try dumping the content of the view into a table without using a where clause

select * into cleanwater_h20_h20 from ln_tc_test_v 

hope you do not have a table called cleanwater_h20_h20 :open_mouth:

Yes, I'll likely have to change what type of object I'm using. A view would be great because it should always be up-to-date and uses lesser resources and processing.

I'd like to find a way to render the view first, and then query against that, but so far the only rendering is in a physical table. It can't be a temp table because applications will use it from different clients and different times.

I will keep this thread updated if I discover something that someone else could use.

this cleanwater_h20_h20 is not the final solution. it is just to investigate what is going on. please post the content and schema of the table cleanwater_h20_h20.

SELECT * FROM dbo.cleanwater_h20_h20

There are 713 rows, was trying to upload in an excel spreadsheet, but can't do that.

SITE_NAME LOGGER_FIELD SITE_ID
ROMP 89 (STN 380)_QUARTER A0017588 17588
SLAUGHTER DRAIN AT ERIE RD MANATEE COUNTY (STN 2179)_QUARTER A0890057 890057
LAKE PARKER (STN 265)_HOURLY X0024905 24905
ELDRIDGE WILDE 113B (STN 995)_HOURLY G0019654 19654
SADDLE CREEK AT P-11 ET (STN 722)_Wx15m A0838153 838153
ROMP TR 5-3 (STN 93)_Quarter A0026020 26020
STARKEY RAINFALL (STN 1243)_QUARTER A0020384 20384
CHINSEGUT HILL (STN 1157)_QUARTER A0023402 23402
LAKE RALEIGH (STN 642)_HOURLY X0019861 19861
ELDRIDGE WILDE 139G (STN 996)_HOURLY G0019716 19716
BOWLEES CREEK AT 44TH MANATEE COUNTY (STN 2181)_HOURLY X0890052 890052
ROMP TR 7-1 (STN 383)_HOURLY G0026121 26121
ROMP TR 1-2 (STN 51)_Hourly G0025083 25083
ROMP TR 1-2 (STN 51)_Hourly G0025082 25082
ROMP TR 1-2 (STN 51)_Hourly G0025084 25084
ROMP TR 1-2 (STN 51)_Hourly G0025081 25081
STATE ROAD 54 DEEP (STN 1101)_HOURLY G0022845 22845
SUGARHOUSE GLENN CREEK MANATEE COUNTY (STN 2167)_QUARTER X0890046 890046
SUGARHOUSE GLENN CREEK MANATEE COUNTY (STN 2167)_QUARTER A0890047 890047
COSME 3 (STN 978)_HOURLY G0019536 19536
ROCK LAKE (STN 1082)_HOURLY X0777894 777894
ELDRIDGE WILDE 2N (STN 997)_HOURLY G0019900 19900
ROMP TR 7-1 (STN 383)_QUARTER A0025618 25618
ROMP TR 1-2 (STN 51)_Quarterl A0025220 25220
BOWLEES CREEK AT 44TH MANATEE COUNTY (STN 2181)_QUARTER A0890053 890053
SWFWMD AT S-160 FLDN (STN 1191)_HOURLY G0018296 18296
CROSS BAR 2W CREWS LAKE (STN 1295)_HOURLY G0020532 20532
CROSS BAR 2W CREWS LAKE (STN 1295)_HOURLY G0020533 20533
ROMP 12 (STN 98)_HOURLY G0024222 24222
ROMP 12 (STN 98)_HOURLY G0024219 24219
ROMP 12 (STN 98)_HOURLY G0024218 24218
ROMP 12 (STN 98)_HOURLY G0024215 24215
ROMP 12 (STN 98)_HOURLY G0025209 25209
ROMP 12 (STN 98)_HOURLY G0024221 24221
ELDRIDGE WILDE 2N (STN 997)_QUARTER A0019526 19526
ROMP TR 7-2 (STN 81)_HOURLY G0026126 26126
ROMP TR 7-2 (STN 81)_HOURLY G0026123 26123
ROMP TR 7-2 (STN 81)_HOURLY G0026127 26127
ROMP TR 7-2 (STN 81)_HOURLY G0026125 26125
ROMP TR 7-2 (STN 81)_HOURLY G0026124 26124
ROMP TR 4-1 (STN 90)_HOURLY G0026001 26001
ROMP TR 4-1 (STN 90)_HOURLY G0026003 26003
ROMP TR 4-1 (STN 90)_HOURLY G0026004 26004
ROMP TR 4-1 (STN 90)_HOURLY G0026000 26000
ROMP TR 4-1 (STN 90)_HOURLY G0026002 26002
WARES CREEK AT 12TH AVE MANATEE COUNTY (STN 2183)_HOURLY X0890048 890048
TARPON ROAD (STN 1112)_HOURLY G0022768 22768
TARPON ROAD (STN 1112)_HOURLY G0766110 766110
CYPRESS CREEK TMR-3 (STN 571)_HOURLY G0018454 18454
CYPRESS CREEK TMR-3 (STN 571)_HOURLY G0018927 18927
ROMP 12 (STN 98)_Quarter A0025208 25208
FLINT CREEK (STN 727)_HOURLY X0018605 18605
FLINT CREEK (STN 727)_HOURLY X0019198 19198
ROMP TR 7-2 (STN 81)_Quarter A0025619 25619
ROMP TR 4-1 (STN 90)_Quarter A0025600 25600
WARES CREEK AT 12TH AVE MANATEE COUNTY (STN 2183)_QUARTER A0890049 890049
TIDEWATER (STN 1113)_HOURLY G0022980 22980
THREE SISTERS SPRINGS (STN 1199)_HOURLYPT X0780043 780043
ELDRIDGE WILDE 2S (STN 998)_HOURLY G0022759 22759
ROMP 123 (STN 372)_HOURLY G0017822 17822
FLINT CREEK (STN 727)_Quarter A0019446 19446
ROMP TR 9-3 (STN 4)_HOURLY G0018067 18067
ROMP TR 9-3 (STN 4)_HOURLY G0018065 18065
ROMP TR 9-3 (STN 4)_HOURLY G0018066 18066
ROMP TR 6-1 (STN 382)_HOURLY G0025696 25696
WITHLACOOCHEE RIVER AT TRILBY (STN 732)_HOURLY X0023546 23546
FLYING EAGLE BERM (STN 1009)_HOURLY X0777893 777893
FLYING EAGLE BERM (STN 1009)_HOURLY X0670406 670406
ROMP 123 (STN 372)_QUARTER A0018133 18133
GOLF COURSE STRUCTURE (STN 1011)_HOURLY X0740879 740879
GOLF COURSE STRUCTURE (STN 1011)_HOURLY X0740880 740880
ROMP TR SA-1 AND 11 TH STREET DEEP (STN 84)_HOURLY G0025646 25646
ROMP TR SA-1 AND 11 TH STREET DEEP (STN 84)_HOURLY G0025653 25653
ROMP TR SA-1 AND 11 TH STREET DEEP (STN 84)_HOURLY G0025650 25650
ROMP TR SA-1 AND 11 TH STREET DEEP (STN 84)_HOURLY G0025647 25647
ROMP TR SA-1 AND 11 TH STREET DEEP (STN 84)_HOURLY G0025643 25643
ROMP TR 6-1 (STN 382)_QUARTER A0025697 25697
WITHLACOOCHEE RIVER AT TRILBY (STN 732)_QUARTER A0023547 23547
ROMP 133 (STN 1379)_HOURLY G0787986 787986
ROMP 133 (STN 1379)_HOURLY G0787982 787982
HILLSBOROUGH RIVER STATE PARK PARKING LOT (STN 1013)_HOURLY G0019461 19461
HILLSBOROUGH RIVER STATE PARK PARKING LOT (STN 1013)_HOURLY G0019465 19465
ROMP TR SA-1 AND 11 TH STREET DEEP (STN 84)_Quarter A0025654 25654
ROMP TR 8-1 (STN 5)_HOURLY G0025707 25707
ROMP TR 8-1 (STN 5)_HOURLY G0025704 25704
ROMP TR 8-1 (STN 5)_HOURLY G0025705 25705
ROMP TR 8-1 (STN 5)_HOURLY G0025708 25708
ROMP TR 8-1 (STN 5)_HOURLY G0025702 25702
ROMP TR 8-1 (STN 5)_HOURLY G0025701 25701
WITHLACOOCHEE RIVER AT YANKEETOWN (STN 1143)_HOURLY X0023386 23386
HILLSBOROUGH RIVER AT FOWLER AVE (STN 1163)_QUARTER X0019223 19223
ROMP 133 (STN 1379)_Quarter A0823563 823563
SANLON RANCH (STN 1094)_HOURLY G0024897 24897
ROMP TR 8-1 (STN 5)_Quarter A0025626 25626
BIG SLOUGH (STN 953)_HOURLY G0025890 25890
BIG SLOUGH (STN 953)_HOURLY G0025891 25891
HILLSBOROUGH RIVER AT MORRIS BRIDGE (STN 1162)_HOURLY X0019245 19245
ROMP 14 (STN 91)_Hourly G0023822 23822
ROMP 14 (STN 91)_Hourly G0023820 23820
ROMP 14 (STN 91)_Hourly G0023821 23821
ROMP 14 (STN 91)_Hourly G0023823 23823
LAKE PRETTY CANAL AT STRUCTURE (STN 469)_HOURLY X0019796 19796
SARASOTA SERVICE OFFICE (STN 1201)_HOURLY G0748542 748542
SARASOTA SERVICE OFFICE (STN 1201)_HOURLY G0753498 753498
SARASOTA SERVICE OFFICE (STN 1201)_HOURLY G0753497 753497
CHANNEL A (STN 747)_HOURLY X0019759 19759
CHANNEL A (STN 747)_HOURLY X0019758 19758
HILLSBOROUGH RIVER AT SULPHUR SPRINGS (STN 1278)_HOURLY X0019206 19206
ROMP 14 (STN 91)_Quarterl A0025180 25180
LOUGHMAN (STN 1050)_HOURLY G0025144 25144
LOUGHMAN (STN 1050)_HOURLY G0025145 25145
LAKE HENRY (STN 1042)_QUARTER X0017496 17496
SR 52 AND CR 581 (STN 550)_HOURLY G0018965 18965
SR 52 AND CR 581 (STN 550)_HOURLY G0018966 18966
S-160 (STN 1192)_Quarter X0019289 19289
S-160 (STN 1192)_Quarter X0019290 19290
S-160 (STN 1192)_Quarter A0018295 18295
CHANNEL A (STN 747)_Quarter A0019516 19516
HILLSBOROUGH RIVER AT SULPHUR SPRINGS (STN 1278)_QUARTER A0019436 19436
ROMP 15 (STN 1)_HOURLY G0024177 24177
ROMP 15 (STN 1)_HOURLY G0024176 24176
ROMP 15 (STN 1)_HOURLY G0024175 24175
ROMP 112 RUTLAND (STN 370)_HOURLY G0023021 23021
ROMP 112 RUTLAND (STN 370)_HOURLY G0755810 755810
SUMTER 13 REPL (STN 1109)_HOURLY G0023032 23032
CHANNEL G (STN 748)_HOURLY X0019756 19756
CHANNEL G (STN 748)_HOURLY X0019757 19757
ROMP 15 (STN 1)_Quarter A0024117 24117
ROMP 112 RUTLAND (STN 370)_Quarter A0023022 23022
SUMTER 13 REPL (STN 1109)_QUARTER A0023029 23029
SWFWMD HEADQUARTERS RAINFALL (STN 1110)_QUARTER A0020882 20882
CYPRESS CREEK TMR-1 (STN 570)_HOURLY G0018439 18439
CYPRESS CREEK TMR-1 (STN 570)_HOURLY G0018431 18431
HILLSBOROUGH RIVER S-155 (STN 1161)_Quarter X0019144 19144
HILLSBOROUGH RIVER S-155 (STN 1161)_Quarter X0019145 19145
HILLSBOROUGH RIVER S-155 (STN 1161)_Quarter A0019437 19437
ROMP 16 (STN 48)_Hourly G0024101 24101
ROMP 16 (STN 48)_Hourly G0024104 24104
ROMP 16 (STN 48)_Hourly G0024103 24103
ROMP 16 (STN 48)_Hourly G0024105 24105
ROMP TR 125 (STN 414)_HOURLY G0022976 22976
THORNHILL RANCH (STN 1164)_HOURLY G0017715 17715
WEBSTER CITY (STN 1168)_HOURLY G0023078 23078
DEBUEL ROAD (STN 987)_HOURLY G0018814 18814
DEBUEL ROAD (STN 987)_HOURLY G0018815 18815
KEYSTONE LAKE CANAL AT STRUCTURE (STN 637)_HOURLY X0019889 19889
ROMP 16 (STN 48)_Quarterl A0025200 25200
ROMP TR 13-3 RACE TRACK RD (STN 66)_HOURLY G0019560 19560
ROMP TR 13-3 RACE TRACK RD (STN 66)_HOURLY G0019557 19557
TAMPA SERVICE OFFICE (STN 1212)_QUARTER A0704708 704708
WEBSTER CITY (STN 1168)_QUARTER A0670223 670223
JOHNSON POND (STN 1021)_HOURLY X0023582 23582
LAKE ARIETTA CANAL (STN 1029)_HOURLY X0017657 17657
ROMP 16_5 (STN 105)_HOURLY G0737414 737414
ROMP 16_5 (STN 105)_HOURLY G0024360 24360
ROMP 16_5 (STN 105)_HOURLY G0024354 24354
ROMP 16_5 (STN 105)_HOURLY G0024355 24355
ROMP 16_5 (STN 105)_HOURLY G0024357 24357
ROMP TR 13-3 RACE TRACK RD (STN 66)_Quarter A0019498 19498
TSALA APOPKA AT INVERNESS (STN 1119)_HOURLY X0023481 23481
WHITE TROUT CANAL AT STR (STN 1132)_HOURLY X0019733 19733
JOHNSON POND (STN 1021)_QUARTER A0023583 23583
LAKE CARROLL AT STRUCTURE (STN 1031)_HOURLY X0019740 19740
ROMP 16_5 (STN 105)_Quarter A0024361 24361
SAWGRASS LAKE (STN 633)_HOURLY X0021179 21179
TSALA APOPKA AT INVERNESS (STN 1119)_QUARTER A0023484 23484
WHITE TROUT CANAL AT STR (STN 1132)_QUARTER A0019507 19507
KEENE LAKE (STN 1043)_HOURLY X0019189 19189
LAKE ELLEN-LIPSEY (STN 1036)_HOURLY X0019735 19735
LAKE ELLEN-LIPSEY (STN 1036)_HOURLY X0019736 19736
ROMP 17 (STN 76)_HOURLY G0024041 24041
ROMP 17 (STN 76)_HOURLY G0024037 24037
ROMP 17 (STN 76)_HOURLY G0024038 24038
ROMP 17 (STN 76)_HOURLY G0024039 24039
ROMP 17 (STN 76)_HOURLY G0024040 24040
ROMP 17 (STN 76)_HOURLY G0024043 24043
SAWGRASS LAKE (STN 633)_QUARTER A0021191 21191
TURNER WELLS (STN 1126)_HOURLY G0777750 777750
TURNER WELLS (STN 1126)_HOURLY G0018029 18029
BANANA LAKE AT BOAT RAMP (STN 948)_HOURLY X0024786 24786
LAKE ANN-PARKER (STN 1028)_HOURLY X0019718 19718
LAKE GIBSON CANAL (STN 886)_HOURLY X0017675 17675
LAKE GIBSON CANAL (STN 886)_HOURLY X0017619 17619
DV-1 DOVER (STN 30)_Quarterl G0018798 18798
DV-1 DOVER (STN 30)_Quarterl G0018796 18796
DV-1 DOVER (STN 30)_Quarterl G0018795 18795
DV-1 DOVER (STN 30)_Quarterl G0018797 18797
DV-1 DOVER (STN 30)_Quarterl A0018298 18298
ROMP 17 (STN 76)_Quarter A0025199 25199
SAWGRASS LAKE CANAL AT STRUCTURE (STN 1158)_HOURLY X0670014 670014
SAWGRASS LAKE CANAL AT STRUCTURE (STN 1158)_HOURLY X0670015 670015
TWOMILE PRAIRIE BARN (STN 2152)_HOURLY X0023579 23579
CARGILL & BOWLING GREEN RNF (STN 1180)_HOURLY G0024946 24946
LAKE EVA (HANIES CITY) (STN 1378)_HOURLY X0017731 17731
LAKE GIBSON CANAL (STN 886)_Quarter A0017620 17620
ROMP 18 (STN 65)_HOURLY G0025879 25879
ROMP 18 (STN 65)_HOURLY G0025878 25878
SR 52 EAST NR GOWERS CORNER (STN 556)_HOURLY G0018973 18973
SR 52 EAST NR GOWERS CORNER (STN 556)_HOURLY G0670301 670301
BOWLEES CREEK AT 63RD AVENUE MANATEE COUNTY (STN 2165)_QUARTER A0890043 890043
BOWLEES CREEK AT 63RD AVENUE MANATEE COUNTY (STN 2165)_QUARTER X0890042 890042
CARGILL & BOWLING GREEN RNF (STN 1180)_QUARTER A0025191 25191
LAKE HAINES (STN 1037)_HOURLY X0025228 25228
ROMP 18 (STN 65)_Quarter A0025603 25603
ST PETE JAMES 11 (STN 1106)_HOURLY G0019597 19597
MEDARD PZ-10 (STN 2140)_HOURLY G0874190 874190
WILDWOOD II (STN 1133)_QUARTER A0023133 23133
ENGLEWOOD 14 (STN 1002)_HOURLY G0025966 25966
ENGLEWOOD 14 (STN 1002)_HOURLY G0025964 25964
LAKE HANCOCK-SADDLE CREEK (P-11) DOWNSTREAM (STN 2131)_QUARTER X0024767 24767
LAKE HANCOCK (STN 634)_HOURLY X0024532 24532
ROMP 19 (STN 13)_HOURLY G0025902 25902
ROMP 19 (STN 13)_HOURLY G0025901 25901
ROMP 19 (STN 13)_HOURLY G0025900 25900
TAMPA 15 (STN 1211)_HOURLY G0780106 780106
TAMPA 15 (STN 1211)_HOURLY G0780114 780114
TAMPA 15 (STN 1211)_HOURLY G0018704 18704
MEDARD PZ-12 (STN 2141)_HOURLY G0874191 874191
DV-2 DOVER (STN 77)_HOURLY G0018019 18019
DV-2 DOVER (STN 77)_HOURLY G0018018 18018
DV-2 DOVER (STN 77)_HOURLY G0770770 770770
LAKE ALFRED NR LAKE ALFRED (STN 1027)_HOURLY G0017652 17652
LAKE ALFRED NR LAKE ALFRED (STN 1027)_HOURLY G0670744 670744
LAKE LENA CANAL AT P-1 (STN 125)_HOURLY X0024894 24894
ROMP 22 (STN 78)_HOURLY G0025711 25711
ROMP 22 (STN 78)_HOURLY G0025712 25712
ROMP 22 (STN 78)_HOURLY G0025714 25714
ROMP 22 (STN 78)_HOURLY G0025715 25715
ROMP 22 (STN 78)_HOURLY G0025717 25717
TSALA APOPKA AT HERNANDO (1118)_HOURLY X0023609 23609
DV-2 DOVER (STN 77)_Quarter A0018173 18173
LAKE ARMISTEAD SCADA (STN 1030)_HOURLY X0019590 19590
LAKE HANCOCK-SADDLE CREEK (P-11) UPSTREAM (STN 2130)_QUARTER X0024759 24759
LAKE LOWERY (STN 1044)_HOURLY X0017535 17535
ROMP 22 (STN 78)_Quarter A0025829 25829
TSALA APOPKA AT HERNANDO (1118)_QUARTER A0023611 23611
MILL CREEK MANATEE COUNTY (STN 1818)_QUARTER A0869133 869133
MILL CREEK MANATEE COUNTY (STN 1818)_QUARTER X0869132 869132
KEYSVILLE (STN 1025)_HOURLY G0670750 670750
KEYSVILLE (STN 1025)_HOURLY G0670751 670751
LAKE HAMILTON AT STRUCTURE (STN 1038)_HOURLY X0025332 25332
LAKE HAMILTON AT STRUCTURE (STN 1038)_HOURLY X0025331 25331
LAKE HANNA (STN 1040)_HOURLY X0019178 19178
LAKE LOWERY (STN 1044)_QUARTER A0026344 26344
ROMP 23 (STN 69)_Hourly G0026219 26219
ROMP 23 (STN 69)_Hourly G0026215 26215
ROMP 23 (STN 69)_Hourly G0026216 26216
ROMP 23 (STN 69)_Hourly G0026217 26217
VAN NESS STRUCTURE (STN 1128)_HOURLY X0740887 740887
VAN NESS STRUCTURE (STN 1128)_HOURLY X0740888 740888
LAKE KELL CANAL AT STRUTURE (STN 1024)_HOURLY X0019300 19300
LAKE HAMILTON AT STRUCTURE (STN 1038)_Quarter A0025151 25151
LAKE HANNA (STN 1040)_QUARTER A0018593 18593
LAKE MAGDALENE (STN 1045)_HOURLY X0019750 19750
ROMP 23 (STN 69)_Quarterl A0025622 25622
WITHLACOOCHEE RIVER AT CROOM (STN 1139)_HOURLY X0023515 23515
WARNERS BAYOU AT RIVERVIEW BOULEVARD MANATEE COUNTY (STN 2166)_QUARTER A0890045 890045
WARNERS BAYOU AT RIVERVIEW BOULEVARD MANATEE COUNTY (STN 2166)_QUARTER X0890044 890044
MEDARD RESERVOIR & ROMP 61 (STN 375)_HOURLY G0017769 17769
MEDARD RESERVOIR & ROMP 61 (STN 375)_HOURLY X0017762 17762
MEDARD RESERVOIR & ROMP 61 (STN 375)_HOURLY G0763305 763305
LAKE HANCOCK CELL 1 INLET (STN 2128)_QUARTERLY X0882041 882041
LAKE PANASOFFKEE JEFFCOAT (STN 362)_HOURLY X0670277 670277
ROMP 24 (STN 408)_HOURLY G0025726 25726
WITHLACOOCHEE RIVER AT HWY 44 (STN 1136)_HOURLY X0023639 23639
ROMP 10 DEEP CREEK (STN 367)_HOURLY G0024447 24447
ROMP 10 DEEP CREEK (STN 367)_HOURLY G0024443 24443
ROMP 10 DEEP CREEK (STN 367)_HOURLY G0024442 24442
MEDARD RESERVOIR & ROMP 61 (STN 375)_Quarter A0018175 18175
LAKE HANCOCK CELL 2 INLET (STN 2129)_QUARTERLY X0882037 882037
LAKE HENRY OUTFLOW (STN 1041)_HOURLY X0025395 25395
LAKE HENRY OUTFLOW (STN 1041)_HOURLY X0025394 25394
LAKE ROGERS 4 (STN 650)_HOURLY X0020007 20007
INGLIS DAM (STN 1017)_Quarter X0022954 22954
INGLIS DAM (STN 1017)_Quarter X0022953 22953
INGLIS DAM (STN 1017)_Quarter A0022955 22955
ROMP 26 (STN 59)_Hourly G0024384 24384
ROMP 26 (STN 59)_Hourly G0024395 24395
ROMP 26 (STN 59)_Hourly G0024383 24383
ROMP 26 (STN 59)_Hourly G0024385 24385
WITHLACOOCHEE RIVER AT HWY 48 (STN 1137)_HOURLY X0023418 23418
ROMP 10 DEEP CREEK (STN 367)_Quarter A0024445 24445
ROMP 116 (STN 55)_HOURLY G0023595 23595
ROMP 116 (STN 55)_HOURLY G0023596 23596
LAKE HANCOCK DSC 1& INLET 3 (STN 2125)_QUARTERLY X0882038 882038
LAKE HANCOCK DSC 1& INLET 3 (STN 2125)_QUARTERLY X0825591 825591
LAKE HENRY OUTFLOW (STN 1041)_Quarter A0025170 25170
MAGDALENE CANAL AT STRUCTURE (STN 1055)_HOURLY X0019753 19753
ROMP 26 (STN 59)_Quarterl A0025210 25210
WITHLACOOCHEE RIVER AT OUTLET RIVER (STN 1135)_HOURLY X0023168 23168
S-353 & ROMP 116 RNF (STN 2151)_HOURLY X0023594 23594
LAKE HANCOCK DSC 2 (STN 2126)_QUARTERLY X0825593 825593
LAKE JOSEPHINE OUTFLOW (STN 1314)_HOURLY X0023733 23733
FLORAL CITY STRUCTURE (STN 1007)_HOURLY_arch2 X0740875 740875
FLORAL CITY STRUCTURE (STN 1007)_HOURLY_arch2 X0740876 740876
MASARYKTOWN DEEP (STN 568)_HOURLY G0020541 20541
ROMP 28 (STN 85)_HOURLY G0023725 23725
ROMP 28 (STN 85)_HOURLY G0023722 23722
ROMP 28 (STN 85)_HOURLY G0023726 23726
ROMP 28 (STN 85)_HOURLY G0023721 23721
ROMP 28 (STN 85)_HOURLY G0734408 734408
ROMP 28 (STN 85)_HOURLY G0023723 23723
S-353 & ROMP 116 RNF (STN 2151)_Quarter A0023597 23597
LAKE HANCOCK DSC 3 (STN 2127)_QUARTERLY X0825594 825594
MCINTOSH (STN 825)_HOURLY G0018311 18311
MCINTOSH (STN 825)_HOURLY G0019246 19246
MCINTOSH (STN 825)_HOURLY G0768554 768554
LAKE TARPON CANAL AT S-551 (STN 648)_Quarter X0022084 22084
LAKE TARPON CANAL AT S-551 (STN 648)_Quarter X0022083 22083
LAKE TARPON CANAL AT S-551 (STN 648)_Quarter A0022898 22898
ROMP 28 (STN 85)_Quarter A0025184 25184
BUCCANEER BAY (STN 1155)_QUARTER A0020912 20912
FLORAL CITY STRUCTURE (STN 1007)_HOURLY X0740875 740875
FLORAL CITY STRUCTURE (STN 1007)_HOURLY X0740876 740876
ROMP 9-5 (STN 101)_HOURLY G0024028 24028
ROMP 9-5 (STN 101)_HOURLY G0024031 24031
ROMP 9-5 (STN 101)_HOURLY G0024026 24026
ROMP 9-5 (STN 101)_HOURLY G0024032 24032
ROMP 9-5 (STN 101)_HOURLY G0024033 24033
LAKE JUNE-IN-WINTER (STN 647)_QUARTER X0025468 25468
LAKE JUNE-IN-WINTER (STN 647)_QUARTER A0025183 25183
MCINTOSH (STN 825)_Quarter A0018684 18684
ROMP 28X (STN 47)_HOURLY G0025412 25412
ROMP 28X (STN 47)_HOURLY G0025411 25411
MEDARD PZ-14 (STN 2142)_HOURLY G0874192 874192
LAKE HANCOCK PUMP STATION (STN 2122)_QUARTER X0825590 825590
LAKE SMART SCADA (STN 1335)_HOURLY X0772960 772960
MOON LAKE (STN 1059)_HOURLY G0020339 20339
MOON LAKE (STN 1059)_HOURLY G0020338 20338
ROMP 28X (STN 47)_Quarter A0025182 25182
TSALA APOPKA AT HERNANDO ( STN 1118)_HOURLY X0023609 23609
LAKE ELLEN SOUTH CANAL (STN 2174)_QUARTER X0885104 885104
LP-6 COLEMAN (STN 20)_HOURLY G0023147 23147
LP-6 COLEMAN (STN 20)_HOURLY G0023148 23148
LAKE STEMPER (STN 1047)_HOURLY X0019304 19304
MORRIS BRIDGE SAWGRASS MARSH (STN 1806)_HOURLY X0019147 19147
ROMP 31 (STN 45)_HOURLY G0023923 23923
ROMP 31 (STN 45)_HOURLY G0023922 23922
ROMP 31 (STN 45)_HOURLY G0023924 23924
TSALA APOPKA AT HERNANDO ( STN 1118)_QUARTER A0023611 23611
S-353_HOURLY X0023481 23481
LP-6 COLEMAN (STN 20)_Quarter A0023149 23149
CE 14 DUNNELLON DEEP (STN 970)_HOURLY G0023020 23020
MORRIS BRIDGE SINK (STN 1304)_HOURLY X0709106 709106
ROMP 31 (STN 45)_Quarter A0025192 25192
INGLIS BYPASS DOWNSTREAM (STN 1016)_HOURLY X0023384 23384
S-353_QUARTER A0023484 23484
LUTZ-LAKE FERN (STN 1052)_HOURLY G0019976 19976
LUTZ-LAKE FERN (STN 1052)_HOURLY G0019975 19975
LAKE TARPON AT SINK (STN 1205)_Quarter X0021375 21375
LAKE TARPON AT SINK (STN 1205)_Quarter X0021379 21379
LAKE TARPON AT SINK (STN 1205)_Quarter A0022889 22889
NORTH LECANTO DEEP (STN 1062)_HOURLY G0020075 20075
ROMP 32 (STN 67)_HOURLY G0025567 25567
ROMP 32 (STN 67)_HOURLY G0023658 23658
ROMP 32 (STN 67)_HOURLY G0025568 25568
TSALA APOPKA AT HERNANDO (STN 1118)_HOURLY X0023609 23609
INVERNESS DOT FLDN (STN 1019)_HOURLY G0023485 23485
BRADEN RIVER MANATEE COUNTY (STN 957)_QUARTER A0026250 26250
BRADEN RIVER MANATEE COUNTY (STN 957)_QUARTER X0026251 26251
LYKES PASCO FLDN (STN 1054)_HOURLY G0023207 23207
MEDARD DAM OUTFLOW (STN 1342)_HOURLY X0017770 17770
ROMP 32 (STN 67)_Quarter A0025177 25177
TSALA APOPKA AT HERNANDO (STN 1118)_QUARTER A0023611 23611
FLORAL CITY POOL (STN 1117)_HOURLY X0022908 22908
MEDARD PZ-8 (STN 1302)_HOURLY G0812418 812418
OCALA MUNICIPAL AIRPORT (STN 1338)_QUARTER A0023336 23336
ROMP 33 (STN 60)_HOURLY G0026170 26170
ROMP 33 (STN 60)_HOURLY G0026169 26169
ROMP 33 (STN 60)_HOURLY G0026167 26167
ROMP 33 (STN 60)_HOURLY G0026171 26171
ROMP 33 (STN 60)_HOURLY G0026172 26172
FLORAL CITY POOL (STN 1117)_QUARTER A0022907 22907
CYPRESS STRAND MANATEE COUNTY (STN 984)_QUARTER A0025770 25770
CYPRESS STRAND MANATEE COUNTY (STN 984)_QUARTER X0025769 25769
MEDARD UPSTREAM (STN 1343)_HOURLY X0759704 759704
RAINBOW SPRINGS FLDN NR DUNNELLON (STN 1317)_HOURLY G0023316 23316
ROMP 33 (STN 60)_Quarter A0025623 25623
FLORAL CITY STRUCTURE (STN 1007)_HOURLY_arch1 X0740875 740875
FLORAL CITY STRUCTURE (STN 1007)_HOURLY_arch1 X0740876 740876
MOCCASIN SLOUGH STRUCTURE (STN 1058)_HOURLY X0740881 740881
MOCCASIN SLOUGH STRUCTURE (STN 1058)_HOURLY X0740882 740882
RAINBOW SPRINGS FLDN NR DUNNELLON (STN 1317)_QUARTER A0023323 23323
PUBLIC WORKS OFFICE MANATEE COUNTY (STN 2173)_QUARTER A0890058 890058
ROMP 43XX (STN 44)_HOURLY G0025532 25532
ROMP 43XX (STN 44)_HOURLY G0025529 25529
LESLIE HEIFNER (STN 782)_HOURLY X0023502 23502
LESLIE HEIFNER (STN 782)_HOURLY X0023501 23501
FROG CREEK MANATEE COUNTY (STN 772)_QUARTER A0026255 26255
FROG CREEK MANATEE COUNTY (STN 772)_QUARTER X0026254 26254
MORRIS BRIDGE NURSERY SINK CYPRESS MARSH (STN 1382)_HOURLY X0792841 792841
P-6 CANAL AT LAKE SMART (STN 1067)_HOURLY X0025385 25385
ROMP 121 (STN 371)_HOURLY G0023250 23250
ROMP 43XX (STN 44)_Quarter A0025189 25189
LESLIE HEIFNER (STN 782)_Quarter A0023504 23504
P-7 CANAL AT LAKE FANNIE (STN 1068)_HOURLY X0025383 25383
PANA VISTA (STN 1334)_HOURLY X0023142 23142
ROMP 121 (STN 371)_QUARTER A0023249 23249
ROMP 44 (STN 37)_HOURLY G0023891 23891
ROMP 44 (STN 37)_HOURLY G0023890 23890
ROMP 110 (STN 75)_HOURLY G0023500 23500
ROMP 110 (STN 75)_HOURLY G0023503 23503
GAMBLE CREEK MANATEE COUNTY (STN 741)_QUARTER A0025768 25768
GAMBLE CREEK MANATEE COUNTY (STN 741)_QUARTER X0025767 25767
ROMP 105 HORSE LAKE (STN 253)_HOURLY G0020757 20757
ROMP 105 HORSE LAKE (STN 253)_HOURLY G0020756 20756
ROMP 105 HORSE LAKE (STN 253)_HOURLY G0020754 20754
PANA VISTA (STN 1334)_QUARTER A0670224 670224
ROMP 49 (STN 40)_Hourly G0017932 17932
ROMP 49 (STN 40)_Hourly G0017928 17928
ROMP 49 (STN 40)_Hourly G0017931 17931
ROMP 49 (STN 40)_Hourly G0017930 17930
RAINBOW HEADSPRING RUN (STN 2134)_HOURLYPT X0815666 815666
ROMP 44 (STN 37)_Quarter A0025544 25544
WYSONG DAM (STN 1142)_HOURLY X0023170 23170
WYSONG DAM (STN 1142)_HOURLY X0023174 23174
ROMP 105 HORSE LAKE (STN 253)_Quarter A0020752 20752
ROMP 103 (STN 895)_HOURLY G0023534 23534
ROMP 49 (STN 40)_Quarterl A0018153 18153
ROMP 55 (STN 38)_HOURLY G0023844 23844
ROMP 55 (STN 38)_HOURLY G0023845 23845
ROMP TR 9-2 (STN 39)_HOURLY G0018078 18078
ROMP TR 9-2 (STN 39)_HOURLY G0018079 18079
ROMP TR 9-2 (STN 39)_HOURLY G0018081 18081
ROMP TR 9-2 (STN 39)_HOURLY G0018077 18077
ROMP TR 9-2 (STN 39)_HOURLY G0018080 18080
ROMP 107 RINGGOLD (STN 368)_HOURLY G0020727 20727
GOVERNMENT HAMMOCK MANATEE COUNTY (STN 1012)_QUARTER A0025771 25771
GOVERNMENT HAMMOCK MANATEE COUNTY (STN 1012)_QUARTER X0026205 26205
ROMP 119 MARION OAKS (STN 18)_HOURLY G0023229 23229
ROMP 119 MARION OAKS (STN 18)_HOURLY G0023231 23231
ROMP 11 SHELL CREEK (STN 369)_HOURLY G0025114 25114
ROMP 60X (STN 374)_HOURLY G0018041 18041
ROMP 60X (STN 374)_HOURLY G0739979 739979
ROMP 97 (STN 24)_HOURLY G0020841 20841
ROMP 97 (STN 24)_HOURLY G0020840 20840
ROMP TR 9-2 (STN 39)_Quarter A0018154 18154
ROMP 119 MARION OAKS (STN 18)_Quarter A0023230 23230
ROMP 11 SHELL CREEK (STN 369)_QUARTER A0025225 25225
ROMP 68 (STN 27)_HOURLY G0018676 18676
ROMP 68 (STN 27)_HOURLY G0018677 18677
ROMP 68 (STN 27)_HOURLY G0777749 777749
ROMP 107 RINGGOLD (STN 368)_QUARTER A0020728 20728
ROMP TR 7-4 (STN 46)_HOURLY G0026201 26201
ROMP TR 7-4 (STN 46)_HOURLY G0026204 26204
ROMP TR 7-4 (STN 46)_HOURLY G0026199 26199
ROMP TR 7-4 (STN 46)_HOURLY G0026203 26203
ROMP TR 7-4 (STN 46)_HOURLY G0026202 26202
ROMP 30 (STN 62)_Hourly G0023978 23978
ROMP 30 (STN 62)_Hourly G0023975 23975
ROMP 30 (STN 62)_Hourly G0023974 23974
ROMP 30 (STN 62)_Hourly G0023976 23976
ROMP 13 TIPPEN BAY (STN 83)_HOURLY G0024196 24196
ROMP 13 TIPPEN BAY (STN 83)_HOURLY G0024198 24198
ROMP 13 TIPPEN BAY (STN 83)_HOURLY G0024193 24193
ROMP 13 TIPPEN BAY (STN 83)_HOURLY G0024194 24194
ROMP 13 TIPPEN BAY (STN 83)_HOURLY G0024195 24195
ROMP 25 LILY (STN 103)_HOURLY G0024003 24003
ROMP 25 LILY (STN 103)_HOURLY G0024004 24004
ROMP 25 LILY (STN 103)_HOURLY G0024008 24008
ROMP 25 LILY (STN 103)_HOURLY G0024005 24005
ROMP 25 LILY (STN 103)_HOURLY G0024006 24006
ROMP 85 (STN 58)_HOURLY G0019327 19327
ROMP 85 (STN 58)_HOURLY G0019329 19329
ROMP 85 (STN 58)_HOURLY G0019330 19330
ROMP TR 9-1 (STN 384)_HOURLY G0018088 18088
ROMP 30 (STN 62)_Quarterl A0025195 25195
ROMP 111 TOMPKINS PARK (STN 413)_HOURLY G0023115 23115
ROMP 111 TOMPKINS PARK (STN 413)_HOURLY G0772681 772681
ROMP 13 TIPPEN BAY (STN 83)_Quarter A0025207 25207
ROMP 25 LILY (STN 103)_Quarter A0024007 24007
ROMP 86_5 CONE RANCH CM-4 (STN 340)_HOURLY G0019363 19363
ROMP 86_5 CONE RANCH CM-4 (STN 340)_HOURLY G0019369 19369
ROMP TR 9-1 (STN 384)_QUARTER A0018155 18155
ROMP 35 (STN 104)_Hourly G0024413 24413
ROMP 35 (STN 104)_Hourly G0024412 24412
ROMP 35 (STN 104)_Hourly G0024411 24411
ROMP 35 (STN 104)_Hourly G0024414 24414
GAP CREEK AT 45TH MANATEE COUNTY (STN 2182)_QUARTER_arch2 A0890054 890054
GAP CREEK AT 45TH MANATEE COUNTY (STN 2182)_QUARTER_arch2 X0890055 890055
PALMA SOLA DRAIN MANATEE COUNTY (STN 1069)_QUARTER A0026238 26238
PALMA SOLA DRAIN MANATEE COUNTY (STN 1069)_QUARTER X0026206 26206
ROMP 131 STARLING SHRINE (STN 94)_HOURLY G0022941 22941
ROMP 39 OAK KNOLL (STN 82)_Hourly G0026091 26091
ROMP 39 OAK KNOLL (STN 82)_Hourly G0026089 26089
ROMP 39 OAK KNOLL (STN 82)_Hourly G0026090 26090
ROMP 39 OAK KNOLL (STN 82)_Hourly G0026092 26092
ROMP 88 (STN 379)_HOURLY G0017708 17708
ROMP 120 (STN 17)_HOURLY G0023349 23349
ROMP 120 (STN 17)_HOURLY G0023348 23348
ROMP TR SA-3 (STN 416)_HOURLY G0025656 25656
ROMP 35 (STN 104)_Quarterl A0781076 781076
ROMP 131 STARLING SHRINE (STN 94)_QUARTER A0823564 823564
ROMP 39 OAK KNOLL (STN 82)_Quarterl A0025611 25611
ROMP 88 (STN 379)_QUARTER A0017530 17530
CL-1 (STN 36)_HOURLY G0023875 23875
CL-1 (STN 36)_HOURLY G0023873 23873
ROMP 134 WILLISTON (STN 16)_HOURLY G0022929 22929
ROMP 134 WILLISTON (STN 16)_HOURLY G0022930 22930
PEARCE DRAIN MANATEE COUNTY (STN 1072)_QUARTER A0026252 26252
PEARCE DRAIN MANATEE COUNTY (STN 1072)_QUARTER X0026253 26253
ROMP 19X EAST MACARTHUR (STN 12)_Hourly G0025906 25906
ROMP 19X EAST MACARTHUR (STN 12)_Hourly G0025903 25903
ROMP 19X EAST MACARTHUR (STN 12)_Hourly G0025904 25904
ROMP 19X EAST MACARTHUR (STN 12)_Hourly G0025907 25907
ROMP 40 FOUR CORNERS (STN 63)_HOURLY G0018053 18053
ROMP 40 FOUR CORNERS (STN 63)_HOURLY G0018051 18051
ROMP 40 FOUR CORNERS (STN 63)_HOURLY G0018052 18052
ROMP 90 (STN 25)_HOURLY G0017516 17516
ROMP 90 (STN 25)_HOURLY G0017518 17518
SARASOTA OFFICE RAINFALL (STN 1200)_QUARTER A0025831 25831
CL-2 (STN 15)_Hourly G0023839 23839
CL-2 (STN 15)_Hourly G0023842 23842
CL-2 (STN 15)_Hourly G0023841 23841
CL-2 (STN 15)_Hourly G0023840 23840
BRYANT SLOUGH STRUCTURE (STN 1145)_HOURLY X0740883 740883
BRYANT SLOUGH STRUCTURE (STN 1145)_HOURLY X0740884 740884
BOWLEES CREEK AT 44TH MANATEE COUNTY (STN 2181)_QUARTER_arch2 A0890053 890053
BOWLEES CREEK AT 44TH MANATEE COUNTY (STN 2181)_QUARTER_arch2 X0890052 890052
ROMP 75 (STN 940)_HOURLY G0872967 872967
ROMP 19X EAST MACARTHUR (STN 12)_Quarterl A0025604 25604
ROMP 40 FOUR CORNERS (STN 63)_Quarter A0018123 18123
ROMP 90 (STN 25)_Quarter A0017517 17517
ROMP 134 WILLISTON (STN 16)_Quarter A0022928 22928
SR 52 SEEP WEST NO FIVAY JUNCTION (STN 1100)_HOURLY G0020458 20458
SR 52 SEEP WEST NO FIVAY JUNCTION (STN 1100)_HOURLY G0020342 20342
CL-3 (STN 41)_HOURLY G0023863 23863
CL-3 (STN 41)_HOURLY G0023865 23865
CL-3 (STN 41)_HOURLY G0023864 23864
ENGLE PARK (STN 1001)_HOURLY G0020547 20547
ENGLE PARK (STN 1001)_HOURLY G0020548 20548
ROMP 20 OSPREY (STN 79)_HOURLY G0026035 26035
ROMP 20 OSPREY (STN 79)_HOURLY G0026038 26038
ROMP 20 OSPREY (STN 79)_HOURLY G0026033 26033
ROMP 20 OSPREY (STN 79)_HOURLY G0026034 26034
ROMP 20 OSPREY (STN 79)_HOURLY G0026036 26036
ROMP 5 CECIL WEBB (STN 86)_HOURLY G0025095 25095
ROMP 5 CECIL WEBB (STN 86)_HOURLY G0025091 25091
ROMP 5 CECIL WEBB (STN 86)_HOURLY G0025090 25090
ROMP 5 CECIL WEBB (STN 86)_HOURLY G0025096 25096
ROMP 5 CECIL WEBB (STN 86)_HOURLY G0025093 25093
GAP CREEK AT 45TH MANATEE COUNTY (STN 2182)_QUARTER_arch3 A0890055 890055
GAP CREEK AT 45TH MANATEE COUNTY (STN 2182)_QUARTER_arch3 X0890054 890054
ROMP 93 (STN 57)_HOURLY G0018970 18970
ROMP 93 (STN 57)_HOURLY G0018971 18971
SR 577 (STN 1102)_HOURLY G0018877 18877
SR 577 (STN 1102)_HOURLY G0018878 18878
COLEY (STN 976)_HOURLY G0025339 25339
ENGLE PARK (STN 1001)_Quarter A0020546 20546
ROMP 48 THATCHER (STN 42)_HOURLY G0017841 17841
ROMP 48 THATCHER (STN 42)_HOURLY G0017840 17840
ROMP 48 THATCHER (STN 42)_HOURLY G0017837 17837
WARES CREEK MANATEE COUNTY (STN 1129)_QUARTER A0026207 26207
WARES CREEK MANATEE COUNTY (STN 1129)_QUARTER X0026208 26208
ROMP 20 OSPREY (STN 79)_Quarter A0025607 25607
ROMP 5 CECIL WEBB (STN 86)_Quarter A0025094 25094
ROMP TR 10-2 (STN 35)_HOURLY G0018117 18117
ROMP TR 10-2 (STN 35)_HOURLY G0018115 18115
WITHLACOOCHEE RIVER AT MAIN GRADE (STN 1134)_HOURLY X0017533 17533
COLEY (STN 976)_QUARTER A0025155 25155
ROMP 45 FORT MEADE (STN 53)_Hourly G0024802 24802
ROMP 45 FORT MEADE (STN 53)_Hourly G0024803 24803
ROMP 45 FORT MEADE (STN 53)_Hourly G0024801 24801
ROMP 45 FORT MEADE (STN 53)_Hourly G0024804 24804
ROMP 50 (STN 43)_HOURLY G0018213 18213
ROMP 50 (STN 43)_HOURLY G0018211 18211
ROMP 50 (STN 43)_HOURLY G0018214 18214
ROMP TR 124 (STN 71)_HOURLY G0022963 22963
ROMP TR 124 (STN 71)_HOURLY G0022964 22964
ROMP 48 THATCHER (STN 42)_Quarter A0018151 18151
WITHLACOOCHEE RIVER NR HOLDER (STN 1138)_HOURLY X0022927 22927
CRESCENT LAKE CANAL (STN 980)_HOURLY X0020008 20008
PEACE RIVER RAINFALL (STN 2178)_QUARTER A0024573 24573
ROMP 45 FORT MEADE (STN 53)_Quarterl A0025157 25157
ROMP 57 (STN 34)_HOURLY G0025343 25343
ROMP 57 (STN 34)_HOURLY G0025341 25341
ROMP 57 (STN 34)_HOURLY G0025344 25344
SLAUGHTER DRAIN AT ERIE RD MANATEE COUNTY (STN 2179)_QUARTER_arch2 A0890057 890057
SLAUGHTER DRAIN AT ERIE RD MANATEE COUNTY (STN 2179)_QUARTER_arch2 X0890056 890056
WITHLACOOCHEE RIVER NR HOLDER (STN 1138)_QUARTER A0022926 22926
CRESCENT LAKE CANAL (STN 980)_QUARTER A0019488 19488
BIG GANT LAKE CANAL (STN 728)_HOURLY X0023062 23062
ROMP 60 MULBERRY (STN 373)_HOURLY G0777757 777757
ROMP 60 MULBERRY (STN 373)_HOURLY G0768709 768709
ROMP 60 MULBERRY (STN 373)_HOURLY G0777756 777756
ROMP 59 (STN 68)_HOURLY G0024838 24838
ROMP 59 (STN 68)_HOURLY G0024839 24839
ROMP 59 (STN 68)_HOURLY G0024840 24840
ROMP 58 (STN 32)_HOURLY G0025241 25241
ROMP 58 (STN 32)_HOURLY G0025240 25240
S-159 (STN 1204)_Quarter X0019268 19268
S-159 (STN 1204)_Quarter X0019271 19271
S-159 (STN 1204)_Quarter A0019471 19471
BAKER CANAL (STN 949)_HOURLY X0018317 18317
CROSS BAR 1N FINEST FARMS (STN 966)_HOURLY G0020481 20481
CROSS BAR 1N FINEST FARMS (STN 966)_HOURLY G0020480 20480
GAP CREEK AT 45TH MANATEE COUNTY (STN 2182)_HOURLY X0890054 890054
ROMP 76 OLD POLK CITY (STN 52)_HOURLY G0017696 17696
ROMP 76 OLD POLK CITY (STN 52)_HOURLY G0017698 17698
ROMP 58 (STN 32)_Quarter A0025146 25146
ROMP 60 MULBERRY (STN 373)_Quarter A0018130 18130
BAY LAKE CANAL (STN 124)_HOURLY X0019746 19746
CROSS BAR 1S PASCO TRAILS (STN 964)_HOURLY G0020425 20425
CROSS BAR 1S PASCO TRAILS (STN 964)_HOURLY G0020423 20423

SELECT * FROM cleanwater_h20_h20 WHERE SITE_ID= 17588;

image

SELECT * FROM ln_tc_test_v WHERE SITE_ID= 17588;
image

I don't see anything in your where clause, that ensures the last 6 digits being numeric.

Try adding this in your where clause:

AND AC.name LIKE '%[0-9][0-9][0-9][0-9][0-9][0-9]'

Same behavior after adding this. In the View data that is returned, the SITE_ID is numeric, from the right, for 6 digits.

When a query is executed against the view on the site_id, which is the converted column, it ignores everything in the WHERE clause on the view, and executes the CONVERT first, so it catches every column even though I've excluded them down the line.

I believe what is happening is that all the data is read and the site_id = 17588 is executed first, and then the view's where clause restrictions are applied. But since the site_id is a converted column to an int data type, it fails because it's finding all of the column names. So if I need to just use the view, as is, without a where clause on a site_id, it's okay.

I'm trying to figure out how to force it to resolve the view first, and then work on the where for the site_id last.

I wonder if subselect work.

Try this:

SELECT T.[name] AS [SITE_NAME]
      ,AC.[name] AS [LOGGER_FIELD]
      ,AC.[SITE_ID]
  FROM sys.[tables] AS T
       INNER JOIN (SELECT [object_id] 
                         ,[system_type_id]
                         ,[user_type_id]
                         ,CONVERT(INT, RIGHT([name], 6)) AS SITE_ID
                     FROM sys.[all_columns]
                    WHERE [system_type_id] = 59
                      AND [name] NOT LIKE '%[_]%'
                      AND [name] LIKE '%[0-9][0-9][0-9][0-9][0-9][0-9]'
                      AND ([name] LIKE 'X0%'
                       OR  [name] LIKE 'A0%'
                       OR  [name] LIKE 'G0%'
                          )
                  ) AS AC
               ON AC.[object_id]=T.[object_id]
       INNER JOIN sys.[types] AS TY
               ON TY.[system_type_id]=AC.[system_type_id]
              AND TY.[user_type_id]=AC.[user_type_id]

what is this IN_Tot column?? this tells me that

select * from ln_tc_test_v has site_id data value IN_Tot

It's actually a column called RAIN_Tot, where IN_Tot is the last 6. It's not in ln_tc_test_v, but it is in sys.all_columns before the where clause in the view is applied.

I was thinking as you are, that the selection over the view would use only the view data, but the optimizer is first paring down the select for the site_id = 17588, and then doing the where trimming afterwards. This is really hard to explain because it's illogical.