This is an archive of the Maximo Yahoo Community. The content of this pages may be a sometimes obsolete so please check post dates.
Thanks to the community owner Christopher Wanko for providing the content.
Can someone assist... I'm trying to write a report that shows all assets that meet my criteria but right now because of my join (I think), it is only showing 1 (one) asset because it is the only asset in the database that meets my criteria and has ldtext...
What I want is a listing of all oif the assets that meet my criteria and then for it to also populate ldtext when those exist for any of the assets listsed
Can anyone tell me what is wrong with my statement?
SELECT asset.cb_rppassetnum, asset.cb_circuit1, asset.cb_secondary, asset.cb_amps, asset.cb_poles, asset.cb_cabletype, asset.cb_cbllngth, asset.cb_loaddesc, asset.cb_rackassetnum, asset.bay, asset.tile, longdescription.ldtext
FROM AMEXMAX4.dbo.asset
LEFT OUTER JOIN AMEXMAX4.dbo.longdescription ON asset.assetid = longdescription.ldkey
WHERE ((asset.cb_circuit1 Is Not Null) AND (longdescription.ldownercol='DESCRIPTION') OR (asset.cb_circuit2 Is Not Null) AND (longdescription.ldownercol='DESCRIPTION') OR (asset.cb_circuit3 Is Not Null) AND (asset.cb_rppassetnum='RPP-1') AND (longdescription.ldownercol='DESCRIPTION'))
ORDER BY asset.cb_circuit1
Hi,
Move all of your where criteris for the longdescription table from the where clause to the outer join criteria. Also add a criteria for the ldownertable to be asset. ldownertable = 'asset'. Other tables could use the same ldkey.
Dave Bone
--- In MAXIMO@yahoogroups.com, "hankinskeith@..." <keith.d.hankins@...> wrote:
>
> Can someone assist... I'm trying to write a report that shows all assets that meet my criteria but right now because of my join (I think), it is only showing 1 (one) asset because it is the only asset in the database that meets my criteria and has ldtext...
>
> What I want is a listing of all oif the assets that meet my criteria and then for it to also populate ldtext when those exist for any of the assets listsed
>
> Can anyone tell me what is wrong with my statement?
>
>
> SELECT asset.cb_rppassetnum, asset.cb_circuit1, asset.cb_secondary, asset.cb_amps, asset.cb_poles, asset.cb_cabletype, asset.cb_cbllngth, asset.cb_loaddesc, asset.cb_rackassetnum, asset.bay, asset.tile, longdescription.ldtext
> FROM AMEXMAX4.dbo.asset
> LEFT OUTER JOIN AMEXMAX4.dbo.longdescription ON asset.assetid = longdescription.ldkey
> WHERE ((asset.cb_circuit1 Is Not Null) AND (longdescription.ldownercol='DESCRIPTION') OR (asset.cb_circuit2 Is Not Null) AND (longdescription.ldownercol='DESCRIPTION') OR (asset.cb_circuit3 Is Not Null) AND (asset.cb_rppassetnum='RPP-1') AND (longdescription.ldownercol='DESCRIPTION'))
> ORDER BY asset.cb_circuit1
>
SELECTassetnum,longdescription.ldtextFROMassetLEFTOUTERJOINlongdescription ON
Then
WHERE ((asset.cb_circuit1 Is Not Null) OR (asset.cb_circuit2 Is Not Null) OR ((asset.cb_circuit3 Is Not Null) AND (asset.cb_rppassetnum='RPP-1')) )
ORDER BY asset.cb_circuit1
this is assuming you want::
All records where cb_circuit1 is not null
OR cb_circuit2 is not null
OR (cb_circuit3 is not null and (asset.cb_rppassetnum='RPP-1'))
________________________________
From: "hankinskeith@ymail.com" <keith.d.hankins@aexp.com>
To: MAXIMO@yahoogroups.com
Sent: Monday, November 7, 2011 1:36 PM
Subject: [MAXIMO List] SQL Help Request: OUTER JOIN
Can someone assist... I'm trying to write a report that shows all assets that meet my criteria but right now because of my join (I think), it is only showing 1 (one) asset because it is the only asset in the database that meets my criteria and has ldtext...
What I want is a listing of all oif the assets that meet my criteria and then for it to also populate ldtext when those exist for any of the assets listsed
Can anyone tell me what is wrong with my statement?
SELECT asset.cb_rppassetnum, asset.cb_circuit1, asset.cb_secondary, asset.cb_amps, asset.cb_poles, asset.cb_cabletype, asset.cb_cbllngth, asset.cb_loaddesc, asset.cb_rackassetnum, asset.bay, asset.tile, longdescription.ldtext
FROM AMEXMAX4.dbo.asset
LEFT OUTER JOIN AMEXMAX4.dbo.longdescription ON asset.assetid = longdescription.ldkey
WHERE ((asset.cb_circuit1 Is Not Null) AND (longdescription.ldownercol='DESCRIPTION') OR (asset.cb_circuit2 Is Not Null) AND (longdescription.ldownercol='DESCRIPTION') OR (asset.cb_circuit3 Is Not Null) AND (asset.cb_rppassetnum='RPP-1') AND (longdescription.ldownercol='DESCRIPTION'))
ORDER BY asset.cb_circuit1
asset.assetid =longdescription.ldkey andlongdescription.ldownertable ='ASSET'andlongdescription.ldownercol='DESCRIPTION'
Let me try that again:
SELECTasset.cb_rppassetnum, asset.cb_circuit1, asset.cb_secondary, asset.cb_amps, asset.cb_poles, asset.cb_cabletype, asset.cb_cbllngth, asset.cb_loaddesc, asset.cb_rackassetnum, asset.bay, asset.tile, longdescription.ldtext
FROM AMEXMAX4.dbo.asset
LEFTOUTERJOINlongdescription ONasset.assetid =longdescription.ldkey andlongdescription.ldownertable ='ASSET'andlongdescription.ldownercol ='DESCRIPTION'WHERE(asset.cb_circuit1 IsNotNull)OR(asset.cb_circuit2 IsNotNull)OR((asset.cb_circuit3 IsNotNull)AND(asset.cb_rppassetnum='RPP-1'))ORDERBYasset.cb_circuit1
________________________________
From: "hankinskeith@ymail.com" <keith.d.hankins@aexp.com>
To: MAXIMO@yahoogroups.com
Sent: Monday, November 7, 2011 1:36 PM
Subject: [MAXIMO List] SQL Help Request: OUTER JOIN
Can someone assist... I'm trying to write a report that shows all assets that meet my criteria but right now because of my join (I think), it is only showing 1 (one) asset because it is the only asset in the database that meets my criteria and has ldtext...
What I want is a listing of all oif the assets that meet my criteria and then for it to also populate ldtext when those exist for any of the assets listsed
Can anyone tell me what is wrong with my statement?
SELECT asset.cb_rppassetnum, asset.cb_circuit1, asset.cb_secondary, asset.cb_amps, asset.cb_poles, asset.cb_cabletype, asset.cb_cbllngth, asset.cb_loaddesc, asset.cb_rackassetnum, asset.bay, asset.tile, longdescription.ldtext
FROM AMEXMAX4.dbo.asset
LEFT OUTER JOIN AMEXMAX4.dbo.longdescription ON asset.assetid = longdescription.ldkey
WHERE ((asset.cb_circuit1 Is Not Null) AND (longdescription.ldownercol='DESCRIPTION') OR (asset.cb_circuit2 Is Not Null) AND (longdescription.ldownercol='DESCRIPTION') OR (asset.cb_circuit3 Is Not Null) AND (asset.cb_rppassetnum='RPP-1') AND (longdescription.ldownercol='DESCRIPTION'))
ORDER BY asset.cb_circuit1
THANK YOU! That worked... I TRULY appreciate it!! Keith
--- In MAXIMO@yahoogroups.com, Chris Brandon <cbrandon63@...> wrote:
>
> Let me try that again:
> Â
> Â SELECTasset.cb_rppassetnum, asset.cb_circuit1, asset.cb_secondary, asset.cb_amps, asset.cb_poles, asset.cb_cabletype, asset.cb_cbllngth, asset.cb_loaddesc, asset.cb_rackassetnum, asset.bay, asset.tile, longdescription.ldtext
> FROM AMEXMAX4.dbo.asset
> LEFTOUTERJOINlongdescription ONasset.assetid =longdescription.ldkey andlongdescription.ldownertable ='ASSET'andlongdescription.ldownercol ='DESCRIPTION'WHERE(asset.cb_circuit1 IsNotNull)OR(asset.cb_circuit2 IsNotNull)OR((asset.cb_circuit3 IsNotNull)AND(asset.cb_rppassetnum='RPP-1'))ORDERBYasset.cb_circuit1
>
>
> ________________________________
> From: "hankinskeith@..." <keith.d.hankins@...>
> To: MAXIMO@yahoogroups.com
> Sent: Monday, November 7, 2011 1:36 PM
> Subject: [MAXIMO List] SQL Help Request: OUTER JOIN
>
>
> Â
> Can someone assist... I'm trying to write a report that shows all assets that meet my criteria but right now because of my join (I think), it is only showing 1 (one) asset because it is the only asset in the database that meets my criteria and has ldtext...
>
> What I want is a listing of all oif the assets that meet my criteria and then for it to also populate ldtext when those exist for any of the assets listsed
>
> Can anyone tell me what is wrong with my statement?
>
> SELECT asset.cb_rppassetnum, asset.cb_circuit1, asset.cb_secondary, asset.cb_amps, asset.cb_poles, asset.cb_cabletype, asset.cb_cbllngth, asset.cb_loaddesc, asset.cb_rackassetnum, asset.bay, asset.tile, longdescription.ldtext
> FROM AMEXMAX4.dbo.asset
> LEFT OUTER JOIN AMEXMAX4.dbo.longdescription ON asset.assetid = longdescription.ldkey
> WHERE ((asset.cb_circuit1 Is Not Null) AND (longdescription.ldownercol='DESCRIPTION') OR (asset.cb_circuit2 Is Not Null) AND (longdescription.ldownercol='DESCRIPTION') OR (asset.cb_circuit3 Is Not Null) AND (asset.cb_rppassetnum='RPP-1') AND (longdescription.ldownercol='DESCRIPTION'))
> ORDER BY asset.cb_circuit1
>
>
>
>
>
>