Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Sql Query

Posted on 2011-09-26
19
Medium Priority
?
207 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
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
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 2000 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
 
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

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

Question has a verified solution.

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

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

783 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