Home » SQL & PL/SQL » SQL & PL/SQL » substring and reference table
substring and reference table [message #579959] Mon, 18 March 2013 06:22 Go to next message
incrediblek
Messages: 4
Registered: March 2013
Location: UK
Junior Member
Hi,

I hope someone can help me.

I have two tables A and B. In table A there is a field which contains a string of 20 characters; this essentially holds 5 codes of 4 characters each.

Table B is a reference table. It holds the 4 character code and the description.

I am trying to run a select query to bring back the description of the code for the first 2 codes in table A but i am not sure how to bring back the descriptions! The below is what i am trying to achieve.

Thanks,


SELECT
  TableA.ID,
  SUBSTR(TableA.Code,1,4) Primary_Code,
  INITCAP(TableB.DESCRIPT) Primary_description,
  SUBSTR(TableA.Code,5,4) Secondary_Code,
  INITCAP(TableB.DESCRIPT) Secondary_description
FROM
TableA,
TableB
WHERE
  AND  ( SUBSTR(TableA.Code,1,4)=TableB.Code (+)  )  
  AND  ( SUBSTR(TableA.Code,5,4)=TableB.Code (+)  )
  AND  (SUBSTR(TableA.Code,1,4)  LIKE  '%I210%' OR SUBSTR(TableA.Code,5,4)  LIKE  '%I210%')



Re: substring and reference table [message #579960 is a reply to message #579959] Mon, 18 March 2013 06:32 Go to previous messageGo to next message
dariyoosh
Messages: 532
Registered: March 2009
Location: Iran / France
Senior Member
Hi,

incrediblek wrote on Mon, 18 March 2013 12:22
I am trying to run a select query to bring back the description of the code for the first 2 codes in table A...


And what do you mean exactly by first 2 codes? the elements are not ordered in your table (unless we're talking about an index-organized table)

http://docs.oracle.com/cd/E11882_01/server.112/e25789/tablecls.htm#i20438
Quote:

...
By default, a table is organized as a heap, which means that the database places rows where they fit best rather than in a user-specified order. Thus, a heap-organized table is an unordered collection of rows. As users add rows, the database places the rows in the first available free space in the data segment. Rows are not guaranteed to be retrieved in the order in which they were inserted.

Note: Index-organized tables use a different principle of organization. See "Overview of Index-Organized Tables".
...



Regards,
Dariyoosh

[Updated on: Mon, 18 March 2013 06:36]

Report message to a moderator

Re: substring and reference table [message #579961 is a reply to message #579960] Mon, 18 March 2013 06:50 Go to previous messageGo to next message
incrediblek
Messages: 4
Registered: March 2013
Location: UK
Junior Member
Sorry, so the first code is characters 1-4 of Field A in Table A, and the second code is characters 5-8 of the same Field.

Re: substring and reference table [message #579962 is a reply to message #579960] Mon, 18 March 2013 06:51 Go to previous messageGo to next message
cookiemonster
Messages: 10929
Registered: September 2008
Location: Rainy Manchester
Senior Member
You need tableb twice in the from. The two links to that table are independent of each other:
SELECT
  TableA.ID,
  SUBSTR(TableA.Code,1,4) Primary_Code,
  INITCAP(b1.DESCRIPT) Primary_description,
  SUBSTR(TableA.Code,5,4) Secondary_Code,
  INITCAP(b2.DESCRIPT) Secondary_description
FROM
TableA,
TableB b1,
tableb b2
WHERE
  AND  ( SUBSTR(TableA.Code,1,4)=b1.Code (+)  )  
  AND  ( SUBSTR(TableA.Code,5,4)=b2.Code (+)  )
  AND  (SUBSTR(TableA.Code,1,4)  LIKE  '%I210%' OR SUBSTR(TableA.Code,5,4)  LIKE  '%I210%')
Re: substring and reference table [message #579963 is a reply to message #579961] Mon, 18 March 2013 06:52 Go to previous messageGo to next message
cookiemonster
Messages: 10929
Registered: September 2008
Location: Rainy Manchester
Senior Member
incrediblek wrote on Mon, 18 March 2013 11:50
Sorry, so the first code is characters 1-4 of Field A in Table A, and the second code is characters 5-8 of the same Field.


That is really bad database design. Each code should be in a seperate column.
Re: substring and reference table [message #579964 is a reply to message #579961] Mon, 18 March 2013 06:54 Go to previous messageGo to next message
dariyoosh
Messages: 532
Registered: March 2009
Location: Iran / France
Senior Member
incrediblek wrote on Mon, 18 March 2013 12:50
Sorry, so the first code is characters 1-4 of Field A in Table A, and the second code is characters 5-8 of the same Field.



@incrediblek

In addition to what cookiemonster said, you may want to take a look at Normalization


Regards,
Dariyoosh

[Updated on: Mon, 18 March 2013 06:55]

Report message to a moderator

Re: substring and reference table [message #579965 is a reply to message #579964] Mon, 18 March 2013 07:04 Go to previous message
incrediblek
Messages: 4
Registered: March 2013
Location: UK
Junior Member
Thank you very much cookiemonster. It worked.

It's an ancient system that we still having running at my workplace, so this will do for now.

Thanks again.
Previous Topic: displaying columns as rows
Next Topic: delete last n sets of rows of a table with same dates in sort field
Goto Forum:
  


Current Time: Sat Aug 30 01:44:18 CDT 2014

Total time taken to generate the page: 0.08324 seconds