SQL Syntax for Openquery where Clause

I have a stored procedure which sends an openquery to your as400 system but can't seem to get the correct sytax for the where clause; can anyone 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 between >=' + '''''' + convert(varchar,@pslot_begin, 100) + '''''' + ' + 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


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.

Kent OlsenDBACommented:

Hi Angie,

It's me again.  :)

It looks like the "BETWEEN" clause is improperly formatted, with the WHERE clause becoming something like:

where PSLOT between >= '100' + PSLOT <= '200'

Assuming that PSLOT is an integer, what you want/need is more like:

where PSLOT between 100 and 200


Kent


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:
Hi Kent
Thanks for your guidance, think I have the result after much moving '' about !!!

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

dont really understand why but I think the CAST as opposed to using CONVERT makes a difference when using two variables passed in the SQL string.
Will give you the points for your help.
Regards
Angie


Kent OlsenDBACommented:

Hi Angie,

I think that the SQL can still use a bit of cleaning up.  :)

...'where PSLOT >=' + cast(@pslot_begin as varchar) +'' + ' AND PSLOT <='+ '' + cast(@pslot_end as varchar) + ''


The  +'' seems to be just fluff that can be safely removed, leaving:

...'where PSLOT >=' + cast(@pslot_begin as varchar) + ' AND PSLOT <=' + cast(@pslot_end as varchar)


Which can be further reduced to DB2 SQL as:

...'where PSLOT between ' + cast(@pslot_begin as varchar) + ' AND ' + cast(@pslot_end as varchar)


Kent
abaxtAuthor Commented:
Thanks Kent :-) have put your changes through as suggested
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
DB2

From novice to tech pro — start learning today.