If I remember correctly (not sure about ver 11), the "." in the dbo.trace will cause the issue.In the past, when doing similar with SQL tables, I SAttached with the ALIAS option and insured no "." was in the name.Robert ThompsonTTC Inc.219-363-7441 -------- Original message --------From: "randyp ctags.com" <[email protected]> Date: 5/22/24 6:01 AM (GMT-06:00) To: [email protected] Subject: Re: [EXTERNAL] [RBASE-L] - Having Trouble with the SQL in a Project Statement
You might create a view combining the related tables. Then simply project from the view. The view might remain rather than being generated for each instance. No additional SQL statements would be required. All the best, Randy Peterson From: [email protected] <[email protected]> on behalf of Steve Sweeney <[email protected]> Sent: Wednesday, May 22, 2024 5:25 AM To: [email protected] <[email protected]> Subject: RE: [EXTERNAL] [RBASE-L] - Having Trouble with the SQL in a Project Statement <!-- @font-face {font-family:"Cambria Math"} @font-face {font-family:Calibri} @font-face {font-family:Aptos} @font-face {font-family:"Segoe UI"} p.x_MsoNormal, li.x_MsoNormal, div.x_MsoNormal {margin:0in; font-size:11.0pt; font-family:"Calibri",sans-serif} a:link, span.x_MsoHyperlink {color:blue; text-decoration:underline} span.x_EmailStyle19 {font-family:"Aptos",sans-serif; color:windowtext} .x_MsoChpDefault {font-size:10.0pt} @page WordSection1 {margin:1.0in 1.0in 1.0in 1.0in} div.x_WordSection1 {} --> Good idea to create the table in R:BASE first. Then, instead of using a sub-select statement join the tables/views directly in the SQL statement: --Create tablename (columnA, columnB ... etc) Then do INSERT INTO tablename (columnA, columnB ... etc) SELECT (tbl1.columnA,tbl1.columnB ... etc) FROM dbo.trace_vw tbl1 , rb_trace_serv_disp_vw tbl2 WHERE tbl1.part_id = 'P7035826608102' AND tbl1.ID_Trace = tbl2.ID_Trace Or, a different twist is to an over-arching view in SQL to join the various tables/views IN the SQL database, then simplify the “INSERT INTO” statement above by reducing the WHERE clause to “part_id = 'P7035826608102' thereby eliminating the need to join the two SQL tables/views in R:BASE: SELECT (columnA, column B … etc) FROM (SQL-joined-table/views) WHERE part_id = 'P7035826608102' Steve Sweeney From: 'Dan Goldberg' via RBASE-L <[email protected]> Sent: Tuesday, May 21, 2024 2:50 PM To: [email protected] Subject: RE: [EXTERNAL] [RBASE-L] - Having Trouble with the SQL in a Project Statement It could be that it does not support project command in external sql. Not sure which type of database you are connecting to, You can do the same like project by creating a temp table with the columns and then do an insert. --Create tablename (column definitions) Then do Insert into tablename select * FROM dbo.trace_vw USING * WHERE part_id = 'P7035826608102' and ID_Trace in (SELECT TRACE_ID from rb_trace_serv_disp_vw) Dan Goldberg From: [email protected] <[email protected]> On Behalf Of paul buckleyandassoc.com Sent: Tuesday, May 21, 2024 2:35 PM To: [email protected] Subject: [EXTERNAL] [RBASE-L] - Having Trouble with the SQL in a Project Statement You don't often get email from [email protected]. Learn why this is important Good afternoon, It’s been the longest time since I’ve reached out to this group for help, I’ve been beating my head against the wall doing mostly SQL stuff. I’m so glad to be back on this RBASE project. I am using the latest version, 11.0.1.40516. I’m trying to improve the performance of a PROJECT statement that uses an SATTACHED SQL view (dbo.trace_vw) and another RBASE table created by projecting from an SATTACHED SQL view (rb_trace_serv_disp_vw). The column Part_ID is common to both tables and ID_Trace is in dbo.trace_vw and TRACE_ID is in rb_trace_serv_disp_vw. If I run just the Select statement (SELECT TRACE_ID from rb_trace_serv_disp_vw) I get the list I’m looking for. If I run the entire project statement as shown below, it generates a table with no rows. I’ve been back and forth over this to where m eyes are just getting crossed. Am I missing something obvious? Any help my current case of “Blues Clues” would be greatly appreciated. PROJECT rb_trace_vw2 FROM dbo.trace_vw USING * WHERE part_id = 'P7035826608102' and ID_Trace in (SELECT TRACE_ID from rb_trace_serv_disp_vw) Thanks in advance, Paul Buckley [email protected] -- 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/CH2PR17MB34795B606421CA4AABA7A0B0D4EA2%40CH2PR17MB3479.namprd17.prod.outlook.com. -- 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/BY3PR19MB5027691DC2AA93D0077EFCC6D4EA2%40BY3PR19MB5027.namprd19.prod.outlook.com. -- 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/BYAPR03MB3718CD5690F7B0AD57C2CCA789EB2%40BYAPR03MB3718.namprd03.prod.outlook.com. -- 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/PH0PR11MB5112307B17C830B4989EFB77C7EB2%40PH0PR11MB5112.namprd11.prod.outlook.com. -- 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/9lIys5dC7vfD19lJ2sn2qX.1716381781.9c66cf2c9a1fe8fc519dd5f9c3415394.MISSINGID%40comcast.net.
