473,395 Members | 1,668 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,395 software developers and data experts.

How to convert sql old outer join query to new syntax

Please help me guys. I converted the old outer join syntax (*=) but when i tested the new query the result was different. I want to know what was my mistake.

Expand|Select|Wrap|Line Numbers
  1. ############ old query ################
  2.  SELECT
  3.     JU_STJS.SEDAI       AS SEDAI,
  4.     JU_STJS.KATA        AS KATA,
  5.     JU_STJS.USRNAM      AS USRNAM,
  6.     JU_STJS.SUU         AS SUU,
  7.     SYU.KATACD          AS M_KATACD,
  8.     SYU.HONTAICD        AS M_HONTAICD,
  9.     JU_STJS.SYRKBN      AS SYRKBN,
  10.     JU_STJS.KNMONEY     AS KNMONEY,
  11.     JU_STJS.KKMONEY     AS KKMONEY,
  12.     JU_STJS.KNKKMONEY   AS KNKKMONEY,
  13.     JU_STJS.RIYOU       AS RIYOU,
  14.     JU_STJS.MONEYKBN    AS MONEYKBN,
  15.     JU_STJS.KZ          AS KZ,
  16.     JU_STJS.UPDYMD      AS UPDYMD,
  17.     SYU.SYRKBN          AS M_SYRKBN,
  18.     SYU.BHNKBN          AS M_BHNKBN,
  19.     SYU.SEQ             AS M_SEQ,
  20.     SYU.KOUSUU          AS M_KOUSUU,
  21.     SYU.MSTITLE         AS M_MSTITLE
  22.  FROM
  23.     MSYUKEI SYU ,
  24.     (SELECT
  25.         JU.SEDAI            AS SEDAI,
  26.         JU.KATA             AS KATA,
  27.         JU.USRNAM           AS USRNAM,
  28.         JU.SUU              AS SUU,
  29.         JU.KATACD           AS KATACD,
  30.         STJS.KNMONEY        AS KNMONEY,
  31.         STJS.KKMONEY        AS KKMONEY,
  32.         STJS.KNKKMONEY      AS KNKKMONEY,
  33.         STJS.RIYOU          AS RIYOU,
  34.         STJS.MONEYKBN       AS MONEYKBN,
  35.         STJS.HONTAICD       AS HONTAICD,
  36.         STJS.SYRKBN         AS SYRKBN,
  37.         STJS.GENKAKBN       AS GENKAKBN,
  38.         STJS.UPDYMD         AS UPDYMD,
  39.         JU.KZ               AS KZ
  40.     FROM
  41.         GYDSTJS STJS,
  42.         GY受注明細書 JU
  43.     WHERE
  44.         JU.SEIBAN = '46705'  AND
  45.         JU.SEIBAN + JU.SEDAI *= STJS.SEIBAN + STJS.SEDAI
  46.     ) JU_STJS
  47.  WHERE
  48.     SYU.KATACD    = JU_STJS.KATACD  AND
  49.     SYU.HONTAICD  *= JU_STJS.HONTAICD  AND
  50.     SYU.SYRKBN    *= JU_STJS.SYRKBN  AND
  51.     ((JU_STJS.GENKAKBN  ='1'  AND  JU_STJS.SYRKBN  ='1')  OR
  52.  JU_STJS.SYRKBN  ='0')
############# new query ########################

