Outer Joiins And Cross Joins Not Working

Jun 17, 2007

full outer joins and cross joins not working!?!?


am using vc++2005, ADO, and MSAccess 2003. MS "documentation" straight out of the VC++2005 help facility at

ms-help://MS.VSCC.v80/MS.MSDN.v80/MS.VisualStudio.v80.en/dv_vdt01/html/419ef633-5a89-41a2-aefe-03540afc9112.htm

provided the following code samples for different types of joins

inner join



Code:
SELECT title, pub_name
FROM titles INNER JOIN
publishers ON titles.pub_id = publishers.pub_id left join



Code:SELECT titles.title_id,
titles.title,
publishers.pub_name
FROM titles LEFT OUTER JOIN publishers
ON titles.pub_id
= publishers.pub_idright join



Code:SELECT titles.title_id,
titles.title,
publishers.pub_name
FROM titles RIGHT OUTER JOIN publishers
ON titles.pub_id
= publishers.pub_idfull join



Code:SELECT titles.title_id,
titles.title,
publishers.pub_name
FROM titles FULL OUTER JOIN publishers
ON titles.pub_id
= publishers.pub_idjoin


Code:
SELECT *
FROM authors CROSS JOIN publishers i created two MSAccess Tables:

Merge1:

K1 x
---- ----
a 1
b 2
c 3

Merge2:

K1 x
---- ----
b 20
c 30
d 40
e 50

and executed the following code to test the different joins. the first three joins worked but the last two did not. would appreciate any insight. DBM is an instance of an AccessDBManager class i have written to encapsulate interactions with Access DBs.


Code:
DBM.SQLExtractString = "select * from Merge1 INNER JOIN Merge2 on Merge1.K1 = Merge2.K1";
DBM.SQLExtract();
s.Format(_T("%d"),DBM.SQLExtractRecords);
MessageBox(s,_T(""),MB_OK);this worked - 2 records returned (K1 = b,c)



Code:DBM.SQLExtractString = "select * from Merge1 LEFT OUTER JOIN Merge2 on Merge1.K1 = Merge2.K1";
DBM.SQLExtract();
s.Format(_T("%d"),DBM.SQLExtractRecords);
MessageBox(s,_T(""),MB_OK);this worked - 3 records returned (K1 = a,b,c)



Code:DBM.SQLExtractString = "select * from Merge1 RIGHT OUTER JOIN Merge2 on Merge1.K1 = Merge2.K1";
DBM.SQLExtract();
s.Format(_T("%d"),DBM.SQLExtractRecords);
MessageBox(s,_T(""),MB_OK);this worked - 4 records returned (K1 = b,c,d,e)



Code:DBM.SQLExtractString = "select * from Merge1 FULL OUTER JOIN Merge2 on Merge1.K1 = Merge2.K1";
DBM.SQLExtract();
s.Format(_T("%d"),DBM.SQLExtractRecords);
MessageBox(s,_T(""),MB_OK);this did not work - 0 records returned instead of 5 (K1 should = a,b,c,d,e)



Code:DBM.SQLExtractString = "select * from Merge1 CROSS JOIN Merge2";
DBM.SQLExtract();
s.Format(_T("%d"),DBM.SQLExtractRecords);
MessageBox(s,_T(""),MB_OK);this did not work - 0 records returned instead of 20 (5 * 4)

appreciate any ideas/comments to get the last two joins to work.

thanks - j

View 3 Replies


ADVERTISEMENT

Joins On Views That Are Formed With Outer Joins

Nov 3, 2000

We find that a delete command on a table where the rows to be deleted involve an inner join between the table and a view formed with an outer join sometimes works, sometimes gives error 625.

If the delete is recoded to use the join key word instead of the = sign
then it alway gives error 4425.


625 21 0 Could not retrieve row from logical page %S_PGID by RID because the entry in the offset table (%d) for that RID (%d) is less than or equal to 0. 1033
4425 16 0 Cannot specify outer join operators in a query containing joined tables. View '%.*ls' contains outer join operators.
The delete with a correleted sub query instead of a join works.

Error 4425 text would imply that joins with view formed by outer joins should be avoided.

Any ideas on the principles involved here.

View 1 Replies View Related

What Does Just 'JOIN' Statement Do W/o Inner Or Outer Or Cross In Front

Jul 26, 2002

There is some code that I am looking at. It is like this

SELECT a.Name, p.Name
FROM applicant a
JOIN Pending p
ON a.ID = p.ID

Thanks list.

View 2 Replies View Related

Outer Joins

Sep 2, 1999

