SQL Expression case

[Former Member]

I have to fetch some data this way because all links are failing. How do I specify that i want to also select the second table, the ICDET76PK. THIs one is not working on the first =. The thing is I want this to be distinct on the ICDET76PK.ITTRN#

because, the testpk1 is a summary table and the other is detail, if there are more than 1 detail records for part# and invoice then we don't want to fetch twice. we basically want to fetch the invoice amt for the part, this is summarized and correct.

(

SELECT 

    PKLIB.TESTPK1.LINTOT01,

FROM

    PKLIB.TESTPK1

WHERE

        PKLIB.TESTPK1.IDINV# = PKLIB.ICDET76PK.ITTRN#

AND     PKLIB.TESTPK1.IDPRT# =

PKLIB.ICDET76PK.ITPRT#

)

[JWiseman · Accepted solution]

hi Paul,

not sure what you mean "select the second table". also, you said "this one is not working on the first"...what is the issue? is there an error? is a number returned but not correct?

if you want to use a select statement in a sql expression field, you'll also need to alias the table that is on the main report.

please have a look at the attachment. extract the contents and change the .txt extension to .rpt. this example uses a sql expression to sum the quantity from a second table.

note that in the sql expression, the order table is aliased. this is because that table exists on the main report. the aliased table in the expression and the main report table are then associated in the WHERE clause.

(

SELECT SUM(`Orders_Detail`.`Quantity`)

FROM `Orders` `Orders2`

INNER JOIN `Orders Detail` `Orders_Detail`

ON `Orders2`.`Order ID`=`Orders_Detail`.`Order ID`

WHERE `Orders2`.`Order ID` = `Orders`.`Order ID`

GROUP BY `Orders2`.`Order ID`

)

also note that using a select in a sql expression is not officially supported...there are issues with oracle sometimes as per forum posts, but this method works quite well in db's like sap hana, sql server, mysql...

i hope this helps,

jamie

sqlexpression.txt.zip

[Former Member · Accepted solution]

Hi Paul,

Try this expression:

SELECT

    PKLIB.TESTPK1.LINTOT01, distinct(PKLIB.ICDET76PK.ITTRN#)

FROM

    PKLIB.TESTPK1,PKLIB.ICDET76PK

WHERE

        PKLIB.TESTPK1.IDINV# = PKLIB.ICDET76PK.ITTRN#

AND     PKLIB.TESTPK1.IDPRT# =

PKLIB.ICDET76PK.ITPRT#

I think u r not mention PKLIB.ICDET76PK  and distinct function is used to remove duplicates from the result..

--Dhana