perhaps of some assistance here?
Bruce
***********************************
Friday, March 14, 2014
Tip of the Day: JOIN, INTERSECT, SUBTRACT, and UNION - Temporary Table
Support
Product.: R:BASE eXtreme 9.5 (32/64)
Build...: 9.5.4.30310 or higher www.rupdates.com
Keywords: JOIN, INTERSECT, SUBTRACT, UNION, TEMPORARY
Section.: Commands, Temporary Tables
Did you know that a TEMPORARY option has been enhanced for the JOIN, INTERSECT,
SUBTRACT, and UNION commands, to support the creation of a temporary table?
The following example uses the JOIN command to create ProdDetail as a temporary
table:
JOIN ProdLocation USING Model WITH Product USING Model +
FORMING TEMPORARY ProdDetail
The following example uses the INTERSECT command to create RepSales as
a temporary table:
INTERSECT Employee WITH SalesBonus FORMING TEMPORARY RepSales +
USING EmpLname, EmpID, HireDate, NetAmount, TransDate, Bonus, BonusPct
The following example uses the SUBTRACT command to create NoBonus as a
temporary table:
SUBTRACT SalesBonus FROM Employee FORMING TEMPORARY NoBonus
The following example uses the UNION command to create SalesRepHistory
as a temporary table:
UNION Employee WITH SalesBonus FORMING TEMPORARY SalesRepHistory
Enjoy the showcase and pride of our eXpanded R:Dream Team!
Very Best R:egards,
Razzak.
www.rbase.com
www.facebook.com/rbase
www.Razzak.com
www.RazzakMemon.com
-- 31 years of continuous innovation!
16 Years of R:BASE Technologies, Inc. making R:BASE what it is today!
------ Original Message ------
From: "'Jim Belisle' via RBASE-L" <[email protected]>
To: "[email protected]" <[email protected]>
Sent: 12/18/2020 5:15:56 PM
Subject: [RBASE-L] - using INNER JOINS?
The following code works and goes to the screen
SELECT control#,transdate,model#,sortoneoh,remainqtyoh, +
partnum,partpcspt,numpart,assypcsae +
FROM ((topenorders t1 +
INNER JOIN orderrows t2 ON t1.control# = t2.control#) J1 +
INNER JOIN prodpart t3 ON j1.model# = t3.model#) J2 +
INNER JOIN assy_part t4 ON j2.partnum = t4.assembly# +
WHERE sortoneoh IN (ad,bl,br,lr,fp,ot,pb,pc,pt,sp) +
AND remainqtyoh > 0 AND packlistpt='Y'
However When I try to insert that info directly into a table, I get an
error.
INSERT INTO tmodprod (control#,transdate,model#,sortone,remainqty, +
partnumpp,partpcs,partnumap,assypcs) +
SELECT control#,transdate,model#,sortoneoh,remainqtyoh, +
partnum,partpcspt,numpart,assypcsae +
FROM ((topenorders t1 +
INNER JOIN orderrows t2 ON t1.control# = t2.control#) J1 +
INNER JOIN prodpart t3 ON j1.model# = t3.model#) J2 +
INNER JOIN assy_part t4 ON j2.partnum = t4.assembly# +
WHERE sortoneoh IN (ad,bl,br,lr,fp,ot,pb,pc,pt,sp) +
AND remainqtyoh > 0 AND packlistpt='Y'
This is the error. Tmodprod is a real temp table already created.
So am I wrong in trying to INSERT using the INNER JOIN code?
James Belisle
Making Information Systems People Friendly Since 1990
--
For group guidelines, visit
http://www.rbase.com/support/usersgroup_guidelines.php
---
You received this message because you are subscribed to the Google
Groups "RBASE-L" group.
To unsubscribe from this group and stop receiving emails from it, send
an email to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/rbase-l/CY4PR0201MB33948E5608EBBA00E571733FA0C20%40CY4PR0201MB3394.namprd02.prod.outlook.com
<https://groups.google.com/d/msgid/rbase-l/CY4PR0201MB33948E5608EBBA00E571733FA0C20%40CY4PR0201MB3394.namprd02.prod.outlook.com?utm_medium=email&utm_source=footer>.
--
For group guidelines, visit
http://www.rbase.com/support/usersgroup_guidelines.php
---
You received this message because you are subscribed to the Google Groups "RBASE-L" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/rbase-l/em69357049-310e-46d8-872a-25c057b19c43%40pathfinder.
