Need help with multiple condition Insert SQL

Need help with multiple condition Insert SQL

Post by Philli » Fri, 21 Jun 2002 06:40:16



HEre is my current statement that does not work but is what I need to get
done. Any Ideas

INSERT INTO [FEE_HISTORY]
                      (FEE_CODE,PAYER_ID,PRICE,DATEOFSERVICE)
SELECT
TEMPFEE.FEE_CODE,TEMPFEE.PAYER_ID,TEMPFEE.PRICE,TEMPFEE.DATEOFSERVICE
FROM         TEMPFEE,  Fee_history
WHERE     FEE_HISTORY.FEE_CODE <> TEMPFEE.FEE_CODE and
FEE_HISTORY.PAYER_ID <>TEMPFEE.PAYER_ID
and FEE_HISTORY.PRICE <> TEMPFEE.PRICE
and
FEE_HISTORY.DATEOFSERVICE <> TEMPFEE.DATEOFSERVICE

I am trying to track the histroy of price changes.

I have a table with a date, the new price, code for the product and who the
product belongs to. Once a month I get a complete table in foxpro but I need
to insert the new records into the sql database where there is an existing
table that comes from an outside source.

Thanks,
Phillip

 
 
 

Need help with multiple condition Insert SQL

Post by Anith Se » Fri, 21 Jun 2002 06:44:49


I am not sure what your TABLE strutures are and have no idea
about your Data. However, just guessing..

INSERT  [FEE_HISTORY]
        (
     FEE_CODE,
     PAYER_ID,
     PRICE,
     DATEOFSERVICE
     )
SELECT
     TEMPFEE.FEE_CODE,
     TEMPFEE.PAYER_ID,
     TEMPFEE.PRICE,
     TEMPFEE.DATEOFSERVICE
FROM
     TEMPFEE
WHERE NOT EXISTS (SELECT * FROM  FEE_HISTORY
      WHERE
      FEE_HISTORY.FEE_CODE = TEMPFEE.FEE_CODE
      and
      FEE_HISTORY.PAYER_ID = TEMPFEE.PAYER_ID
      and
      FEE_HISTORY.PRICE = TEMPFEE.PRICE
      and
      FEE_HISTORY.DATEOFSERVICE = TEMPFEE.DATEOFSERVICE)

Does this help? Post back with table DDLs and sample data as INSERT
statements, if you get any errors or if this is not what you expect.

- Anith


Quote:> HEre is my current statement that does not work but is what I need to get
> done. Any Ideas

> INSERT INTO [FEE_HISTORY]
>                       (FEE_CODE,PAYER_ID,PRICE,DATEOFSERVICE)
> SELECT
> TEMPFEE.FEE_CODE,TEMPFEE.PAYER_ID,TEMPFEE.PRICE,TEMPFEE.DATEOFSERVICE
> FROM         TEMPFEE,  Fee_history
> WHERE     FEE_HISTORY.FEE_CODE <> TEMPFEE.FEE_CODE and
> FEE_HISTORY.PAYER_ID <>TEMPFEE.PAYER_ID
> and FEE_HISTORY.PRICE <> TEMPFEE.PRICE
> and
> FEE_HISTORY.DATEOFSERVICE <> TEMPFEE.DATEOFSERVICE

> I am trying to track the histroy of price changes.

> I have a table with a date, the new price, code for the product and who
the
> product belongs to. Once a month I get a complete table in foxpro but I
need
> to insert the new records into the sql database where there is an existing
> table that comes from an outside source.

> Thanks,
> Phillip


 
 
 

Need help with multiple condition Insert SQL

Post by Philli » Fri, 21 Jun 2002 23:39:22


What I ended up with.
Now I need to figure out the update statement in case they change a fee but
leave the date the same.

See any problems with this update statement?

INSERT
INTO Fee_History
SELECT     A.FEE_CODE, A.PAYER_ID,
A.PRICE, A.DATEOFSERVICE
FROM         Feetemp
A LEFT OUTER
JOIN
                      Fee_History B ON A.FEE_CODE
= B.FEE_CODE AND A.PAYER_ID = B.PAYER_ID AND A.PRICE = B.PRICE
AND
                      A.DATEOFSERVICE = B.DATEOFSERVICE
WHERE     (B.PAYER_ID IS NULL)

