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 anyone help me with my SQL... It worked well until I added the 3rd attribute cb_circuit1... Then it throws the following error...
SELECT assetnum + ' - ' + cb_rpppanassetnum + ' - ' + cb_circuit1 FROM asset
WHERE
asset.cb_dfdassetnum='RFDEV-RMDFD-2';
Error
Msg 8114, Level 16, State 5, Line 2
Error converting data type varchar to numeric.
It has something to do with the concatenate function and teh non VARCHAR attribute but I don't konw how to fix this...
Thank you in advance... SQL is not my strongsuit...
Keith
Is cb_circuit1 a numeric field? Can you confirm this is SQLServer? You may
need to CAST the value as a string.
Try something like:
SELECT assetnum + ' - ' + cb_rpppanassetnum + ' - ' + CAST(cb_circuit1 as
varchar) FROM asset
WHERE
asset.cb_dfdassetnum='RFDEV-RMDFD-2';
Chris.
On Tue, Nov 15, 2011 at 1:10 PM, hankinskeith@ymail.com <
keith.d.hankins@aexp.com> wrote:
> **
>
>
> Can anyone help me with my SQL... It worked well until I added the 3rd
> attribute cb_circuit1... Then it throws the following error...
>
> SELECT assetnum + ' - ' + cb_rpppanassetnum + ' - ' + cb_circuit1 FROM
> asset
> WHERE
> asset.cb_dfdassetnum='RFDEV-RMDFD-2';
>
> Error
> Msg 8114, Level 16, State 5, Line 2
> Error converting data type varchar to numeric.
>
> It has something to do with the concatenate function and teh non VARCHAR
> attribute but I don't konw how to fix this...
>
> Thank you in advance... SQL is not my strongsuit...
> Keith
>
>
>
Hi Keith,
As the error indicates, its a datatype mismath
. You need to use convert or cast with that.
Thanks & regards,
Victor.
--- In MAXIMO@yahoogroups.com, "hankinskeith@..." <keith.d.hankins@...> wrote:
>
> Can anyone help me with my SQL... It worked well until I added the 3rd attribute cb_circuit1... Then it throws the following error...
>
> SELECT assetnum + ' - ' + cb_rpppanassetnum + ' - ' + cb_circuit1 FROM asset
> WHERE
> asset.cb_dfdassetnum='RFDEV-RMDFD-2';
>
> Error
> Msg 8114, Level 16, State 5, Line 2
> Error converting data type varchar to numeric.
>
> It has something to do with the concatenate function and teh non VARCHAR attribute but I don't konw how to fix this...
>
> Thank you in advance... SQL is not my strongsuit...
> Keith
>
Thank you! Resolved... Any chance you could help me with one more...
Getting this error:
I understand why... trying to use outer join w/3 tables... but I don't know to get the data I need... I only need ldtext from table asset1... but I do need to join both instances of the asset table (asset1 and asset2)...
Would greatly appreciate any help!
SELECT asset1.cb_rppassetdesc, asset1.cb_circuit1, asset1.cb_amps, asset1.cb_poles, asset1.cb_cabletype, asset1.cb_cbllngth, asset1.cb_secondary, asset2.manufacturer, asset2.description, asset1.cb_rackassetnum, asset1.bay, asset1.tile, longdescription.ldtext
FROM asset asset1, asset asset2
LEFT OUTER JOIN longdescription ON asset.assetid = longdescription.ldkey and
longdescription.ldownertable = 'ASSET' and longdescription.ldownercol = 'DESCRIPTION'
WHERE ((1 = 1) AND (asset1.cb_circuit1 Is Not Null) AND (asset1.cb_loaddesc = asset2.assetnum))
OR ((1 = 1) AND (asset1.cb_circuit2 Is Not Null) AND (asset1.cb_loaddesc = asset2.assetnum))
OR ((1 = 1) AND (asset1.cb_circuit3 Is Not Null) AND (asset1.cb_loaddesc = asset2.assetnum))
ORDER BY asset1.cb_circuit1
--- In MAXIMO@yahoogroups.com, "Victor" <vikas17.j@...> wrote:
>
> Hi Keith,
>
> As the error indicates, its a datatype mismath
> . You need to use convert or cast with that.
>
> Thanks & regards,
> Victor.
>
> --- In MAXIMO@yahoogroups.com, "hankinskeith@" <keith.d.hankins@> wrote:
> >
> > Can anyone help me with my SQL... It worked well until I added the 3rd attribute cb_circuit1... Then it throws the following error...
> >
> > SELECT assetnum + ' - ' + cb_rpppanassetnum + ' - ' + cb_circuit1 FROM asset
> > WHERE
> > asset.cb_dfdassetnum='RFDEV-RMDFD-2';
> >
> > Error
> > Msg 8114, Level 16, State 5, Line 2
> > Error converting data type varchar to numeric.
> >
> > It has something to do with the concatenate function and teh non VARCHAR attribute but I don't konw how to fix this...
> >
> > Thank you in advance... SQL is not my strongsuit...
> > Keith
> >
>
Hi Keith,
You need to use the alias defined for asset1 and asset2 in the joins instead of Asset.
Corrected :asset1.assetid = longdescription.ldkey
SQL :
SELECT asset1.cb_rppassetdesc, asset1.cb_circuit1, asset1.cb_amps, asset1.cb_poles, asset1.cb_cabletype, asset1.cb_cbllngth, asset1.cb_secondary, asset2.manufacturer, asset2.description, asset1.cb_rackassetnum, asset1.bay, asset1.tile, longdescription.ldtext
> FROM asset asset1, asset asset2
> LEFT OUTER JOIN longdescription ON asset1.assetid = longdescription.ldkey and
> longdescription.ldownertable = 'ASSET' and longdescription.ldownercol = 'DESCRIPTION'
> WHERE ((1 = 1) AND (asset1.cb_circuit1 Is Not Null) AND (asset1.cb_loaddesc = asset2.assetnum))
> OR ((1 = 1) AND (asset1.cb_circuit2 Is Not Null) AND (asset1.cb_loaddesc = asset2.assetnum))
> OR ((1 = 1) AND (asset1.cb_circuit3 Is Not Null) AND (asset1.cb_loaddesc = asset2.assetnum))
> ORDER BY asset1.cb_circuit1
It would be easier for us to give you the solution if you can share the error with us.
Thanks & regards,
Victor.
--- In MAXIMO@yahoogroups.com, "hankinskeith@..." <keith.d.hankins@...> wrote:
>
> Thank you! Resolved... Any chance you could help me with one more...
>
> Getting this error:
>
> I understand why... trying to use outer join w/3 tables... but I don't know to get the data I need... I only need ldtext from table asset1... but I do need to join both instances of the asset table (asset1 and asset2)...
>
> Would greatly appreciate any help!
>
> SELECT asset1.cb_rppassetdesc, asset1.cb_circuit1, asset1.cb_amps, asset1.cb_poles, asset1.cb_cabletype, asset1.cb_cbllngth, asset1.cb_secondary, asset2.manufacturer, asset2.description, asset1.cb_rackassetnum, asset1.bay, asset1.tile, longdescription.ldtext
> FROM asset asset1, asset asset2
> LEFT OUTER JOIN longdescription ON asset.assetid = longdescription.ldkey and
> longdescription.ldownertable = 'ASSET' and longdescription.ldownercol = 'DESCRIPTION'
> WHERE ((1 = 1) AND (asset1.cb_circuit1 Is Not Null) AND (asset1.cb_loaddesc = asset2.assetnum))
> OR ((1 = 1) AND (asset1.cb_circuit2 Is Not Null) AND (asset1.cb_loaddesc = asset2.assetnum))
> OR ((1 = 1) AND (asset1.cb_circuit3 Is Not Null) AND (asset1.cb_loaddesc = asset2.assetnum))
> ORDER BY asset1.cb_circuit1
>
> --- In MAXIMO@yahoogroups.com, "Victor" <vikas17.j@> wrote:
> >
> > Hi Keith,
> >
> > As the error indicates, its a datatype mismath
> > . You need to use convert or cast with that.
> >
> > Thanks & regards,
> > Victor.
> >
> > --- In MAXIMO@yahoogroups.com, "hankinskeith@" <keith.d.hankins@> wrote:
> > >
> > > Can anyone help me with my SQL... It worked well until I added the 3rd attribute cb_circuit1... Then it throws the following error...
> > >
> > > SELECT assetnum + ' - ' + cb_rpppanassetnum + ' - ' + cb_circuit1 FROM asset
> > > WHERE
> > > asset.cb_dfdassetnum='RFDEV-RMDFD-2';
> > >
> > > Error
> > > Msg 8114, Level 16, State 5, Line 2
> > > Error converting data type varchar to numeric.
> > >
> > > It has something to do with the concatenate function and teh non VARCHAR attribute but I don't konw how to fix this...
> > >
> > > Thank you in advance... SQL is not my strongsuit...
> > > Keith
> > >
> >
>
Hi Victor -
I tried that but get the following error:
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "asset1.assetid" could not be bound.
Here is the SQL statement I used:
SELECT asset1.cb_rppassetdesc, asset1.cb_circuit1, asset1.cb_amps,
asset1.cb_poles, asset1.cb_cabletype, asset1.cb_cbllngth, asset1.cb_secondary,
asset2.manufacturer, asset2.description, asset1.cb_rackassetnum, asset1.bay,
asset1.tile, longdescription.ldtext
FROM asset asset1, asset asset2
LEFT OUTER JOIN longdescription ON asset1.assetid = longdescription.ldkey and
longdescription.ldownertable = 'ASSET' and longdescription.ldownercol =
'DESCRIPTION'
WHERE ((1 = 1) AND (asset1.cb_circuit1 Is Not Null) AND (asset1.cb_loaddesc =
asset2.assetnum))
OR ((1 = 1) AND (asset1.cb_circuit2 Is Not Null) AND (asset1.cb_loaddesc =
asset2.assetnum))
OR ((1 = 1) AND (asset1.cb_circuit3 Is Not Null) AND (asset1.cb_loaddesc =
asset2.assetnum))
ORDER BY asset1.cb_circuit1
--- In MAXIMO@yahoogroups.com, "Victor" <vikas17.j@...> wrote:
>
>
> Hi Keith,
>
> You need to use the alias defined for asset1 and asset2 in the joins instead of Asset.
> Corrected :asset1.assetid = longdescription.ldkey
>
> SQL :
> SELECT asset1.cb_rppassetdesc, asset1.cb_circuit1, asset1.cb_amps, asset1.cb_poles, asset1.cb_cabletype, asset1.cb_cbllngth, asset1.cb_secondary, asset2.manufacturer, asset2.description, asset1.cb_rackassetnum, asset1.bay, asset1.tile, longdescription.ldtext
> > FROM asset asset1, asset asset2
> > LEFT OUTER JOIN longdescription ON asset1.assetid = longdescription.ldkey and
> > longdescription.ldownertable = 'ASSET' and longdescription.ldownercol = 'DESCRIPTION'
> > WHERE ((1 = 1) AND (asset1.cb_circuit1 Is Not Null) AND (asset1.cb_loaddesc = asset2.assetnum))
> > OR ((1 = 1) AND (asset1.cb_circuit2 Is Not Null) AND (asset1.cb_loaddesc = asset2.assetnum))
> > OR ((1 = 1) AND (asset1.cb_circuit3 Is Not Null) AND (asset1.cb_loaddesc = asset2.assetnum))
> > ORDER BY asset1.cb_circuit1
>
>
> It would be easier for us to give you the solution if you can share the error with us.
>
> Thanks & regards,
> Victor.
> --- In MAXIMO@yahoogroups.com, "hankinskeith@" <keith.d.hankins@> wrote:
> >
> > Thank you! Resolved... Any chance you could help me with one more...
> >
> > Getting this error:
> >
> > I understand why... trying to use outer join w/3 tables... but I don't know to get the data I need... I only need ldtext from table asset1... but I do need to join both instances of the asset table (asset1 and asset2)...
> >
> > Would greatly appreciate any help!
> >
> > SELECT asset1.cb_rppassetdesc, asset1.cb_circuit1, asset1.cb_amps, asset1.cb_poles, asset1.cb_cabletype, asset1.cb_cbllngth, asset1.cb_secondary, asset2.manufacturer, asset2.description, asset1.cb_rackassetnum, asset1.bay, asset1.tile, longdescription.ldtext
> > FROM asset asset1, asset asset2
> > LEFT OUTER JOIN longdescription ON asset.assetid = longdescription.ldkey and
> > longdescription.ldownertable = 'ASSET' and longdescription.ldownercol = 'DESCRIPTION'
> > WHERE ((1 = 1) AND (asset1.cb_circuit1 Is Not Null) AND (asset1.cb_loaddesc = asset2.assetnum))
> > OR ((1 = 1) AND (asset1.cb_circuit2 Is Not Null) AND (asset1.cb_loaddesc = asset2.assetnum))
> > OR ((1 = 1) AND (asset1.cb_circuit3 Is Not Null) AND (asset1.cb_loaddesc = asset2.assetnum))
> > ORDER BY asset1.cb_circuit1
> >
> > --- In MAXIMO@yahoogroups.com, "Victor" <vikas17.j@> wrote:
> > >
> > > Hi Keith,
> > >
> > > As the error indicates, its a datatype mismath
> > > . You need to use convert or cast with that.
> > >
> > > Thanks & regards,
> > > Victor.
> > >
> > > --- In MAXIMO@yahoogroups.com, "hankinskeith@" <keith.d.hankins@> wrote:
> > > >
> > > > Can anyone help me with my SQL... It worked well until I added the 3rd attribute cb_circuit1... Then it throws the following error...
> > > >
> > > > SELECT assetnum + ' - ' + cb_rpppanassetnum + ' - ' + cb_circuit1 FROM asset
> > > > WHERE
> > > > asset.cb_dfdassetnum='RFDEV-RMDFD-2';
> > > >
> > > > Error
> > > > Msg 8114, Level 16, State 5, Line 2
> > > > Error converting data type varchar to numeric.
> > > >
> > > > It has something to do with the concatenate function and teh non VARCHAR attribute but I don't konw how to fix this...
> > > >
> > > > Thank you in advance... SQL is not my strongsuit...
> > > > Keith
> > > >
> > >
> >
>
Nodify your query something like this :
SELECT asset1.cb_rppassetdesc, asset1.cb_circuit1, asset1.cb_amps,
asset1.cb_poles, asset1.cb_cabletype, asset1.cb_cbllngth, asset1.cb_secondary,
asset2.manufacturer, asset2.description, asset1.cb_rackassetnum, asset1.bay,
asset1.tile, longdescription.ldtext
FROM asset asset1 left outer join asset asset2 on asset1.cb_loaddesc = asset2.assetnum
LEFT OUTER JOIN longdescription ON asset1.assetid = longdescription.ldkey and
longdescription.ldownertable = 'ASSET' and longdescription.ldownercol =
'DESCRIPTION'
WHERE ((1 = 1) AND (asset1.cb_circuit1 Is Not Null))
OR ((1 = 1) AND (asset1.cb_circuit2 Is Not Null))
OR ((1 = 1) AND (asset1.cb_circuit3 Is Not Null))
ORDER BY asset1.cb_circuit1
Hope this helps.
Thanks & regards,
Victor.
--- In MAXIMO@yahoogroups.com, "hankinskeith@..." <keith.d.hankins@...> wrote:
>
> Hi Victor -
>
> I tried that but get the following error:
>
> Msg 4104, Level 16, State 1, Line 1
> The multi-part identifier "asset1.assetid" could not be bound.
>
>
> Here is the SQL statement I used:
>
> SELECT asset1.cb_rppassetdesc, asset1.cb_circuit1, asset1.cb_amps,
> asset1.cb_poles, asset1.cb_cabletype, asset1.cb_cbllngth, asset1.cb_secondary,
> asset2.manufacturer, asset2.description, asset1.cb_rackassetnum, asset1.bay,
> asset1.tile, longdescription.ldtext
> FROM asset asset1, asset asset2
> LEFT OUTER JOIN longdescription ON asset1.assetid = longdescription.ldkey and
> longdescription.ldownertable = 'ASSET' and longdescription.ldownercol =
> 'DESCRIPTION'
> WHERE ((1 = 1) AND (asset1.cb_circuit1 Is Not Null) AND (asset1.cb_loaddesc =
> asset2.assetnum))
> OR ((1 = 1) AND (asset1.cb_circuit2 Is Not Null) AND (asset1.cb_loaddesc =
> asset2.assetnum))
> OR ((1 = 1) AND (asset1.cb_circuit3 Is Not Null) AND (asset1.cb_loaddesc =
> asset2.assetnum))
> ORDER BY asset1.cb_circuit1
>
>
> --- In MAXIMO@yahoogroups.com, "Victor" <vikas17.j@> wrote:
> >
> >
> > Hi Keith,
> >
> > You need to use the alias defined for asset1 and asset2 in the joins instead of Asset.
> > Corrected :asset1.assetid = longdescription.ldkey
> >
> > SQL :
> > SELECT asset1.cb_rppassetdesc, asset1.cb_circuit1, asset1.cb_amps, asset1.cb_poles, asset1.cb_cabletype, asset1.cb_cbllngth, asset1.cb_secondary, asset2.manufacturer, asset2.description, asset1.cb_rackassetnum, asset1.bay, asset1.tile, longdescription.ldtext
> > > FROM asset asset1, asset asset2
> > > LEFT OUTER JOIN longdescription ON asset1.assetid = longdescription.ldkey and
> > > longdescription.ldownertable = 'ASSET' and longdescription.ldownercol = 'DESCRIPTION'
> > > WHERE ((1 = 1) AND (asset1.cb_circuit1 Is Not Null) AND (asset1.cb_loaddesc = asset2.assetnum))
> > > OR ((1 = 1) AND (asset1.cb_circuit2 Is Not Null) AND (asset1.cb_loaddesc = asset2.assetnum))
> > > OR ((1 = 1) AND (asset1.cb_circuit3 Is Not Null) AND (asset1.cb_loaddesc = asset2.assetnum))
> > > ORDER BY asset1.cb_circuit1
> >
> >
> > It would be easier for us to give you the solution if you can share the error with us.
> >
> > Thanks & regards,
> > Victor.
> > --- In MAXIMO@yahoogroups.com, "hankinskeith@" <keith.d.hankins@> wrote:
> > >
> > > Thank you! Resolved... Any chance you could help me with one more...
> > >
> > > Getting this error:
> > >
> > > I understand why... trying to use outer join w/3 tables... but I don't know to get the data I need... I only need ldtext from table asset1... but I do need to join both instances of the asset table (asset1 and asset2)...
> > >
> > > Would greatly appreciate any help!
> > >
> > > SELECT asset1.cb_rppassetdesc, asset1.cb_circuit1, asset1.cb_amps, asset1.cb_poles, asset1.cb_cabletype, asset1.cb_cbllngth, asset1.cb_secondary, asset2.manufacturer, asset2.description, asset1.cb_rackassetnum, asset1.bay, asset1.tile, longdescription.ldtext
> > > FROM asset asset1, asset asset2
> > > LEFT OUTER JOIN longdescription ON asset.assetid = longdescription.ldkey and
> > > longdescription.ldownertable = 'ASSET' and longdescription.ldownercol = 'DESCRIPTION'
> > > WHERE ((1 = 1) AND (asset1.cb_circuit1 Is Not Null) AND (asset1.cb_loaddesc = asset2.assetnum))
> > > OR ((1 = 1) AND (asset1.cb_circuit2 Is Not Null) AND (asset1.cb_loaddesc = asset2.assetnum))
> > > OR ((1 = 1) AND (asset1.cb_circuit3 Is Not Null) AND (asset1.cb_loaddesc = asset2.assetnum))
> > > ORDER BY asset1.cb_circuit1
> > >
> > > --- In MAXIMO@yahoogroups.com, "Victor" <vikas17.j@> wrote:
> > > >
> > > > Hi Keith,
> > > >
> > > > As the error indicates, its a datatype mismath
> > > > . You need to use convert or cast with that.
> > > >
> > > > Thanks & regards,
> > > > Victor.
> > > >
> > > > --- In MAXIMO@yahoogroups.com, "hankinskeith@" <keith.d.hankins@> wrote:
> > > > >
> > > > > Can anyone help me with my SQL... It worked well until I added the 3rd attribute cb_circuit1... Then it throws the following error...
> > > > >
> > > > > SELECT assetnum + ' - ' + cb_rpppanassetnum + ' - ' + cb_circuit1 FROM asset
> > > > > WHERE
> > > > > asset.cb_dfdassetnum='RFDEV-RMDFD-2';
> > > > >
> > > > > Error
> > > > > Msg 8114, Level 16, State 5, Line 2
> > > > > Error converting data type varchar to numeric.
> > > > >
> > > > > It has something to do with the concatenate function and teh non VARCHAR attribute but I don't konw how to fix this...
> > > > >
> > > > > Thank you in advance... SQL is not my strongsuit...
> > > > > Keith
> > > > >
> > > >
> > >
> >
>
Thank you so much! That worked and was a BIG help!! Keith
--- In MAXIMO@yahoogroups.com, "Victor" <vikas17.j@...> wrote:
>
> Nodify your query something like this :
>
> SELECT asset1.cb_rppassetdesc, asset1.cb_circuit1, asset1.cb_amps,
> asset1.cb_poles, asset1.cb_cabletype, asset1.cb_cbllngth, asset1.cb_secondary,
> asset2.manufacturer, asset2.description, asset1.cb_rackassetnum, asset1.bay,
> asset1.tile, longdescription.ldtext
> FROM asset asset1 left outer join asset asset2 on asset1.cb_loaddesc = asset2.assetnum
> LEFT OUTER JOIN longdescription ON asset1.assetid = longdescription.ldkey and
> longdescription.ldownertable = 'ASSET' and longdescription.ldownercol =
> 'DESCRIPTION'
> WHERE ((1 = 1) AND (asset1.cb_circuit1 Is Not Null))
> OR ((1 = 1) AND (asset1.cb_circuit2 Is Not Null))
> OR ((1 = 1) AND (asset1.cb_circuit3 Is Not Null))
> ORDER BY asset1.cb_circuit1
>
> Hope this helps.
>
> Thanks & regards,
> Victor.
>
> --- In MAXIMO@yahoogroups.com, "hankinskeith@" <keith.d.hankins@> wrote:
> >
> > Hi Victor -
> >
> > I tried that but get the following error:
> >
> > Msg 4104, Level 16, State 1, Line 1
> > The multi-part identifier "asset1.assetid" could not be bound.
> >
> >
> > Here is the SQL statement I used:
> >
> > SELECT asset1.cb_rppassetdesc, asset1.cb_circuit1, asset1.cb_amps,
> > asset1.cb_poles, asset1.cb_cabletype, asset1.cb_cbllngth, asset1.cb_secondary,
> > asset2.manufacturer, asset2.description, asset1.cb_rackassetnum, asset1.bay,
> > asset1.tile, longdescription.ldtext
> > FROM asset asset1, asset asset2
> > LEFT OUTER JOIN longdescription ON asset1.assetid = longdescription.ldkey and
> > longdescription.ldownertable = 'ASSET' and longdescription.ldownercol =
> > 'DESCRIPTION'
> > WHERE ((1 = 1) AND (asset1.cb_circuit1 Is Not Null) AND (asset1.cb_loaddesc =
> > asset2.assetnum))
> > OR ((1 = 1) AND (asset1.cb_circuit2 Is Not Null) AND (asset1.cb_loaddesc =
> > asset2.assetnum))
> > OR ((1 = 1) AND (asset1.cb_circuit3 Is Not Null) AND (asset1.cb_loaddesc =
> > asset2.assetnum))
> > ORDER BY asset1.cb_circuit1
> >
> >
> > --- In MAXIMO@yahoogroups.com, "Victor" <vikas17.j@> wrote:
> > >
> > >
> > > Hi Keith,
> > >
> > > You need to use the alias defined for asset1 and asset2 in the joins instead of Asset.
> > > Corrected :asset1.assetid = longdescription.ldkey
> > >
> > > SQL :
> > > SELECT asset1.cb_rppassetdesc, asset1.cb_circuit1, asset1.cb_amps, asset1.cb_poles, asset1.cb_cabletype, asset1.cb_cbllngth, asset1.cb_secondary, asset2.manufacturer, asset2.description, asset1.cb_rackassetnum, asset1.bay, asset1.tile, longdescription.ldtext
> > > > FROM asset asset1, asset asset2
> > > > LEFT OUTER JOIN longdescription ON asset1.assetid = longdescription.ldkey and
> > > > longdescription.ldownertable = 'ASSET' and longdescription.ldownercol = 'DESCRIPTION'
> > > > WHERE ((1 = 1) AND (asset1.cb_circuit1 Is Not Null) AND (asset1.cb_loaddesc = asset2.assetnum))
> > > > OR ((1 = 1) AND (asset1.cb_circuit2 Is Not Null) AND (asset1.cb_loaddesc = asset2.assetnum))
> > > > OR ((1 = 1) AND (asset1.cb_circuit3 Is Not Null) AND (asset1.cb_loaddesc = asset2.assetnum))
> > > > ORDER BY asset1.cb_circuit1
> > >
> > >
> > > It would be easier for us to give you the solution if you can share the error with us.
> > >
> > > Thanks & regards,
> > > Victor.
> > > --- In MAXIMO@yahoogroups.com, "hankinskeith@" <keith.d.hankins@> wrote:
> > > >
> > > > Thank you! Resolved... Any chance you could help me with one more...
> > > >
> > > > Getting this error:
> > > >
> > > > I understand why... trying to use outer join w/3 tables... but I don't know to get the data I need... I only need ldtext from table asset1... but I do need to join both instances of the asset table (asset1 and asset2)...
> > > >
> > > > Would greatly appreciate any help!
> > > >
> > > > SELECT asset1.cb_rppassetdesc, asset1.cb_circuit1, asset1.cb_amps, asset1.cb_poles, asset1.cb_cabletype, asset1.cb_cbllngth, asset1.cb_secondary, asset2.manufacturer, asset2.description, asset1.cb_rackassetnum, asset1.bay, asset1.tile, longdescription.ldtext
> > > > FROM asset asset1, asset asset2
> > > > LEFT OUTER JOIN longdescription ON asset.assetid = longdescription.ldkey and
> > > > longdescription.ldownertable = 'ASSET' and longdescription.ldownercol = 'DESCRIPTION'
> > > > WHERE ((1 = 1) AND (asset1.cb_circuit1 Is Not Null) AND (asset1.cb_loaddesc = asset2.assetnum))
> > > > OR ((1 = 1) AND (asset1.cb_circuit2 Is Not Null) AND (asset1.cb_loaddesc = asset2.assetnum))
> > > > OR ((1 = 1) AND (asset1.cb_circuit3 Is Not Null) AND (asset1.cb_loaddesc = asset2.assetnum))
> > > > ORDER BY asset1.cb_circuit1
> > > >
> > > > --- In MAXIMO@yahoogroups.com, "Victor" <vikas17.j@> wrote:
> > > > >
> > > > > Hi Keith,
> > > > >
> > > > > As the error indicates, its a datatype mismath
> > > > > . You need to use convert or cast with that.
> > > > >
> > > > > Thanks & regards,
> > > > > Victor.
> > > > >
> > > > > --- In MAXIMO@yahoogroups.com, "hankinskeith@" <keith.d.hankins@> wrote:
> > > > > >
> > > > > > Can anyone help me with my SQL... It worked well until I added the 3rd attribute cb_circuit1... Then it throws the following error...
> > > > > >
> > > > > > SELECT assetnum + ' - ' + cb_rpppanassetnum + ' - ' + cb_circuit1 FROM asset
> > > > > > WHERE
> > > > > > asset.cb_dfdassetnum='RFDEV-RMDFD-2';
> > > > > >
> > > > > > Error
> > > > > > Msg 8114, Level 16, State 5, Line 2
> > > > > > Error converting data type varchar to numeric.
> > > > > >
> > > > > > It has something to do with the concatenate function and teh non VARCHAR attribute but I don't konw how to fix this...
> > > > > >
> > > > > > Thank you in advance... SQL is not my strongsuit...
> > > > > > Keith
> > > > > >
> > > > >
> > > >
> > >
> >
>