We are trying to grab data from a System i (or iSeries or AS/400) using a DataSet. We have this working and can grab everything from the table. The end result is we want to output a Report (from Visual Studio 2005). The problem is Visual Studio doesn't like my SQL statement:
select vcmplntnum, vvltnnum, vcakey, vdtcode, vdtcodedsc,
vdvcode, vdvcodedsc, veename, vccode, vccodedesc, vcdate,
vcaowner, vrcode, 1 as sortorder
from qmfiles.nvmastp
where (vclosedate = 0)
union
select vcmplntnum, vvltnnum, vcakey, vdtcode, vdtcodedsc,
vdvcode, vdvcodedsc, veename, vccode, vccodedesc, vcdate,
vcaowner, vrcode, 2 as sortorder
from qmfiles.nvmastp
where (vclosedate > 0) and (date(substr(digits(vclosedate), 1, 4) || '-' || substr(digits(vclosedate), 5, 2) || '-' || substr(digits(vclosedate), 7, 2)) >= curdate() - 1 month)
The problem is if I use either "||" or CONCAT (which are both valid on the System i) the parser in the DataSet tells me it isn't valid. The reason I have to do that is because the date is stored in a decimal field (yyyymmdd).
Now, from the bit of playing I have done with the reports, you have to use a TableAdapter or DataTable? Like I mentioned before, I have the TableAdapter working, but can't use the above SQL statement. How do I fill a DataTable with the needed information? I am seeing ways to do this with XML in my searches but not from a table. Is there an example out there somewhere that can point me in the general direction?