I have been using an outer join to display information in a form. I would like to edit the rows that are returned. The join shows all of the rows in the 1st table, and the corresponding rows in the 2nd other. If there are no corresponding rows in the 2nd table, a NULL is displayed. The problem is when I try to edit one of the fields that is "NULL", I get an error. I know why the error is happening, because the NULL field is a row that does not exist, yet. The jet database engine will automatically insert rows into the 2nd table, but SQL server will not. I have tried to set primary key and foreign key properties for the tables in question, but it still does not work. Does anyone know how to do this?

Thanks for the help.
Davant

View 1 Replies View Related

Outer Joins

Apr 29, 1999

I have been reading about outer joins and how they are much faster to process on larger data sets than NOT EXISTS are..Did anyone find any significant performance gain..Logically, using an outer join does not make sense in this context..Can someone shed some light on this subject?

View 2 Replies View Related

Outer Joins

Jul 20, 2005

I am trying to create an outer join between two tables in a query thatincludes several other tables.When I double-click on the Join line, it presents three join options:1) ONLY records from table1 and table2 where join fields are equal2) ALL values from table1 and ONLY records from table2 where joinfields are equal3) ALL values from table2 and ONLY records from table1 where joinfields are equalIn my case, I want option 2 - all values from table1, and if there isno match to table2, I want a blank to appear in the output.When I select this option, I get the following error:"Can't have outer joins if there are more than two tables in thequery."How can I get around this, since there are other tables in my query?Thanks.Dennis HancyEaton CorporationCleveland, OH

View 1 Replies View Related

Order Of Inner Joins And Cross Apply

Sep 29, 2015

Will the order of inner joins and cross apply effect the results of a query?

Example:
FROM dbo.vw_Info v
CROSS APPLY dbo.usf_LastFee(v.StoreID, v.AgreementID, v.CustomerID, ABS(v.PrePaymentBalance), cp.ConfirmationNo) lf
INNER JOIN dbo.Customers c

[Code] ....

I want to change the position of doing "CROSS APPLY". Will it effects query results?

View 2 Replies View Related

Help Withe SQL Inner And Outer Joins.

Nov 25, 2003

Hello-

I am confused with inner and outer joins. Can someone explain to me exactly what the differences are? The reason I am asking is I am trying to populate a datagrid from multiple tables. The SQL query currently pulls out all customers that have revenue only, When in fact I need it to pull out all customers regardless of revenue so that I can see which customers have "0" revenue.

Here is my sql query which sorts each day into a new datagrid column.

SQL = "SELECT revenue_forecast.oct_03 AS 'forecast', pb_customers.customer_name AS 'customer', sum(case when day(pb_report_shippers.shipper_date_time) = 1 then pb_report_shippers.total_ext_price + pb_report_shippers.setup_cost else 0 end) as day1, sum(case when day(pb_report_shippers.shipper_date_time) = 2 then pb_report_shippers.total_ext_price + pb_report_shippers.setup_cost else 0 end) as day2, FROM pb_report_shippers Left outer JOIN pb_jobs ON pb_report_shippers.job_id = pb_jobs.job_id left outer JOIN pb_customers ON pb_jobs.customer_id = pb_customers.customer_id left outer JOIN revenue_forecast ON pb_customers.customer_id = revenue_forecast.customer_id WHERE pb_report_shippers.shipper_date_time between cast('11/01/03' as datetime) and cast('11/2/03' as datetime) AND pb_report_shippers.job_completed IN('1','0') AND pb_customers.customer_deleted <> '1' GROUP by pb_customers.customer_name, revenue_forecast.oct_03 Order BY pb_customers.customer_name"


Any help would be appreciated. Thanks

View 3 Replies View Related

Outer Joins Using More Than Two Tables.

Jun 14, 2001

Is it possible to utilize more than two tables in a single outer join?
I have one table that I want every row and 18 others where I only want an entry if one is present meeting the conditions of "1.customerid = 2.Customerid" etc. I haven't run across this before and would appreciate any help.

View 2 Replies View Related

Two Outer-joins In One Query

Oct 5, 2003

I wrote a SQL query that firstly do an outer-join for two table and after I add another
table with outer-join as well.
I'm getting the following message:
Server: Msg 301, Level 16, State 1, Line 1
Query contains an outer-join request that is not permitted.
May be someone knows what is it?
If I remove the second join- it work....

View 2 Replies View Related

Outer Joins In SQL Server

Aug 6, 2002

