473,461 Members | 1,986 Online
Bytes | Software Development & Data Engineering Community
Create Post

Home Posts Topics Members FAQ

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 1193

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, ...
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
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
1
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
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
0
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and...
0
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The...

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.