Solved

Oracle 9i  Select static values depending on IF

Posted on 2014-07-18
4
315 Views
Last Modified: 2014-07-18
I have an existing select stmt that returns a billing address
SELECT 
C.Cust_Name AS CUST_NAME,
C.Cust_Add1 AS CUST_ADD1,
C.Cust_add2 AS CUST_ADD2,
Z.ZIP_CITY AS CUST_CITY,
Z.STATE_CODE AS CUST_PROV,
C.zip_code AS CUST_POSTAL
FROM E_ORD_H E
left JOIN M_CUST_H C on C.CUST_CODE = E.CUST_CODE and C.COMP_CODE = E.COMP_CODE
left JOIN M_ZIP Z on Z.ZIP_CODE = C.ZIP_CODE and Z.COUNTRY_CODE = C.COUNTRY_CODE and Z.ZIP_STAT = 'A'
WHERE E.ORD_NUM = (
   select min(D4.ord_num) from E_FRT_ORD_D4 D4 
   where D4.load_num = :p_load_num
   and D4.comp_code_Actual = E.Comp_Code)
AND E.COMP_CODE = :p_comp_code

Open in new window


I need to modify this.  I need to evaluate another field E.ORD_FRT_TERM. If this field is = 'PRE' I want to return hard coded (Static) values for each column. If <> 'PRE' I want to return the original results.
How do I do this?
0
Comment
Question by:JDCam
  • 2
  • 2
4 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40204760
Something like:

...
case when E.ORD_FRT_TERM = 'PRE' then 'staticValue' else E.ORD_FRT_TERM  end
...
0
 

Author Comment

by:JDCam
ID: 40204825
Sorry..no
More like

IF E_ORD_FRT_TERM = 'PRE'
     SELECT
     'MY NAME' AS CUST_NAME,
     '123 My STRRE' AS CUST_ADD1,
     'UNIT 123' AS CUST_ADD2,
    'MY CITY' AS CUST_CITY,
    'MY STATE' AS CUST_STATE,
    'MY ZIP' AS CUST_ZIP
ELSE  (Original Query, which returs 6 columns of an address)
0
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 40204840
Do you need this in a single select statement or can you do it in code?

Where does E_ORD_FRT_TERM come from?

If you need a single select maybe something like:

SELECT 
C.Cust_Name AS CUST_NAME,
C.Cust_Add1 AS CUST_ADD1,
C.Cust_add2 AS CUST_ADD2,
Z.ZIP_CITY AS CUST_CITY,
Z.STATE_CODE AS CUST_PROV,
C.zip_code AS CUST_POSTAL
FROM E_ORD_H E
left JOIN M_CUST_H C on C.CUST_CODE = E.CUST_CODE and C.COMP_CODE = E.COMP_CODE
left JOIN M_ZIP Z on Z.ZIP_CODE = C.ZIP_CODE and Z.COUNTRY_CODE = C.COUNTRY_CODE and Z.ZIP_STAT = 'A'
WHERE E.ORD_NUM = (
   select min(D4.ord_num) from E_FRT_ORD_D4 D4 
   where D4.load_num = :p_load_num
   and D4.comp_code_Actual = E.Comp_Code)
AND E.COMP_CODE = :p_comp_code
AND E_ORD_FRT_TERM <>  'PRE'
union all
SELECT
      'MY NAME' AS CUST_NAME,
      '123 My STRRE' AS CUST_ADD1,
      'UNIT 123' AS CUST_ADD2,
     'MY CITY' AS CUST_CITY,
     'MY STATE' AS CUST_STATE,
     'MY ZIP' AS CUST_ZIP
FROM DUAL
where E_ORD_FRT_TERM = 'PRE'

Open in new window

0
 

Author Closing Comment

by:JDCam
ID: 40205042
thanks.. this idea works fine
0

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Oracle Stored Procedure for User Account data 2 28
Oracle SQL - Query help 7 54
ServiceCenter IR Query Expressions 1 15
Oracle collections 15 18
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

786 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question