Hi,
I need to change some SQL statements in Oracle to to SQL Server syntax.
For example, the following is a query in Oracle SQL syntax.
================================================== ===========================
SELECT
COUNT(*)
FROM TABLE1 T1,
TABLE2 T2,
TABLE3 T3,
TABLE4 T4,
TABLE5 T5
WHERE
(
T1.NO(+)=T3.NO
AND T3.NO=T2.NO(+)
AND T4.NO(+)=T3.NO
AND T3.NO=T5.NO(+)
)
AND
(
T1.CODE(+)='VALUE1'
AND T2.CODE(+)='VALUE2'
AND T4.CODE(+)='VALUE3'
AND T5.CODE(+)='VALUE4'
)
================================================== =========================
Can you anyone please tell me the SQL Server equivalent? I am having doubts in the outerjoin part.
Thanks in advance
Regards
R

View 7 Replies View Related

Creating Outer Joins

Dec 30, 2004

I have a query:

select * from furn_piece_master fpm, furn_piece_detail fpd, furn_detail fd
where fpd.fpd_master_id = fpm.fpm_id
and fpm.ymr = '200411U'
and fpd.ymr = fpm.ymr
and fpd.chain = fpm.chain
and fpd.store = fpm.store
and fpd.week = fpm.week
and fd.ymr = fpd.ymr
and fd.chain = fpd.chain
and fd.store = fpd.store
and fd.report_type = fpd.report_type
and fd.week = fpd.week
and fd.item_key = fpd.item_key

Now I want to do an outer join on the furn_detail (fd) store only, but leave the rest of the furn_detail inner joins. How would I do this?

View 1 Replies View Related

SQL Outer Joins Problem

Aug 17, 2006

Hi,

I'm upgrading from SQL 2000 -> 2005 at the moment. Having an issue with some old Outer Join SP's which use the =* and *= operators.

The issue i am having is this. Using this Procedure on the Pubs DB:
SELECT title, qty FROM titles, sales
WHERE titles.title_id *= sales.title_id
ORDER BY qty

Works fine. I get 23 rows with 2 of them having NULL in qty.

This also works, giving the same result:
SELECT title, qty
FROM titles
LEFT OUTER JOIN sales
ON titles.title_id = sales.title_id
ORDER BY qty

All looking good. Now, just say i want to limit the rows from the Right table. Lets just select Store #6380. Using the old *= operator:
SELECT title, qty
FROM titles, sales
WHERE titles.title_id *= sales.title_id
AND Stor_ID = 6380
ORDER BY qty

This works fine, giving me all 18 titles, with sales amounts next to two of them, the rest are null, which is the response I want. However, if I insert the same WHERE clause into the OUTER JOIN code:
SELECT title, qty
FROM titles
RIGHT OUTER JOIN sales
ON titles.title_id = sales.title_id
WHERE Stor_Id = 6380
ORDER BY title

It seems to act like an Inner Join, not an Outer Join, giving me only rows that match on both sides. I can't seem to work out why this would do this. Am I overlooking something simple? Can someone give me some idea why it's not working?

Thanks,

Josh

View 6 Replies View Related

OUTER LEFT JOINs On SQL CE

Feb 17, 2008

Hi,

First of all, is this an appropriate place to get answers related to SQL CE? If not, do you have any recomended forums elsewhere?

I'm trying to get a list of various related tables using ResultSets on SQLCE 3. The query is something like this:

SELECT A.*
, F.Descricao AS FamiliasDescricao
, M.Descricao AS MarcasDescricao
, I.Descricao AS IVADescricao
FROM Artigos AS A
LEFT OUTER JOIN Familias AS F ON A.FamiliasUID = F.UID
LEFT OUTER JOIN Marcas AS M ON A.MarcasUID = M.UID
LEFT OUTER JOIN IVA AS I ON A.IVAUID = I.UID
INNER JOIN ArtigosTipos AS AT ON A.ArtigosTiposUID = AT.UID;

The column ArtigosTiposUID cannot be NULL, so an INNER JOIN is used, but the other UID columns can have a NULL value, and I need all the rows on Artigos to show up even if these other UIDs are NULL. The query runs fine like this in VS2005, returning NULL values for the columns if there are no rows on the other tables, both on the SQL Server 2005 database and the .sdf database used on the Windows Mobile device. But on Windows Mobile SQL CE gives me an "Unspecified error [7]", Native error 25607, an the stack trace ends with:

em System.Data.SqlServerCe.SqlCeCommand.ProcessResult s()
em System.Data.SqlServerCe.SqlCeCommand.CompileQueryP lan()
em System.Data.SqlServerCe.SqlCeCommand.ExecuteComman d()
em System.Data.SqlServerCe.SqlCeCommand.ExecuteResult Set()

If I replace all the LEFT OUTER JOINs with INNER JOINs only the rows where all UIDs have a value show up, but as I said, I want all rows on tabela Artigos. Even if I remove all JOINs except the last one and replace it with a LEFT OUTER JOIN I get the same erro, all rows having the column ArtigosTiposUID defined... it seems as if the simple presence of LEFT OUTER JOIN makes SQL CE return an error.