> I am not sure what your TABLE strutures are and have no idea
> about your Data. However, just guessing..

> INSERT  [FEE_HISTORY]
>         (
>      FEE_CODE,
>      PAYER_ID,
>      PRICE,
>      DATEOFSERVICE
>      )
> SELECT
>      TEMPFEE.FEE_CODE,
>      TEMPFEE.PAYER_ID,
>      TEMPFEE.PRICE,
>      TEMPFEE.DATEOFSERVICE
> FROM
>      TEMPFEE
> WHERE NOT EXISTS (SELECT * FROM  FEE_HISTORY
>       WHERE
>       FEE_HISTORY.FEE_CODE = TEMPFEE.FEE_CODE
>       and
>       FEE_HISTORY.PAYER_ID = TEMPFEE.PAYER_ID
>       and
>       FEE_HISTORY.PRICE = TEMPFEE.PRICE
>       and
>       FEE_HISTORY.DATEOFSERVICE = TEMPFEE.DATEOFSERVICE)

> Does this help? Post back with table DDLs and sample data as INSERT
> statements, if you get any errors or if this is not what you expect.

> - Anith



> > HEre is my current statement that does not work but is what I need to
get
> > done. Any Ideas

> > INSERT INTO [FEE_HISTORY]
> >                       (FEE_CODE,PAYER_ID,PRICE,DATEOFSERVICE)
> > SELECT
> > TEMPFEE.FEE_CODE,TEMPFEE.PAYER_ID,TEMPFEE.PRICE,TEMPFEE.DATEOFSERVICE
> > FROM         TEMPFEE,  Fee_history
> > WHERE     FEE_HISTORY.FEE_CODE <> TEMPFEE.FEE_CODE and
> > FEE_HISTORY.PAYER_ID <>TEMPFEE.PAYER_ID
> > and FEE_HISTORY.PRICE <> TEMPFEE.PRICE
> > and
> > FEE_HISTORY.DATEOFSERVICE <> TEMPFEE.DATEOFSERVICE

> > I am trying to track the histroy of price changes.

> > I have a table with a date, the new price, code for the product and who
> the
> > product belongs to. Once a month I get a complete table in foxpro but I
> need
> > to insert the new records into the sql database where there is an
existing
> > table that comes from an outside source.

> > Thanks,
> > Phillip

 
 
 

1. DTS Multiple Condition Workflow Using OR Condition

Hi All,

I posted this question to the dts new group but did not get a response.  I
am using SS 7.0 and I created a dts package to load a data warehouse staging
database from flat files.  The flat files are database segments extracted
from an IMS database.  I created 9 data transformations to load the
individual files in parallel.  I want to create an active x script that will
execute if any of the steps fail.  I.e. if t1 failed or t2 failed or t3
failed ... execute the script.  The dts workflow interface only allows you
AND conditions together.

I thought of two possible solutions.
1.  Use com objects to query the results of each step.
2.  Use a global variable initialized to success and active x code in each
of the transfomations to update the var to failed if the step fails.

---------------------------------------
Here is the code I tried for solution #1:
---------------------------------------

    set oPKG = DTSGlobalVariables.Parent
    if oPKG.Steps("CB_ORDER_LOAD").ExecutionResult =
DTSStepExecResult_Failure
       or  oPKG.Steps("CB_ORDER_NOTE_LOAD").ExecutionResult =
DTSStepExecResult_Failure ... then
        ...
    end if

This resulted in the following error:

    Step 'CB_ORDER_LOAD' was not found.

CB_ORDER_LOAD is the description of the data transformation step.  I could
not figure out how to get the actual name of the step.

-------------
Solution #2:
-------------

I tried puting code in an an active x script in the data transformation and
I also tried puting the code in the workflow active x script, but I could
not figure out how to get the status of the step.

Can anyone help me get the above solutions to work or know of another way to
handle the problem?

Thanks in advance!

2. OLEDB Driver Version

3. List Box HELP

4. Need help with and/or condition in SQL

5. Step by Step MS Access 97 Data Disk

6. Needing to add a condition based on a condition

7. A question for Access developers.

8. multiple OR conditions ===>vs multiple table scans ?

9. How to work with Binary Data???insert,select with where condition???HELP

10. SQL JOIN with multiple conditions.

11. SQL statement - selecting multiple items based on complex condition

12. Multiple conditions in sql statement