469,592 Members | 1,879 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,592 developers. It's quick & easy.

Error: Not a Group By Expression

6
I am using 2 tables linked together by the vendor id.
I want the vendor to display along with the other fields and the vendor type from the second file.
I have a vendor that shows 2 lines b/c it has 2 types. I understand why it shows 2 times, but would like to know if it is possible to have those 2 types on the same line in the same column. Ex:
Type(s)
---------------
OTR,Dray

I was hoping the GROUP BY by might cover that but it doesn't like it.
Any help would be appreciated.

SELECT vndmt.VENDOR_ID,
vndmt.VENDOR_NAME,
vndmt.CITY_NAME,
vndmt.STATE_CODE,
vndmt.OTR_CONTRACT_FLAG,
vndmt.DRAYAGE_CONTRACT_FLAG,
vndmt.LTL_CONTRACT_FLAG,
vndmt.BROKER_CONTRACT_FLAG,
vndmt.SCAC_CODE,
vndmt.INACTIVATE_FLAG,
vndmt.HAZMAT_EXPIRY_DATE,
vndmt.IS_HOUSE_HOLD_GOODS,
vndtype.VENDOR_TYPE
FROM MIL_VENDOR_TYPE_ML vndtype RIGHT OUTER JOIN MIL_VENDOR_MT vndmt
ON vndmt.vendor_id = vndtype.vendor_id
GROUP BY vndmt.vendor_id
ORDER BY vndmt.vendor_id;

Heres the output
168 C S X INTERMODAL ATLANTA GA N N CSXO N 30-JUN-08 N BKR <--Type (1)
168 C S X INTERMODAL ATLANTA GA N N CSXO N 30-JUN-08 N BNB<-- Type (2)
Dec 26 '06 #1
1 2118
b1randon
171 Expert 100+
I am using 2 tables linked together by the vendor id.
I want the vendor to display along with the other fields and the vendor type from the second file.
I have a vendor that shows 2 lines b/c it has 2 types. I understand why it shows 2 times, but would like to know if it is possible to have those 2 types on the same line in the same column. Ex:
Type(s)
---------------
OTR,Dray

I was hoping the GROUP BY by might cover that but it doesn't like it.
Any help would be appreciated.

SELECT vndmt.VENDOR_ID,
vndmt.VENDOR_NAME,
vndmt.CITY_NAME,
vndmt.STATE_CODE,
vndmt.OTR_CONTRACT_FLAG,
vndmt.DRAYAGE_CONTRACT_FLAG,
vndmt.LTL_CONTRACT_FLAG,
vndmt.BROKER_CONTRACT_FLAG,
vndmt.SCAC_CODE,
vndmt.INACTIVATE_FLAG,
vndmt.HAZMAT_EXPIRY_DATE,
vndmt.IS_HOUSE_HOLD_GOODS,
vndtype.VENDOR_TYPE
FROM MIL_VENDOR_TYPE_ML vndtype RIGHT OUTER JOIN MIL_VENDOR_MT vndmt
ON vndmt.vendor_id = vndtype.vendor_id
GROUP BY vndmt.vendor_id
ORDER BY vndmt.vendor_id;

Heres the output
168 C S X INTERMODAL ATLANTA GA N N CSXO N 30-JUN-08 N BKR <--Type (1)
168 C S X INTERMODAL ATLANTA GA N N CSXO N 30-JUN-08 N BNB<-- Type (2)
The only way I can think of to get them into the same column is to use a stored procedure and iterate through your rows, appending the different types when all the other columns match. I don't think there's a way to do it with SQL, but I'm not quite a guru with that so someone else may know.
Jan 3 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

6 posts views Thread by Peter Frost | last post: by
7 posts views Thread by John Øllgård Jensen | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.