Can SQLCreator/SQLExecutor create columns and alter values. I've completed all the left join I require and now wanting to create a new column with calculated values.
Duplicate an existing column with new name / Select existingTable.existingColumn as newColumn
Can I use if-then statements / If existingTable.existingValue = 'DI' then newColumn = '1234'
Yes, assuming you have the correct permissions of course. It's more common to use the SQLExecutor for it, but keep in mind it will be triggered for every input feature. It'll then send the SQL query to the database.
@hlouie As @redgeographics mentions, you can use pretty well any SQL statement in SQLCreator or SQLExecutor. These transformers do not have to return any data, If you right click on a table in the Database tables list, you get examples of SQL scripts.
The Run... button lets you test your scripts - use with caution! A short tutorial on the Knowledge Centre and perhaps this section
@hlouie As @redgeographics mentions, you can use pretty well any SQL statement in SQLCreator or SQLExecutor. These transformers do not have to return any data, If you right click on a table in the Database tables list, you get examples of SQL scripts.
The Run... button lets you test your scripts - use with caution! A short tutorial on the Knowledge Centre and perhaps this section
With the SQL statement below
I can add a new column under select with
COMP.PIPETYPE AS PAC_NO,
But how do I change the values in this new column?
Can I use if-then statements / If PAC_NO = 'DI' then PAC_NO = '1234'
/*
SewerServiceLine
*/
SELECT COMP.*,
VAR.*,
UNITTYPE.[DESCRIPT] AS UNITTYPE_RESOLVED,
ADDRKEY.[CACITY],
ADDRKEY.[CAPOSTDIR],
ADDRKEY.[CAPROVINCE],
ADDRKEY.[CASTNAME],
ADDRKEY.[CASTNO],
ADDRKEY.[CASUFFIX],
STKEY.[DESCRIPT] AS SEGKEY_RESOLVED,
AREA.[DESCRIPT] AS AREA_RESOLVED,
SUBAREA.[DESCRIPT] AS SUBAREA_RESOLVED,
DISTRICT.[DESCRIPT] AS DISTRICT_RESOLVED,
LOC.[DESCRIPT] AS LOC_RESOLVED,
PIPETYPE.[DESCRIPT] AS PIPETYPE_RESOLVED,
SURF.[DESCRIPT] AS SURF_RESOLVED,
SIC.[DESCRIPT] AS SIC_RESOLVED,
SRVTYPE.[DESCRIPT] AS SRVTYPE_RESOLVED,
MFGKEY.[CODE] AS MFGKEY_RESOLVED,
SERVSTAT.[DESCRIPT] AS SEVRSTAT_RESOLVED,
OWN.[DESCRIPT] AS OWN_RESOLVED
FROM ASSETMANAGEMENT_SEWER.[COMPSSL] AS COMP
LEFT JOIN BURNABY.[VARSSL] AS VAR ON COMP.[COMPKEY] = VAR.[COMPKEY]
LEFT JOIN ASSETMANAGEMENT_SEWER.[SEWERSERVICETYPE] AS UNITTYPE ON COMP.[UNITTYPE] = UNITTYPE.[CODE]
LEFT JOIN PROPERTY.[ADDRESS] AS ADDRKEY ON COMP.[ADDRKEY] = ADDRKEY.[ADDRKEY]
LEFT JOIN ASSETMANAGEMENT_STREET.[COMPSEG] AS COMPSEG ON COMP.[SEGKEY] = COMPSEG.[COMPKEY]
LEFT JOIN ASSETMANAGEMENT_STREET.[STREET] AS STKEY ON COMPSEG.[STKEY] = STKEY.[STKEY]
LEFT JOIN ASSETMANAGEMENT_SEWER.[SEWERAREA] AS AREA ON COMP.[AREA] = AREA.[CODE]
LEFT JOIN ASSETMANAGEMENT_SEWER.[SEWERSUBAREA] AS SUBAREA ON COMP.[SUBAREA] = SUBAREA.[CODE]
LEFT JOIN ASSETMANAGEMENT_SEWER.[SEWERDISTRICT] AS DISTRICT ON COMP.[DISTRICT] = DISTRICT.[CODE]
LEFT JOIN ASSETMANAGEMENT_SEWER.[SEWERLOCATION] AS LOC ON COMP.[LOC] = LOC.[CODE]
LEFT JOIN ASSETMANAGEMENT_SEWER.[SEWERPIPETYPE] AS PIPETYPE ON COMP.[PIPETYPE] = PIPETYPE.[CODE]
LEFT JOIN ASSETMANAGEMENT_SEWER.[SEWERSIC] AS SIC ON COMP.[SIC] = SIC.[CODE]
LEFT JOIN ASSETMANAGEMENT_SEWER.[SEWERSERVICETYPE] AS SRVTYPE ON COMP.[SRVTYPE] = SRVTYPE.[CODE]
LEFT JOIN ASSETMANAGEMENT_SEWER.[SEWERSURFACECOVER] AS SURF ON COMP.[SURF] = SURF.[CODE]
LEFT JOIN RESOURCES.[MFG] AS MFGKEY ON COMP.[MFGKEY] = MFGKEY.[MFGKEY]
LEFT JOIN ASSETMANAGEMENT_SEWER.[SEWERSERVICESTATUS] AS SERVSTAT ON COMP.[SERVSTAT] = SERVSTAT.[CODE]
LEFT JOIN ASSETMANAGEMENT_SEWER.[SEWEROWNERSHIP] AS OWN ON COMP.[OWN] = OWN.[CODE]
WHERE COMP.[COMPKEY] > 1
With the SQL statement below
I can add a new column under select with
COMP.PIPETYPE AS PAC_NO,
But how do I change the values in this new column?
Can I use if-then statements / If PAC_NO = 'DI' then PAC_NO = '1234'
/*
SewerServiceLine
*/
SELECT COMP.*,
VAR.*,
UNITTYPE.[DESCRIPT] AS UNITTYPE_RESOLVED,
ADDRKEY.[CACITY],
ADDRKEY.[CAPOSTDIR],
ADDRKEY.[CAPROVINCE],
ADDRKEY.[CASTNAME],
ADDRKEY.[CASTNO],
ADDRKEY.[CASUFFIX],
STKEY.[DESCRIPT] AS SEGKEY_RESOLVED,
AREA.[DESCRIPT] AS AREA_RESOLVED,
SUBAREA.[DESCRIPT] AS SUBAREA_RESOLVED,
DISTRICT.[DESCRIPT] AS DISTRICT_RESOLVED,
LOC.[DESCRIPT] AS LOC_RESOLVED,
PIPETYPE.[DESCRIPT] AS PIPETYPE_RESOLVED,
SURF.[DESCRIPT] AS SURF_RESOLVED,
SIC.[DESCRIPT] AS SIC_RESOLVED,
SRVTYPE.[DESCRIPT] AS SRVTYPE_RESOLVED,
MFGKEY.[CODE] AS MFGKEY_RESOLVED,
SERVSTAT.[DESCRIPT] AS SEVRSTAT_RESOLVED,
OWN.[DESCRIPT] AS OWN_RESOLVED
FROM ASSETMANAGEMENT_SEWER.[COMPSSL] AS COMP
LEFT JOIN BURNABY.[VARSSL] AS VAR ON COMP.[COMPKEY] = VAR.[COMPKEY]
LEFT JOIN ASSETMANAGEMENT_SEWER.[SEWERSERVICETYPE] AS UNITTYPE ON COMP.[UNITTYPE] = UNITTYPE.[CODE]
LEFT JOIN PROPERTY.[ADDRESS] AS ADDRKEY ON COMP.[ADDRKEY] = ADDRKEY.[ADDRKEY]
LEFT JOIN ASSETMANAGEMENT_STREET.[COMPSEG] AS COMPSEG ON COMP.[SEGKEY] = COMPSEG.[COMPKEY]
LEFT JOIN ASSETMANAGEMENT_STREET.[STREET] AS STKEY ON COMPSEG.[STKEY] = STKEY.[STKEY]
LEFT JOIN ASSETMANAGEMENT_SEWER.[SEWERAREA] AS AREA ON COMP.[AREA] = AREA.[CODE]
LEFT JOIN ASSETMANAGEMENT_SEWER.[SEWERSUBAREA] AS SUBAREA ON COMP.[SUBAREA] = SUBAREA.[CODE]
LEFT JOIN ASSETMANAGEMENT_SEWER.[SEWERDISTRICT] AS DISTRICT ON COMP.[DISTRICT] = DISTRICT.[CODE]
LEFT JOIN ASSETMANAGEMENT_SEWER.[SEWERLOCATION] AS LOC ON COMP.[LOC] = LOC.[CODE]
LEFT JOIN ASSETMANAGEMENT_SEWER.[SEWERPIPETYPE] AS PIPETYPE ON COMP.[PIPETYPE] = PIPETYPE.[CODE]
LEFT JOIN ASSETMANAGEMENT_SEWER.[SEWERSIC] AS SIC ON COMP.[SIC] = SIC.[CODE]
LEFT JOIN ASSETMANAGEMENT_SEWER.[SEWERSERVICETYPE] AS SRVTYPE ON COMP.[SRVTYPE] = SRVTYPE.[CODE]
LEFT JOIN ASSETMANAGEMENT_SEWER.[SEWERSURFACECOVER] AS SURF ON COMP.[SURF] = SURF.[CODE]
LEFT JOIN RESOURCES.[MFG] AS MFGKEY ON COMP.[MFGKEY] = MFGKEY.[MFGKEY]
LEFT JOIN ASSETMANAGEMENT_SEWER.[SEWERSERVICESTATUS] AS SERVSTAT ON COMP.[SERVSTAT] = SERVSTAT.[CODE]
LEFT JOIN ASSETMANAGEMENT_SEWER.[SEWEROWNERSHIP] AS OWN ON COMP.[OWN] = OWN.[CODE]
WHERE COMP.[COMPKEY] > 1
Did you try it with a small test query? As long as you don't do it in a production database "just giving it a try" is often the best way to find out.