Expand|Select|Wrap|Line Numbers
  1.  SELECT
  2.     JU_STJS.SEDAI       AS SEDAI,
  3.     JU_STJS.KATA        AS KATA,
  4.     JU_STJS.USRNAM      AS USRNAM,
  5.     JU_STJS.SUU         AS SUU,
  6.     SYU.KATACD          AS M_KATACD,
  7.     SYU.HONTAICD        AS M_HONTAICD,
  8.     JU_STJS.SYRKBN      AS SYRKBN,
  9.     JU_STJS.KNMONEY     AS KNMONEY,
  10.     JU_STJS.KKMONEY     AS KKMONEY,
  11.     JU_STJS.KNKKMONEY   AS KNKKMONEY,
  12.     JU_STJS.RIYOU       AS RIYOU,
  13.     JU_STJS.MONEYKBN    AS MONEYKBN,
  14.     JU_STJS.KZ          AS KZ,
  15.     JU_STJS.UPDYMD      AS UPDYMD,
  16.     SYU.SYRKBN          AS M_SYRKBN,
  17.     SYU.BHNKBN          AS M_BHNKBN,
  18.     SYU.SEQ             AS M_SEQ,
  19.     SYU.KOUSUU          AS M_KOUSUU,
  20.     SYU.MSTITLE         AS M_MSTITLE
  21.  FROM
  22.     MSYUKEI SYU LEFT OUTER JOIN
  23.     (SELECT
  24.         JU.SEDAI            AS SEDAI,
  25.         JU.KATA             AS KATA,
  26.         JU.USRNAM           AS USRNAM,
  27.         JU.SUU              AS SUU,
  28.         JU.KATACD           AS KATACD,
  29.         STJS.KNMONEY        AS KNMONEY,
  30.         STJS.KKMONEY        AS KKMONEY,
  31.         STJS.KNKKMONEY      AS KNKKMONEY,
  32.         STJS.RIYOU          AS RIYOU,
  33.         STJS.MONEYKBN       AS MONEYKBN,
  34.         STJS.HONTAICD       AS HONTAICD,
  35.         STJS.SYRKBN         AS SYRKBN,
  36.         STJS.GENKAKBN       AS GENKAKBN,
  37.         STJS.UPDYMD         AS UPDYMD,
  38.         JU.KZ               AS KZ
  39.     FROM
  40.         GY受注明細書 JU LEFT OUTER JOIN GYDSTJS STJS 
  41.         on JU.SEIBAN + JU.SEDAI = STJS.SEIBAN + STJS.SEDAI
  42.     WHERE
  43.         JU.SEIBAN = '46705'
  44.     ) JU_STJS ON SYU.HONTAICD = JU_STJS.HONTAICD AND
  45.       SYU.SYRKBN = JU_STJS.SYRKBN
  46.  WHERE
  47.     SYU.KATACD    = JU_STJS.KATACD  AND
  48.     ((JU_STJS.GENKAKBN  ='1'  AND  JU_STJS.SYRKBN  ='1')  OR
  49.  JU_STJS.SYRKBN  ='0')
Thanks in advance.
I really don't know what to do with this.
i've been searching the solution for this for almost 2 weeks now. T_T
Nov 16 '10 #1
0 1192

Sign in to post your reply or Sign up for a free account.

Similar topics

1
by: Rahul Khandpur | last post by:
hi friends, i have one query related to oracle9i outer join condition it is mentioned that we can't use (+) with the operand of OR and IN operators example select...
4
by: Gunnar Vřyenli | last post by:
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...
1
by: Omavlana | last post by:
Hi, I need your help to resolve this problem. I have written a right outer join query between 2 indipendent tables as follows. select b.Account_desc, b.Account, a.CSPL_CSPL from...
5
by: kumar_rangan1976 | last post by:
I need the below sybase code to be migrated in UDB : select distinct c.partnumber as I_PART, case when d.IntegratorID = 'DCX05' then 'U' when d.IntegratorID = 'DCX04' then 'M'...
2
by: vaddi | last post by:
hello, I have query , which has got 2 outer joins. The query is: SELECT A.Project_ID, A.Title, A.comm1 AS Comments, A.Partner AS PM, A.Staff_Assigned AS TL, A.Contact_Name AS FL, A.MD,...
1
karthickkuchanur
by: karthickkuchanur | last post by:
1). Im looking at an example here: SELECT P.PublisherCode, P.PublisherName, B.BookCode, B.BookTitle FROM Publisher P LEFT INNER JOIN Book B ON P.PublisherCode = B.PublisherCode how this is...
1
by: Rich P | last post by:
Greetings, Left Join (outer join) queries are generally for excluding stuff. select t1.* from tblx t1 Left Join tbly t2 on t1.ID = t2.ID and t1.fld1 = t2.fld1 and t1.fld2 = t2.fld2... Where...
1
by: hergele | last post by:
Hello all.. I am trying to convert an application which is working on Oracle to postgreSql.. I've seen a query in code something like this... select * from table1 a1 , table1 a2, table2 a3, ...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
0
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
0
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.