October 30, 2008 at 10:21 am
OK I have been trying to get a table to populate with the information i need it to so I wrote a stored procedure I thought would work but its not. If I execute the Procedure then it shows me the information but does nto place it in the table like I want. I am still learning about SQL and hope one of you can help me here is the stored procedure I wrote let me know if you guys or gals know what I am missing please
USE [BOMVerifier]
GO
/****** Object: StoredProcedure [dbo].[Get_BomInfo] Script Date: 10/30/2008 12:14:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[Get_BomInfo]
@PARNT CHAR(15),
@CUSNO NUMERIC(10,0),
@REVCD CHAR(3),
@CSEQN NUMERIC(3,0),
@child CHAR(15),
@DESCP CHAR(30),
@PSREQ NUMERIC(11,5),
@PSUNM CHAR(2),
@PSCCL CHAR(3),
@PSPCL CHAR(3),
@PSEDT varchar(50),
@PSDDT varchar(50),
@PSBFL char(1),
@SGVND numeric(6,0),
@PLNID char(6),
@ORDNO numeric(6,0),
@LineNumber numeric(3,0),
@CustPN varchar(50),
@PalletNum varchar(50),
@BulkContainer varchar(50),
@SingleBox varchar(50),
@Palletqty int,
@FullpalletBox varchar(50),
@PartialPalletBox varchar(50),
@Lid varchar(50),
@Divider varchar(50),
@Layer varchar(50),
@BandType varchar(50),
@BandPosition varchar(50),
@BandCount int,
@MaxSkidHeight varchar(50),
@MaxSkidWeight int,
@PalletLabelFormat varchar(50),
@BandMarkingRequired int,
@FoamPackRequired int,
@FoamCellPackRequired int,
@ShrinkWrapRequired int,
@ContainerSealMethod varchar(50),
@EmptyCellsOK int,
@SpecialInstructions varchar(2000),
@Active int,
@DateStamp smalldatetime,
@EntryBy varchar(200)
go
SELECT PARNT = @PARNT , CUSNO, A.REVCD, A.CSEQN, A.CHILD, A.DESCP, A.PSREQ, A.PSUNM, A.PSCCL, A.PSPCL, A.PSEDT, A.PSDDT, A.PSBFL, A.SGVND, A.PLNID,
A.ORDNO, A.LINE# AS LineNumber, p.CustPN, plt.PalletNum, pbc.BulkContainer, psb.SingleBox, p.palletqty, pfbx.fullpalletBox,
ppbx.PartialPalletBox, pl.Lid, pd.Divider, ply.Layer, pbt.BandType, pbp.BandPosition,p.BandCount, p.MaxSkidHeight, P.MaxSkidWeight, pplf.PalletLabelFormat,
CONVERT(INTEGER, P.BandMarkingRequired) as BandMarkingRequired, CONVERT(INTEGER, P.FoamPackRequired) as FoamPackRequired,
CONVERT(INTEGER, P.FoamCellPackRequired) as FoamCellPackReuired, CONVERT(INTEGER, P.ShrinkWrapRequired) as ShrinkWrapRequired,
pcsm.containerSealMethod , CONVERT(INTEGER, P.EmptyCellsOK) AS EmptyCellsOK, p.specialInstructions,
CONVERT(INTEGER, P.Active) as Active, p.DateStamp, p.EntryBy
FROM OPENQUERY(AS400,
'select
PARNT, REVCD, CSEQN, CHILD,
DESCP, PSREQ, PSUNM, PSCCL,
PSPCL, CAST(PSEDT AS VARCHAR(10)) as PSEDT,CAST(PSDDT AS VARCHAR(10)) as PSDDT, PSBFL,
SGVND, PLNID, ORDNO, LINE#, CUSNO
FROM V92FILES.WKPS0426')
as a inner join shipinsp07.dbo.Packaging AS P on PARNT = P.PartNum
left outer join shipInsp07.dbo.PackagingContainerSealMethod as pcsm on p.containerSealMethodID = pcsm.ID
left outer join shipinsp07.dbo.PackagingPalletLabelFormat as pplf on P.PalletLabelFormatID = pplf.ID
left outer join shipinsp07.dbo.packagingBandPosition as pbp on p.BandPositionID = pbp.ID
left outer join shipinsp07.dbo.PackagingBandType as pbt on P.BandTypeID = pbt.ID
left outer join shipinsp07.dbo.packagingLayer as ply on P.LayerID = ply.ID
left outer join shipinsp07.dbo.PackagingDivider as pd on P.DividerID = pd.ID
left outer join shipinsp07.dbo.packagingLid as pl on P.LidID = pl.ID
left outer joinshipinsp07.dbo.PackagingPartialPalletBox as ppbx on p.PartialPalletBoxID = ppbx.ID
left outer join shipinsp07.dbo.PackagingFullPalletBox as pfbx on P.FullpalletBoxID = pfbx.ID
left outer join shipinsp07.dbo.PackagingSingleBox as psb on P.SingleBoxID = psb.ID
left outer join shipinsp07.dbo.PackagingBulkContainer as pbc on P.BulkContainerID = pbc.ID
left outer join shipinsp07.dbo.PackagingPalletNum as plt on P.PalletNumID = plt.ID
October 30, 2008 at 10:35 am
Hey khaysmer,
It Looks like you may be missing the insert statement before your select.
typically it would look something like
Insert into Yourtable (value1, value2, value3)
SELECT PARNT = @PARNT , CUSNO, A.REVCD, A.CSEQN, A.CHILD, A.DESCP, A.PSREQ, A.PSUNM, A.PSCCL, A.PSPCL, A.PSEDT, A.PSDDT, A.PSBFL, A.SGVND, A.PLNID,
A.ORDNO, A.LINE# AS LineNumber, p.CustPN, plt.PalletNum, pbc.BulkContainer, psb.SingleBox, p.palletqty, pfbx.fullpalletBox,
ppbx.PartialPalletBox, pl.Lid, pd.Divider, ply.Layer, pbt.BandType, pbp.BandPosition,p.BandCount, p.MaxSkidHeight, P.MaxSkidWeight, pplf.PalletLabelFormat,
CONVERT(INTEGER, P.BandMarkingRequired) as BandMarkingRequired, CONVERT(INTEGER, P.FoamPackRequired) as FoamPackRequired,
CONVERT(INTEGER, P.FoamCellPackRequired) as FoamCellPackReuired, CONVERT(INTEGER, P.ShrinkWrapRequired) as ShrinkWrapRequired,
pcsm.containerSealMethod , CONVERT(INTEGER, P.EmptyCellsOK) AS EmptyCellsOK, p.specialInstructions,
CONVERT(INTEGER, P.Active) as Active, p.DateStamp, p.EntryBy
FROM OPENQUERY(AS400,
'select
PARNT, REVCD, CSEQN, CHILD,
DESCP, PSREQ, PSUNM, PSCCL,
PSPCL, CAST(PSEDT AS VARCHAR(10)) as PSEDT, CAST(PSDDT AS VARCHAR(10)) as PSDDT, PSBFL,
SGVND, PLNID, ORDNO, LINE#, CUSNO
FROM V92FILES.WKPS0426')
as a inner join shipinsp07.dbo.Packaging AS P on PARNT = P.PartNum
left outer join shipInsp07.dbo.PackagingContainerSealMethod as pcsm on p.containerSealMethodID = pcsm.ID
left outer join shipinsp07.dbo.PackagingPalletLabelFormat as pplf on P.PalletLabelFormatID = pplf.ID
left outer join shipinsp07.dbo.packagingBandPosition as pbp on p.BandPositionID = pbp.ID
left outer join shipinsp07.dbo.PackagingBandType as pbt on P.BandTypeID = pbt.ID
left outer join shipinsp07.dbo.packagingLayer as ply on P.LayerID = ply.ID
left outer join shipinsp07.dbo.PackagingDivider as pd on P.DividerID = pd.ID
left outer join shipinsp07.dbo.packagingLid as pl on P.LidID = pl.ID
left outer join shipinsp07.dbo.PackagingPartialPalletBox as ppbx on p.PartialPalletBoxID = ppbx.ID
left outer join shipinsp07.dbo.PackagingFullPalletBox as pfbx on P.FullpalletBoxID = pfbx.ID
left outer join shipinsp07.dbo.PackagingSingleBox as psb on P.SingleBoxID = psb.ID
left outer join shipinsp07.dbo.PackagingBulkContainer as pbc on P.BulkContainerID = pbc.ID
left outer join shipinsp07.dbo.PackagingPalletNum as plt on P.PalletNumID = plt.ID
Hope that helps.
bh.
October 30, 2008 at 10:36 am
first thing i notice is you declared 43 variables, but only use one of them; i guess there's more to this than you pasted?
next a minor thing is that your OPENQUERY is aliased as "a", then there is a dozen or so left outer joins...
but there is not a single left outer join that explicitly references that "A" alias....i can assume i guess that the join for "inner join shipinsp07.dbo.Packaging AS P
on a.PARNT = P.PartNum" is what ties them together....with that many left outer joins, are we sure that you are sure that the PARNT is coming from "A" aliased subquery?
next , your openquery plus the inner join retrieves a recordset, right?
Since everything else is just left joins against those pairs of tables, I'd just make sure this returns a query:
SELECT *
FROM OPENQUERY(AS400,
'select
PARNT, REVCD, CSEQN, CHILD,
DESCP, PSREQ, PSUNM, PSCCL,
PSPCL, CAST(PSEDT AS VARCHAR(10)) as PSEDT, CAST(PSDDT AS VARCHAR(10)) as PSDDT, PSBFL,
SGVND, PLNID, ORDNO, LINE#, CUSNO
FROM V92FILES.WKPS0426') as a
inner join shipinsp07.dbo.Packaging AS P
on a.PARNT = P.PartNum
Lowell
October 30, 2008 at 10:52 am
What do you mean i used only one of them. if its with the a.PARNT = @PARNT its cause some one here at work said that was all i had to do the program would know the rest where being used cause of it.
October 30, 2008 at 11:54 am
all i meant was you are joining lots of tables in this query....if any two of the tables have a column named PARNT then you will get an error that says ambiguous name "PARNT";
you obviously went to a lot of trouble to alias all your joining tables, but seemed to have forgotten one.
Lowell
October 30, 2008 at 12:29 pm
Nope the only table with the PARNT feild is in the openQuery and I check the open query produces the information I need from it and doing a query using just the select feilds shows me all the data I needed. Now I added the insert into Table the first guy recommened. it seems to get passed where it was but now it has an error
msg 2001, level16, state 4, Procedure Get_BomInfo, Line 0
Procedure or function 'Get_BomInfo' expects Parameter '@PARNT' , which was not supplied
but now if i do the query with out the insert it will show me the data just not input it into the table
October 30, 2008 at 1:50 pm
OK I still see the error when I execute the stored procdure it places the information in the table but shows the error. but now all I have to have is the table auto get the information so sombody doesn't have to execute the stored procedure each time for the information to popluate any more advice. You guys have been great help so far and got me over a hugh hump in the program.
October 30, 2008 at 1:56 pm
You can set up a SQL Server Agent job to execute the Sproc at defined intervals.
October 30, 2008 at 2:04 pm
I am curious as to what your error is. While looking at the code in your original post, I also noticed a "go" where you probably don't want one. I have reposted your code below and put a comment next to the go.
USE [BOMVerifier]
GO
/****** Object: StoredProcedure [dbo].[Get_BomInfo] Script Date: 10/30/2008 12:14:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[Get_BomInfo]
@PARNT CHAR(15),
@CUSNO NUMERIC(10,0),
@REVCD CHAR(3),
@CSEQN NUMERIC(3,0),
@child CHAR(15),
@DESCP CHAR(30),
@PSREQ NUMERIC(11,5),
@PSUNM CHAR(2),
@PSCCL CHAR(3),
@PSPCL CHAR(3),
@PSEDT varchar(50),
@PSDDT varchar(50),
@PSBFL char(1),
@SGVND numeric(6,0),
@PLNID char(6),
@ORDNO numeric(6,0),
@LineNumber numeric(3,0),
@CustPN varchar(50),
@PalletNum varchar(50),
@BulkContainer varchar(50),
@SingleBox varchar(50),
@Palletqty int,
@FullpalletBox varchar(50),
@PartialPalletBox varchar(50),
@Lid varchar(50),
@Divider varchar(50),
@Layer varchar(50),
@BandType varchar(50),
@BandPosition varchar(50),
@BandCount int,
@MaxSkidHeight varchar(50),
@MaxSkidWeight int,
@PalletLabelFormat varchar(50),
@BandMarkingRequired int,
@FoamPackRequired int,
@FoamCellPackRequired int,
@ShrinkWrapRequired int,
@ContainerSealMethod varchar(50),
@EmptyCellsOK int,
@SpecialInstructions varchar(2000),
@Active int,
@DateStamp smalldatetime,
@EntryBy varchar(200)
go -- This probably should not be here.
SELECT PARNT = @PARNT , CUSNO, A.REVCD, A.CSEQN, A.CHILD, A.DESCP, A.PSREQ, A.PSUNM, A.PSCCL, A.PSPCL, A.PSEDT, A.PSDDT, A.PSBFL, A.SGVND, A.PLNID,
A.ORDNO, A.LINE# AS LineNumber, p.CustPN, plt.PalletNum, pbc.BulkContainer, psb.SingleBox, p.palletqty, pfbx.fullpalletBox,
ppbx.PartialPalletBox, pl.Lid, pd.Divider, ply.Layer, pbt.BandType, pbp.BandPosition,p.BandCount, p.MaxSkidHeight, P.MaxSkidWeight, pplf.PalletLabelFormat,
CONVERT(INTEGER, P.BandMarkingRequired) as BandMarkingRequired, CONVERT(INTEGER, P.FoamPackRequired) as FoamPackRequired,
CONVERT(INTEGER, P.FoamCellPackRequired) as FoamCellPackReuired, CONVERT(INTEGER, P.ShrinkWrapRequired) as ShrinkWrapRequired,
pcsm.containerSealMethod , CONVERT(INTEGER, P.EmptyCellsOK) AS EmptyCellsOK, p.specialInstructions,
CONVERT(INTEGER, P.Active) as Active, p.DateStamp, p.EntryBy
FROM OPENQUERY(AS400,
'select
PARNT, REVCD, CSEQN, CHILD,
DESCP, PSREQ, PSUNM, PSCCL,
PSPCL, CAST(PSEDT AS VARCHAR(10)) as PSEDT, CAST(PSDDT AS VARCHAR(10)) as PSDDT, PSBFL,
SGVND, PLNID, ORDNO, LINE#, CUSNO
FROM V92FILES.WKPS0426')
as a inner join shipinsp07.dbo.Packaging AS P on PARNT = P.PartNum
left outer join shipInsp07.dbo.PackagingContainerSealMethod as pcsm on p.containerSealMethodID = pcsm.ID
left outer join shipinsp07.dbo.PackagingPalletLabelFormat as pplf on P.PalletLabelFormatID = pplf.ID
left outer join shipinsp07.dbo.packagingBandPosition as pbp on p.BandPositionID = pbp.ID
left outer join shipinsp07.dbo.PackagingBandType as pbt on P.BandTypeID = pbt.ID
left outer join shipinsp07.dbo.packagingLayer as ply on P.LayerID = ply.ID
left outer join shipinsp07.dbo.PackagingDivider as pd on P.DividerID = pd.ID
left outer join shipinsp07.dbo.packagingLid as pl on P.LidID = pl.ID
left outer join shipinsp07.dbo.PackagingPartialPalletBox as ppbx on p.PartialPalletBoxID = ppbx.ID
left outer join shipinsp07.dbo.PackagingFullPalletBox as pfbx on P.FullpalletBoxID = pfbx.ID
left outer join shipinsp07.dbo.PackagingSingleBox as psb on P.SingleBoxID = psb.ID
left outer join shipinsp07.dbo.PackagingBulkContainer as pbc on P.BulkContainerID = pbc.ID
left outer join shipinsp07.dbo.PackagingPalletNum as plt on P.PalletNumID = plt.ID
October 30, 2008 at 2:13 pm
I have change the code some and that go is gone here is what i have now.
USE [BOMVerifier]
GO
/****** Object: StoredProcedure [dbo].[Get_BomInfo] Script Date: 10/30/2008 12:14:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[Get_BomInfo]
@PARNT CHAR(15),
@CUSNO NUMERIC(10,0),
@REVCD CHAR(3),
@CSEQN NUMERIC(3,0),
@child CHAR(15),
@DESCP CHAR(30),
@PSREQ NUMERIC(11,5),
@PSUNM CHAR(2),
@PSCCL CHAR(3),
@PSPCL CHAR(3),
@PSEDT varchar(50),
@PSDDT varchar(50),
@PSBFL char(1),
@SGVND numeric(6,0),
@PLNID char(6),
@ORDNO numeric(6,0),
@LineNumber numeric(3,0),
@CustPN varchar(50),
@PalletNum varchar(50),
@BulkContainer varchar(50),
@SingleBox varchar(50),
@Palletqty int,
@FullpalletBox varchar(50),
@PartialPalletBox varchar(50),
@Lid varchar(50),
@Divider varchar(50),
@Layer varchar(50),
@BandType varchar(50),
@BandPosition varchar(50),
@BandCount int,
@MaxSkidHeight varchar(50),
@MaxSkidWeight int,
@PalletLabelFormat varchar(50),
@BandMarkingRequired int,
@FoamPackRequired int,
@FoamCellPackRequired int,
@ShrinkWrapRequired int,
@ContainerSealMethod varchar(50),
@EmptyCellsOK int,
@SpecialInstructions varchar(2000),
@Active int,
@DateStamp smalldatetime,
@EntryBy varchar(200),
@PartNum Char(15)
as
insert into BOM_Packaging_Verification (PARNT,CUSNO,REVCD,CSEQN,CHILD,DESCP,PSREQ,PSUNM,PSCCL,PSPCL,PSEDT,PSDDT,PSBFL,SGVND,PLNID,
ORDNO,LineNumber,CustPN,PalletNum,BulkContainer,SingleBox,palletqty,FullPalletBox,
PartialPalletBox,Lid,Divider,Layer,BandType,BandPosition,BandCount,MaxSkidHeight,MaxSkidWeight,PalletLabelFormat,
BandMarkingRequired,FoamPackRequired,FoamCellPackRequired,ShrinkWrapRequired,
ContainerSealMethod,EmptyCellsOK,specialInstructions,Active,DateStamp,EntryBy,PartNum)
SELECT A.PARNT , A.CUSNO, A.REVCD, A.CSEQN, A.CHILD, A.DESCP, A.PSREQ, A.PSUNM, A.PSCCL, A.PSPCL, A.PSEDT, A.PSDDT, A.PSBFL, A.SGVND, A.PLNID,
A.ORDNO, A.LINE# AS LineNumber, p.CustPN, plt.PalletNum, pbc.BulkContainer, psb.SingleBox, p.palletqty, pfbx.fullpalletBox,
ppbx.PartialPalletBox, pl.Lid, pd.Divider, ply.Layer, pbt.BandType, pbp.BandPosition,p.BandCount, p.MaxSkidHeight, P.MaxSkidWeight, pplf.PalletLabelFormat,
CONVERT(INTEGER, P.BandMarkingRequired) as BandMarkingRequired, CONVERT(INTEGER, P.FoamPackRequired) as FoamPackRequired,
CONVERT(INTEGER, P.FoamCellPackRequired) as FoamCellPackRequired, CONVERT(INTEGER, P.ShrinkWrapRequired) as ShrinkWrapRequired,
pcsm.ContainerSealMethod , CONVERT(INTEGER, P.EmptyCellsOK) AS EmptyCellsOK, p.specialInstructions,
CONVERT(INTEGER, P.Active) as Active, p.DateStamp, p.EntryBy, P.PartNum
FROM OPENQUERY(AS400,
'select
PARNT, REVCD, CSEQN, CHILD,
DESCP, PSREQ, PSUNM, PSCCL,
PSPCL, CAST(PSEDT AS VARCHAR(10)) as PSEDT,CAST(PSDDT AS VARCHAR(10)) as PSDDT, PSBFL,
SGVND, PLNID, ORDNO, LINE#, CUSNO
FROM V92FILES.WKPS0426')
as a inner join shipinsp07.dbo.Packaging AS P on PARNT = P.PartNum
left outer join shipInsp07.dbo.PackagingContainerSealMethod as pcsm on p.containerSealMethodID = pcsm.ID
left outer join shipinsp07.dbo.PackagingPalletLabelFormat as pplf on P.PalletLabelFormatID = pplf.ID
left outer join shipinsp07.dbo.packagingBandPosition as pbp on p.BandPositionID = pbp.ID
left outer join shipinsp07.dbo.PackagingBandType as pbt on P.BandTypeID = pbt.ID
left outer join shipinsp07.dbo.packagingLayer as ply on P.LayerID = ply.ID
left outer join shipinsp07.dbo.PackagingDivider as pd on P.DividerID = pd.ID
left outer join shipinsp07.dbo.packagingLid as pl on P.LidID = pl.ID
left outer joinshipinsp07.dbo.PackagingPartialPalletBox as ppbx on p.PartialPalletBoxID = ppbx.ID
left outer join shipinsp07.dbo.PackagingFullPalletBox as pfbx on P.FullpalletBoxID = pfbx.ID
left outer join shipinsp07.dbo.PackagingSingleBox as psb on P.SingleBoxID = psb.ID
left outer join shipinsp07.dbo.PackagingBulkContainer as pbc on P.BulkContainerID = pbc.ID
left outer join shipinsp07.dbo.PackagingPalletNum as plt on P.PalletNumID = plt.ID
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply