General information
Category:
Databases,Tables, Views, Indexing and SQL syntax
>>Thanks for such a quick response Sergey. The view has three tables. The first two are connected with an Inner Join with the primary key in the first table. The second and third tables are connected with a Left out Join. The parameter is public variable. Let me know if you need more.
>
>Can you post SQL for the view
SELECT Company.orgname, Company.statecorpi, Company.stateoforg,;
Company.fiscyrend, Company.orgdate, Company.citybuslic,;
Company.countybusl, Company.statebusli, Licenses.licenseid,;
Licenses.companyid, Licenses.code, Licenses.filingfee, Licenses.statetax,;
Licenses.agency, Licenses.address1, Licenses.address2, Licenses.city,;
Licenses.postal, Licenses.county, Licenses.country, Licenses.phone,;
Licenses.internatio, Licenses.intadd, Licenses.type, Licenses.dt1,;
Licenses.dt2, Licenses.dt3, Licenses.dt4, Licenses.dt5, Licenses.dt6,;
Licenses.dt7, Licenses.dt8, Licenses.dt9, Licenses.dt10, Licenses.dt11,;
Licenses.dt12, Cmstates.state, Licenses.citylic, Licenses.countylic,;
Licenses.statelic, Licenses.foreignlic, Licenses.otherlic,;
Licenses.dtype, Licenses.taxidno, Company.companyid, Cmstates.code,;
Company.stateofind,;
IIF(Licenses.dtype=1,Licenses.dt4,IIF(Licenses.dtype=2,Licenses.dt5,IIF(Licenses.dtype=3,Licenses.dt2,IIF(Licenses.dtype=4,Licenses.dt1,DATE())))) AS firstdt,;
PADR(IIF(Licenses.dtype=1,"Annual",IIF(Licenses.dtype=2,"Semiannual",IIF(Licenses.dtype=3,"Quarterly",IIF(Licenses.dtype=4,"Monthly","No Data")))),12) AS datetype;
FROM ;
nch_mgr!company ;
INNER JOIN nch_mgr!licenses ;
ON Company.companyid = Licenses.companyid ;
LEFT OUTER JOIN nch_mgr!cmstates ;
ON Licenses.code = Cmstates.code;
WHERE Company.companyid = ( ?zcompany );
ORDER BY Company.orgname, Licenses.licenseid
Previous
Next
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only