Home > IT Answers > Oracle > Query
ATitus
85 pts.
 Query
Oracle, SQL, DB2, Database
I need to create a query on a file that has several detail lines for the same shipper number, I would like to see only one line per shipper number, using SQL I get the result I want by using the following command --- SELECT DISTINCT compny, shpinv, acode, cabbv, dabbv, shipdt, oem
FROM futdtalib/vlpasn WHERE acode = 'I'

How can I get the same results using Query so that I can schedule a job to run nightly and create a report.

Any help would be appreciated. I have been away from the 400 for a while :)

thanks
ASKED: Dec 6, 2006  9:56 AM GMT
UPDATED: December 6, 2006  6:24:02 PM GMT
RELATED QUESTIONS
KeithHodges
0 pts.
Answer Wiki:
You may find RUNSQLSTM easier than using query. Just cut and paste your statement into a source member, add a semicolon to the end of the statement. then use command RUNSQLSTM to execute it.
Last Wiki Answer Submitted:  Dec 6, 2006  10:40 AM (GMT)  by  KeithHodges   0 pts.
To see other answers submitted to the Answer Wiki View Answer History.
RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _



 

Excellent response from Keith Hodges. I’ve been using this since IBM Rochester let me know about RUNSQLSTM:

From: KeithHodges send a private message to KeithHodges

Date Sent: 06 Dec 2006 10:40 EST (15:40 GMT)

Subject: RE: Query

You may find RUNSQLSTM easier than using query. Just cut and paste your statement into a source member, add a semicolon to the end of the statement. then use command RUNSQLSTM to execute it.

A. Titus, All you need to do is create a source file such as QSQLSRC and create that source member he mentions. - John

Example:

Work with Members Using PDM POQUITO
File . . . . . . QSQLSRC
Text
OWN_MASLH RPGLE Roll Bus & Pers Names into ALPHANAME 11/01/2006
*************** Beginning of data
CREATE VIEW OWN_MASLH ( PARCEL_ID,
BOOK,
MAP,
PARCEL,
SPLIT,
CHK_DIG,
TAX_ID,
BUS_NAME,
C_O,
LNAME,
FNAME,
MIDDLE,
GEN,
ALPHANAME,
T1DELF,
LINE_NBR_M,
NAME_TYPE,
R_TAX_YEAR
) AS

SELECT PARCEL_ID,
BOOK,
MAP,
PARCEL,
CHK_DIG,
TAX_ID,
BUS_NAME,
C_O,
FNAME,
MIDDLE, GEN, T1DELF, LINE_NBR_M, NAME_TYPE, R_TAX_YEAR,
CASE WHEN TRIM(LNAME) ‘ ‘
THEN LNAME || ‘ ‘ || FNAME || ‘ ‘ || MIDDLE || ‘ ‘ || G
ELSE BUS_NAME END AS ALPHANAME
FROM OWN_MAS

My example creates something like a logical which you can read from end-to-end like a report.

I wish I could index it.

JohndeCoville
 0 pts.