Data Mis Match

[kevfeedback]

I am obtaining details from a column in one table, but am having to substring this to return just the section that I need. Unfortunately I need to join this output to another table, using the retrieved data and all attempts using different functions just brings back zero rows. I can copy and paste the output into a select statement against the second table and that works fine, but I am limited in that I only have read access and cannot create any new objects.

I am using the code below, but it doesn't work! Any ideas anyone?

select x.*, Y.CUSTOMER_NAME, Y.CUSTOMER_NUMBER, Y.SIC_CODE
from
(SELECT st_date sell_date, st_branch store_code, st_stkcode item, st_stkdesc item_desc,
st_out qty, substr(st_remarks,20,5)cust_code
FROM till_sales wst
where wst.st_date = '12-JAN-2009'
and st_remarks is not null
and st_type = '3'
and st_branch = '27') x, (SELECT CUSTOMERS.CUSTOMER_NAME, CUSTOMERS.CUSTOMER_NUMBER, CUSTOMERS.SIC_CODE
FROM CUSTOMERS CUSTOMERS) Y
where cust_code = customer_NUMBER

[skhanal]

What are the datatypes and length for cust_code and customer_NUMBER?

[kevfeedback]

Cust_Code is being stripped out of a long varchar field, and is coming back at varchar(10) which Customer Number is varchar(30).

Thanks

[kevfeedback]

Sorry, 'which' shoudl read 'while'. Obviously too early over here....

[skhanal]

Have you tried RTRIM

select x.*, Y.CUSTOMER_NAME, Y.CUSTOMER_NUMBER, Y.SIC_CODE
from
(SELECT st_date sell_date, st_branch store_code, st_stkcode item, st_stkdesc item_desc,
st_out qty, substr(st_remarks,20,5)cust_code
FROM till_sales wst
where wst.st_date = '12-JAN-2009'
and st_remarks is not null
and st_type = '3'
and st_branch = '27') x, (SELECT CUSTOMERS.CUSTOMER_NAME, CUSTOMERS.CUSTOMER_NUMBER, CUSTOMERS.SIC_CODE
FROM CUSTOMERS CUSTOMERS) Y
where RTRIM(cust_code) = RTRIM(customer_NUMBER )

[kevfeedback]

No, I hadn't thought of that, but having checked it still doesn't bring back any data.

[srikanth.mss]

Run the two queries individually and let me know what is the result you got.

Query 1:
--------
SELECT st_date sell_date, st_branch store_code, st_stkcode item, st_stkdesc item_desc,
st_out qty, substr(st_remarks,20,5)cust_code
FROM till_sales wst
where wst.st_date = '12-JAN-2009'
and st_remarks is not null
and st_type = '3'
and st_branch = '27'

Query 2:
--------

SELECT CUSTOMERS.CUSTOMER_NAME, CUSTOMERS.CUSTOMER_NUMBER, CUSTOMERS.SIC_CODE
FROM CUSTOMERS CUSTOMERS

I want know which table data contains data always since i have an thought of left outer joins.