DEV Community

Query Filter
Query Filter

Posted on

cursor

set nocount on
set quoted_identifier off

DECLARE
@INTERNIDENTITY varchar(50),
@INTERNINSTID varchar(50),
@INTERNVERSION varchar(50),
@INTERNTIMESTAMP varchar(50),
@INTERNRECTYPE varchar(50),
@ORDERID varchar(50),
@SYNCNUM varchar(50),
@IDSOURCE varchar(50),
@MEMOID varchar(50),
@MEMOTYPE varchar(50),
@BIDPX varchar(50),
@BIDSIZE varchar(50),
@LASTSALE varchar(50),
@MARKETCENTERID varchar(50),
@MEMOCOMMENTS varchar(50),
@OFFERPX varchar(50),
@OFFERSIZE varchar(50),
@ORDERIDS varchar(50),
@ORDSYNCNUM varchar(50),
@OVERRIDERULESET varchar(16384),
@QUOTECONDITIONINDICATOR varchar(50),
@QUOTETIME varchar(50),
@RULE92COMMENT varchar(50),
@SALESTRADERCOMMENTS varchar(50),
@TICKDIRECTION varchar(50),
@TOTALDOLVOL varchar(50),
@TOTALVOLUMETRADED varchar(50),
@TRADERCOMMENTS varchar(50),
@LULDLOWERPXBAND varchar(50),
@LULDUPPERPXBAND varchar(50),
@LULDPXBANDTIMESTAMP varchar(50),
@output varchar(32767),
@pos int

DECLARE cur CURSOR FOR
SELECT INTERNIDENTITY, INTERNINSTID, INTERNVERSION, INTERNTIMESTAMP, INTERNRECTYPE,
ORDERID, SYNCNUM, IDSOURCE, MEMOID, MEMOTYPE, BIDPX, BIDSIZE, LASTSALE, MARKETCENTERID,
MEMOCOMMENTS, OFFERPX, OFFERSIZE, ORDERIDS, ORDSYNCNUM, OVERRIDERULESET,
QUOTECONDITIONINDICATOR, QUOTETIME, RULE92COMMENT, SALESTRADERCOMMENTS, TICKDIRECTION,
TOTALDOLVOL, TOTALVOLUMETRADED, TRADERCOMMENTS, LULDLOWERPXBAND, LULDUPPERPXBAND, LULDPXBANDTIMESTAMP
FROM ZMemo

OPEN cur
FETCH cur INTO
@INTERNIDENTITY, @INTERNINSTID, @INTERNVERSION, @INTERNTIMESTAMP, @INTERNRECTYPE,
@ORDERID, @SYNCNUM, @IDSOURCE, @MEMOID, @MEMOTYPE, @BIDPX, @BIDSIZE, @LASTSALE, @MARKETCENTERID,
@MEMOCOMMENTS, @OFFERPX, @OFFERSIZE, @ORDERIDS, @ORDSYNCNUM, @OVERRIDERULESET,
@QUOTECONDITIONINDICATOR, @QUOTETIME, @RULE92COMMENT, @SALESTRADERCOMMENTS, @TICKDIRECTION,
@TOTALDOLVOL, @TOTALVOLUMETRADED, @TRADERCOMMENTS, @LULDLOWERPXBAND, @LULDUPPERPXBAND, @LULDPXBANDTIMESTAMP

WHILE @@sqlstatus = 0
BEGIN
-- Helper macro for cleaning leading byte 0xC2 and trimming
DECLARE @fields TABLE(field_name varchar(50), field_value varchar(16384))
INSERT @fields VALUES
('INTERNIDENTITY', @INTERNIDENTITY), ('INTERNINSTID', @INTERNINSTID), ('INTERNVERSION', @INTERNVERSION),
('INTERNTIMESTAMP', @INTERNTIMESTAMP), ('INTERNRECTYPE', @INTERNRECTYPE), ('ORDERID', @ORDERID),
('SYNCNUM', @SYNCNUM), ('IDSOURCE', @IDSOURCE), ('MEMOID', @MEMOID), ('MEMOTYPE', @MEMOTYPE),
('BIDPX', @BIDPX), ('BIDSIZE', @BIDSIZE), ('LASTSALE', @LASTSALE), ('MARKETCENTERID', @MARKETCENTERID),
('MEMOCOMMENTS', @MEMOCOMMENTS), ('OFFERPX', @OFFERPX), ('OFFERSIZE', @OFFERSIZE), ('ORDERIDS', @ORDERIDS),
('ORDSYNCNUM', @ORDSYNCNUM), ('QUOTECONDITIONINDICATOR', @QUOTECONDITIONINDICATOR),
('QUOTETIME', @QUOTETIME), ('RULE92COMMENT', @RULE92COMMENT), ('SALESTRADERCOMMENTS', @SALESTRADERCOMMENTS),
('TICKDIRECTION', @TICKDIRECTION), ('TOTALDOLVOL', @TOTALDOLVOL), ('TOTALVOLUMETRADED', @TOTALVOLUMETRADED),
('TRADERCOMMENTS', @TRADERCOMMENTS), ('LULDLOWERPXBAND', @LULDLOWERPXBAND), ('LULDUPPERPXBAND', @LULDUPPERPXBAND),
('LULDPXBANDTIMESTAMP', @LULDPXBANDTIMESTAMP)

-- Clean simple fields inline
SELECT @INTERNIDENTITY = case when ascii(left(@INTERNIDENTITY,1))=194 then substring(@INTERNIDENTITY,2,49) else @INTERNIDENTITY end
SET @INTERNIDENTITY = ltrim(rtrim(@INTERNIDENTITY))
SET @INTERNINSTID = ltrim(rtrim(case when ascii(left(@INTERNINSTID,1))=194 then substring(@INTERNINSTID,2,49) else @INTERNINSTID end))
SET @INTERNVERSION = ltrim(rtrim(case when ascii(left(@INTERNVERSION,1))=194 then substring(@INTERNVERSION,2,49) else @INTERNVERSION end))
SET @INTERNTIMESTAMP = ltrim(rtrim(case when ascii(left(@INTERNTIMESTAMP,1))=194 then substring(@INTERNTIMESTAMP,2,49) else @INTERNTIMESTAMP end))
SET @INTERNRECTYPE = ltrim(rtrim(case when ascii(left(@INTERNRECTYPE,1))=194 then substring(@INTERNRECTYPE,2,49) else @INTERNRECTYPE end))
SET @ORDERID = ltrim(rtrim(case when ascii(left(@ORDERID,1))=194 then substring(@ORDERID,2,49) else @ORDERID end))
SET @SYNCNUM = ltrim(rtrim(case when ascii(left(@SYNCNUM,1))=194 then substring(@SYNCNUM,2,49) else @SYNCNUM end))
SET @IDSOURCE = ltrim(rtrim(case when ascii(left(@IDSOURCE,1))=194 then substring(@IDSOURCE,2,49) else @IDSOURCE end))
SET @MEMOID = ltrim(rtrim(case when ascii(left(@MEMOID,1))=194 then substring(@MEMOID,2,49) else @MEMOID end))
SET @MEMOTYPE = ltrim(rtrim(case when ascii(left(@MEMOTYPE,1))=194 then substring(@MEMOTYPE,2,49) else @MEMOTYPE end))
SET @BIDPX = ltrim(rtrim(case when ascii(left(@BIDPX,1))=194 then substring(@BIDPX,2,49) else @BIDPX end))
SET @BIDSIZE = ltrim(rtrim(case when ascii(left(@BIDSIZE,1))=194 then substring(@BIDSIZE,2,49) else @BIDSIZE end))
SET @LASTSALE = ltrim(rtrim(case when ascii(left(@LASTSALE,1))=194 then substring(@LASTSALE,2,49) else @LASTSALE end))
SET @MARKETCENTERID = ltrim(rtrim(case when ascii(left(@MARKETCENTERID,1))=194 then substring(@MARKETCENTERID,2,49) else @MARKETCENTERID end))
SET @MEMOCOMMENTS = ltrim(rtrim(case when ascii(left(@MEMOCOMMENTS,1))=194 then substring(@MEMOCOMMENTS,2,49) else @MEMOCOMMENTS end))
SET @OFFERPX = ltrim(rtrim(case when ascii(left(@OFFERPX,1))=194 then substring(@OFFERPX,2,49) else @OFFERPX end))
SET @OFFERSIZE = ltrim(rtrim(case when ascii(left(@OFFERSIZE,1))=194 then substring(@OFFERSIZE,2,49) else @OFFERSIZE end))
SET @ORDERIDS = ltrim(rtrim(case when ascii(left(@ORDERIDS,1))=194 then substring(@ORDERIDS,2,49) else @ORDERIDS end))
SET @ORDSYNCNUM = ltrim(rtrim(case when ascii(left(@ORDSYNCNUM,1))=194 then substring(@ORDSYNCNUM,2,49) else @ORDSYNCNUM end))
SET @QUOTECONDITIONINDICATOR = ltrim(rtrim(case when ascii(left(@QUOTECONDITIONINDICATOR,1))=194 then substring(@QUOTECONDITIONINDICATOR,2,49) else @QUOTECONDITIONINDICATOR end))
SET @QUOTETIME = ltrim(rtrim(case when ascii(left(@QUOTETIME,1))=194 then substring(@QUOTETIME,2,49) else @QUOTETIME end))
SET @RULE92COMMENT = ltrim(rtrim(case when ascii(left(@RULE92COMMENT,1))=194 then substring(@RULE92COMMENT,2,49) else @RULE92COMMENT end))
SET @SALESTRADERCOMMENTS = ltrim(rtrim(case when ascii(left(@SALESTRADERCOMMENTS,1))=194 then substring(@SALESTRADERCOMMENTS,2,49) else @SALESTRADERCOMMENTS end))
SET @TICKDIRECTION = ltrim(rtrim(case when ascii(left(@TICKDIRECTION,1))=194 then substring(@TICKDIRECTION,2,49) else @TICKDIRECTION end))
SET @TOTALDOLVOL = ltrim(rtrim(case when ascii(left(@TOTALDOLVOL,1))=194 then substring(@TOTALDOLVOL,2,49) else @TOTALDOLVOL end))
SET @TOTALVOLUMETRADED = ltrim(rtrim(case when ascii(left(@TOTALVOLUMETRADED,1))=194 then substring(@TOTALVOLUMETRADED,2,49) else @TOTALVOLUMETRADED end))
SET @TRADERCOMMENTS = ltrim(rtrim(case when ascii(left(@TRADERCOMMENTS,1))=194 then substring(@TRADERCOMMENTS,2,49) else @TRADERCOMMENTS end))
SET @LULDLOWERPXBAND = ltrim(rtrim(case when ascii(left(@LULDLOWERPXBAND,1))=194 then substring(@LULDLOWERPXBAND,2,49) else @LULDLOWERPXBAND end))
SET @LULDUPPERPXBAND = ltrim(rtrim(case when ascii(left(@LULDUPPERPXBAND,1))=194 then substring(@LULDUPPERPXBAND,2,49) else @LULDUPPERPXBAND end))
SET @LULDPXBANDTIMESTAMP = ltrim(rtrim(case when ascii(left(@LULDPXBANDTIMESTAMP,1))=194 then substring(@LULDPXBANDTIMESTAMP,2,49) else @LULDPXBANDTIMESTAMP end))

-- OVERRIDERULESET: leading-byte safe + trim
IF ascii(left(@OVERRIDERULESET,1))=194
    SET @OVERRIDERULESET = substring(@OVERRIDERULESET,2,16383)
SET @OVERRIDERULESET = ltrim(rtrim(@OVERRIDERULESET))

-- Replace CR/LF with <<EOLN>>
SET @pos = charindex(char(13), @OVERRIDERULESET)
WHILE @pos > 0
BEGIN
    SET @OVERRIDERULESET = stuff(@OVERRIDERULESET, @pos, 1, '<<EOLN>>')
    SET @pos = charindex(char(13), @OVERRIDERULESET)
END

SET @pos = charindex(char(10), @OVERRIDERULESET)
WHILE @pos > 0
BEGIN
    SET @OVERRIDERULESET = stuff(@OVERRIDERULESET, @pos, 1, '<<EOLN>>')
    SET @pos = charindex(char(10), @OVERRIDERULESET)
END

-- Concatenate all fields
SET @output =
    @INTERNIDENTITY + '¬' + @INTERNINSTID + '¬' + @INTERNVERSION + '¬' + @INTERNTIMESTAMP + '¬' +
    @INTERNRECTYPE + '¬' + @ORDERID + '¬' + @SYNCNUM + '¬' + @IDSOURCE + '¬' + @MEMOID + '¬' +
    @MEMOTYPE + '¬' + @BIDPX + '¬' + @BIDSIZE + '¬' + @LASTSALE + '¬' + @MARKETCENTERID + '¬' +
    @MEMOCOMMENTS + '¬' + @OFFERPX + '¬' + @OFFERSIZE + '¬' + @ORDERIDS + '¬' + @ORDSYNCNUM + '¬' +
    @OVERRIDERULESET + '¬' + @QUOTECONDITIONINDICATOR + '¬' + @QUOTETIME + '¬' + @RULE92COMMENT + '¬' +
    @SALESTRADERCOMMENTS + '¬' + @TICKDIRECTION + '¬' + @TOTALDOLVOL + '¬' + @TOTALVOLUMETRADED + '¬' +
    @TRADERCOMMENTS + '¬' + @LULDLOWERPXBAND + '¬' + @LULDUPPERPXBAND + '¬' + @LULDPXBANDTIMESTAMP

-- Output the cleaned concatenated string
SELECT @output

FETCH cur INTO 
    @INTERNIDENTITY, @INTERNINSTID, @INTERNVERSION, @INTERNTIMESTAMP, @INTERNRECTYPE,
    @ORDERID, @SYNCNUM, @IDSOURCE, @MEMOID, @MEMOTYPE, @BIDPX, @BIDSIZE, @LASTSALE, @MARKETCENTERID,
    @MEMOCOMMENTS, @OFFERPX, @OFFERSIZE, @ORDERIDS, @ORDSYNCNUM, @OVERRIDERULESET,
    @QUOTECONDITIONINDICATOR, @QUOTETIME, @RULE92COMMENT, @SALESTRADERCOMMENTS, @TICKDIRECTION,
    @TOTALDOLVOL, @TOTALVOLUMETRADED, @TRADERCOMMENTS, @LULDLOWERPXBAND, @LULDUPPERPXBAND, @LULDPXBANDTIMESTAMP
Enter fullscreen mode Exit fullscreen mode

END

CLOSE cur
DEALLOCATE cur
go

Top comments (0)