Left Outer Join Issue

[ehillbama57]

I have two database files that I need to join in an SQL. ARMST will be joined to SYNOT6 which may have no associated records or may have many associated records to ARMST. The two files are joined on company code and customer number. This is what I am doing so far and it is working like I want:

================================================== ============================
select a.accust,
a.acname,
a.aca01,
a.aca02,
a.aca03,
a.aca04,
a.aca06,
a.aca05,
a.acsr,
a.accrcd,
a.accrlm,
coalesce(ics.tomdy(max(b.snedat)), ' '),
coalesce(ics.tomdy(max(b.snfdat)), ' '),
a.acslno,
a.acslnm
from armst a left outer join synot6 b on a.accomp = b.sncomp and a.accust = b.sncust
group by a.accust, a.acname, a.aca01, a.aca02, a.aca03, a.aca04, a.aca06, a.aca05, a.acsr, a.accrcd, a.accrlm, a.acslno, a.acslnm
================================================== ============================

As you can see I'm using a User Defined Function (ics.tomdy) in the secondary file (to return a date from numeric YYYYMMDD to character MM/DD/YY)

This is working like I need it to but I need to add one more field from the secondary file. It is the user id for the date found in b.snedat. I'm getting the latest date there by using MAX. I need to retrieve the user id for the record that has the MAX(b.snedat) in it. Is this possible or do I need to go another route completely?

I'm still learning some of the advanced (as well as NOT advanced) uses of SQL and need some help on this one.

Thanks!!!!!

[Rocky]

I take it that there aren't multiple userids for a given b.snedat...

I'd create a view over sncomp

create view vsncomp as
select
  snuserid,
  coalesce(ics.tomdy(max(b.snedat)), ' ') snedat,
  coalesce(ics.tomdy(max(b.snfdat)), ' ') snfdat
from
  sncomp
group by
  snuserid;

I would then use this view in your join.

[ehillbama57]

Rocky,

That is close but silly me forgot to mention one other thing. The userid in this file can come from two different fields depending on the contents:

IF SNUSRP <> *BLANKS;
then use this field;
ELSE;
use field SNUSER;
ENDIF;

Not sure how I would code the view in this instance? CASE? I'm not very familiar with the syntax of CASE even after GOOGLING.

Thanks for your help!!!!!

[Rocky]

 
 create view vsncomp as select   CASE WHEN SNUSRP <> '' THEN       fld    ELSE     SNUSER   END SNUSERID,   coalesce(ics.tomdy(max(b.snedat)), ' ') snedat,   coalesce(ics.tomdy(max(b.snfdat)), ' ') snfdat from   sncomp group by CASE WHEN SNUSRP <> '' THEN   fld ELSE   SNUSER END;

This should take care of it - use the above created by this in your join - the field for hour user id is SNUSERID.

[Rocky]

 
 create view vsncomp as    select     CASE        WHEN SNUSRP <> '' THEN          fld           ELSE          SNUSER       END SNUSERID,     coalesce(ics.tomdy(max(b.snedat)), ' ') snedat,     coalesce(ics.tomdy(max(b.snfdat)), ' ') snfdat  from    sncomp group by     CASE      WHEN SNUSRP <> '' THEN        fld       ELSE        SNUSER    END;

This should take care of it - use the above created by this in your join - the field for hour user id is SNUSERID.

[Rocky]

I can't edit or delete posts - for some reason it took my nicely formatted code and flatted it out...

[ehillbama57]

Rocky,

I'll take the code any way I can get it. :-)

I still need to join over accomp=sncomp, and accust=sncust. So shouldn't the SNCOMP and SNCUST fields be included in the view create somewhere? I tried to add them in the select and got errors.


Thanks for your help!!!!

[Rocky]

Sorry - yes you just add those fields to the list in the select and in the group by...

[TedHolt]

Eric, I believe you are barking up the wrong tree. My guess is that you want SNEDAT, SNFDAT, and the user ID to come from the same row (record). Is that correct?

If so, then you need to select that record (one record per company & customer, that is) before joining to ARMST. Rather than fool with the MAX function, I would use an OLAP function.

with sn as (
select sncomp, sncust, snedat, snfdat,
       case when snusrp <> ' '
              then snusrp else snuser end as suser,
       row_number () over (partition by sncomp, sncust
                               order by snedat desc) as rownbr
 from synot6)