Is there a way to run the queries on VS using the SQL CE engine so that one can check whether the query will run successfuly on Windows Mobile?

Any other sugestions?

Thank you,
Hugo Lopes

View 6 Replies View Related

Mixing INNER And OUTER Joins

Sep 26, 2006

I've encountered this problem multiple times in the past and I have a solution but wonder if there might be a more elegant method of achieving the same result...

Take the following example:SELECT * FROM [User]
LEFT OUTER JOIN [Profile] ON [Profile].[UserId] = [User].[UserId]
INNER JOIN [Department] ON [Department].[DepartmentId] = [Profile].[DepartmentId]

Users may or may not have a profile (but never more than one). A profile may or may not have a department (but never more than one).

Now, this will return only users that have a profile even though an outer join has been used. What I really want is to return all users and include their profile and department details but only when the profile has a department.

The solution I have used in the past is:

SELECT * FROM [User]
LEFT OUTER JOIN
(
SELECT *
FROM [Profile]
INNER JOIN [Department] ON [Department].[DepartmentId] = [Profile].[DepartmentId]
) [ProfileDepartment] ON [ProfileDepartment].[UserId] = [User].[UserId]

The trouble here is that I've lost the ability to reference department and profile independantly in the outer query. Also, more complex scenarios can also become horribly complex if this needs to be done multiple times in the same query.

I could do this:SELECT * FROM [User]
LEFT OUTER JOIN [Profile] ON [Profile].[UserId] = [User].[UserId] AND [Profile].[DepartmentId] IS NOT NULL
LEFT OUTER JOIN [Department] ON [Department].[DepartmentId] = [Profile].[DepartmentId]

But again I feel that the intention is not at all clear. I want to inner join department to profile because I'm only interested in profiles with a department and departments referenced by a profile.

I would like to be able to specify that the departments should be inner joined to profiles and whichever profiles remain get outer joined to users whilst retaining department and profile as seperate entities within the query.

Is there any way to use brackets to indicate an order of precedance to the logical joins within the from clause?

Daniel

View 3 Replies View Related

Views &&amp; Outer Joins

Sep 18, 2006

I'm having problems with queries that use a view in an outer join. It's just sits there and grinds. Below is some queries to illustrate.

SELECT EvalID,RecIndex
FROM dbo.vCurrentEvalInfo

Executed in 1 second in SQL Query Analyzer
Returns 29,820 Rows
----

SELECT RecIndex
FROM dbo.tblCNAKeyInformation

Executed in 1 second in SQL Query Analyzer
Returns 47,978 Rows
----

SELECT C.RecIndex, E.EvalID
FROM dbo.tblCNAKeyInformation C LEFT OUTER JOIN dbo.vCurrentEvalInfo E
ON C.RecIndex=E.RecIndex

Executed in 1 second in SQL Query Analyzer
Returns 47,978 Rows
----

SELECT C.RecIndex, E.EvalID, E.DATE_COMP, E.EvalDate, E.EvalCode, E.EvalResults
FROM dbo.tblCNAKeyInformation C LEFT OUTER JOIN dbo.vCurrentEvalInfo E
ON C.RecIndex=E.RecIndex

Executed in 1 second in SQL Query Analyzer
Returns 47,978 Rows
----

Never Finishes Executing. If I include any column from the dbo.tblCNAKeyInformation and the query just grinds.

exampe:
SELECT C.RecIndex, E.EvalID, E.DATE_COMP, E.EvalDate, E.EvalCode, E.EvalResults, C.LastName
FROM dbo.tblCNAKeyInformation C LEFT OUTER JOIN dbo.vCurrentEvalInfo E
ON C.RecIndex=E.RecIndex


Also, the below query doesn't finish executing either.
SELECT RecIndex
FROM dbo.tblCNAKeyInformation
WHERE (RecIndex NOT IN
(SELECT RecIndex
FROM dbo.vCurrentEvalInfo))

Any I missing something Here? I seems like it's recalcualting the view with each scan of the outer table on the join instead of using a cached copy for x number of micro seconds ago.

I could solve this my dumping the view to a temp table, however that seems like an ugly hack to me.

Any help would be appreciated.

View 1 Replies View Related

How Can We Trust Outer Joins

Apr 26, 2006

In the future =* or *= will not be suportted but I found more then one examples that show outer join does not bring all the rows =* does.

Can any one tell me what is wrong? Market table has 19 markets Budget table has rows for BudgetYear=2006 but none for 2007

BTW the same is true in SQL2005 and 2000

----------------------------------------------------------------

