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
ADVERTISEMENT
Aug 22, 2015
I'm a Power Builder (PB) developer. I've migrated PB from 10 .5 to 12.5, and now I need to migrate the stored procedures from SQL Server 2005 to 2012 as well.I get a invalid expression error when I run the procedures in Power Builder. We have previously been using a lower database compatibility model in 2005, which allowed the procedures to work there. I have learned that =* is the old way to write right outer joins.
For example:
select *
from A
right outer join
B
[code]...
Is my approach correct, or do I need to add a WHERE condition to it? I don't have access to the production database to check. The development database is in SQL Server 2012 too, sO I will not be able to run the old version there to check.
View 8 Replies
View Related
Oct 18, 2013
I've got 2 tables of towns. I'm using outer join because i need all the town from both tables. However I'm sometimes getting duplicates.
My query
select a.town, b.town
from a
outer join b on a.town = b.town
group by a.town, b.town
How to stop getting null values?
portsmouth null
portsmouth portsmouth
southampton southampton
null southampton
TownA null
null TownB
I'm looking for distinct values like this:
portsmouth portsmouth
southampton southampton
TownA null
null TownB
etc...
View 2 Replies
View Related
Aug 2, 2006
I would like to populate a grid with data from 2 different tables.
Table1: [PK]id(int), name(nvarchar), areaID(int)
Table2: [PK][FK]areaID(int), areaDescription(nvarchar)
My cerrent query is:
SELECT Table1.id, Table1.name, Table2.areaDescription FROM Table1 INNER JOIN Table2 ON Table1.areaID = Table2.areaID
However, sometimes the areaID in Table1 will only be populated at a later stage and therefore will be NULL in Table1. Table2 is used as a lookup table when inserting into Table1. This query therefore ommits any records in Table1 which do not have an areaID. I would like to view ALL records(ones without an areaID as well) as they would be populated in the grid and selected to be updated on web forms because they are incomplete and then subsequently assigned an areaID.
Any help with this query would be much appreciated...
View 2 Replies
View Related
Aug 14, 2015
CREATE TABLE A (ID INT IDENTITY (1,1))
CREATE TABLE B (ID INT, EMPID VARCHAR(10))
INSERT INTO A DEFAULT VALUES
GO 5
INSERT INTO B VALUES (1,'E23')
INSERT INTO B VALUES (1,'E24')
INSERT INTO B VALUES (2,'E23')
from the above code i would like to get output like
ID EMPID
1 23
2 23
3 null
4 null
5 null
1 24
2 null
3 null
4 null
5 null
I'm trying like
select a.id, b.empid from (
select * from a cross join (
select distinct empid from b) b
) a
left outer join b on a.id = b.id but i get repetitive rows.
View 7 Replies
View Related
Oct 8, 2015
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.
View 5 Replies
View Related
Feb 5, 2008
Hi,
I've an int field and when this field has a null, I want to show empty/blanks instead of 0. How can this be achieved?
I've tried the below:
declare @a as int
set @a= null
select isnull(@a,'')
o/p: 0
select cast(isnull(@a,'') as varchar)
o/p: 0
select case convert(varchar,@a)
when null then ''
else @a
end
o/p: null
Thanks for your help!
Subha
View 14 Replies
View Related
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
Aug 20, 2006
I am using Visual Web Developer Express 2005 and SQL Server Express 2005.
I have set up a trigger that fires when an update has been performed on a specific table. The trigger checks to see if specific columns have been updated. If they have, then the trigger code is executed. This part works fine.
I am now trying to find a way to check if null values exist in either one of two field from the same table. If either field contains a null value, then I don't want the trigger code to be executed.
How can I check for null values and skip a block of code within my Transact Sql trigger.
Thanks.....
View 2 Replies
View Related
Jun 23, 2015
i want to display the max Date in place of NULL when Indicator is "1"
INPUT
Emp Num
Date
Indicator
1
Null
1
[code]....
View 3 Replies
View Related
May 10, 2010
what is difference between outer apply and outer join ,both return rows from left input as well as right input . isnt it?
View 3 Replies
View Related
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
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
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
Jun 15, 2015
I'm running the following test query on a single table:
SELECT sph.datestamp, sph.stocksymbol, sph.closing, DATENAME(dw, sph.datestamp),
CASE DATENAME(dw, sph.datestamp)
WHEN 'Monday' then 'Monday'
ELSE (SELECT CAST(sph2.datestamp AS nvarchar) FROM BI_Test.dbo.StockDB AS sph2 WHERE sph2.DateStamp = DATEADD(d, -1, sph.datestamp) AND sph2.StockSymbol = 'NYA')
END AS TestCase,
[Code] ....
And here's an example of the output I'm getting:
Why the exact same subquery in the THEN of the second CASE statement is returning NULL when the first one completes as expected?
View 7 Replies
View Related
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
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
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
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
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
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
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
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
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
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
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
Jun 29, 2015
I have tables and a function as representated by the code below. The names for objects here are just for representation and not the actual names of objects. Table RDTEST may have one or multiple values for RD for each PID. So the function GIVERD will return one or multiple values of RD for each value of PID passed to it.
When I run the following query, I get the required result except the rows for CID 500 for which PID is NULL in table T1. I want the rows for CID 500 as well with PID values as NULL.
SELECT A.CID,
A.ANI,
A.PID,
B.RD
FROM T1 AS A CROSS APPLY GIVERD(A.PID) B
CREATE TABLE [DBO].[RDTEST](
[PID] [INT] NULL,
[RD] [INT] NULL
)
[Code] ....
View 4 Replies
View Related
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
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
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
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
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
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