select a.accomp, a.accust, a.acname, a.aca01, a.aca02, a.acslno,
       tomdy(b.snedat), tomdy(b.snfdat), b.suser
  from armst as a
  left join sn as b
    on (a.accomp, a.accust) = (b.sncomp, b.sncust)
   and (b.rownbr = 1)
order by a.accomp, a.accust

The SN common table expression numbers the rows for each company/customer from latest SNEDAT to the earliest. Join that to ARMST, but the expression (b.rownbr = 1) only joins to the first row for each company/customer. You don't need GROUP BY, because there is only one row per company/customer in each table.

You could also embed the first SELECT in the join.

select a.accomp, a.accust, a.acname, a.aca01, a.aca02, a.acslno,
       b.snedat, b.snfdat, b.suser
  from armst as a
  left join ( select sncomp, sncust, snedat, snfdat,
                case when snusrp <> ' '
                     then snusrp else snuser end as suser,
                row_number () over (partition by sncomp, sncust
                               order by snedat desc) as rownbr
                from synot6) as b
    on (a.accomp, a.accust) = (b.sncomp, b.sncust)
   and (b.rownbr = 1)
order by a.accomp, a.accust

I prefer common table expressions because I think they're easier to read and give me more flexibility.

Rocky's recommendation that you create a view is also a good way to go.

You can read more about this sort of thing here: http://www.itjungle.com/fhg/fhg031516-story02.html

I omitted some of the columns (fields) from ARMST. You will need to add them to the main SELECT, not the CTE SELECT.

If my guess is wrong, and SNEDAT and SNFDAT can come from different rows, this query won't work. If that's the case, and you can't figure it out, let us know and one of us with help you.

[ehillbama57]

Hey Ted!!!!! I think that is going to do the trick. My supervisor and I have been kicking this around for awhile. We are making our first journey into using a custom SQL for a PUI grid. Clients wanted more than the 9,999 subfile/grid limit for export to EXCEL. This is going to help immensely in the future.

The SNEDAT should be the highest or latest date. That is the row I want to return. Not necessarily the first row. That is why we were using the MAX function. Any help with this?

Hope all is well with you and if you're ever over this way, drop by!!!

[ehillbama57]

Ted. Never mind. I see what you did with the DESC. For some reason I was reading that as a "description" abbreviation!!!!

[TedHolt]

The GROUP BY over so many non-key fields raised a red flag. I hope it works, Eric. If you need more help with it, you know how to get ahold of me.

[ehillbama57]

Back to this again... The SQL worked great as is but the users want the salesman number (a.acslno) to display as blank instead of zero on my grid. I have the statement looking like this now:

with sn as (select sncomp, sncust, snedat, snfdat, case when snusrp <> ' ' then snusrp else snuser end as suser, row_number() over (partition by sncomp, sncust order by snedat desc) as rownbr from synot6) select a.accust, a.acname, a.aca01, a.aca02, a.aca03, a.aca04, a.aca06, a.aca05, a.acsr, a.accrcd, a.accrlm, coalesce(ics.tomdy(b.snedat), ' '), coalesce(b.suser, ' '), coalesce(ics.tomdy(b.snfdat), ' '), cast(case when a.acslno <> 0 then a.acslno else ' ' end as char(4)) as slno, a.acslnm from armst as a left join sn as b on a.accomp = b.sncomp and a.accust = b.sncust and b.rownbr = 1 where a.aca05 <> 0

Trying to use CAST along with a CASE scenario. This isn't working correctly. Running this in Navigator I get all +++++ when a.acslno is zero and the correct value when non-zero.

Any help on this one? Thanks!!!!!

[tomliotta]

...but the users want the salesman number (a.acslno) to display as blank instead of zero on my grid.

What is "grid"? Viewing results in iNav may not give quite the same visible results as in some other client used by users. The presentation of values can depend on how a client handles numeric and character data in a single column.

case when a.acslno <> 0 then a.acslno else ' ' end

Try this way:

case when a.acslno <> 0 then DIGITS(a.acslno) else ' ' end

Also, what is the database column definition of A.ACSLNO? Manipulation of a numeric value as characters can take some experimenting.

[ehillbama57]

Tom... I'm using Profoundlogic's UI product. The grid is just a subfile in a browser. The subfile has no fields defined but is filled with whatever is returned from the SQL.

A.ACSLNO is a 4.0 field. I need that to be blank if it is zero so the subfile/grid will NOT show "0". I'll try your suggestion when I get to work in the morning.

I appreciate the help!!!!!