SELECT Market_code, Description,
Revenue = SUM( CASE WHEN BudgetType = 'B' THEN BudgetAmount ELSE 0 END ),
CM = SUM( CASE WHEN BudgetType = 'C' THEN BudgetAmount ELSE 0 END ),
Tooling = SUM( CASE WHEN BudgetType = 'T' THEN BudgetAmount ELSE 0 END )
FROM Budget RIGHT OUTER JOIN Market ON Budget.Customer_Market_code = Market.Market_code
Where Budget.BudgetYear=2007 or Budget.BudgetYear is null
GROUP BY Market_code, Description
ORDER BY Description

-- ------------------GIVES only 12 results -------------------------------



SELECT Market_code, Description,
Revenue = SUM( CASE WHEN BudgetType = 'B' THEN BudgetAmount ELSE 0 END ),
CM = SUM( CASE WHEN BudgetType = 'C' THEN BudgetAmount ELSE 0 END ),
Tooling = SUM( CASE WHEN BudgetType = 'T' THEN BudgetAmount ELSE 0 END )
FROM Budget, Market
Where Budget.Customer_Market_code =* Market.Market_code
AND Budget.BudgetYear=2007
GROUP BY Market_code, Description
ORDER BY Description

-- ------------------GIVES 19 results -the true------------------------------

View 1 Replies View Related

Informix OUTER Joins To T-SQL ?

Jan 4, 2008



Hello,

I am currently in the process of converting an ASP.net app from an Informix backend to SQL Server 2005.

Informix has an OUTER keyword which wraps a table in a query and allows it to return NULL.

i.e. this:

SELECT * FROM Table1 OUTER(Table2) WHERE Table1id = Table2id

is the T-SQL equivalent of :


SELECT * FROM Table1 LEFT JOIN Table2 ON Table1ID = Table2ID

However Informix has something called a 'Nested Simple Join' which looks like the following :

SELECT * FROM Table1 OUTER(Table2, Table3) WHERE Table1id = Table2id AND Table2id=Table3id


Would anyone know the T-SQL equivalent of the above - any help would be greatly appreciated.

THANKS

View 1 Replies View Related

Application Roles For Cross-Database Joins

Aug 25, 2005

I have an application that segregates data into two differentdatabases. Database A has stored procs that perform joins betweentables in database A and database B. I am thinking that I have reachedthe limits of Application Roles, but correct me if I am wrong.My application creates a connection to database A as 'testuser' withread only access, then executes sp_setapprole to gain read writepermissions. Even then the only way 'testuser' can get data out of thedatabases is via stored procs or views, no access to tables directly.Anyone know of a solution? Here is the error I get:Server: Msg 916, Level 14, State 1, Procedure pr_GetLocationInfo, Line38Server user 'testuser' is not a valid user in database 'DatabaseB'The system user is in fact in database A and B.thanksJason Schaitel

View 4 Replies View Related

Right/left Outer Joins && Performance

Nov 13, 2003

Other than being much less readable, is there a downside to combining left and right outer joins in the same SELECT? I'm reviewing some generally poor code done by a contractor and it's peppered with queries with both left and right joins. I've always thought it was just a semantic difference, but I was just wondering if, other than readability, there were any performance issues.

Thanks,
Pete

View 1 Replies View Related

Outer Joins Does Not Work As Expected

Jul 27, 2000

Hi,


I am trying to extract the non matching records and the matching ones
in two tables from two servers, one a linked server in one go.
For example if table A has records,
Rec1, Rec2, Rec3 and Rec6
AND Table B has
Rec1, Rec2, Rec3 and Rec7
I need to get in the result set
Rec1, Rec2, Rec3, Rec6 and Rec7.

The real query I ran is as follows. I want to know the list of all tables
in GlobalDB database in sg_qt1 and sg_q5 servers.
NOTE : sg_q5 is a Linked server to sg_qt1.

Select Substring(a.name,1,30), Substring(user_name(a.uid),1,19)
from sysobjects a
full outer JOIN sg_q5.globaldb.dbo.sysobjects b
ON a.name = b.name
where a.xtype = 'u' and b.xtype = 'u'
and a.name not in ('dtproperties','rowcounts','globalDBrowcounts')

If I run it from sg_qt1, the result I get contain all tables from sg_qt1
but not the non-matching ones from sg_q5.

I am obviously doing something wrong, but what is it?

Thanks.
If possible please reply to, r.wimalaratne@iaea.org

Ranjit

View 1 Replies View Related

Outer Joins And Substring Error

Nov 28, 2005

My problem exists on the last line - TOAD doesn't seem to like the two sets of parenthesis together. Am I supposed to put something in between? It's OK in the line above because the substring is on the other side.

