[CLOSED] sql pass through joins

In using sql pass through, we are joining two home grown views that return 0 rows in WebFocus. The query return the correct number of rows when executed in the database, but 0 rows in WebFocus. The combined query is:

select *
from demographic, majors
where demographic.pidm in (11111, 22222, 33333)
and our_view.our_term_code('201010') = '0'
and demographic.pidm = majors.pidm
;
END

Now we can run:

select *
from demographic
where demographic.pidm in (11111, 222222, 33333)
;
END

and we can run:

select *
from majors
where majors.pidm in (11111, 222222, 333333)
and our_view.our_term_code('201010') = '0'
;
END

and these return data, but joining them together in WebFocus returns 0 rows.

What is the part that I am missing as to why this join is not possible??

Thanks for your input.

This message has been edited. Last edited by: Kerry,

WebFocus 7.6.9
Solaris
All output

Posts: 5 | Registered: September 21, 2009

njsden

Virtuoso

posted

Hide Post

This may or may not be related but .. what does "our_view" refer to in your query? Is that an actual VIEW or maybe a stored procedure? It certainly does not look like a view since it is not listed in your FROM clause but the name is certainly misleading.

Anyway, could you provide us with some more details as to what database you're going against and how are OUR_VIEW and MAJORS physically related?

Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.

Posts: 1533 | Registered: August 12, 2005

Ignored post by njsden posted

Show Post

PBrightwell

Master

posted

Hide Post

The way your code is written, your files are all named SQLOUT. You are writting over the file with your next query. Also, your files need to be sorted by the common field. Try:
select *
from demographic, majors
where demographic.pidm in (11111, 22222, 33333)
and our_view.our_term_code('201010') = '0'
and demographic.pidm = majors.pidm
;
TABLE
ON TABLE HOLD AS FILE1
END

Now we can run:

select *
from demographic
where demographic.pidm in (11111, 222222, 33333)
;
TABLE
ON TABLE HOLD AS FILE2
END

and we can run:

select *
from majors
where majors.pidm in (11111, 222222, 333333)
and our_view.our_term_code('201010') = '0'
;
TABLE
ON TABLE HOLD AS FILE3
END

Pat
WF 7.6.8, AIX, AS400, NT
AS400 FOCUS, AIX FOCUS,
Oracle, DB2, JDE, Lotus Notes

Posts: 755 | Location: TX | Registered: September 25, 2007

Ignored post by PBrightwell posted

Show Post

Francis Mariani

Expert

posted

Hide Post

quote:
select *
from demographic, majors
where demographic.pidm in (11111, 22222, 33333)
and our_view.our_term_code('201010') = '0'
and demographic.pidm = majors.pidm
;

As njsden specifies, the SQL looks a bit wrong, you mention the two tables demographic and majors, but the selection criteria is on our_view.

This is most likely not a WebFOCUS problem. Did you test that SQl in a SQL client before inserting it into WebFOCUS?

Francis

Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server

Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005

Ignored post by Francis Mariani posted

Show Post

sharrow

Member

posted

Hide Post

Thanks everyone for your responses.

Both of these SQL queries:

select *
from demographic
where demographic.pidm in (11111, 222222, 33333)
;
END

and

select *
from majors
where majors.pidm in (11111, 222222, 333333)
and our_view.our_term_code('201010') = '0'
;
END

work in both SQLplus and in WebFocus. They both pull back the correct rows. The issue is when I join these queries together into this query:

select *
from demographic, majors
where demographic.pidm in (11111, 22222, 33333)
and our_view.our_term_code('201010') = '0'
and demographic.pidm = majors.pidm
;

the query works in SQLplus on the datebase but returns 0 rows in WebFocus.

Thanks for your reponses.

-Steve

WF 7.6.9
Solaris 10
Oracle 10g

WebFocus 7.6.9
Solaris
All output

Posts: 5 | Registered: September 21, 2009

Ignored post by sharrow posted

Show Post

sharrow

Member

posted

Hide Post

I should also say that both of these are home grown views. I am wondering if we have this issue because they are both views?

Also in the query:

select *
from demographic, majors
where demographic.pidm in (11111, 22222, 33333)
and our_view.our_term_code('201010') = '0'
and demographic.pidm = majors.pidm
;

the line:

and our_view.our_term_code('201010') = '0'

is our way of using a package variable to pass a value to a sub-query that is part of the majors view.

Thanks again.

WebFocus 7.6.9
Solaris
All output

Posts: 5 | Registered: September 21, 2009

Ignored post by sharrow posted

Show Post

Francis Mariani

Expert

posted

Hide Post

I've never seen code that uses package variables, so I can't help you debug this, but you could try creating an Oracle Stored Procedure containing your SQL and then call the Stored Procedure Using SQL Passthru.

Francis

Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server

Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005

Ignored post by Francis Mariani posted

Show Post

Tony A

Expert

posted

Hide Post