| Author |
Topic |
|
nguyenl
Posting Yak Master
128 Posts |
 Posted - 2008-07-28 : 14:00:52
|
| Not Sure where to begin with this. I am suppose to translate this Informix SQL query into SQL. Please help. Where should I start?CREATE PROCEDURE "informix".getbldg_x(propid INTEGER, taxyear INTEGER) RETURNING CHAR(03), INTEGER, CHAR(04), INTEGER, CHAR(18), CHAR(02), CHAR(02), INTEGER, INTEGER; DEFINE aBldg CHAR(03); DEFINE aRecid INTEGER; DEFINE aYrBlt CHAR(04); DEFINE aSqft INTEGER; DEFINE wrkA1 INTEGER; DEFINE wrkA2 INTEGER; DEFINE wrkA3 INTEGER; DEFINE wrkA4 INTEGER; DEFINE wrkA5 INTEGER; DEFINE aCode CHAR(18); DEFINE aQuality CHAR(02); DEFINE aCondition CHAR(02); DEFINE aNumSt INTEGER; DEFINE aPctGood INTEGER; DEFINE tmpCode CHAR(04);-- DEFINE acurrent_tax_year INTEGER;-- added for specific year-- select current_tax_year into acurrent_tax_year from system_options; FOREACH SELECT 'RES', recid1, year_built, main_fn_area, uppr_fn_area, unfin_area, bsmt_area, bldg_style, quality, condition, num_stories, phy_pct_good INTO aBldg, aRecid, aYrBlt, wrkA1, wrkA2, wrkA3, wrkA4, tmpCode, aQuality, aCondition,aNumSt, aPctGood FROM residence, parcel WHERE parcel.property_id = propid and parcel.parcel_year = taxyear-- AND parcel.parcel_year > 0 and parcel.parcel_year < 3000 AND parcel.link_id = residence.link_id IF wrkA1 IS NULL THEN LET wrkA1 = 0; END IF; IF wrkA2 IS NULL THEN LET wrkA2 = 0; END IF; IF wrkA3 IS NULL THEN LET wrkA3 = 0; END IF; IF wrkA4 IS NULL THEN LET wrkA4 = 0; END IF; LET aSqFt = wrkA1 + wrkA2 + wrkA3 + wrkA4; IF aSqFt = 0 THEN LET aSqFt = NULL; END IF; SELECT description INTO aCode FROMvalue_list WHERE table_name = 'RESIDENCE' AND column_name = 'BLDG_STYLE' AND code = TRIM(tmpCode); IF aCode is NULL THEN LET aCode = tmpCode; END IF; RETURN aBldg, aRecid, aYrBlt, aSqFt, aCode, aQuality, aCondition, aNumSt, aPctGood with RESUME; END FOREACH; FOREACH SELECT 'COM', recid1, year_built, grnd_fl_area, bldg_type, quality, condition, num_stories, phy_pct_good INTO aBldg, aRecid, aYrBlt, wrkA1, tmpCode, aQuality, aCondition, aNumSt, aPctGood FROM comm_section, parcel WHERE parcel.property_id = propid-- AND parcel.parcel_year > 0 and parcel.parcel_year < 3000 AND parcel.parcel_year = taxyear AND parcel.link_id = comm_section.link_id IF wrkA1 IS NULL THEN LET wrkA1 = 0; END IF; LET aSqFt = wrkA1; IF aSqFt = 0 THEN LET aSqFt = NULL; END IF; SELECT description INTO aCode FROM value_list WHERE table_name = 'COMM_SECTION' AND column_name = 'BLDG_TYPE' AND code = TRIM(tmpCode); IF aCode is NULL THEN LET aCode = tmpCode; END IF; RETURN aBldg, aRecid, aYrBlt, aSqFt, aCode, aQuality, aCondition,aNumSt, aPctGood with RESUME; END FOREACH; FOREACH SELECT 'DET', recid1, year_built, main_fn_area, uppr_fn_area, structure, quality, condition, num_stories, phy_pct_good INTO aBldg, aRecid, aYrBlt, wrkA1, wrkA2, tmpCode, aQuality, aCondition, aNumSt, aPctGood FROM det_structure, parcel WHERE parcel.property_id = propid AND parcel.parcel_year = taxyear--- AND parcel.parcel_year > 0 and parcel.parcel_year < 3000 AND parcel.link_id = det_structure.link_idIF wrkA1 IS NULL THEN LET wrkA1 = 0; END IF; IF wrkA2 IS NULL THEN LET wrkA2 = 0; END IF; LET aSqFt = wrkA1 + wrkA2; IF aSqFt = 0 THEN LET aSqFt = NULL; END IF; SELECT description INTO aCode FROM value_list WHERE table_name = 'DET_STRUCTURE' AND column_name = 'STRUCTURE' AND code = TRIM(tmpCode); IF aCode IS NULL THEN LET aCode = tmpCode; END IF; RETURN aBldg, aRecid, aYrBlt, aSqFt, aCode, aQuality, aCondition, aNumSt, aPctGood with RESUME; END FOREACH; FOREACH SELECT 'MOB', recid1, year_built, tot_liv_area, make, quality, condition, 0,phy_pct_good INTO aBldg, aRecid, aYrBlt, wrkA1, aCode, aQuality, aCondition, aNumSt, aPctGood FROM mobile_home, parcel WHERE parcel.property_id = propid AND parcel.parcel_year = taxyear--- AND parcel.parcel_year > 0 and parcel.parcel_year < 3000 AND parcel.link_id = mobile_home.link_id IF wrkA1 IS NULL THEN LET wrkA1 = 0; END IF; LET aSqFt = wrkA1; IF aSqFt = 0 THEN LET aSqFt = NULL; END IF; LET aNumSt = NULL; RETURN aBldg, aRecid, aYrBlt, aSqFt, aCode, aQuality, aCondition, aNumSt, aPctGood with RESUME; END FOREACH;END PROCEDURE; |
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2008-07-29 : 04:44:03
|
| Well knowing nothing about Informix this might be hopelessly optimistic but the code doesn't look too complicated. The pieces that do the work seem relatively straightforward. (If I'm right about the FOREACH being analogous to a CURSOR though it is horribly inefficiently).I think you should ditch this and not try to translate it -- just write something from scratch that does the same. I think this might actually translate best to a TSQL view as I think it returns a table.Do you have a specification for this piece of code -- do you know exactly what it is *supposed* to do and the results you should get from a sample piece of input?-------------Charlie |
 |
|
|
nguyenl
Posting Yak Master
128 Posts |
Posted - 2008-07-30 : 17:19:30
|
| Doing a google search, it seems like SQL does have a foreach command. Can't I keep these foreach command here?Do you understand what it is doing when it is defining? For example: DEFINE aBldg CHAR(03);DEFINE aRecid INTEGER;DEFINE aYrBlt CHAR(04);DEFINE aSqft INTEGER;DEFINE wrkA1 INTEGER;DEFINE wrkA2 INTEGER;DEFINE wrkA3 INTEGER;DEFINE wrkA4 INTEGER;DEFINE wrkA5 INTEGER;DEFINE aCode CHAR(18);DEFINE aQuality CHAR(02);DEFINE aCondition CHAR(02);DEFINE aNumSt INTEGER;DEFINE aPctGood INTEGER;DEFINE tmpCode CHAR(04); |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2008-07-30 : 18:33:15
|
| I hope you realize that this forum is for Microsoft SQL Server Transact SQL, not Informix SQL.Ordinarily, I would suggest posting your question on dbforumns.com, but they seem to be down for now.CODO ERGO SUM |
|
|
|
|
|
|
Informix SQL Procedure