SELECT distinct fp.active_fl, fp.empl_id, fp.proj_mgr_name, pm.project, fp.subctr_id, rd.CONT_CRNCY
FROM planner.rbws_proj_mgr pm, finance.fin_proj fp, finance.rbws_detl rd
WHERE SUBSTR(fp.proj_id, 1, 9) = pm.project (+)
AND pm.PROJECT = substr(rd.PROJ_ID,1,9) (+)

View 2 Replies View Related

SQL Server 2005 - Help With Outer Joins

Mar 26, 2007

Hello,

I'm currently trying to rewrite some existing non-ANSI joins and have come across one that I'm not sure how to handle.

Here is a sample of the existing code:

SELECT receipts_payment_method.transaction_number,
receipts_detail.terms_amt,
receipts_detail.allowed_amt,
isNull(receipts_payment_method.check_number,'n/a') check_number,
isNull(receipts_payment_method.credit_card_type,'n/a') credit_card_type,
isNull(paymnt_method.payment_desc,'n/a') payment_desc,
isNull(cc_type.credit_card_desc,'n/a') credit_card_desc
FROM receipts,
receipts_detail,
receipts_payment_method,
paymnt_method,
cc_type
WHERE (receipts_payment_method.payment_id *= paymnt_method.payment_id) and (cc_type.credit_card_id =* receipts_payment_method.credit_card_type) and (receipts_payment_method.transaction_number = receipts.transaction_number);

I'm not sure how to rewrite the two joins since there are 3 tables involved (receipts_payment_method, paymnt_method, and cc_type) along with an "and".

Any help would be appreciated! Thanks in advance!

View 1 Replies View Related

How Do You Do Multiple Left Outer Joins???

May 31, 2006

I need to do multiple left outer join to return search profiles that could contain NULL in them that could also be foreign keys.  I bolded the three IDs that could be NULL or have a foreign key for a value.  An example with my code would be great I've tried decyphering the many employee and company examples on the web but I haven't figured it out yet.  Right now I only get profiles that have foreign key values and it misses the rest in the search.   So NULL MakeID or ModelID no result on that item my SQL statement below.  Using SQL Server 2005.  Pretty new this to SQL and databases but so far this has been the only trying part.

Thanks

String dbsql = "SELECT a.EquipmentID " +
                         "     , a.SerialNo " +
                         "     , b.Category " +
                         "     , c.Subcategory " +
                         "     , d.Make " +
                         "     , e.Model " +
                         "     , f.Status " +
                         "  FROM tblEquipInfo a " +
                         "     , tblEquipCat b " +
                         "     , tblEquipSubcat c " +
                         "     , tblEquipMake d " +
                         "     , tblEquipModel e " +
                         "     , tblStatus f " +
                         " WHERE b.Category = '" + val + "' " +
                         "   AND a.CategoryID = b.CategoryID " +
                         "   AND a.SubcategoryID = c.SubcategoryID " +
                         "   AND a.MakeID = d.MakeID " +
                         "   AND a.ModelID = e.ModelID " +
                         "   AND a.StatusID = f.StatusID";

 

 

View 3 Replies View Related

Arithmetic Overflow Error When Doing Outer Joins

Jul 19, 2006

Hey everyone,

I have two tables, one is a large table (v_userviews) containing a list of all the servers and various information about those servers. The other table (l_printers) contains printer information for those servers. I am working on a view to consolidate the printer information in l_printers with the other server information in v_userviews.

I've been trying to get outer joins to work but I am getting this error:
"Server: Msg 8115, Level 16, State 2, Line 2
Arithmetic overflow error converting expression to data type int.
Warning: Null value is eliminated by an aggregate or other SET operation."

Here is my select statement:

select u.propid, u.address,
SUM((CASE u.Tree WHEN 'tree1' then 1 ELSE 0 END)) AS One,
SUM((CASE u.Tree WHEN 'tree2' then 1 ELSE 0 END)) AS Two,
SUM((CASE u.Tree WHEN 'tree3' then 1 ELSE 0 END)) AS Three,
SUM((CASE u.Tree WHEN 'tree4' then 1 ELSE 0 END)) AS Four,
SUM((CASE u.Tree WHEN 'tree5' then 1 ELSE 0 END)) AS Five,
SUM((CASE u.Tree WHEN 'tree6' then 1 ELSE 0 END)) AS Six,
SUM((CASE u.Tree WHEN 'tree7' then 1 ELSE 0 END)) AS Seven,
SUM((CASE u.Tree WHEN 'tree8' then 1 ELSE 0 END)) AS Eight,
SUM((CASE u.Tree WHEN 'tree9' then 1 ELSE 0 END)) AS Nine,
SUM((CASE u.Tree WHEN 'tree10' then 1 ELSE 0 END)) AS Ten,
SUM((CASE u.Tree WHEN 'tree11' then 1 ELSE 0 END)) AS Eleven,
SUM((CASE u.Tree WHEN 'tree12' then 1 ELSE 0 END)) AS Twelve,
SUM((CASE u.Tree WHEN 'tree13' then 1 ELSE 0 END)) AS Thirteen,
SUM((CASE u.Tree WHEN 'tree14' then 1 ELSE 0 END)) AS Fourteen,

