Solved

Sql Query

Posted on 2011-09-26
19
181 Views
Last Modified: 2012-05-12
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
0
Comment
Question by:pepps11976
  • 8
  • 8
  • 3
19 Comments
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 36598139
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
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 36598142
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
 

Author Comment

by:pepps11976
ID: 36598213
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
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 36598217
Are tehere any records that match that query?
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 36598222
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
 

Author Comment

by:pepps11976
ID: 36598238
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
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 36598248
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
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 36598254
you get the error surely becasuse there isnt a comma after the foreign user phrase in some instances?
0
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 500 total points
ID: 36598264
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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 50

Expert Comment

by:Lowfatspread
ID: 36598279
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
 

Author Comment

by:pepps11976
ID: 36598291
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
 

Author Comment

by:pepps11976
ID: 36598316
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
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 36598443
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
 

Author Comment

by:pepps11976
ID: 36598545
Hi Low Fat Spread i get unable to parse query text with the code
0
 

Author Comment

by:pepps11976
ID: 36599107
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
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 36601796
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
 

Author Comment

by:pepps11976
ID: 36602152
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
 

Author Comment

by:pepps11976
ID: 36709518
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
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 36709563
well that would be better ...

you also probably want to confirm the format...

e.g.  ltext like '%foreign user: %,%manufacture of%'
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Suggested Solutions

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now