|
WIMS supports the ability to run SQL Statements (i.e. Select queries, update statements) using the SQL Console. To use the SQL Console you must be a Super User (or a Manager with the "Allowed to run SQL Console (if Manager):" User Setting checked).
To use the SQL Console:
Go to System Setup, Admin Console, Action Scripts Tab and click execute on the following line: Execute custom SQL commands against the current database. Experienced users only!
You will be presented with a window that allows you to execute SQL Statements directly against the database.
EXAMPLES:
1. Update using the REPLACE function:
Fix the LegalDesc field in the Location table by replacing the single quote ( ' ) with `:
Update LOCATION Set LEGALDESC = replace(LEGALDESC,'''','`') WHERE LEGALDESC LIKE '%''%'
NOTE: To use a single quote in an Oracle statement you have to use two single quotes. Therefore in the Replace above to get a quote you must first quote the string ', then put two single quotes '' and then close the string '. This gives you four single quotes in a row: ''''
Replace 'Influent' with 'Raw'
Update Vardesc set name = Replace(name, 'Influent', 'Raw')
2. Find all variables that are not assigned to a location.
Select VarNum,Name,Locid from Vardesc where Locid not in (Select LOCID from Location) OR LocId is Null
3. Replace Variable Names that start with SB DAF with DAF:
Update Vardesc Set ShortName = Replace(ShortName,'PE','Pri Eff') Where ShortName like 'PE%'
4. Format of dates - use the TO_CHAR SQL function :
Select SAMPLEDTTIME, TO_CHAR(SAMPLEDTTIME,'mm/dd/yyyy'),TO_CHAR(SAMPLEDTTIME,'yyyy-mm-dd'),TO_CHAR(SAMPLEDTTIME,'HH24:MI:SS') FROM LC_SAMPLE WHERE SAMPLEDTTIME IS NOT NULL AND ROWNUM<4
Using the format can allow you to truncate the datetime to just a date to allowing you to link LabCal SampleDtTime to the datatbl:
Select LC_SAMPLE.ID,LC_SAMPLE.SAMPLEDBY,LC_SAMPLE.SAMPLEDTTIME, LC_SAMPLETEST.VARID, Datatbl.TextValue FROM LC_SAMPLE,LC_SAMPLETEST,DATATBL WHERE LC_SAMPLE.SAMPLEDTTIME < '2008-02-26' AND LC_SAMPLE.ID=LC_SAMPLETEST.SAMPLEID and LC_SAMPLETEST.VARID = DATATBL.VARID and TO_CHAR(LC_SAMPLE.SAMPLEDTTIME,'mm/dd/yyyy') = TO_CHAR(DATATBL.DATESTAMP,'mm/dd/yyyy')
You can use the TO_CHAR Function to match up hours:
Select SAMPLEDTTIME, TO_CHAR(SampleDtTime,'HH24') FROM LC_SAMPLE WHERE SAMPLEDTTIME IS NOT NULL WHERE ROWNUM < 5
NOTE: Using TO_CHAR you can match up LabCal Samples to the hourly results:
Select LC_SAMPLE.ID,LC_SAMPLE.SAMPLEDBY,LC_SAMPLE.SAMPLEDTTIME, LC_SAMPLETEST.VARID, DATADDH.TextValue FROM LC_SAMPLE,LC_SAMPLETEST,DATADDH WHERE LC_SAMPLE.SAMPLEDTTIME < '2008-02-05' AND LC_SAMPLE.ID=LC_SAMPLETEST.SAMPLEID and LC_SAMPLETEST.VARID = DATADDH.VARID and TO_CHAR(LC_SAMPLE.SAMPLEDTTIME,'mm/dd/yyyy') = TO_CHAR(DATADDH.DATESTAMP,'mm/dd/yyyy') AND TO_CHAR(SAMPLEDTTIME,'HH24') = TO_CHAR(DATADDH.DATESTAMP, 'HH24')
5. Update the StoretCodes in VARDESC table that do not have zero padding. I.E. StoretCode is 310 instead of 00310:
UPDATE VARDESC SET STORETCODE = CONCAT('00',StoretCode) WHERE LENGTH(STORETCODE)=3
6. Update the SCADA Interface StartTime and/or StopTime to a default time:
Update vardesc Set StartTime = (Select StartTime from Vardesc where VarNum = 1) where VARTYPE = 'P'
|