SQL Database Integration

Re: Shape command with Where clause and Order By clause

m181354 — Thu, 31 Jan 2008 12:50:19 GMT

That worked great! Thanks Now what about the Order By clause? I get an error with the below code on the Order By clause.

command += "{select * ";

command += "from ";

command += "[LaborSummary] as [LaborSummary] ";

command += "where (LaborSummary.[CA1] = '" + ca1Prefix + "')} ";

command += "append ";

command += "({select * ";

command += "from ";

command += "[DistributedRecords] as [DistributedRecords]}";

command += "relate [CA1] to [CA1],[HOUR_DOLLAR] TO [HOUR_DOLLAR]) as [DistributedRecords] ";

command += "order by DistributedRecords.[CA1], DistributedRecords.[BE], DistributedRecords.[DATE]";

djay — Wed, 30 Jan 2008 23:20:04 GMT

I'm not a SQL expert, but I believe you need the } after the where clause. So I would have:

...from "dbo"."LaborSummary" as "LaborSummary" where "SA1" = "whatever"} as "LaborSummary" append...

m181354 — Wed, 30 Jan 2008 23:03:20 GMT

Does anyone know how to use the where clause with the shape clause? I get a error on the following code at the Where clause....

command += "{select ";

command += "[CA1],[HOUR_DOLLAR],[DIST_NONDIST],[MASTER],";

command += "[PROGRAM],[PROG_DESC],[BUY_OFF_MONTH],[CAM],[ANALYST],";

command += "[WBS],[OBS],[ECAM],[APPROVED_SIGNED],";

command += "[SV_HRS],[CTD_EAC_HRS],[CTD_ACWP_HRS],[BUY_OFF_DELTA_HRS],";

command += "[WAS_ETC_HRS],[NOW_ETC_HRS],[DELTA_ETC_HRS],";

command += "[WAS_EAC_HRS],[NOW_EAC_HRS],[EAC_DELTA_HRS],";

command += "[SV_DOL],[CTD_EAC_DOL],[CTD_ACWP_DOL],[BUY_OFF_DELTA_DOL],[WAS_ETC_DOL],";

command += "[NOW_ETC_DOL],[DELTA_ETC_DOL],[WAS_EAC_DOL],[NOW_EAC_DOL],[EAC_DELTA_DOL] ";

command += "from "; command += "[LaborSummary]} as [LaborSummary] ";

command += "where (LaborSummary.[CA1] = '" + ca1Prefix + "') ";

command += "append ";

command += "({select ";

command += "[CA1],[HOUR_DOLLAR],[BE],[DATE],[DISTR_HRS],[DISTR_DOL] ";

command += "from ";

command += "[DistributedRecords] as [DistributedRecords]} as [DistributedRecords]";

command += "relate [CA1] to [CA1],[HOUR_DOLLAR] TO [HOUR_DOLLAR]) as [DistributedRecords] ";

command += "order by DistributedRecords.[CA1], DistributedRecords.[BE], DistributedRecords.[DATE] ";