Using a WHERE AND Clause syntax in an Openquery to AS400

Please bear with me as I'm still new to programming.
I have an openquery to a linked AS400 which is working fine using a where clause of where pslot >= @pslot, but I would like to capture where pslot is >=@pslot_begin and <=@pslot_end, can anyone help me with the correct syntax to do this ??

As you can see I've had a go myself but I either get an error message about tokens or currently about incorrect syntax near the keyword AND, an extract of my openquery as it stands at the moment is as follows;
set @result = 'SELECT  *, dbo.CalcQty(fuomm,qshst,uomcv,uombt)Sales_Quantity INTO tbl_sales_history_enquiry
FROM OPENQUERY(BUSINT, "SELECT ECSTNE Cust_Num, EDTNOE DAC_No, ESREP2 Sales_Rep,
                      PSLOT Time_Slot, XSHST Sales_Value, XSBUY Purchase_Value, GICAT1 Item_Catrgory, GIGRP1
                      Item_Group, DITMD Item_Desc,UOMBU1 Base_UOM, UOMTU Trans_UOM,
                      GICLS Commodity, GBRND1 Brand, DCNAM Cust_Name, DICAT Item_Cat,
                      DIGRP Item_Grp, DBRND Brand_Name, DSREP Sales_Rep2, GAREA2 Area_Code, GTGRP2
                      Trading_Grp, GTCAT2 Trading_Cat, DAREA Area_Desc, DTGRP Trading_Grp2, DTCAT Trading_Cat2,
                      (eitn14 || eitn24 || eitn34 || eitn44 || eitn54)item_code,fuomm,qshst,uomcv,uombt FROM SALESPER where PSLOT >='+ convert(varchar, @pslot_begin)+ ''AND 'PSLOT <='''+ convert(varchar,@pslot_end)+'")'


Many thanks
Angie
abaxtAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
I think there is an issue with quotes and double quotes...

set @result = 'SELECT  *, dbo.CalcQty(fuomm,qshst,uomcv,uombt)Sales_Quantity INTO tbl_sales_history_enquiry
FROM OPENQUERY(BUSINT, "SELECT ECSTNE Cust_Num, EDTNOE DAC_No, ESREP2 Sales_Rep,
                      PSLOT Time_Slot, XSHST Sales_Value, XSBUY Purchase_Value, GICAT1 Item_Catrgory, GIGRP1
                      Item_Group, DITMD Item_Desc,UOMBU1 Base_UOM, UOMTU Trans_UOM,
                      GICLS Commodity, GBRND1 Brand, DCNAM Cust_Name, DICAT Item_Cat,
                      DIGRP Item_Grp, DBRND Brand_Name, DSREP Sales_Rep2, GAREA2 Area_Code, GTGRP2
                      Trading_Grp, GTCAT2 Trading_Cat, DAREA Area_Desc, DTGRP Trading_Grp2, DTCAT Trading_Cat2,
                      (eitn14 || eitn24 || eitn34 || eitn44 || eitn54)item_code,fuomm,qshst,uomcv,uombt
        FROM SALESPER where PSLOT >= ''' + convert(varchar(20), @pslot_begin, 100 )
               + ''' AND PSLOT <='''+ convert(varchar,@pslot_end,100)+''' )'

select @result

You should also provide a third parameter to the convert function to ensure that the date value is formatted so the AS400 will be able to read it properly.
Please refer to the books online about the CONVERT and CAST function to choose the good one

ram2098Commented:
Try this....

Declare @Sqlstr varchar(8000)

select @sqlstr = 'SELECT ECSTNE Cust_Num, EDTNOE DAC_No, ESREP2 Sales_Rep,
                      PSLOT Time_Slot, XSHST Sales_Value, XSBUY Purchase_Value, GICAT1 Item_Catrgory, GIGRP1
                      Item_Group, DITMD Item_Desc,UOMBU1 Base_UOM, UOMTU Trans_UOM,
                      GICLS Commodity, GBRND1 Brand, DCNAM Cust_Name, DICAT Item_Cat,
                      DIGRP Item_Grp, DBRND Brand_Name, DSREP Sales_Rep2, GAREA2 Area_Code, GTGRP2
                      Trading_Grp, GTCAT2 Trading_Cat, DAREA Area_Desc, DTGRP Trading_Grp2, DTCAT Trading_Cat2,
                      (eitn14 || eitn24 || eitn34 || eitn44 || eitn54)item_code,fuomm,qshst,uomcv,uombt FROM SALESPER where PSLOT >=' + '''' + convert(varchar, @pslot_begin) + '''' + ' AND PSLOT <= ' + '''' + convert(varchar,@pslot_end) + ''''

set @result = 'SELECT  *, dbo.CalcQty(fuomm,qshst,uomcv,uombt)Sales_Quantity INTO tbl_sales_history_enquiry
FROM OPENQUERY(BUSINT, ' + '''' + @sqlstr + '''' + ')'

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
abaxtAuthor Commented:
Thanks, I tried the code you supplied but got an error message about incorrect syntax near 10605 (which is what the variable @pslot_begin is set to, the full stored procedure is listed below;

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

alter              proc stp_create_as400_sales_history_enquiry_table_new_new
as

declare @enquiry_period_begin as int
declare @enquiry_period_end as int
declare @pslot_begin as int
declare @pslot_end as int
declare @result varchar(1000)
declare @sqlstr varchar(8000)
exec stp_get_enquiry_period @enquiry_period_begin = @pslot_begin output, @enquiry_period_end = @pslot_end output

select @sqlstr = 'SELECT ECSTNE Cust_Num, EDTNOE DAC_No, ESREP2 Sales_Rep,
                      PSLOT Time_Slot, XSHST Sales_Value, XSBUY Purchase_Value, GICAT1 Item_Catrgory, GIGRP1
                      Item_Group, DITMD Item_Desc,UOMBU1 Base_UOM, UOMTU Trans_UOM,
                      GICLS Commodity, GBRND1 Brand, DCNAM Cust_Name, DICAT Item_Cat,
                      DIGRP Item_Grp, DBRND Brand_Name, DSREP Sales_Rep2, GAREA2 Area_Code, GTGRP2
                      Trading_Grp, GTCAT2 Trading_Cat, DAREA Area_Desc, DTGRP Trading_Grp2, DTCAT Trading_Cat2,
                      (eitn14 || eitn24 || eitn34 || eitn44 || eitn54)item_code,fuomm,qshst,uomcv,uombt FROM SALESPER where PSLOT >=' + '''' + convert(varchar, @pslot_begin) + '''' + ' AND PSLOT <= ' + '''' + convert(varchar,@pslot_end) + ''''

set @result = 'SELECT  *, dbo.CalcQty(fuomm,qshst,uomcv,uombt)Sales_Quantity INTO tbl_sales_history_enquiry
FROM OPENQUERY(BUSINT, ' + '''' + @sqlstr + '''' + ')'

EXECUTE (@result)

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Thanks for looking at this
Angie
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

Richard QuadlingSenior Software DeveloperCommented:
Hi angelIII,

Could you use ...

where PSLOT is between @pslot_begin AND @pslot_end

?
 

Regards,

Richard Quadling.
Guy Hengel [angelIII / a3]Billing EngineerCommented:
Yes, same as WHERE >= and <
abaxtAuthor Commented:
Yes, all I would like to do is capture where the column Pslot is equal to and between two variables that I pass in

Thanks
Angie
ram2098Commented:
Try this...

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

alter              proc stp_create_as400_sales_history_enquiry_table_new_new
as

declare @enquiry_period_begin as int
declare @enquiry_period_end as int
declare @pslot_begin as int
declare @pslot_end as int
declare @result varchar(1000)
declare @sqlstr varchar(8000)
exec stp_get_enquiry_period @enquiry_period_begin = @pslot_begin output, @enquiry_period_end = @pslot_end output

select @sqlstr = 'SELECT ECSTNE Cust_Num, EDTNOE DAC_No, ESREP2 Sales_Rep,
                      PSLOT Time_Slot, XSHST Sales_Value, XSBUY Purchase_Value, GICAT1 Item_Catrgory, GIGRP1
                      Item_Group, DITMD Item_Desc,UOMBU1 Base_UOM, UOMTU Trans_UOM,
                      GICLS Commodity, GBRND1 Brand, DCNAM Cust_Name, DICAT Item_Cat,
                      DIGRP Item_Grp, DBRND Brand_Name, DSREP Sales_Rep2, GAREA2 Area_Code, GTGRP2
                      Trading_Grp, GTCAT2 Trading_Cat, DAREA Area_Desc, DTGRP Trading_Grp2, DTCAT Trading_Cat2,
                      (eitn14 || eitn24 || eitn34 || eitn44 || eitn54)item_code,fuomm,qshst,uomcv,uombt FROM SALESPER where PSLOT >=' + '''''''' + convert(varchar, 1000) + '''''''' + ' AND PSLOT <= ' + '''''''' + convert(varchar,2000) + ''''''''

set @result = 'SELECT  *, dbo.CalcQty(fuomm,qshst,uomcv,uombt)Sales_Quantity INTO tbl_sales_history_enquiry
FROM OPENQUERY(BUSINT, ' + '''' + @sqlstr + ')' + ''''

EXECUTE (@result)

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
abaxtAuthor Commented:
I got the following error message....

Server: Msg 170, Level 15, State 1, Line 8
Line 8: Incorrect syntax near '1000'.
Stored Procedure: Planning_Sales_History.dbo.stp_create_as400_sales_history_enquiry_table_new_new
      Return Code = 0
ram2098Commented:
Can you use a print before the Execute and display the result here? This would really help?


SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

alter              proc stp_create_as400_sales_history_enquiry_table_new_new
as

declare @enquiry_period_begin as int
declare @enquiry_period_end as int
declare @pslot_begin as int
declare @pslot_end as int
declare @result varchar(1000)
declare @sqlstr varchar(8000)
exec stp_get_enquiry_period @enquiry_period_begin = @pslot_begin output, @enquiry_period_end = @pslot_end output

select @sqlstr = 'SELECT ECSTNE Cust_Num, EDTNOE DAC_No, ESREP2 Sales_Rep,
                      PSLOT Time_Slot, XSHST Sales_Value, XSBUY Purchase_Value, GICAT1 Item_Catrgory, GIGRP1
                      Item_Group, DITMD Item_Desc,UOMBU1 Base_UOM, UOMTU Trans_UOM,
                      GICLS Commodity, GBRND1 Brand, DCNAM Cust_Name, DICAT Item_Cat,
                      DIGRP Item_Grp, DBRND Brand_Name, DSREP Sales_Rep2, GAREA2 Area_Code, GTGRP2
                      Trading_Grp, GTCAT2 Trading_Cat, DAREA Area_Desc, DTGRP Trading_Grp2, DTCAT Trading_Cat2,
                      (eitn14 || eitn24 || eitn34 || eitn44 || eitn54)item_code,fuomm,qshst,uomcv,uombt FROM SALESPER where PSLOT >=' + '''''''' + convert(varchar, 1000) + '''''''' + ' AND PSLOT <= ' + '''''''' + convert(varchar,2000) + ''''''''

set @result = 'SELECT  *, dbo.CalcQty(fuomm,qshst,uomcv,uombt)Sales_Quantity INTO tbl_sales_history_enquiry
FROM OPENQUERY(BUSINT, ' + '''' + @sqlstr + ')' + ''''

EXECUTE (@result)
PRINT @result

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
ram2098Commented:
Sorry ..before exec...

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

alter              proc stp_create_as400_sales_history_enquiry_table_new_new
as

declare @enquiry_period_begin as int
declare @enquiry_period_end as int
declare @pslot_begin as int
declare @pslot_end as int
declare @result varchar(1000)
declare @sqlstr varchar(8000)
exec stp_get_enquiry_period @enquiry_period_begin = @pslot_begin output, @enquiry_period_end = @pslot_end output

select @sqlstr = 'SELECT ECSTNE Cust_Num, EDTNOE DAC_No, ESREP2 Sales_Rep,
                      PSLOT Time_Slot, XSHST Sales_Value, XSBUY Purchase_Value, GICAT1 Item_Catrgory, GIGRP1
                      Item_Group, DITMD Item_Desc,UOMBU1 Base_UOM, UOMTU Trans_UOM,
                      GICLS Commodity, GBRND1 Brand, DCNAM Cust_Name, DICAT Item_Cat,
                      DIGRP Item_Grp, DBRND Brand_Name, DSREP Sales_Rep2, GAREA2 Area_Code, GTGRP2
                      Trading_Grp, GTCAT2 Trading_Cat, DAREA Area_Desc, DTGRP Trading_Grp2, DTCAT Trading_Cat2,
                      (eitn14 || eitn24 || eitn34 || eitn44 || eitn54)item_code,fuomm,qshst,uomcv,uombt FROM SALESPER where PSLOT >=' + '''''''' + convert(varchar, 1000) + '''''''' + ' AND PSLOT <= ' + '''''''' + convert(varchar,2000) + ''''''''

set @result = 'SELECT  *, dbo.CalcQty(fuomm,qshst,uomcv,uombt)Sales_Quantity INTO tbl_sales_history_enquiry
FROM OPENQUERY(BUSINT, ' + '''' + @sqlstr + ')' + ''''

print @result
--EXECUTE (@result)

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Guy Hengel [angelIII / a3]Billing EngineerCommented:
convert(varchar, 1000) + '''''''' + ' AND PSLOT <= ' + '''''''' + convert(varchar,2000)

should be
convert(varchar, @pslot_begin, 100) + '''''''' + ' AND PSLOT <= ' + '''''''' + convert(varchar, @pslot_end,100)
abaxtAuthor Commented:
I put your code in ram2098 and then adjusted it like angelIII indicated and this is what was returned back by the print @result

SELECT  *, dbo.CalcQty(fuomm,qshst,uomcv,uombt)Sales_Quantity INTO tbl_sales_history_enquiry
FROM OPENQUERY(BUSINT, 'SELECT ECSTNE Cust_Num, EDTNOE DAC_No, ESREP2 Sales_Rep,
                      PSLOT Time_Slot, XSHST Sales_Value, XSBUY Purchase_Value, GICAT1 Item_Catrgory, GIGRP1
                      Item_Group, DITMD Item_Desc,UOMBU1 Base_UOM, UOMTU Trans_UOM,
                      GICLS Commodity, GBRND1 Brand, DCNAM Cust_Name, DICAT Item_Cat,
                      DIGRP Item_Grp, DBRND Brand_Name, DSREP Sales_Rep2, GAREA2 Area_Code, GTGRP2
                      Trading_Grp, GTCAT2 Trading_Cat, DAREA Area_Desc, DTGRP Trading_Grp2, DTCAT Trading_Cat2,
                      (eitn14 || eitn24 || eitn34 || eitn44 || eitn54)item_code,fuomm,qshst,uomcv,uombt FROM SALESPER where PSLOT >='''10605''' AND PSLOT <= '''10605''')'
Stored Procedure: Planning_Sales_History.dbo.stp_create_as400_sales_history_enquiry_table_new_new
      Return Code = 0

hope this helps
thanks to you both
Angie
ram2098Commented:
Yes.. the problem is with the paranthesis..try this..

AngleIII, thanks for the correction. I was trying with some values :)

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

alter              proc stp_create_as400_sales_history_enquiry_table_new_new
as

declare @enquiry_period_begin as int
declare @enquiry_period_end as int
declare @pslot_begin as int
declare @pslot_end as int
declare @result varchar(1000)
declare @sqlstr varchar(8000)
exec stp_get_enquiry_period @enquiry_period_begin = @pslot_begin output, @enquiry_period_end = @pslot_end output

select @sqlstr = 'SELECT ECSTNE Cust_Num, EDTNOE DAC_No, ESREP2 Sales_Rep,
                      PSLOT Time_Slot, XSHST Sales_Value, XSBUY Purchase_Value, GICAT1 Item_Catrgory, GIGRP1
                      Item_Group, DITMD Item_Desc,UOMBU1 Base_UOM, UOMTU Trans_UOM,
                      GICLS Commodity, GBRND1 Brand, DCNAM Cust_Name, DICAT Item_Cat,
                      DIGRP Item_Grp, DBRND Brand_Name, DSREP Sales_Rep2, GAREA2 Area_Code, GTGRP2
                      Trading_Grp, GTCAT2 Trading_Cat, DAREA Area_Desc, DTGRP Trading_Grp2, DTCAT Trading_Cat2,
                      (eitn14 || eitn24 || eitn34 || eitn44 || eitn54)item_code,fuomm,qshst,uomcv,uombt FROM SALESPER where PSLOT >=' + '''''''' + convert(varchar, @pslot_begin, 100) + '''''''' + ' AND PSLOT <= ' + '''''''' + convert(varchar, @pslot_end,100) + ''''''''

set @result = 'SELECT  *, dbo.CalcQty(fuomm,qshst,uomcv,uombt)Sales_Quantity INTO tbl_sales_history_enquiry
FROM OPENQUERY(BUSINT, ' + '''' + @sqlstr + '''' + ')'

EXECUTE (@result)

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
abaxtAuthor Commented:
I tried this and I still got the same error message....

Server: Msg 170, Level 15, State 1, Line 8
Line 8: Incorrect syntax near '10605'.
Stored Procedure: Planning_Sales_History.dbo.stp_create_as400_sales_history_enquiry_table_new_new
      Return Code = 0


ram2098Commented:
Try this..

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

alter              proc stp_create_as400_sales_history_enquiry_table_new_new
as

declare @enquiry_period_begin as int
declare @enquiry_period_end as int
declare @pslot_begin as int
declare @pslot_end as int
declare @result varchar(1000)
declare @sqlstr varchar(8000)
exec stp_get_enquiry_period @enquiry_period_begin = @pslot_begin output, @enquiry_period_end = @pslot_end output

select @sqlstr = 'SELECT ECSTNE Cust_Num, EDTNOE DAC_No, ESREP2 Sales_Rep,
                      PSLOT Time_Slot, XSHST Sales_Value, XSBUY Purchase_Value, GICAT1 Item_Catrgory, GIGRP1
                      Item_Group, DITMD Item_Desc,UOMBU1 Base_UOM, UOMTU Trans_UOM,
                      GICLS Commodity, GBRND1 Brand, DCNAM Cust_Name, DICAT Item_Cat,
                      DIGRP Item_Grp, DBRND Brand_Name, DSREP Sales_Rep2, GAREA2 Area_Code, GTGRP2
                      Trading_Grp, GTCAT2 Trading_Cat, DAREA Area_Desc, DTGRP Trading_Grp2, DTCAT Trading_Cat2,
                      (eitn14 || eitn24 || eitn34 || eitn44 || eitn54)item_code,fuomm,qshst,uomcv,uombt FROM SALESPER where PSLOT >=' + '''''' + convert(varchar, @pslot_begin, 100) + '''''' + ' AND PSLOT <= ' + '''''' + convert(varchar, @pslot_end,100) + ''''''

set @result = 'SELECT  *, dbo.CalcQty(fuomm,qshst,uomcv,uombt)Sales_Quantity INTO tbl_sales_history_enquiry
FROM OPENQUERY(BUSINT, ' + '''' + @sqlstr + '''' + ')'

EXECUTE (@result)

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
abaxtAuthor Commented:
This is the returned error message now...

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error.  
[OLE/DB provider returned message: [IBM][iSeries Access ODBC Driver][DB2 UDB]SQL0401 - Comparison operator >= operands not compatible.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' ICommandPrepare::Prepare returned 0x80004005:   ].

Thanks for sticking with this for me

Cheers
Angie
ram2098Commented:
I guess this is some syntax issue with AS400. I think AS400 does not support ">=".
Sorry..I am not good at AS400. Please check for any equivalent syntax.
abaxtAuthor Commented:
I think I have solved the problem with the following syntax;

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

ALTER  proc stp_create_as400_sales_history_enquiry_table
as

declare @enquiry_period_begin as int
declare @enquiry_period_end as int
declare @pslot_begin as int
declare @pslot_end as int
declare @result varchar(1000)
declare @sqlstr varchar(8000)
exec stp_get_enquiry_period @enquiry_period_begin = @pslot_begin output, @enquiry_period_end = @pslot_end output

select @sqlstr = 'SELECT ECSTNE Cust_Num, EDTNOE DAC_No, ESREP2 Sales_Rep,
                      PSLOT Time_Slot, XSHST Sales_Value, XSBUY Purchase_Value, GICAT1 Item_Catrgory, GIGRP1
                      Item_Group, DITMD Item_Desc,UOMBU1 Base_UOM, UOMTU Trans_UOM,
                      GICLS Commodity, GBRND1 Brand, DCNAM Cust_Name, DICAT Item_Cat,
                      DIGRP Item_Grp, DBRND Brand_Name, DSREP Sales_Rep2, GAREA2 Area_Code, GTGRP2
                      Trading_Grp, GTCAT2 Trading_Cat, DAREA Area_Desc, DTGRP Trading_Grp2, DTCAT Trading_Cat2,
                      (eitn14 || eitn24 || eitn34 || eitn44 || eitn54)item_code,fuomm,qshst,uomcv,uombt FROM SALESPER where PSLOT >=' + cast(@pslot_begin as varchar) +'' + ' AND PSLOT <='+ '' + cast(@pslot_end as varchar) + ''
set @result = 'SELECT  *, dbo.CalcQty(fuomm,qshst,uomcv,uombt)Sales_Quantity INTO tbl_sales_history_enquiry
FROM OPENQUERY(BUSINT, ' + '''' + @sqlstr + '''' + ')'

EXECUTE (@result)
PRINT @RESULT


GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Will send you the points for your assistance above.
Thanks
Angie
ram2098Commented:
Good to see the issue is resolved.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.