Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Lookup table in Oracle - correct SQL for results?

 
Danny Jackson
Greenhorn
Posts: 7
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I am trying to implement a lookup/crosswalk table in Oracle via Toad. The table defs. follow:

Crosswalk/Lookup Table def is:
CREATE TABLE FPPS_OWNER.CARC_RARC_CROSSWALK
(
RARC_ID VARCHAR2(15 BYTE),
CARC_ID VARCHAR2(15 BYTE)
)

CREATE TABLE FPPS_OWNER.ADJUSTMENT_REASON
(
ADJUSTMENT_REASON_CD VARCHAR2(5 BYTE) NOT NULL,
DESCRIPTION VARCHAR2(512 BYTE) NOT NULL,
)



CREATE TABLE FPPS_OWNER.REMITTANCE_REMARK
(
REMITTANCE_REMARK_CD VARCHAR2(5 BYTE) NOT NULL,
DESCRIPTION VARCHAR2(200 BYTE),
)


Where REMITTANCE_REMARK.REMITTANCE_REMARK_CD = CARC_RARC_CROSSWALK.RARC_ID
And ADJUSTMENT_REASON.ADJUSTMENT_REASON_CD = CARC_RARC_CROSSWALK.CARC_ID

All I need to do is get the REMITTANCE_REMARK.REMITTANCE_REMARK_CD and REMITTANCE_REMARK.DESCRIPTION Given an input ADJUSTMENT_REASON.ADJUSTMENT_REASON_CD value

I am trying to do this in Toad against Oracle and I am having great difficulty with Oracles add-in 'language' - the resulting code will be running in Java. Any hints would be greatly appreciated.
 
Stefan Evans
Bartender
Posts: 1807
10
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
What have you got thus far for a select statement?

Also I note that the crosswalk table has fields of size 15 byte, whereas the fields being referenced are only 5 byte?

If they are meant to be used as foreign keys/checks shouldn't they be the same size?
 
Danny Jackson
Greenhorn
Posts: 7
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Yes, you are correct - the size is off. I'm unable to changed the size at this point since only the DBA can
do this. - I will mention this to him The values for these id fields are no more than 6 characters long in
any case. The SQL I've been running in Java is:
sql is: SELECT DISTINCT REMITTANCE_REMARK.REMITTANCE_REMARK_CD, REMITTANCE_REMARK.DESCRIPTION, REMITTANCE_REMARK.FEE_USAGE_FLAG,REMITTANCE_REMARK.CREATED_BY, REMITTANCE_REMARK.START_DATE, REMITTANCE_REMARK.END_DATE, REMITTANCE_REMARK.DATE_CREATED, ADJUSTMENT_REASON.ADJUSTMENT_REASON_CD FROM fpps_owner.REMITTANCE_REMARK, fpps_owner.ADJUSTMENT_REASON INNER JOIN fpps_owner.CARC_RARC_CROSSWALK ON CARC_RARC_CROSSWALK.CARC_ID=ADJUSTMENT_REASON.ADJUSTMENT_REASON_CD WHERE CARC_RARC_CROSSWALK.CARC_ID = 4 ORDER BY REMITTANCE_REMARK.DESCRIPTION.

which returns 45 records, which is incorrect because the following returns the 7 records that I'm looking for. Clearly the join is incorrect but I'm having a hellish
time just creating the above statement in Oracle/Toad.


This returns the 7 id values I'm looking for
SELECT DISTINCT CARC_RARC_CROSSWALK.CARC_ID, CARC_RARC_CROSSWALK.RARC_ID FROM CARC_RARC_CROSSWALK
WHERE CARC_RARC_CROSSWALK.CARC_ID='16'
ORDER BY CARC_RARC_CROSSWALK.CARC_ID

Thanks for your time!
 
Danny Jackson
Greenhorn
Posts: 7
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I apologize for the misleading id values of 16 and 7 in the SQL - they are interchangeable because they both return the same incorrect record counts in both queries.
 
Stefan Evans
Bartender
Posts: 1807
10
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
It looks like you have missed specifying a join in your query.
There are three tables mentioned in the from clause.
You have joined the AdjustmentReason and the Crosswalk table, so the results from there will be constrained.
The remittanceRemark table is not included in any joins, and so it will work as a crossproduct multiplying your results.
So each row in remittanceRemark table will be included, and matched with the results of the other query.

My first recommendation would be get rid of the word "distinct" in your queries. Forget it exists.
Oftentimes it is used as a crutch to filter large results, when actually what you've got is an incorrectly joined table. Don't get me wrong. There ARE times to use it, but this probably isn't one of them.
If it DOES return repeating results, then check your joins, and see if there IS duplicate data in the tables.

Your second query for instance only queries the one table. If you get duplicate results, then I would recommend you take a look at that data.

I think your first from clause could be rewritten as



Notice it is structured so that every table you add to the from clause defines its relationship via the join syntax.
 
Danny Jackson
Greenhorn
Posts: 7
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks! I thought there was a missing join but when I try to add it in Oracle/Toad I get invalid field errors even when the table_name.field is built by Toads 'querybuilder'
thing so I haven't been able to implement it. I'll try again tomorrow. I agree that 'Distinct' is a sloppy crutch - the data is not intuitive at this point so I'm not always sure
that what I'm looking at is correct; there is probably duplicate data in the DB and it will have to be purged.

I'll insert the extra join one way or another. I really appreciate your extra eyes here

Have a good night!
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

A sidenote: cross is a keyword in SQL, don't use it as an identifier or an alias. What's worse in this particular case is that cross join is the syntax which produces, well, a cross join. So, the above code probably wouldn't parse, but even if it did in another SQL it could cause some serious confusion in its reader(s).
 
Stefan Evans
Bartender
Posts: 1807
10
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
A sidenote: cross is a keyword in SQL, don't use it as an identifier or an alias


whoops. Shows how long it is since I've written real sql code
thanks for the catch.
I've edited the example to not use a keyword as an alias.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic