How long is Oracle8.1.6, I started using Oracle in the beginning of this year, I didn't expect the version of the problem so soon.
Let's take an operation in Oracle8.1.6
Table A: Define some ID and corresponding values
ID Value
1 Liu Guoqiang
2 she
Table B: Record data, one AID is corresponding to the above ID, but not foreign key, its value may not be defined in A
ID. . . AID
1 . . . 1
2 . . . 5
The 5 appearing above is not defined in the table.
Now you have a record from the SELECT from the B table, and add a field, which means that the AID corresponds to Value.
The original code is
Select B.ID, A.ID, A.Value from B, a a a a a a a a a.Aid = A.ID;
If you use the above sentence, you can find that if you insert an AID in the B table, the result will not be selected. what else can we do? Oracle's tool tutorial is too lacking, I can't find it in the program directory, where is there? I have to find it in the online help of SQL Server, it is easy to find it, MS's things are good. (I hope that there is no big pile of bricks to come over), the modified SQL statement is as follows
SELECT B.ID, B.AID, (Select Count (ID) from a where id = B.AID)> 0 THEN A. Value Else 'Uneasted this ID' end) from B, A a WHERE B .Aid = (Case When (Select Count (ID) from a where id = B.AID)> 0 THEN A.ID ELSE B.AID END);
Try it, I found that the unfolded B.AID in A can be elected, but a repetition record will appear, which is easy to solve.
Select Distinct B.ID, B.AID, (Select Count (ID) from a where id = B.AID)> 0 THEN A. Value Else 'Uneascent This ID' end) from B, A WHERE B.AID = (SELECT Count (ID) from a where id = b.AID)> 0 THEN A.ID ELSE B.AID END);
A distinct is solved.
It's really failure. Today is used in a machine that is 8.1.5 in an Oracle version. It turned out to have an error.
How can it be, is it really lacked.
When you are busy with the compiler, it is really bad, it may be 8.1.5 does not support the case statement, or this version of Case is not like this, I think it should be the previous one. Just use Oracle to meet the version problem. How can I solve this?
The head says that I want to do it again, but who told me to compile the machine with 8.1.6, the machine tested by others is also 8.1.6? Moreover, it is not only here to use this structure. I didn't find other methods that can be solved with SQL statements. The stored procedures of Oracle's dynamic creation record set have not learned, how much time can be placed. In SQL Server, it seems that you can use left Join, Right Join, but in Oracle, even 8.1.6 can not be used (perhaps my use is not right, ^ _ ^). What should I do next week? . . . . . . . . .
Writing this above, I didn't expect that I was turned out to pass the path. I tried it in Oracle a few days ago (of course 8.1.6), I can use Union to connect two SELECT statements. I will try it immediately in 8.1.5, haha, old days bless, really, immediately, the result will come out. A defined plus is not defined in A. Isn't it all? Top plots plus a reordering. I am a genius (呦, who uses stone, blood ... Boom, my huge body fell down, raising a dust)
Select B.ID, A.ID AID, A.Value from B, A a a a a a where b.Aid = A.ID Union Select B.ID, B.AID, 'Uneasted this name' Value from B, A A WHERE B.AID <> A.ID;
The above should be one of the cases, sorting
Select ID, AID, Value from (SELECT B.ID, A.ID, A.Value from B, a a a a a a a a a.AID = A.ID Union Select B.ID, B.AID, 'Undefined Name 'Value from B, a a a a where b.aid <> a.id) Order by ID;
This will not become, maybe it can be said that this solution is still much better than the method of CASE above. The method of the above case, as we continue to use the IF judgment conditions when using for cycle, the performance is naturally not good, really because of the disaster!
I can go to the Great Wall tomorrow, haha.
July 11, 2004 Note:
This is actually the external connection problem in the database. Is there no problem with external connection? Of course it is impossible. Later, I found a solution, but also quite
SELECT B.ID, A.ID, A.Value from B, a a a a a a a a a .Aid = A.ID ( );
That's really bad, add one ( ) behind it;