count(u.server) as totalservers,
sum(cast(left(u.totalspace,len(u.totalspace)-2) as int)) as totalspace,
sum(cast(left(u.totalusedspace,len(u.totalusedspac e)-2) as int)) as totalusedspace,
count(p.printer) as numprinters

from serverops.dbo.v_userviews u LEFT OUTER JOIN novell_twr.dbo.l_printers p ON u.propid = p.propid
where u.os='netware'and u.state in ('ny', 'nj', 'fl')
group by u.propid, u.address

the l_printers table is in this format:

Printers Server Propid
nvarchar nvarchar varchar



Thanks for all your help. :beer:

View 6 Replies View Related

Speed Up 6 Consecutive Left Outer Joins

Apr 28, 2008

Hi,

The following query should return a list of clr_id's that have a match in at least 1 of the other fields mentioned in the joins.

declare @keyWord varchar(40)
set @keyWord = 'merc'
set NOCOUNT on

SELECT distinct clr.clr_id
FROM CLR
LEFT OUTER JOIN CO ON CLR.CO_ID = CO.CO_ID
LEFT OUTER JOIN CLR_NM ON CLR.CLR_ID = CLR_NM.CLR_ID
LEFT OUTER JOIN CLR_USE_YR ON CLR.CLR_ID = CLR_USE_YR.CLR_ID
LEFT OUTER JOIN MODL ON CLR_USE_YR.MODL_ID = MODL.MODL_ID
LEFT OUTER JOIN PAINT_CD ON CLR.CLR_ID = PAINT_CD.CLR_ID
WHERE co.long_nm like '%'+@keyWord+'%'
OR clr_nm.clr_nm like '%'+@keyWord+'%'
OR clr_use_yr.yr_num like '%'+@keyWord+'%'
OR paint_cd.paint_cd like '%'+@keyWord+'%'
OR modl.modl_nm like '%'+@keyWord+'%'

The query runs at 3secs.
Could I improve the query somehow?
I was thinking that, since I actually need a distinct set of clr_id's, I should somehow check only the clr_id's that don't have a match in any of the previous joins.

View 19 Replies View Related

Full Outer Joins Supported? Unsupported?

Mar 12, 2007

 

Has anyone tried using a TSQL full outer join and managed to get it working? Any documentation for Full Outer Joins in CE?

 

Any ideas? Thanks, Bill

View 1 Replies View Related

Not Grasping Outer Joins For Ms-sqlserver :: 3 Tables To Join

Mar 15, 2006

I'm new to ms-sqlserver ( 2000 ) and need to get an OUTER JOIN workingon a three table query.Assumptions:-- I have events in the Event table.-- Each event CAN have one Transaction, but it's not guaranteed-- Each transaction, ir present, will have one or more Amount recordsThis would be the pseudo-query without any special joins:-----------------------------------------SELECTa.Name,SUM( c.amount ) as TotalFROMEvent a,Transaction b,Amounts cWHEREa.EventID = b.EventIDANDb.TransID = c.TransID-----------------------------------------This is fine if there is a Transaction for the Event. But, if there'sno transaction for an event, no record is pulled of course.What I need is for a record to come back for each event regardless ofthe presence of a Transaction. If there's no transaction, then the"Total" column should be 0.How would I get an OUTER JOIN to work on this so that each Event gets arecord?TIA-BEP

View 4 Replies View Related

Query Optimizer Problems With Many LEFT/RIGHT OUTER JOINs

Mar 14, 2008

I am having an issue with large queries using Microsoft SQL Server 2005 - 9.00.2221.00 (X64).

I have a query with many INNER/LEFT OUTER/RIGHT OUTER joins which is taking very very very long to run. This looks exactly like this problem described in http://support.microsoft.com/kb/318530. However, this doc says it was fixed in SP1, which is already installed.


Basically I have a query:

SELECT ....
FROM TABLEA

INNER JOIN TABLEB ...
LEFT OUTER TABLEC...
LEFT OUTER TABLED...
RIGHT OUTER TABLEF...
LEFT OUTER TABLEJ..
LEFT OUTER TABLEH...
LEFT OUTER TABLEI...
RIGHT OUTER TABLEK...
LEFT OUTER TABLEM..
... 17 joined tables in all......
WHERE TABLEB.field1 = 'abc'


