iSeries query: Compare two fields in one file

365 pts.
Tags:
AS/400
AS/400 queries
iSeries
Try to do a multiple select statement and then compare the results of that statement. See below: SELECT "T16.APAR".P._PARCEL_NR FROM QS36F."T16.APAR" WHERE "T16.APAR".P_PARCEL_NR NE "T16.APAR".P_PARCEL_NR (SELECT "T16.APAR".P_PARCEL_NR FROM QS36F."T16.APAR" AS "T16.APAR" WHERE "T16.APAR".P_YEAR = 2012 AND "T16.APAR".P_ROLL_TYPE = 'R' AND "T16.APAR".P_EXCLUSION_VALUE > 1; (SELECT "T16.APAR".P_PARCEL_NR FROM QS36F."T16.APAR" AS "T16.APAR" WHERE "T16.APAR".P_YEAR = 2013 AND "T16.APAR".P_ROLL_TYPE = 'R' AND "T16.APAR".P_EXCLUSION_VALUE < 1) Any help is appreciated!

Software/Hardware used:
Iseries, As400
ASKED: January 18, 2013  3:11 PM
UPDATED: January 21, 2013  2:05 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Discuss This Question: 9  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • philpl1jb
    I think we need a spec..something like this You want to report parcel numbers from file QS36F when - a row with the same parcel number???? for year 2012 has roll_type R and Exclusion > 1 and??? / or???? - that row (or is this some other row) has Year 2013, Roll_type R and Exclusion < 1  
    48,565 pointsBadges:
    report
  • lorit
    I want a report of parcel numbers from QS36F that are missing the exclusion value from year 2012 to 2013. Both 2012 parcel numbers and exclusion value are stored in the same file with 2013 information.  I want it to compare 2012 vs 2013 and give me a list of parcel numbers that are missing the exclusion value from 2012 to 2013
    365 pointsBadges:
    report
  • philpl1jb
    Ok  .. almost there Are you saying that you want the parcel number of any rows that are 2013 and exclusion <> 1 Plus the parcel number of any rows that are 2012 and exclusion <> 1 ???
    48,565 pointsBadges:
    report
  • philpl1jb
    So now that I read it again .. you would be matching the 2012 and 2013 row on parcel number and if both had Exclusion <> 1??? then you want that row reported?
    48,565 pointsBadges:
    report
  • philpl1jb
    One simple way to "merge" two Selects is with the UNION, UNION ALL, INTERSECT, or EXCEPT keywords This case sounds like an INTERSECT .. the value must appear in both lists. http://www.itjungle.com/fhg/fhg071404-story01.html    
    48,565 pointsBadges:
    report
  • lorit
    I need a list of parcel numbers that had an exclusion in 2012 ( > 1) but the 2013 record exclusion field is < 1)
    365 pointsBadges:
    report
  • lorit
    How do I tweek the above query to capture that information?  I'm really new to Iseries and query.  Thanks!
    365 pointsBadges:
    report
  • philpl1jb
    Then the Intersect (must be in both) should do the trick Something like this   SELECT "T16.APAR".P_PARCEL_NR FROM QS36F."T16.APAR" AS "T16.APAR" WHERE "T16.APAR".P_YEAR = 2012 AND "T16.APAR".P_ROLL_TYPE = 'R' AND "T16.APAR".P_EXCLUSION_VALUE > 1)   INTERSECT   (SELECT "T16.APAR".P_PARCEL_NR FROM QS36F."T16.APAR" AS "T16.APAR" WHERE "T16.APAR".P_YEAR = 2013 AND "T16.APAR".P_ROLL_TYPE = 'R' AND "T16.APAR".P_EXCLUSION_VALUE < 1)
    48,565 pointsBadges:
    report
  • lorit
    Thank you!
    365 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following