I'm completely new here and I'm more oriented in infrastructure and server management, but sometimes I have to change/create some small code in ColdFusion for our company.
Now I have simple task, but with very complicated code.
I have 2 datasources (let's call them DS1 and DS2). In DS1 I have 2 tables (LLX - table of loads and LLL - table of load parts) from which I need to get data. That I've done with 2 queries and then QoQ to get them together since there is many rows in LLL table for each "load" from LLX and I need their SUM.
<cfquery datasource="TEST" name="get_load_2">
SELECT llx.lxpron datetime, llx.lxload load, llx.lxadr2 destination, llx.lxasn asn, llx.lxstat stat, llx.lxshpt shipto, llx.lxsdte lxsdte
FROM XXX.XXX.LLX LLX
WHERE llx.lxfacl='07' and llx.lxpron >'' and llx.lxasn = '' and llx.lxload > 392700 and llx.lxwhse <> 'F3'
<cfif IsDefined("from")>and llx.lxsdte>='#from#'</cfif>
<cfif IsDefined("to")>and llx.lxsdte<='#to#'</cfif>
ORDER BY right(llx.lxpron,8), left(llx.lxpron,5), right(llx.lxpron,10), left(llx.lxpron,4)
</cfquery>
<cfquery datasource="TEST" name="get_load_3">
SELECT sum(LLLQTY) as lqty, lll.llload lload
FROM XXX.XXX.LLL LLL
WHERE lll.llfacl='07' and lll.llload > 392700 and lll.llwhse <> 'F3'
<cfif IsDefined("from")>and lll.llsdte>='#from#'</cfif>
<cfif IsDefined("to")>and lll.llsdte<='#to#'</cfif>
GROUP BY lll.llload
</cfquery>
<cfquery name="test" dbtype="query">
SELECT *
FROM get_load_2, get_load_3
WHERE get_load_3.lload = get_load_2.load
</cfquery>
Result of this is simple table with all selected loads and sum of needed parts in each load. That works just fine. Now comes the hard part. I need to load data from DS2 which I do with this Query:
<cfquery datasource="ADC" name="ADC_get_PICK_SUM">
SELECT PICK.LOADNO LOADNO, sum(QUANTITY) SUM
FROM ADC.ADC.PICK PICK
where PICK.LOADNO > '392700'
GROUP BY pick.loadno
</cfquery>
There are loads with "actually scanned quantity" but there are NOT all loads which I get from previous query from DS1 since they are not started yet.
Now I need to get all these information together and put result to the table which should look like this:
|Date||Time|| Load ||Destination||Needed||Scanned||Closed|
|10.8||1000||393206||Leipzig || 755 || 755 || Yes |
|10.8||1200||393207||TRNAVA || 852 || 536 || No |
|11.8||1300||393210||Leipzig || 698 || || No |
|12.8||1100||393215||TRNAVA || 350 || || No |
And so on, but I'm not able to create table with all data from DS1 and only few rows from DS2 since there are no data for some loads from DS1 since they are not "scanned" and I need this field to be empty. Actualy I don't even need to show the numbers since I will compare them and only make the table colors change when there is nothing scanned, partially scanned or full amount scanned. But whatever I do I end up with list of only the loads where there is match of load numbers in both datasources.
I'm connecting both datasources with this:
<cfset QueryAddRow(test) />
<cfquery name="GetJoinedData" dbtype="query" >
SELECT *
FROM ADC_get_PICK_SUM, test
WHERE ADC_get_PICK_SUM.LOADNO = test.load
ORDER BY datetime
</cfquery>
Then I just have simple table:
<table class="back" bgcolor="grey" cellpadding="1" cellspacing="1" border=0 align="center">
<tr bgcolor="Silver">
<th>Date</th>
<th>Time</th>
<th>Load</th>
<th>Destination</th>
<th>Ship to</th>
<th>Status</th>
<th>Scanned</th>
<th></th>
<th>Closed</th>
</tr>
<cfoutput query="getjoineddata">
<tr bgcolor="white">
<td>#right(datetime,10)#</td>
<td>#left(datetime,4)#</td>
<td>#load#</td>
<td>#destination#</td>
<td>#shipto#</td>
<td>#lqty#</td>
<td>#SUM#</td>
<td>#LOADNO#</td>
<cfif stat eq 0> <td bgcolor="red"></td>
<cfelseif stat gt 1> <td bgcolor="green"></td>
<cfelse> <td>#stat#</td>
</cfif>
</tr>
</cfoutput>
</table>
I'm searching whole week for some knowledge, but I didn't find anything exactly like this case. I tried to use LEFT JOIN instead of the first QoQ but since there is "sum(LLLQTY) as lqty" needed QoQ won't let me do that. Then I tried many mutations of this code but still with no luck and I'm really getting desperate.
Could you please advice me how to get the correct result? I know all the data are loaded from both databases and all rows does have "load number" as identifier (load numbers are the same in all 3 tables but not all load numbers from DS1 are in DS2). And the rest of the page is just some html formatting, so what I posted here is all the CF code I have there.
