SQL Query Help - Right Outer Join With Where Clause
Aug 23, 2007
Using SQL Server Reporting services 2005
I am reporting on a system with 32 devices, each of these devices can have certain events that happen to it that are logged and timestamped.
I need a table to show the count of each events that have happened to it within a certain time period.
This code snippet below works fine BUT if there are no events that happen to a certain device in the time period, then that device is 'missing' from the table.
What I need is basically a row for every device, regardless of if it has had any events happen to it (I will just show '0' for the event count)
Any thoughts? I'm a complete newbie at this by the way.
Thanks
Code Snippet
SELECT DeviceStatusWords.DeviceName, COUNT(DeviceEventDurationLog.StatusBit) AS BitCount, DeviceEventDurationLog.StatusBit AS Bit
FROM DeviceEventDurationLog RIGHT OUTER JOIN
DeviceStatusWords ON DeviceEventDurationLog.DeviceID = DeviceStatusWords.DeviceID
WHERE (DeviceEventDurationLog.TimeIn > @StartDate) AND (DeviceEventDurationLog.TimeIn < @EndDate)
GROUP BY DeviceStatusWords.DeviceName, DeviceEventDurationLog.StatusBit
ORDER BY DeviceStatusWords.DeviceName
I am trying to get all of the Fund_cdes to show up even if there was no transaction on the brkg fact table. The problem I coming up with is I am also retricting what I show in the brkg fact table so I am not getting all of the row from the fund table. How do I write a left join that shows all of the fund cdes
SELECT SEP_ACCOUNT.sep_acct_cde as Account, FUND.fund_cde as FUND, BRKG_FACT.accum_unit_cnt as Units_Purchased, BRKG_FACT.transaction_amt as Amount_Purchased FROM BRKG_FACT SEP_ACCOUNT FUND where BRKG_FACT.sep_acct_id_num = SEP_ACCOUNT.sep_acct_id_num brkg_fact.FUND_ID_NUM = FUND_DIM.FUND_ID_NUM brkg_fact.SEP_ACCT_ID_NUM = 5 and brkg_fact.product_cde <> 'MM' and brkg_fact.transaction_amt <= 0 and brkg_fact.source_sys_id_num <> 3 and brkg_fact.source_sys_id_num <> 5 and BRKG_FACT.trans_process_dte >= '1/1/2008' and BRKG_FACT.trans_process_dte <= '1/2/2008' order by fund_cde
current output
ACCOUNT FUND UNITS_PURCHASED AMOUNT_PURCHASED U BLCD -0.01137 -1.48000 U BOND -0.01283 -1.67000 U CGDE -0.06743 -0.95000 U EQIN -0.13277 -2.39000 U GRST -0.11799 -4.07000 U IX4S -0.53996 -12.55000 U LCCS -0.18216 -5.31000
wanted output ACCOUNT FUND UNITS_PURCHASED AMOUNT_PURCHASED U BLCD -0.01137 -1.48000 U BOND -0.01283 -1.67000 U BWDS NULL NULL U CGDE -0.06743 -0.95000 U EQIN -0.13277 -2.39000 U GAFR NULL NULL U GRST -0.11799 -4.07000 U IX4S -0.53996 -12.55000 U LIGE NULL NULL U LCCS -0.18216 -5.31000
I am reporting on a system with 32 devices, each of these devices can have certain events that happen to it that are logged and timestamped. I need a to show the count of each events that have happened to it within a certain time period. This code snippet below works fine BUT if there are no events that happen to a certain device in the time period, then that device is 'missing' from the table. What I need is basically a row for every device, regardless of if it has had any events happen to it (I will just show '0' for the event count) Any thoughts? I'm a complete newbie at this by the way.
Thanks
Code Snippet
SELECT DeviceStatusWords.DeviceName, COUNT(DeviceEventDurationLog.StatusBit) AS BitCount, DeviceEventDurationLog.StatusBit AS Bit FROM DeviceEventDurationLog RIGHT OUTER JOIN DeviceStatusWords ON DeviceEventDurationLog.DeviceID = DeviceStatusWords.DeviceID WHERE (DeviceEventDurationLog.TimeIn > @StartDate) AND (DeviceEventDurationLog.TimeIn < @EndDate) GROUP BY DeviceStatusWords.DeviceName, DeviceEventDurationLog.StatusBit ORDER BY DeviceStatusWords.DeviceName
I have a SQL query where am using WHERE clause as a result of which the NULL values are getting filtered...can u please help me to tranform this query into LEFT OUTER JOINS so as to avoid this filtration....my query is
SELECT A.JOINT_ID,A.SIZE_NBMM,A.ISFIELDJOINT,A.WELDTEST_CRI_ID,C.LINE_ID,D.TESTLEVELNO,E.COMPLETE FROM EALPS_DRWREVSPLJTS
A,EALPS_DRW_REV_SPL B,EALPS_DRW_REV_LINE C,EALPS_WELDTESTCRIT D,EALPS_ACTV_SEQ E WHERE B.SPOOL_ID=A.SPOOL_ID AND
B.LINE_ID=C.LINE_ID AND D.WELD_TEST_CRIT_ID=A.WELDTEST_CRI_ID AND E.ACTIVITY_CODE='VT' AND E.JOINT_ID=A.JOINT_ID
I want to retrieve: ALL [tasks] with [times].[status] for a specific user for a given week, whether rows exist in [times] or not.
Here's my query:
select tasks.id, times.status from tasks LEFT OUTER JOIN times ON tasks.id = times.taskid where upt.userid=11 AND times.startweek='05/05/2008'
That just doesn't return any the records though for the 05/05/2008 date. Its as though I want to join with a WHERE clause on the [times] table. What can I do?
Hello,I'm trying to link two tables... one for Employees and the other forTimecardsI need to get a list of employees that do not have timecards on anSPECIFIC DATEI tried the follonwingSELECT Employess.EmployeeIDFROM Employees LEFT OUTER JOIN Timecards on Employees.EmployeeID =Timecards.lmpEmployeeIDWHERE lmpEmployeeID is NULL and lmpTimecardDate = '10/24/2007'But it doesn't work. However, when I comment the date condition out(lmpTimecardDate = '10/24/2007') it works all right but It's not whatI needAnother interesting point... if I use the following query... it worksall rightSELECT Employess.EmployeeIDFROM EmployeesWHERE Employees.EmployeeID not in (select Timecards.EmployeeID fromTimecardswhere TimecardDate = '10/24/2007')I'd like to be able to use the Left Outer Join option.... Am I doingsomething wrong?... or is it that if It doesn't like the condition I'musgin in the WHERE clause (TimecardDate = '10/24/2007')Thanks for your helpPablo
I am using web developer 2008, while connecting to I wanted to fetch data from Lotus notes database file, for this i used notesql connector, while connectiong to notes database i am fetting error
ERROR [42000] [Lotus][ODBC Lotus Notes]Table reference has to be a table name or an outer join escape clause in a FROM clause
I have already checked that database & table name are correct, please help me out How i can fetch the lotus notes data in my asp.net pages.
I am using web developer 2008, while connecting to I wanted to fetch data from Lotus notes database file, for this i used notesql connector, while connectiong to notes database i am fetting error
ERROR [42000] [Lotus][ODBC Lotus Notes]Table reference has to be a table name or an outer join escape clause in a FROM clause
I have already checked that database & table name are correct, please help me out How i can fetch the lotus notes data in my asp.net pages.
We have two tables that have somewhat of a parent-child relationship. We are trying to use a SQL-92 outer join that returns the same results as a TSQL *= outer join. The difficulty we are having is that some of the parent records do not have any corresponding child records, but we still want to see those parent records with 0 (zero) for the count. How can we accomplish this with a SQL-92 compliant join (if it is even possible)? In the query results below, we would like the first set of results.
Thanks in advance for any help. -David Edelman
Test script below, followed by results =========================================== create table parent (p_id int NOT NULL) go create table child (p_id int NOT NULL, c_type varchar(6) NULL) go insert parent values (1) insert parent values (2) insert parent values (3) insert parent values (4) insert parent values (5) insert parent values (6) insert parent values (7) insert parent values (8) insert parent values (9) insert parent values (10) go
I have a MSDE query that includes a "left outer join..." clause. It runs fine in MSDE Query (a 3rd party GUI tool) and produces 12 rows. column 3 has some NULL values (because of the outer join).
But when I use the same query in an ASP.NET page, and display the result in a datagrid, it only displays 7 rows - the rows with the NULL value in column 3 do not display.
Is there a parameter somewhere in datagrid or dataset that I should be setting?
Hi!I have a problem with a query:Two tables:CREATE TABLE Emp (empno INT, depno INT)CREATE TABLE Work (empno INT, depno INT, date DATETIME)I want a list of all employees that belongs to a department (from Emptable), together with ("union") all employeees WORKING on that department aspescial day (An employee can have been borrowed from another departmentwhich he does not belong)Sample dataINSERT INTO Emp (empno, depno) VALUES (1,10)INSERT INTO Emp (empno, depno) VALUES (2,10)INSERT INTO Emp (empno, depno) VALUES (3,20)INSERT INTO Work (empno, depno, date) VALUES (1,10,'2003-10-17')INSERT INTO Work (empno, depno, date) VALUES (3,10,'2003-10-17')INSERT INTO Work (empno, depno, date) VALUES (3,10,'2003-10-18')Note that Employee 3 works on a department to which he does not belong (heis borrowed to another department)The following querySELECT empno, depno, date FROM work WHERE depno = 10 AND date = '2003-10-17'gives me this result set:empno depno date1 10 2003-10-17 00:00:00.0003 10 2003-10-17 00:00:00.000But I want employee 2 to appear in the result set as well, because hebelongs to department 10 (eaven thoug he is not working this particular day)The result set should look like thisempno depno date1 10 2003-10-01 00:00:00.0002 10 NULL3 10 2003-10-01 00:00:00.000I have tried different approaches, but none of them is good.Could someone please help me?Thanks in advanceRegards,Gunnar VøyenliEDB-konsulent asNORWAY
Hi,I need your help to resolve this problem. I have written a right outerjoin query between 2 indipendent tables as follows.select b.Account_desc, b.Account, a.CSPL_CSPL from Actual_data_final aright outer join Actual_account_Tbl b on a.account_desc =b.account_desc where (a.source_type = 'TY02' or a.source_type isnull) and (a.month = '2ND HALF' or a.month is null) and (a.year = 2004or a.year is null) and (a.product = 'NP' or a.product is null) orderby b.SnoBut the problem is I have few records in table Actual_account_Tbl butdo not match the condition "a.account_desc = b.account_desc".As per right outer join, I suppose to get those records as a result ofthe above query with null values of a.CSPL_CSPL. But it is notdisplaying.Please help me to resolve this problem.Regards,Omav
I am stuck in a bit of a mess, and i really don't know how to get out of it.
I am a dot.net developer, working with MS SQL server 2000.
I am trying to write a query which essentially gives the amount of stock available for a particular stock_code.
select Stock_code, description from stock
Now what i want to do is, for each one of the stock_code that apears in the table above i want to look into another table called pop_item, and get the closest delivery_date for that particular stock_code. So i have top 1 and order it by delivery_date desc.
Individually for a particular stock_code, i can do this no problem. And here is my code for stock_code='E0016/6'
select top 1 stock_code, delivery_date, order_number,qty_order-qty_delivered as onorder from pop_item where stock_code='E0016/6' and qty_order>qty_delivered order by delivery_date desc
But I can't seem to be able to do this for all the stock_code, and not a specific one, cause even though i try and left outer join it, i can't access the outer stock_code from the first query into the next...
i.e
select stock.Stock_code, description, tempp.stock_code, tempp.delivery_date, tempp.onorder from stock
left outer join
(select top 1 stock_code, delivery_date, order_number,qty_order-qty_delivered as onorder from pop_item where
--Can't say this(stock_code= stock.stock_code and ) qty_order>qty_delivered order by delivery_date desc) as tempp
on tempp.stock_code=stock.stock_code
Now my question is, is there anyway to access stock.stock_code within the second query? Casue the whole query on top returns only one value for delivery_date, only of the highest delivery date in the whole of pop_item. which make sense... but i don;t know how to get around this...
OOOOOOOOOOOOOOOOOOhhhhhhhhhhhhhhhhhhhhhhhhhhhhh!
Hope someone can help me.
Regards,
Munira
ps- should i be using a cursor, can i call cursors from asp.net. every where i read about cursors they adivice us not to use them.
What is the best way to use a left join in a SQL statement with multiple tables (more than 2)? I have a query that uses 7 tables, where most of the joins are inner joins, except for one, which needs to be a left join. The current SQL statement looks something like this:
FROM [table1],[table2],[table3],[table4],[table5],[table6],[table7] WHERE [table4].[field2]=[table1.field2]{this is an inner join} [table4].[field2]=[table2.field2]{this is an inner join} [table4].[field2]=[table3.field2]{this is an inner join} [table4].[field2]=[table5.field2]{this is an inner join} [table5].[field3]=[table6.field2]{this is an inner join} [table5].[field4]=[table7.field2]{this is needs to be a left join}
As it stands now, the last line in the WHERE clause is an INNER JOIN and limits the number of rows in my result. I need to select rows from [table7].[field2] whether or not a matching record exists in [table5].[field4]. The other INNER JOINS in the SQL statement must have matching records. Please advise.
Hi - I'm struggling with a query, which is as follows. (I have changed the context slightly for simplicity)
I have 4 tables: users, scores, trials, tests Each pair of users takes a series of upto 4 tests in 1 trial, getting a score for each test. There are a different numbers of trials for each pair of users.
Important: Users do not take all tests. EG TrialId 1 contains userA & userB with userA scoring 10 on test1, 20 on test2 and userB scoring 30 on test2, 40 on test3, 50 on test4 and is userA & userB's 1st attempt. TrialId 2 may be the same, but their 2nd attempt. TrialId 3 may be the 1st attempt for 2 different users etc.
Suppose the Tests table has 4 tests (1,test1),(2,test2),(3,test3),(4,test4)
There are always 2 users for each trial id.
I want a query which will return all scores for all users for all trials, BUT must include NULLs if a user did not take a test on that trial.
I thought it may involve a cross join between the Tests table and the Trials table.
I was trying to run the follwoing query.It is giving me error
"select woancestor.wonum,wplabor.laborcode, wplabor.quantity,wplabor.laborhrs from wplabor,workorder left outer join woancestor on workorder.wonum=woancestor.wonum and wplabor left outer join labtrans on wplabor.laborcode=labtrans.laborcode where woancestor.ancestor='572099' and workorder.istask=1 and workorder.wonum=wplabor.wonum"
Error is "Server: Msg 156, Level 15, State 1, Line 3 Incorrect syntax near the keyword 'left'."
But the following query works fine
"select woancestor.wonum,wplabor.laborcode, wplabor.quantity,wplabor.laborhrs from wplabor,workorder left outer join woancestor on workorder.wonum=woancestor.wonum where woancestor.ancestor='572099' and workorder.istask=1 and workorder.wonum=wplabor.wonum"
Hi, I have this oracle query with outer join situation. how can i convert it into sql server query.
SELECT distinct ae.dB_CONTRACT,CP.PC_CODE,BID_ITEM.ITEM_NO, 'N',BID_ITEM.PRICE_WORDS,OFF_ITEM.DESCPT, OFF_ITEM.UNITS,OFF_ITEM.TYPE_ITEM, PRES_ITEM.RET_PERC FROM BID_TOTAL,BID_ITEM,OFF_ITEM,PRES_ITEM, AE_CONTRACT AE, CONTRACT_PC CP WHERE RANK_NUMB = 1 AND BID_TOTAL.DB_CONTRACT = 37044 AND BID_TOTAL.DB_CONTRACT = BID_ITEM.DB_CONTRACT AND BID_TOTAL.BID_VENDOR = BID_ITEM.BID_VENDOR AND BID_ITEM.DB_CONTRACT = OFF_ITEM.DB_CONTRACT AND BID_ITEM.ITEM_NO = OFF_ITEM.ITEM_NO AND OFF_ITEM.ITEM_NO = PRES_ITEM.ITEM_NO (+) AND AE.DB_CONTRACT=BID_TOTAL.DB_cONTRACT AND CP.DB_CONTRACT = AE.DB_CONTRACT AND CP.pc_code = 1
I need to convert a OUTER APPLY hint in my query to LEFT JOIN.How it can be done?The code which is presently is this: OUTER APPLY Additional Fields. nodes('/AdditionalFields/AdditionalField') AS AF (C)
The 2nd table is for storing product codes for customers, in other words, one product can have different ProductCode for different customers. But some customers do not have ProductCode for a ProductID.
I want to create a query to return all the Products and its ProductCode (null is valid) for a specific customer.
I tried:
SELECT dbo.tblProductCodes.ProductCode, dbo.tblProductCodes.CustomerID, dbo.tblProducts.ProductName, dbo.tblProducts.ProductID FROM dbo.tblProducts LEFT OUTER JOIN dbo.tblProductCodes ON dbo.tblProducts.ProductID = dbo.tblProductCodes.ProductID WHERE dbo.tblProductCodes.CustomerID = 2
But the query left out all products that does not have ProductCode value in tblProductCodes table for CustomerID = 2. I want all the ProductName returned from query and render null or empty string for ProductCode value if the code does not exist in tblProductCodes table for the customer.
Cod_Lingua - Des_Lingua ------------------------------ ITA Italian GER German ENG English FRA French
and another table with product/description
ProductID - Cod_Lingua - Description ------------------------------------------- 1 ITA Mia Descrizione 1 ENG My Description
I've this SELECT:
SELECT Tab_Lingue.Cod_Lingua, Descrizioni_Lingua.Description FROM Descrizioni_Lingua RIGHT OUTER JOIN Tab_Lingue ON Tab_Lingue.Cod_Lingua=Descrizioni_Lingua.Cod_Lingua WHERE Descrizioni_Lingua.ProductID=1
I get these results: ITA - Mia Descrizione ENG - My Description
I don't want this. I'd like to have this: ITA - Mia Descrizione ENG - My Description GER - (null) FRA - (null)
I was writing a query using both left outer join and inner join. And the query was ....
SELECT Â Â Â Â Â Â Â S.companyname AS supplier, S.country,P.productid, P.productname, P.unitprice,C.categoryname FROM Â Â Â Â Â Â Â Production.Suppliers AS S LEFT OUTER JOIN Â Â Â Â Â Â (Production.Products AS P Â Â Â Â Â Â Â Â INNER JOIN Production.Categories AS C
[code]....
However ,the result that i got was correct.But when i did the same query using the left outer join in both the cases
i.e..
SELECT Â Â Â Â Â Â Â S.companyname AS supplier, S.country,P.productid, P.productname, P.unitprice,C.categoryname FROM Â Â Â Â Â Â Â Production.Suppliers AS S LEFT OUTER JOIN (Production.Products AS P LEFT OUTER JOIN Production.Categories AS C ON C.categoryid = P.categoryid) ON S.supplierid = P.supplierid WHERE S.country = N'Japan';
The result i got was same,i.e
supplier   country   productid   productname   unitprice   categorynameSupplier QOVFD   Japan   9   Product AOZBW   97.00   Meat/PoultrySupplier QOVFD   Japan  10   Product YHXGE   31.00   SeafoodSupplier QOVFD   Japan  74   Product BKAZJ   10.00   ProduceSupplier QWUSF   Japan   13   Product POXFU   6.00   SeafoodSupplier QWUSF   Japan   14   Product PWCJB   23.25   ProduceSupplier QWUSF   Japan   15   Product KSZOI   15.50   CondimentsSupplier XYZ   Japan   NULL   NULL   NULL   NULLSupplier XYZ   Japan   NULL   NULL   NULL   NULL
and this time also i got the same result.My question is that is there any specific reason to use inner join when join the third table and not the left outer join.
OLEDB source 1 SELECT ... ,[MANUAL DCD ID] <-- this column set to sort order = 1 ... FROM [dbo].[XLSDCI] ORDER BY [MANUAL DCD ID] ASC
OLEDB source 2 SELECT ... ,[Bo Tkt Num] <-- this column set to sort order = 1 ... FROM ....[dbo].[FFFenics] ORDER BY [Bo Tkt Num] ASC
These two tasks are followed immediately by a MERGE JOIN
All columns in source1 are ticked, all column in source2 are ticked, join key is shown above. join type is left outer join (source 1 -> source 2)
result of source1 (..dcd column) ... 4-400-8000119 4-400-8000120 4-400-8000121 4-400-8000122 <--row not joining 4-400-8000123 4-400-8000124 ...
result of source2 (..tkt num column) ... 4-400-1000118 4-400-1000119 4-400-1000120 4-400-1000121 4-400-1000122 <--row not joining 4-400-1000123 4-400-1000124 4-400-1000125 ...
All other rows are joining as expected. Why is it failing for this one row?
I have a merge join (full outer join) task in a data flow. The left input comes from a flat file source and then a script transformation which does some custom grouping. The right input comes from an oledb source. The script transformation output is asynchronous (SynchronousInputID=0). The left input has many more rows (200,000+) than the right input (2,500). I run it from VS 2005 by right-click/execute on the data flow task. The merge join remains yellow and the task never finishes. I do see a row count above the flat file destination that reaches a certain number and seems to get stuck there. When I test with a smaller file on the left it works OK. Any suggestions?
hello, i am running mysql server 5 and i have sql syntax like this: select sales.customerid as cid, name, count(saleid) from sales inner join customers on customers.customerid=sales.customerid group by sales.customerid order by sales.customerid; it works fine and speedy. but when i change inner join to right join, in order to get all customers even there is no sale, my server locks up. note: there is about 10000 customers and 15000 sales. what can be the problem? thanks,
Where function_code is the function of the area e.g. Auditorium, Classrom, etc, etc. And not all components are available for all functions e.g. Carpeting is available for Classrooms but not Power Plants or Warehouses.
I need to self join the above table to itself on system_code and system_component_code and find out which rows are missing from each side.
A query that I've been banging away at with no success is:
SELECT c1.*, c2.* FROM [dbo].[component_multiplier_table] c1 FULL OUTER JOIN [dbo].[component_multiplier_table] c2 ON (c1.system_component_code = c2.system_component_code) AND (c1.[system_code] = c2.[system_code]) WHERE c1.function_code = '2120' AND c2.[function_code] = '2750' AND (c1.[system_code] IS NULL OR c2.system_code IS NULL);
I added the is null conditions, no joy. I've tried every flavor of outer join w/o success.
Could any T-SQL gurus out there help me figure out how to do this in a set before I start coding
SELECT * FROM a LEFT OUTER JOIN b ON a.id = b.id instead of
SELECT * FROM a LEFT JOIN b ON a.id = b.id
generates a different execution plan?
My query is more complex, but when I change "LEFT OUTER JOIN" to "LEFT JOIN" I get a different execution plan, which is absolutely baffling me! Especially considering everything I know and was able to research essentially said the "OUTER" is implied in "LEFT JOIN".
Oi! What follows is a hypothetical situation, but it is a totally analogous to a real problem Im having, but provides an easier model to understand.
Imagine that you have database-driven battleship game and its time to render the board. Also imagine that you have to render more than one board and that the ships are all the size of one point on the grid.
One sql result you need is a list of all the points on the grid, regardless of whether or not there is a ship on it. This will make rendering much easier for you, because you can simply look at the record index to determine if a ship is present. The data that is stored about the position of the ships consists of one record containing the grid index and ship name.
One possible way to retrieve this data is to build a table that you will not change which contains a record for each point on the grid. Is it possible to union or join on this table to retrieve a list of results that contain both unoccupied locations and occupied ones?
Here is what I've come up with, but it contains results that have a null location when there are no ship records:
SELECT Grids.GridID, Ships.GridLocation, Ships.Name FROM Grids FULL OUTER JOIN Ships ON Ships.GridID = Grids.GridID WHERE Grids.PlayerID = 1
UNION-- (**not** UNION ALL)
SELECT Grids.GridID, GridLocations.GridLocation, (SELECT ShipName FROM Ships WHERE GridID = Grids.GridID AND GridLocation = GridLocations.GridLocation) FROM GridLocations, Grids WHERE Grids.PlayerID = 1
The following query doesn't work, it display 1 in leads column instead of 0:
select t.Tdate, count(l.id) as Leads, sum(t.shown) as Views from tracking t left outer join Leads l on r.clientid = t.clientid where l.clientid = 101 and l.Ldate >= 'April 2,2004' and t.Tdate >= 'April 2,2004' group by t.Tdate
The problem is that the Task data in tblLookup consists only of the first 5 chars of the same kind of data in tblRecords (e.g., if a field on that record in tblRecords says "BILLYGOAT", that field in tblLookup is entered only as "BILLY").
I am having problems with an outer join statement. I have written a procedure that tests a table for missing and corrupt data and to test my procedure, I take a table with 100% correct entries and corrupt them by hand. Then I test if my repaird data is looking like the correct data did. To do the test, I copy the correct data into a temp table "copy", join it with the "repaired" table and check if any fields look different. The problem is, that i don't get the missing data. The statement is looking like this:
select o.*,'#',k.* from repaired o right outer join copy k on (str(o.a) + 'A' + str(o.b) + 'A' + str(o.c) =str(k.a)+ 'A' + str(k.b) + 'A' + str(k.c) ) where o.D<>k.D or o.E<>k.E or o.F<>k.F or ...
I have dont the concatenation because I thougt, that a join with 3 fields could be responsible for not finding the missing data in table "copy". Before that it looked like:
... on (o.a=k.a and o.b=o.b and o.c = k.c) where ...
In table "copy" is a record missing that is in table "repaired". Why is my statement not printingout that missing record? Shouldn't be an outer join exactly what I have to use for finding missing data?
