Solved

Just got my query working for one function. Now i need to get my other function working just like it

Posted on 2013-05-24
5
265 Views
Last Modified: 2013-05-28
Here is the query i have working on my search and populate function on my aspx webpage

declare @Seek as varchar(20)
set @seek = 'AR42154'

select
*
from amipartnumbers as APN 
left join (
            select *
            from JD
            cross apply (
                values 
                 (1, OEMPartNumer )
                ,(2, OEMSubNumber )
                ,(3, OEMSubNumber2)
                --,(4, OEMSubNumber3)
                --,(5, OEMSubNumber4)
              ) as xapply (lvl, OEMRef)
           ) JDT on APN.OEMItem = JDT.OEMRef
where OEMPartNumer = @Seek
or OEMSubNumber    = @seek
or OEMSubNumber2   = @seek
--or OEMSubNumber3   = @seek
--or OEMSubNumber4   = @seek

Open in new window


NOW, i need to get this working on my UPDATE query that imports users csv file and updates it with this query and returns it back to user.

This is the query i had before but it doesnt match all my numbers. only some of them

UPDATE imports 
SET imports.AMIPartNumber = 
coalesce(AMIA.Item,AMIB.Item,AMIC.Item,AMID.Item,AMIE.Item), 
Imports.AMIDescription = 
coalesce(AMIA.Description,AMIB.Description,AMIC.Description,AMID.Description,AMIE.Description) 
FROM imports as I 
LEFT JOIN JD as JDA 
ON I.OEMPartNumber = JDA.OEMPartNumber 
LEFT JOIN amipartnumbers as AMIA 
ON JDA.OEMPartNumber = AMIA.OEMItem 
LEFT JOIN JD as JDB 
ON I.OEMPartNumber = JDB.OEMSubNumber 
LEFT JOIN amipartnumbers as AMIB 
ON JDB.OEMSubNumber = AMIB.OEMItem 
LEFT JOIN JD as JDC 
ON I.OEMPartNumber = JDC.OEMSubNumber2 
LEFT JOIN amipartnumbers as AMIC 
ON JDB.OEMSubNumber2 = AMIC.OEMItem 
LEFT JOIN JD as JDD 
ON I.OEMPartNumber = JDD.OEMSubNumber3 
LEFT JOIN amipartnumbers as AMID 
ON JDB.OEMSubNumber2 = AMID.OEMItem 
LEFT JOIN JD as JDE 
ON I.OEMPartNumber = JDE.OEMSubNumber4 
LEFT JOIN amipartnumbers as AMIE 
ON JDB.OEMSubNumber2 = AMIE.OEMItem ;

select * from imports

Open in new window

0
Comment
Question by:bignadad
  • 3
  • 2
5 Comments
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39194279
as always, please try as a select before trying it as an update, may be quite wrong (I'm too tired really), but try it:
--UPDATE imports 
--SET
--  imports.AMIPartNumber = AMI.Item
--, Imports.AMIDescription = AMI.Description

select
  AMI.Item
, AMI.Description
, I.OEMPartNumber

FROM imports as I 
INNER JOIN (
            select
              JDT.OEMRef
            , APN.Item
            , APN.Description
            from amipartnumbers as APN 
            inner join (
                        select OEMref
                        from JD
                        cross apply (
                            values 
                             (1, OEMPartNumer )
                            ,(2, OEMSubNumber )
                            ,(3, OEMSubNumber2)
                            ,(4, OEMSubNumber3)
                            ,(5, OEMSubNumber4)
                          ) as xapply (lvl, OEMRef)
                       ) JDT on APN.OEMItem = JDT.OEMRef
            ) AMI on I.OEMPartNumber = AMI.OEMRef

Open in new window

0
 
LVL 2

Author Comment

by:bignadad
ID: 39194975
ive tried messing around with that query but i keep getting error on AMI.Item and AMI.Description in SET.

tried to change to table name and didnt work. and tried to change to APN and didnt work
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 39196158
Hi. Could I suggest that instead of you needing to mess around with any query, that we all use the right tables and fields names? (and that the tables are accurately defined).

i.e. If I look back over the past couple of related questions I get confused about the table structures (and names)
e.g.
>>is it JD of JDSubs,
>>how many SubNumber[n] fields do really exist in JD/JDSubs
>>is there a field called "OEMPartNumer" or should it be "OEMPartNumBer"? :)

http://sqlfiddle.com/#!3/ff223/1 related to the earlier proposal for an update
http://sqlfiddle.com/#!3/8ac12/48 relates to the transposition (using cross apply)

There are differences in both of those for names/structures - it would help us, and especially you, if these discrepancies were removed. Once this is done any code proposed should not need much messing about with (I hope).

I'll have a crack at this using the latest sqlfiddle plus the import table, and get back with any progress (if any) - but it would be easier if we used the real definitions.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39196169
righty-oh, have something working, see http://sqlfiddle.com/#!3/76902/8
/* http://www.experts-exchange.com/Programming/Languages/SQL_Syntax/Q_28138157.html */
/* Just got my query working for one function. Now i need to get my other function working just like it */

/* at this stage the imports table has null in [AMIPartNumber] */
select
*
from imports
;
/*
   this displays the matches found by normalizing (transposing) table JD
   using cross apply
*/

select
*
from imports
left join (
            select *
            from JD
            cross apply (
                values 
                 (1, OEMPartNumer )
                ,(2, OEMSubNumber )
                ,(3, OEMSubNumber2)
              ) as xapply (lvl, OEMRef)
           ) JDT on imports.OEMPartNumber = JDT.OEMRef
;

/* so moving on from there we should be able to update table imports [AMIPartNumber] */

UPDATE imports
SET imports.AMIPartNumber =JDT.OEMPartNumer
from imports
left join (
            select *
            from JD
            cross apply (
                values 
                 (1, OEMPartNumer )
                ,(2, OEMSubNumber )
                ,(3, OEMSubNumber2)
              ) as xapply (lvl, OEMRef)
           ) JDT on imports.OEMPartNumber = JDT.OEMRef
;

/* and to show the effect of that update */
select
*
from imports
;

Open in new window

{edit, a correction}
0
 
LVL 2

Author Closing Comment

by:bignadad
ID: 39202285
Going to work on getting tables normalized.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
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 video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

757 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

19 Experts available now in Live!

Get 1:1 Help Now