Home > Development, SQL > SQL Query Syteline 9

SQL Query Syteline 9

February 17th, 2015 Leave a comment Go to comments

In Syteline 9, there is one significant database schema change.  All major tables like item are replaced with a view and _mst table.  There are some context variables need to be set, before you can run query against to the views.  Otherwise query to the view will return no data.  And here is the variable declaration, with sample query after that.

DECLARE @Br NVARCHAR(8)

SET @Br = ‘SiteName’’

DECLARE @Context NVARCHAR(100)

SET @Context = @Br

DECLARE @BinVar VARBINARY(128)

SET @BinVar = CONVERT (VARBINARY(128), @Context)

set CONTEXT_INFO @BinVar

 

 

select co.slsman,co.co_num,co.type, co.order_date, coitem.co_line, coitem.item, coitem.qty_ordered_conv,coitem.price_conv,co.exch_rate

, ca.name, coitem.qty_ordered_conv * coitem.price_conv ‘Amount’, coitem.co_release

from coitem

join co on co.co_num = coitem.co_num

left join custaddr ca on ca.cust_num = co.cust_num and ca.cust_seq = co.cust_seq

where isnull(co.slsman,”) = ”

Update 2/10/2015

You may now just call this Sp.

DECLARE @Infobar InfobarType;
EXEC [dbo].[SetSiteSp] ‘CCSSL’, @Infobar OUTPUT

Categories: Development, SQL Tags:
  1. No comments yet.
  1. No trackbacks yet.
You must be logged in to post a comment.