Solved

Sql Query

Posted on 2011-09-26
19
184 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

867 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

12 Experts available now in Live!

Get 1:1 Help Now