The query plan for this is using TABLEA as the "main" table and joining everything else to it. The problem is, TABLEA has 117 MILLION records. TABLEB has 10,000 records which match the WHERE. I stopped this query after it ran for 62 HOURS.

If I simply change the query to:

SELECT ....
FROM TABLEB

INNER JOIN TABLEA ...
LEFT OUTER TABLEC...
LEFT OUTER TABLED...
RIGHT OUTER TABLEF...
LEFT OUTER TABLEJ..
LEFT OUTER TABLEH...
LEFT OUTER TABLEI...
RIGHT OUTER TABLEK...
LEFT OUTER TABLEM..
... 17 joined tables in all......
WHERE TABLEB.field1 = 'abc'

The query runs in 15 mins. The query plan now uses TABLEB and the WHERE clause to join all the other tables.

The problem is, this query is generated from a report writter, and I have no control over the way it creates the SQL code.

Any ideas how I can fix this?

View 1 Replies View Related

Transact SQL :: Null Values After Converting To Right Outer Joins

Sep 25, 2015

I have tried all possible combinations of changing this. But was not able to make the results tally.I am giving you a part of the query, there are others queries involving 4 tables which are based on this same temporary table query.

SELECT c.juris_id, b.jrnl_mo_yr 
FROM a_trueup a,
#t_mths b, r_rj c   
WHERE a.rlzd_mo_yr
=* b.jrnl_mo_yr
AND 
a.juris_id
=* c.juris_id

[code]....

I tried using left outer join as mentioned in blogs but got a different result (14 rows).I also used set null off/on options but no luck ..

View 15 Replies View Related

SSMA For Oracle And Migrating Views With Outer Joins

May 23, 2008



I am useing SQL Server Migration Tools for Oracle to move some data for archive purposes.

The data is moving great, however the SSMA crashes or just plain stops when it tries to convert a view that has an outer join.

Is ther any sort of fix for this?

View 1 Replies View Related

Analysis :: Cross Joins Across User Defined Hierarchies Aren't Supported

Sep 20, 2011

I have some confusion on crossjoin function within MDx.while I try to crossjoin the different level sets of same Hierarchy. It shows error as

For example.
‘The Customer Geography hierarchy is used more than once in the Crossjoin function.’
select {
{[Customer].[Customer Geography].[Country].&[United States]}*
{[Customer].[Customer Geography].[State-Province].members}}
on 0
FROM [Adventure Works]
WHERE Measures.[Internet Sales Amount]

Cannot we Cross joins across user defined hierarchies ,or they aren't supported .?Coz I really need to implement as above MDx within my real Cube.I try to implement by making as another Hierarchy Member but it doesn’t gives the value result as what we want/need.with

member [Customer].[Country].[United States ]as [Customer].[Customer Geography].[Country].&[United States]
select {
{[Customer].[Country].[United States ]}*
{[Customer].[Customer Geography].[State-Province].members}}
on 0
FROM [Adventure Works]
WHERE Measures.[Internet Sales Amount]

View 11 Replies View Related

DB Engine :: Linked Server - Getting Error When Performing Cross Instance Query With Joins

Apr 26, 2015

I've successfully created a Linked Server that connects a local DB Engine with another DB Engine through an ip over an extranet. I am able to run simple Select statement queries on the Local DB Engine and get results from the linked server. However when attempting to perform more complex queries that join tables from the linked server with tables from the local DB server, I get the following error message after several minutes of execution:

OLE DB provider "SQLNCLI11" for linked server "<ip of Linked Server>" returned message "Protocol error in TDS stream".
OLE DB provider "SQLNCLI11" for linked server "<ip of Linked Server>" returned message "Communication link failure".

Msg -1, Level 16, State 1, Line 0

Session Provider: Physical connection is not usable [xFFFFFFFF].

OLE DB provider "SQLNCLI11" for linked server "<ip of Linked Server>" returned message "Communication link failure".

Msg -1, Level 16, State 1, Line 0

Session Provider: Physical connection is not usable [xFFFFFFFF].

OLE DB provider "SQLNCLI11" for linked server "<ip of Linked Server>" returned message "Communication link failure".

Msg 10054, Level 16, State 1, Line 0

TCP Provider: An existing connection was forcibly closed by the remote host.

How I can resolve it. I've read on Distributed Transactions but I understand that it only applies to manipulation statements?

Both are SQL servers. Linked Server is SQL2008R2 if not mistaken. Local DB Engine is SQL2014.

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved