Sql Query

Hi all i have the following query
SELECT     dbo.itran.it_dtedelv, dbo.itran.it_stock, dbo.itran.it_quan, 'Comming Soon' AS USDollarValue, 'Link Microtek Ltd' AS Purchaser, dbo.ihead.ih_delad1 AS Recipient, 
                      dbo.ihead.ih_delad3, SUBSTRING(dbo.itran.it_memo, CHARINDEX('Foreign End User: ', dbo.itran.it_memo) + 19, CHARINDEX(',', dbo.itran.it_memo) 
                      - CHARINDEX('Foreign End User: ', dbo.itran.it_memo) - 19) AS EndUser, SUBSTRING(dbo.itran.it_memo, CHARINDEX(',', dbo.itran.it_memo) + 2, 
                      CHARINDEX('Manufacturer of', dbo.itran.it_memo) - CHARINDEX(',', dbo.itran.it_memo) - 3) AS Address, SUBSTRING(dbo.itran.it_memo, CHARINDEX('No.', 
                      dbo.itran.it_memo) + 4, 9) AS LicenseNumber
FROM         dbo.ihead LEFT OUTER JOIN
                      dbo.itran ON dbo.ihead.ih_doc = dbo.itran.it_doc
WHERE     (dbo.itran.it_memo LIKE '%Foreign End User%') AND (dbo.ihead.ih_sorder = 'ORD15686') AND (dbo.itran.it_status = 'a') AND (dbo.itran.it_recno = '3')

Open in new window


i would now like to change my where clause to only say
WHERE     (dbo.itran.it_memo LIKE '%Foreign End User%')

however when i do this i get the following error
Error Code
can anybody help

john
pepps11976Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
LowfatspreadConnect With a Mentor Commented:
or (more likely) there is a comma before the foreign user phrase and you code isn't allowing for that...

CHARINDEX(',', T.it_memo)

needs to be CHARINDEX(',', T.it_memo,t.pos+1)   ? to search for the next comma agter the foreign user...
0
 
Lee SavidgeCommented:
I assume this is a varchar(max) field or an text/ntext field. You'll need to cast the whereclause.

Lee
SELECT     dbo.itran.it_dtedelv, dbo.itran.it_stock, dbo.itran.it_quan, 'Comming Soon' AS USDollarValue, 'Link Microtek Ltd' AS Purchaser, dbo.ihead.ih_delad1 AS Recipient, 
                      dbo.ihead.ih_delad3, SUBSTRING(dbo.itran.it_memo, CHARINDEX('Foreign End User: ', dbo.itran.it_memo) + 19, CHARINDEX(',', dbo.itran.it_memo) 
                      - CHARINDEX('Foreign End User: ', dbo.itran.it_memo) - 19) AS EndUser, SUBSTRING(dbo.itran.it_memo, CHARINDEX(',', dbo.itran.it_memo) + 2, 
                      CHARINDEX('Manufacturer of', dbo.itran.it_memo) - CHARINDEX(',', dbo.itran.it_memo) - 3) AS Address, SUBSTRING(dbo.itran.it_memo, CHARINDEX('No.', 
                      dbo.itran.it_memo) + 4, 9) AS LicenseNumber
FROM         dbo.ihead LEFT OUTER JOIN
                      dbo.itran ON dbo.ihead.ih_doc = dbo.itran.it_doc
WHERE     (cast(dbo.itran.it_memo as nvarchar(1000)) LIKE '%Foreign End User%') AND (dbo.ihead.ih_sorder = 'ORD15686') AND (dbo.itran.it_status = 'a') AND (dbo.itran.it_recno = '3')

Open in new window

0
 
Lee SavidgeCommented:
the nvarchar(1000) is an abritrary size I picked. If it isn't large enough to contain the text in that field, increase it but your maximum is 4000.
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
pepps11976Author Commented:
Hi Isavidge this is now my new code which does not error
SELECT     dbo.itran.it_dtedelv, dbo.itran.it_stock, dbo.itran.it_quan, 'Comming Soon' AS USDollarValue, 'Link Microtek Ltd' AS Purchaser, dbo.ihead.ih_delad1 AS Recipient, 
                      dbo.ihead.ih_delad3, SUBSTRING(dbo.itran.it_memo, CHARINDEX('Foreign End User: ', dbo.itran.it_memo) + 19, CHARINDEX(',', dbo.itran.it_memo) 
                      - CHARINDEX('Foreign End User: ', dbo.itran.it_memo) - 19) AS EndUser, SUBSTRING(dbo.itran.it_memo, CHARINDEX(',', dbo.itran.it_memo) + 2, 
                      CHARINDEX('Manufacturer of', dbo.itran.it_memo) - CHARINDEX(',', dbo.itran.it_memo) - 3) AS Address, SUBSTRING(dbo.itran.it_memo, CHARINDEX('No.', 
                      dbo.itran.it_memo) + 4, 9) AS LicenseNumber
FROM         dbo.ihead LEFT OUTER JOIN
                      dbo.itran ON dbo.ihead.ih_doc = dbo.itran.it_doc
WHERE     (CAST(dbo.itran.it_memo AS nvarchar(4000)) LIKE '%Foreign End User%')

Open in new window


however nothing is returned now
0
 
Lee SavidgeCommented:
Are tehere any records that match that query?
0
 
LowfatspreadCommented:
isnt it because your where clause is '%Foreign End User%'
but the substring/charindex is 'Foreign End User: '

so you aren't guaranteed to have the same string...?

what are you actually trying to do?
0
 
pepps11976Author Commented:
I am trying to buils a report but collecting text out of a memo feild.

I have chnanged the where clause like you just said but before it returns results it flashes this error again once i click ok it returns some results  Error
john
0
 
LowfatspreadCommented:
try this


SELECT     T.it_dtedelv, T.it_stock
, T.it_quan, 'Comming Soon' AS USDollarValue
, 'Link Microtek Ltd' AS Purchaser, H.ih_delad1 AS Recipient, 
                      H.ih_delad3
, SUBSTRING(T.it_memo+' ', t.pos + 19
   , case when CHARINDEX(',', T.it_memo) > t.pos+19 
          then charindex(',', T.it_memo) 
          else len(t.it_memo) +1 end
          - t.pos - 19
    ) AS EndUser
, SUBSTRING(T.it_memo, CHARINDEX(',', T.it_memo) + 2, 
                      CHARINDEX('Manufacturer of', T.it_memo) - CHARINDEX(',', T.it_memo) - 3) AS Address, SUBSTRING(T.it_memo, CHARINDEX('No.', 
                      T.it_memo) + 4, 9) AS LicenseNumber
FROM         dbo.ihead as H
LEFT OUTER JOIN   (select x.*
                        ,charindex('Foreign End User: ',it_memo) as pos
                    from dbo.itran as x
                   WHERE it_memo LIKE '%Foreign End User%' 
                    AND it_status = 'a' 
                    AND it_recno = '3'
                  ) as T
ON H.ih_doc = T.it_doc

Where (H.ih_sorder = 'ORD15686')

Open in new window

0
 
LowfatspreadCommented:
you get the error surely becasuse there isnt a comma after the foreign user phrase in some instances?
0
 
LowfatspreadCommented:
you've got a similar problem with the address field....

why are you using this silly format?

why not have separate columns on the table?
0
 
pepps11976Author Commented:
Ok this is now the code i am using from you previous post
SELECT     T.it_dtedelv, T.it_stock, T.it_quan, 'Comming Soon' AS USDollarValue, 'Link Microtek Ltd' AS Purchaser, H.ih_delad1 AS Recipient, H.ih_delad3, 
                      SUBSTRING(T.it_memo + ' ', T.pos + 19, CASE WHEN CHARINDEX(',', T .it_memo) > t .pos + 19 THEN charindex(',', T .it_memo) ELSE len(t .it_memo) 
                      + 1 END - T.pos - 19) AS EndUser, SUBSTRING(T.it_memo, CHARINDEX(',', T.it_memo) + 2, CHARINDEX('Manufacturer of', T.it_memo) - CHARINDEX(',', T.it_memo) - 3) 
                      AS Address, SUBSTRING(T.it_memo, CHARINDEX('No.', T.it_memo) + 4, 9) AS LicenseNumber
FROM         dbo.ihead AS H LEFT OUTER JOIN
                          (SELECT     it_doc, it_stock, it_desc, it_anal, it_quan, it_price, it_disc, it_vat, it_lineval, it_sdisc, it_odisc, it_status, it_date, it_recno, it_exvat, it_discval, it_sett1, 
                                                   it_sett2, it_overall, it_vatval, it_vatpct, it_memo, it_cost, it_priorty, it_due, it_qtyallc, it_dteallc, it_qtypick, it_dtepick, it_qtydelv, it_dtedelv, it_numdelv, 
                                                   it_qtyinv, it_dteinv, it_numinv, it_exdate, it_exref, it_fcurr, it_fcrate, it_fcdec, it_mark, it_delt, it_ntrn, it_mtrn, it_comcode, it_supunit, it_cntorig, it_ntmass, 
                                                   it_vattyp, it_fcmult, it_jcstdoc, it_jphase, it_jccode, it_jline, it_btchser, it_fromjc, it_qtyover, it_qtyorig, it_massut, it_cwcode, it_bscost, it_worder, 
                                                   it_woref, it_porder, it_poref, it_poline, it_uorder, it_uline, it_pacc, it_pprod, it_pprice, it_pfcurr, it_pfcrate, it_pdisc, it_pqty, it_ponow, it_pochg, it_wochg, 
                                                   it_lineno, sq_amtime, sq_amdate, sq_amuser, it_fundec, it_pfundec, it_delad, it_delad1, it_delad2, it_delad3, it_delad4, it_deladpc, it_narr1, it_narr2, 
                                                   it_pjcode, it_pjline, it_wonow, it_delware, id, it_jlineid, it_plineid, it_jwiprid, it_project, it_job, it_uqalloc, it_multsu, it_rcvat, it_activid, 
                                                   CHARINDEX('Foreign End User: ', it_memo) AS pos
                            FROM          dbo.itran AS x
                            WHERE      (it_memo LIKE '%Foreign End User%') AND (it_status = 'a') AND (it_recno = '3')) AS T ON H.ih_doc = T.it_doc
WHERE     (H.ih_sorder = 'ORD15686')

Open in new window


getting this error Error
sorry if i am doing this wrong i am still a newbie :)
0
 
pepps11976Author Commented:
Just to clarifi this is the query i am trying to create
SELECT     dbo.itran.it_dtedelv, dbo.itran.it_stock, dbo.itran.it_quan, 'Comming Soon' AS USDollarValue, 'Link Microtek Ltd' AS Purchaser, dbo.ihead.ih_delad1 AS Recipient, 
                      dbo.ihead.ih_delad3, SUBSTRING(dbo.itran.it_memo, CHARINDEX('Foreign End User: ', dbo.itran.it_memo) + 19, CHARINDEX(',', dbo.itran.it_memo) 
                      - CHARINDEX('Foreign End User: ', dbo.itran.it_memo) - 19) AS EndUser, SUBSTRING(dbo.itran.it_memo, CHARINDEX(',', dbo.itran.it_memo) + 2, 
                      CHARINDEX('Manufacturer of', dbo.itran.it_memo) - CHARINDEX(',', dbo.itran.it_memo) - 3) AS Address, SUBSTRING(dbo.itran.it_memo, CHARINDEX('No.', 
                      dbo.itran.it_memo) + 4, 9) AS LicenseNumber
FROM         dbo.ihead LEFT OUTER JOIN
                      dbo.itran ON dbo.ihead.ih_doc = dbo.itran.it_doc
WHERE     (CAST(dbo.itran.it_memo AS nvarchar(1000)) LIKE '%Foreign End User: %') AND (dbo.itran.it_status = 'a')

Open in new window


john
0
 
LowfatspreadCommented:
try

SELECT     T.it_dtedelv, T.it_stock, T.it_quan, 'Comming Soon' AS USDollarValue, 'Link Microtek Ltd' AS Purchaser, H.ih_delad1 AS Recipient, H.ih_delad3,
           SUBSTRING(T.it_memo , T.Fpos + 19, CASE WHEN CHARINDEX(',', T.it_memo,t.pos) > 0 THEN CHARINDEX(',', T.it_memo,t.pos) ELSE len(t .it_memo)
                      + 1 END - T.pos - 18) AS EndUser
          , SUBSTRING(T.it_memo, CHARINDEX(',', T.it_memo) + 2, t.mpos - CHARINDEX(',', T.it_memo) - 3)
                      AS Address
          , SUBSTRING(T.it_memo, CHARINDEX('No.', T.it_memo) + 4, 9) AS LicenseNumber
FROM         dbo.ihead AS H
LEFT OUTER JOIN
                          (SELECT     it_dtedelv, it_stock, it_quan ,it_doc
                                      ,convert(nvarchar(4000),it_memo) +', ' as it_memo  
                                     , CHARINDEX('Foreign End User: ', it_memo) AS Fpos
                                     ,CHARINDEX('Manufacturer of', it_memo) as Mpos
                            FROM          dbo.itran AS x
                            WHERE (it_memo LIKE '%Foreign End User:%')
                              and (it_memo LIKE '%Manufacturer of%')      
                              AND (it_status = 'a')
                              AND (it_recno = '3')
       ) AS T
  ON H.ih_doc = T.it_doc
WHERE     (H.ih_sorder = 'ORD15686')
0
 
pepps11976Author Commented:
Hi Low Fat Spread i get unable to parse query text with the code
0
 
pepps11976Author Commented:
Ok this is what the memo feild would look like
With reference to maales Spec: 102-004440 - issue 4
Notes of Spec
Export Licence No. 0123456789

Line Item 4
maales Part no. 206-058943-t4338-0026 002
Part Revision No: 002

Foreign End User: The State of the Netherlands, ODC "Het Arsenaal", Rijkszee-en Marinehaven, 1781 ZZ Den Helder, The Netherlands
Manufacturer of Commodity: hicrosemin Lovell

Open in new window


and from that i need 3 peices of data all in seperate columns these are

Export Licence No.
Foreign end User: but only up to the comma, so in this case "The State of the Netherlands"
And any thing after the comma so in this case "ODC "Het Arsenaal", Rijkszee-en Marinehaven, 1781 ZZ Den Helder, The Netherlands" but not including where it starts to say "Manufacturer of Commodity" the characters will be different in all cases

the where clause only needs to say

where it_memo LIKE '%Export%') AND (dbo.itran.it_status = 'a')

John
0
 
LowfatspreadCommented:
it is unclear from your post if the data is all in one column on one row
or if the data is spread across multiple rows within the same column...

what was returned by 36598443 ?

what does this return

SELECT   top 10 substring(it_memo,10),   CHARINDEX('Foreign End User: ', it_memo) AS Fpos
                  ,CHARINDEX('Manufacturer of', it_memo) as Mpos
                            FROM          dbo.itran AS x
                            WHERE (it_memo LIKE '%export%')  
                              AND (it_status = 'a')
                              AND (it_recno = '3')

 
0
 
pepps11976Author Commented:
Lowfatspread just looking at your previous question saying about the format and why not have in separate columns, the text is coming straight out of a fox pro table and I have no control over this so all the text will always appear in 1 column

The code I pasted right at the start of the post works it's just the where clause that messes it up all I need the where to be is WHERE     (dbo.itran.it_memo LIKE '%Foreign End User:%)

I just don't understand why that does not work, the only control I have is what the user types into the fox pro database could I get them to put in a special character or something that would make this easier?

Thanks for your help on this
0
 
pepps11976Author Commented:
I think i understand now if the query is run and the text is not consistant through out "which it isnt" it will fail, so i have add a date in the where clause to only return from when i know data (text) was consistant and it seems to work.

Am i right in saying this?

john
0
 
LowfatspreadCommented:
well that would be better ...

you also probably want to confirm the format...

e.g.  ltext like '%foreign user: %,%manufacture of%'
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.