TSQL: Conditional Union Statement

Jul 20, 2005

Is it possible to have a conditional union statement in a stored proc?

Here's an example on the northwind database. If says there's a syntax
error near the UNION statement. Looks like it doesn't like having the
BEGIN directly in front of it.

Is the only solution to create a dynamic sql string then call exec on
it?

Any help appreciated.

Tom.

CREATE PROCEDURE usp_test
(
@both int = 1
)
AS

SET NOCOUNT ON

SELECT * FROM territories WHERE regionid = 1

IF @both = 1
BEGIN

UNION

SELECT * FROM territories WHERE regionid = 2

END
GO

View 5 Replies


ADVERTISEMENT

Conditional Union Possible?

Dec 15, 2005

I'm trying to achieve something like the following:


SELECT 'page' as type, page_filename as filename, page_title as title, page_metadescription as intro
FROM tbl_pages
WHERE CONTAINS(*, @searchterm)

IF 1=1 BEGIN
UNION all

SELECT 'news' as type, 'thiswillbeafilename.asp' as filename, news_title as title, news_intro as intro
FROM tbl_news
WHERE CONTAINS(*, @searchterm)
END
IF 2=2 BEGIN
UNION ALL

SELECT 'resource' as type, resource_filename as filename, resource_longtitle as title, resource_summary
FROM tbl_resources
WHERE CONTAINS(*, @searchterm)
END


I'm pretty sure this isn't possible using the approach i have here, however is there an different method i can use to ahcieve the same effect other than using dynamic sql?

Cheers,

View 3 Replies View Related

T-SQL (SS2K8) :: Selection With Conditional Insert Union

Apr 4, 2014

In this selection when there is particular segment found in the selection then need to add 2 records per segment otherwise just selected results

--Drop table #list
CREATE TABLE #LIST ( email varchar(20),Segment varchar(20), Addr1 varchar(20), City varchar(20),ST varchar(20), Code int)
--Drop table #subject
CREATE TABLE #Subject (Segment varchar(20), Fname varchar(20), LName varchar(20))

[Code] ....

---Selection results
SELECT L.Segment , l.email, l.Addr1 , l.City ,l.ST, l.code, s.Fname , s.LName into #expectedresult FROM #LIST L JOIN #Subject S ON l.Segment =s.Segment

--Now here what i am trying to get, union the below two record to the above "selection Results" for only segment ='VEC_BAL' or segment = 'PPC_BAL'

--2 records
SELECT l.segment , l.email , Addr1, City, ST, 999 as Code,'' fname, ''lname from #LIST l where email ='mmm@m.com'
SELECT l.segment , l.email , Addr1, City, ST, 888 as Code, '' fname, ''lname from #LIST l where email ='lrk@m.com'

--- expected results please execute below block ( per each segment 2 records will insert if there is segment ='VEC_BAL' or 'PPC_BAL' ). If there is no Vec_Bal or PPC BAL then no additional inserts, if there is only VEC_BAL no PPC_BAL then only 2 records insert
SELECT L.Segment , l.email, l.Addr1 , l.City ,l.ST, l.code, s.Fname , s.LName into #expctresults1 FROM #LIST L JOIN #Subject S ON l.Segment =s.Segment
union

[Code] .....

View 3 Replies View Related

TSQL UNION, But Get The Differance In Dollar Amount In The Result

May 26, 2006

GridView_1
Category Name SubCategory Name Amount
Construction Construction 2,877.00
Design Design 0.00
Soft Cost Inspection 0.00

GridView_2
Category Name SubCategory Name Amount
Construction Construction 2,800.00
Construction Contingency 300.00
Design Design 500.00
Soft Cost Inspection 980.00
Soft Cost Survey 145.00
Soft Cost Testing 720.00
Soft Cost Management 1000.00
Soft Cost Other Costs 10,000.00

QUESTION:
How could I UNION the two result sets together and where the Category Name and SubCategory Name are equal for GridView1 and GridView2, show the difference in the Amount column.

This is the result I am looking for.


Category Name SubCategory Name DIFFERANCE
Construction Construction 77.00 = (GridView1 - GridView2)
Construction Contingency 300.00
Design Design 500.00
Soft Cost Inspection 980.00
Soft Cost Survey 145.00
Soft Cost Testing 720.00
Soft Cost Management 1000.00
Soft Cost Other Costs 10,000.00

I am trying to do it on the back end. Though, if you have a clever way to produce a resulting gridview in C# with the results I need, I am all game!
This is what I have so far: (TSQL code)



SELECT

tblCategories.txtCategoryName,

tblSubCategories.txtSubCategoryName,

tblEstimatesLineItems.curEscAmount

FROM

tblEstimatesLineItems, tblCategories, tblSubCategories

WHERE

tblEstimatesLineItems.lngzEstimateId = 24 AND --@lngzEstimateId_Compare1

tblEstimatesLineItems.lngzCategoryId = tblCategories.idsCategoryId AND

tblEstimatesLineitems.lngzSubCategoryId = tblSubCategories.idsSubCategoryId

union all

SELECT

tblCategories.txtCategoryName,

tblSubCategories.txtSubCategoryName,

tblEstimatesLineItems.curEscAmount

FROM

tblEstimatesLineItems, tblCategories, tblSubCategories

WHERE

tblEstimatesLineItems.lngzEstimateId = 25 AND --@lngzEstimateId_Compare1

tblEstimatesLineItems.lngzCategoryId = tblCategories.idsCategoryId AND

tblEstimatesLineitems.lngzSubCategoryId = tblSubCategories.idsSubCategoryId

View 7 Replies View Related

SQL Server 2012 :: Have Conditional Join / Union Based On Parameters Passed To Stored Procedure

Jul 15, 2014

I am writing a stored procedure that takes in a customer number, a current (most recent) sales order quote, a prior (to most current) sales order quote, a current item 1, and a prior item 1, all of these parameters are required.Then I have current item 2, prior item 2, current item 3, prior item 3, which are optional.

I added an IF to check for the value of current item 2, prior item 2, current item 3, prior item 3, if there are values, then variable tables are created and filled with data, then are retrieved. As it is, my stored procedure returns 3 sets of data when current item 1, prior item 1, current item 2, prior item 2, current item 3, prior item 3 are passed to it, and only one if 2, and 3 are omitted.I would like to learn how can I return this as a one data set, either using a full outer join, or a union all?I am including a copy of my stored procedure as it is.

View 6 Replies View Related

TSQL - Use ORDER BY Statement Without Insertin The Field Name Into The SELECT Statement

Oct 29, 2007

Hi guys,
I have the query below (running okay):



Code Block
SELECT DISTINCT Field01 AS 'Field01', Field02 AS 'Field02'
FROM myTables
WHERE Conditions are true
ORDER BY Field01

The results are just as I need:


Field01 Field02

------------- ----------------------

192473 8461760

192474 22810



Because other reasons. I need to modify that query to:



Code Block
SELECT DISTINCT Field01 AS 'Field01', Field02 AS 'Field02'
INTO AuxiliaryTable
FROM myTables
WHERE Conditions are true
ORDER BY Field01
SELECT DISTINCT [Field02] FROM AuxTable
The the results are:

Field02

----------------------

22810
8461760

And what I need is (without showing any other field):

Field02

----------------------

8461760
22810


Is there any good suggestion?
Thanks in advance for any help,
Aldo.

View 3 Replies View Related

Using Conditional Statement In Stored Prcodure To Build Select Statement

Jul 20, 2005

hiI need to write a stored procedure that takes input parameters,andaccording to these parameters the retrieved fields in a selectstatement are chosen.what i need to know is how to make the fields of the select statementconditional,taking in consideration that it is more than one fieldaddedfor exampleSQLStmt="select"if param1 thenSQLStmt=SQLStmt+ field1end ifif param2 thenSQLStmt=SQLStmt+ field2end if

View 2 Replies View Related

UNION In A WHILE Statement??

Jun 3, 2008

 Hello,Im wondering if someone can offer some advice, Im trying to get a union in the statement below to it will return all the data as rows in one table..... DECLARE @counter INT
SET @counter = 0

WHILE @counter < 24
BEGIN

SELECT "Hour"=(SELECT @counter),
"HourAverage"=(SELECT COUNT(*) FROM webstats WHERE (hourstamp = @counter) AND (datestamp < getdate())) / (@numberofdays),
"HourToday"=(SELECT COUNT(*) FROM webstats WHERE (hourstamp = @counter) AND datestamp > DATEADD(d,-1,GETDATE()) AND datestamp < DATEADD(d,1,GETDATE()))

SET @counter = @counter + 1

ENDIf i write each statment out with the values as apose to @counter and union under each i get the result i am looking for... SELECT "Hour"=(SELECT 0),
"HourAverage"=(SELECT COUNT(*) FROM webstats WHERE (hourstamp = 0) AND (datestamp < getdate())) / (@numberofdays),
"HourToday"=(SELECT COUNT(*) FROM webstats WHERE (hourstamp = 0) AND datestamp > DATEADD(d,-1,GETDATE()) AND datestamp < DATEADD(d,1,GETDATE()))
UNION
SELECT "Hour"=(SELECT 1),
"HourAverage"=(SELECT COUNT(*) FROM webstats WHERE (hourstamp = 1) AND (datestamp < getdate())) / (@numberofdays),
"HourToday"=(SELECT COUNT(*) FROM webstats WHERE (hourstamp = 1) AND datestamp > DATEADD(d,-1,GETDATE()) AND datestamp < DATEADD(d,1,GETDATE()))
UNION

etc. etc. Does doing a while loop have any performace advantages to my other example? Thanks Bart   

View 3 Replies View Related

I Need Help With This Tsql Statement

Apr 2, 2007

Every time I try this statement I keep getting a syntext error near count  I must be over looking something can some one help me with this. 
 
SELECT 'Quarter 1' as 'qtr'       count(jobid) as 'transcount',       count(distinct job.patientid) as 'patientcount',       sum(job.LANGUAGE_TCOST) as 'lcost',       Sum(job.LANGUAGE_DISC_COST) as 'dlcost',       avg(LANGUAGE_DISC) as 'avgLDisc',       (sum(job.LANGUAGE_TCOST) + sum(job.LANGUAGE_DISC_COST)) as 'LGrossAmtBilled',       (sum(LANGUAGE_TCOST) / count(distinct job.patientid)) as 'PatAvgL',       (sum(LANGUAGE_TCOST) / count(jobid)) as 'RefAvgL',       sum(LANGUAGE_DISC) as 'avgPercentDiscL',       JOB.JURISDICTION,       PAYER.PAY_COMPANY,       PAYER.PAY_CITY,       PAYER.PAY_STATE,       PAYER.PAY_SALES_STAFF_ID,       JOB.INVOICE_DATE       INVOICE_AR.INVOICE_DATE AS EXPR1,       INVOICE_AR.AMOUNT_DUE      
FROM JOB        INNER JOIN INVOICE_AR                ON JOB.JOBID = INVOICE_AR.JOBID       LEFT OUTER JOIN PAYER                ON PAYER.PAYERID = JOB.PAYER.ID       LEFT OUTER JOIN STATES                ON JOB.JURISDICTION = STATES.INITIALS
WHERE      (INVOICE_AR.AMOUNT_DUE > 0)AND       (INVOICE-AR.INVOICE_DATE BETWEEN @startdate and @enddate)AND         (MONTH(INVOICE_AR.INVOICE_DATE) IN (1,2,3))AND         (PAYER.PAYCOMPANY like '%' + @Company + '%')                Group By        JOB.JURISDICTION        PAYER.PAY_COMPANY        PAYER.PAY_CITY        PAYER.PAY_STATE        PAYER.PAY_SALES_STAFF_ID,        JOB.INVOICE_DATE,        INVOICE_AR.INVOICE_DATE,        INVOICE_AR.AMOUNT_DUE
UNION ALL
SELECT 'Quarter 2' as 'qtr'       count(jobid) as 'transcount',       count(distinct job.patientid) as 'patientcount',       sum(job.LANGUAGE_TCOST) as 'lcost',       Sum(job.LANGUAGE_DISC_COST) as 'dlcost',       avg(LANGUAGE_DISC) as 'avgLDisc',       (sum(job.LANGUAGE_TCOST) + sum(job.LANGUAGE_DISC_COST)) as 'LGrossAmtBilled',       (sum(LANGUAGE_TCOST) / count(distinct job.patientid)) as 'PatAvgL',       (sum(LANGUAGE_TCOST) / count(jobid)) as 'RefAvgL',       sum(LANGUAGE_DISC) as 'avgPercentDiscL',       JOB.JURISDICTION,       PAYER.PAY_COMPANY,       PAYER.PAY_CITY,       PAYER.PAY_STATE,       PAYER.PAY_SALES_STAFF_ID,       JOB.INVOICE_DATE       INVOICE_AR.INVOICE_DATE AS EXPR1,       INVOICE_AR.AMOUNT_DUE      
FROM JOB        INNER JOIN INVOICE_AR                ON JOB.JOBID = INVOICE_AR.JOBID       LEFT OUTER JOIN PAYER                ON PAYER.PAYERID = JOB.PAYER.ID       LEFT OUTER JOIN STATES                ON JOB.JURISDICTION = STATES.INITIALS
WHERE      (INVOICE_AR.AMOUNT_DUE > 0)AND       (INVOICE-AR.INVOICE_DATE BETWEEN @startdate and @enddate)AND         (MONTH(INVOICE_AR.INVOICE_DATE) IN (4,5,6))AND         (PAYER.PAYCOMPANY like '%' + @Company + '%')                Group By        JOB.JURISDICTION        PAYER.PAY_COMPANY        PAYER.PAY_CITY        PAYER.PAY_STATE        PAYER.PAY_SALES_STAFF_ID,        JOB.INVOICE_DATE,        INVOICE_AR.INVOICE_DATE,        INVOICE_AR.AMOUNT_DUE
UNION ALL
SELECT 'Quarter 3' as 'qtr'       count(jobid) as 'transcount',       count(distinct job.patientid) as 'patientcount',       sum(job.LANGUAGE_TCOST) as 'lcost',       Sum(job.LANGUAGE_DISC_COST) as 'dlcost',       avg(LANGUAGE_DISC) as 'avgLDisc',       (sum(job.LANGUAGE_TCOST) + sum(job.LANGUAGE_DISC_COST)) as 'LGrossAmtBilled',       (sum(LANGUAGE_TCOST) / count(distinct job.patientid)) as 'PatAvgL',       (sum(LANGUAGE_TCOST) / count(jobid)) as 'RefAvgL',       sum(LANGUAGE_DISC) as 'avgPercentDiscL',       JOB.JURISDICTION,       PAYER.PAY_COMPANY,       PAYER.PAY_CITY,       PAYER.PAY_STATE,       PAYER.PAY_SALES_STAFF_ID,       JOB.INVOICE_DATE       INVOICE_AR.INVOICE_DATE AS EXPR1,       INVOICE_AR.AMOUNT_DUE      
FROM JOB        INNER JOIN INVOICE_AR                ON JOB.JOBID = INVOICE_AR.JOBID       LEFT OUTER JOIN PAYER                ON PAYER.PAYERID = JOB.PAYER.ID       LEFT OUTER JOIN STATES                ON JOB.JURISDICTION = STATES.INITIALS
WHERE      (INVOICE_AR.AMOUNT_DUE > 0)AND       (INVOICE-AR.INVOICE_DATE BETWEEN @startdate and @enddate)AND         (MONTH(INVOICE_AR.INVOICE_DATE) IN (7,8,9))AND         (PAYER.PAYCOMPANY like '%' + @Company + '%')                Group By        JOB.JURISDICTION        PAYER.PAY_COMPANY        PAYER.PAY_CITY        PAYER.PAY_STATE        PAYER.PAY_SALES_STAFF_ID,        JOB.INVOICE_DATE,        INVOICE_AR.INVOICE_DATE,        INVOICE_AR.AMOUNT_DUE
UNION ALL
SELECT 'Quarter 4' as 'qtr'       count(jobid) as 'transcount',       count(distinct job.patientid) as 'patientcount',       sum(job.LANGUAGE_TCOST) as 'lcost',       Sum(job.LANGUAGE_DISC_COST) as 'dlcost',       avg(LANGUAGE_DISC) as 'avgLDisc',       (sum(job.LANGUAGE_TCOST) + sum(job.LANGUAGE_DISC_COST)) as 'LGrossAmtBilled',       (sum(LANGUAGE_TCOST) / count(distinct job.patientid)) as 'PatAvgL',       (sum(LANGUAGE_TCOST) / count(jobid)) as 'RefAvgL',       sum(LANGUAGE_DISC) as 'avgPercentDiscL',       JOB.JURISDICTION,       PAYER.PAY_COMPANY,       PAYER.PAY_CITY,       PAYER.PAY_STATE,       PAYER.PAY_SALES_STAFF_ID,       JOB.INVOICE_DATE       INVOICE_AR.INVOICE_DATE AS EXPR1,       INVOICE_AR.AMOUNT_DUE      
FROM JOB        INNER JOIN INVOICE_AR                ON JOB.JOBID = INVOICE_AR.JOBID       LEFT OUTER JOIN PAYER                ON PAYER.PAYERID = JOB.PAYER.ID       LEFT OUTER JOIN STATES                ON JOB.JURISDICTION = STATES.INITIALS
WHERE      (INVOICE_AR.AMOUNT_DUE > 0)AND       (INVOICE-AR.INVOICE_DATE BETWEEN @startdate and @enddate)AND         (MONTH(INVOICE_AR.INVOICE_DATE) IN (10,11,12))AND         (PAYER.PAYCOMPANY like '%' + @Company + '%')                Group By        JOB.JURISDICTION        PAYER.PAY_cOMPANY        PAYER.PAY_CITY        PAYER.PAY_STATE        PAYER.PAY_SALES_STAFF_ID,        JOB.INVOICE_DATE,        INVOICE_AR.INVOICE_DATE,        INVOICE_AR.AMOUNT_DUE         Order By 'QTR' asc

View 4 Replies View Related

TSQL - WITH Statement

Sep 5, 2007

Hi guys,
I need help with this one...
Iam Trying to understand how to use the statement WITH
I am running the code below, but getting error.

note: I have SQL SERVER 2005 in my PC, but retrieving data from the SQL SERVER 2000 (in the server)


Thanks in advance,
Aldo.




Code Snippet
WITH MyCTE (FILTER, SORTGROUP)
AS
(
SELECT ACCOUNTS.FILTER, ACCOUNTS.SORTGROUP FROM ACCOUNTS
)
SELECT * FROM MyCTE AS CTE_01;

Error Messages:
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'WITH'.

View 6 Replies View Related

Conditional Where Statement

Jul 23, 2007

I have a stored procedure that performs a search function with params:@username nvarchar(50)@country nvarchar(50)and like 10 more.A user may provide values for these params optionally.So when the @username var is left blank, there should be no filtering on the username field (every field should be selected regardless of the username)Currently my statement is:select username,country from myUsers whereusername=@username and country=@countryWith this statement when a user provides no value for username the username field selects on ''m which returns ofcourse nothing...What can I do to solve this?Thanks!

View 6 Replies View Related

SQL Conditional WHERE Statement

Feb 4, 2008

Hi, is it possible to do a conditional WHERE in T-SQL? I have a table with a column that consists of a reference that starts with either a single alpha character or two alpha characters followed by four numeric digits (the numeric portion is always unique but the alpha isn’t). E.g. A1234, AB1235, AB1236, C1237, HT1238. What I want to do is select a range of rows based on the numeric portion of this reference column. In other words I want to select say 50 rows starting from row 1000 (rows 1000 to 1050) regardless of whether there is one or two alpha characters preceding the numerics.The Stored procedure I have so far works (using COUNT for testing) for selecting a range of rows that has two alpha's at the start. However, if I simply add an OR to the WHERE to select rows where there is a single alpha in the reference column, when a single alpha reference is found it will fail the first logical check for two alpha's giving an error condition. Therefore, how can I incorporate a conditional WHERE using IF or some alternative method, so that it will also give me all the rows in the number sequence that start with either single or double alpha's within the same SELECT / WHERE statement?

Thanks for any help.ALTER PROCEDURE [dbo].[sp_Test]

(
@startRef int,
@endRef int
)

AS

BEGIN

SELECT Count(*) FROM myTable
WHERE ((SUBSTRING(Ref,3,LEN(Ref)-2) BETWEEN @startRef AND (@startRef + @endRef)))

END
RETURN
 

View 2 Replies View Related

Conditional WHERE Statement?

Jul 23, 2005

Hi all,I have one for all the blackbelters out there: is there a way i canmake a stored procedure where i can control the where statement withvariables? I have to do some complex transformations to get compose afact table for MSAS and there a a lot of similarities between thequeries and a few differences because of different account methodsetc. (booking in starting date, booking stuff on order entry datesetc) I want to put a combination of different rules in differentmembers of dimensions.An example of what i mean:CREATE STORED PROCEDURE dbo.FILLFACT (@PAR1, @PAR2)ASINSERT INTO FactTable (blah blah)SELECTIF @PAR1 = 'OrderDate'SourceView.OrderdateELSESourceView.StartDate,etc etc...FROMSourceViewWHEREIF @PAR2 = 'WholeTable'1=1IF @PAR2 = 'Incomplete'EndDate IS NULL OR EXIST (SELECT * FROM Exceptions WHERE...., etc)This way i could fill my fact table withEXEC dbo.FillFact 'beginDate','Wholetable'EXEC dbo.FillFact 'begindate', 'Rulebook1'EXEC dbo.FillFact 'BeginDate', 'Exceptions'etcetera.This is not an actual SQL script i use, just an example of what i'mtalking about. Or maybe i could pass the where statement entirley as avariable? But i can't use SET @PAR1 = 'EndDate IS NULL' and then useWHERE @PAR1 can I?I hope i'm making sense. Does anyone know if this is possible? Rightnow i have a procedure that is composed of a dozen of sql scripts thatare mostly the same, but i have to copy it for every combination ofsituations and then, of course, new stuff has to be added on 12different places. Again and again.Any thoughts?TIA,Gert-Jan van der Kamp

View 2 Replies View Related

Conditional Statement! HELP!

Nov 22, 2007

Hi, I've been looling around, but was unable to correctly use a conditional statement in a T-SQL Query.
I have this SQL query and in it how I would do if it was C#. If someone could please help me get
what I need I would appreciate it a lot. Here is the query:




Code Block

SELECT Ficha, Almoxarifado, [Código do Item], Descrição, Unidades.Unidade,
[1ª Contagem], [2ª Contagem], Recontagem, Observações, Cancelar FROM Fichas
INNER JOIN Itens ON [Código do Item] = Código
INNER JOIN Unidades ON Itens.Unidade = Unidades.ID
WHERE Ficha BETWEEN 01 AND 5000 AND Recontagem IS NULL AND

/* What I would do in C#, but need in T-SQL*/
if ([1ª Contagem] > [2ª Contagem])
{

if ([2ª Contagem] / [1ª Contagem] < 0.99)

return true;
else
{

if ([1ª Contagem] / [2ª Contagem] < 0.99)

return true;
}






I really need a help in this. Does anyone know how to accomplish this?
Thanks.
Regars,
Fábio

View 3 Replies View Related

HELP With A Select/Union Statement

Mar 31, 2006

I have 3 tables One table is the order Table, Bill to table and ship to table
I have to Views created as followed

This query uses the Ship to table to pull the ship to information to the shipping system.
SELECT Cust_address.NAME, Cust_address.ADDR_1, Cust_address.ADDR_2, Cust_address.ADDR_3, Cust_address.CITY, Cust_address.STATE, Cust_address.ZIPCODE, Cust_address.COUNTRY, Cust_address.SHIP_VIA, customer_order.ID
FROM Cust_address INNER JOIN customer_order ON (Cust_address.CUSTOMER_ID = customer_order.CUSTOMER_ID) AND (Cust_address.ADDR_NO = customer_order.SHIP_TO_ADDR_NO);


This query uss the Bill to as the ship to inforamtion
SELECT CUSTOMER.ID, CUSTOMER.SHIPTO_ID, CUSTOMER.NAME, CUSTOMER.ADDR_1, CUSTOMER.ADDR_2, CUSTOMER.ADDR_3, CUSTOMER.CITY, CUSTOMER.STATE, CUSTOMER.ZIPCODE, CUSTOMER.COUNTRY, CUSTOMER.SHIP_VIA, customer_order.ID, customer_order.SHIP_TO_ADDR_NO
FROM CUSTOMER INNER JOIN customer_order ON CUSTOMER.ID = customer_order.CUSTOMER_ID;


I need this infroamtion in one table which I have done in the UNION statement as followed:
SELECT Cust_address.NAME, Cust_address.ADDR_1, Cust_address.ADDR_2, Cust_address.ADDR_3, Cust_address.CITY, Cust_address.STATE, Cust_address.ZIPCODE, Cust_address.COUNTRY, Cust_address.SHIP_VIA, customer_order.ID
FROM Cust_address INNER JOIN customer_order ON (Cust_address.CUSTOMER_ID = customer_order.CUSTOMER_ID) AND (Cust_address.ADDR_NO = customer_order.SHIP_TO_ADDR_NO)
UNION ALL
SELECT CUSTOMER.NAME, CUSTOMER.ADDR_1, CUSTOMER.ADDR_2, CUSTOMER.ADDR_3, CUSTOMER.CITY, CUSTOMER.STATE, CUSTOMER.ZIPCODE, CUSTOMER.COUNTRY, CUSTOMER.SHIP_VIA, customer_order.ID
FROM CUSTOMER INNER JOIN customer_order ON CUSTOMER.ID=customer_order.CUSTOMER_ID;

Here is the problem when I pull information out of the ship to table I get 2 results as followed My key field to pull this information is the Last field Custoemr ID this custoemr ID exist in both tables but contains different information I want to ONLY pull in the info that I need in this case it would be the first line that is the Correct shipping information.

NAMEADDR_1ADDR_2ADDR_3CITYSTATEZIPCODECOUNTRYSHIP_VIAID
DIEBOLD INC (4076A)ATTN: RANCE AARON343 MANOR DRPACIFICACA9404418932
DIEBOLD, INCOHUPS #88X08X18932

MY POINT: Is there a way to select a over all DISTINCT order ID.

Thank you for any help hope this make sense!

View 4 Replies View Related

Union And Order Statement

Jun 8, 2007

I have the following query and for some reason when I try to run it, it tells me there is an incorrect syntax near the 'union' statement. Can you tell me what is wrong with this?

SELECT MDN,
DATEPART(d,CallDate) as CallDate,
DATEPART(hh,CallDate) as Hour,
sum(ceiling((Cast(DurationSeconds as Decimal)/60))) as Minutes
FROM VoiceCallDetailRecord
WHERE DurationSeconds > 0 and CallDate >= '02/19/2007' and calldate < '03/19/2007' and COS = 3
AND (((CONVERT(varchar, CallDate, 108) Between '21:00:00' AND '23:59:59') OR (CONVERT(varchar, CallDate, 108) Between '00:00:00' AND '07:00:00'))
OR DATEPART(weekday, CallDate) in (1,7)) and NOT (Left(Endpoint,3) IN ('011')
or (Left(Endpoint,4) IN ('1340','1876','1868','1809',
'1246','1242','1780','1403',
'1250','1604','1807','1519',
'1204','1506','1709','1867',
'1902','1705','1613','1416',
'1905','1902','1514','1450',
'1418','1819','1306','1867')))
order BY DATEPART(d,CallDate), DATEPART(hh,CallDate), MDN
UNION
SELECT MDN,
DATEPART(d,CallDate) as CallDate,
DATEPART(hh,CallDate) as Hour,
sum(ceiling((Cast(DurationSeconds as Decimal)/60))) as Minutes
FROM ZeroChargeVCDRecord
WHERE DurationSeconds > 0 and CallDate >= '02/19/2007' and calldate < '03/19/2007' and COS = 3
AND (((CONVERT(varchar, CallDate, 108) Between '21:00:00' AND '23:59:59') OR (CONVERT(varchar, CallDate, 108) Between '00:00:00' AND '07:00:00'))
OR DATEPART(weekday, CallDate) in (1,7)) and NOT (Left(Endpoint,3) IN ('011')
or (Left(Endpoint,4) IN ('1340','1876','1868','1809',
'1246','1242','1780','1403',
'1250','1604','1807','1519',
'1204','1506','1709','1867',
'1902','1705','1613','1416',
'1905','1902','1514','1450',
'1418','1819','1306','1867')))
order BY DATEPART(d,CallDate), DATEPART(hh,CallDate), MDN

View 5 Replies View Related

UNION ALL SELECT Statement Help

Aug 9, 2007

I am not completly sure if I have this posted in the right forum so if I don't just let me know and I will move it. Here is my problem. I need to be able to use the WHERE keyword more than once in one SELECT statement and have not been able to figure this out. I need to be able to first search for information under one column with the WHERE keyword like usual and then I need to be able to search the returned results with another WHERE keyword to narrow down the returned results. I tried writing two SELECT statements and joining them with a UNION ALL keyword like this:

"SELECT LI.ID, LI.CNID, CD.ID, LI.FDName, LI.FDR " & _
"FROM FinalDrive AS LI INNER JOIN CarData AS CD " & _
"ON LI.CNID = CD.ID WHERE LI.CNID = '1'" & _
"UNION ALL" & _
"SELECT LI.ID, LI.CNID, CD.ID, LI.FDName, LI.FDR " & _
"FROM FinalDrive AS LI INNER JOIN CarData AS CD " & _
"ON LI.CNID = CD.ID WHERE LI.FDName = 'Car1"


This hasn't worked and I didn't expect it to. Everytime I run this code I get an Unhandled SqlExecption:

Invalid column name 'Towing'.

Can anyone help me with figuring out how to use the WHERE keyword more than once. I am using Visual Basic.Net with ADO.Net. Thanks!

View 5 Replies View Related

Parameters With A Union Statement

Jul 3, 2007

I have a report that is using a union statement to pull in data from two identical tables except that one is for current month, the other for archived data.



What I want to do is prompt the user once for a date and use the value to select from the right table. Since a sales date can only exist in one of the tables, one union will work, the other not.



But the report in prompting me for a parameter for each query....which is in Informix and the prompt is this: "?"



Is there anyway to force both halves of the query to see this as one parameter so the user is only prompted once?



Thanks

View 2 Replies View Related

TSQL From An Access SQL Statement

Feb 21, 2001

Good morning one and all,

I have some queries that were written in access that I need to port into SQL 7, the whole process is boring and mundane. Does any1 know of a translator (i.e. access sql to t-sql) or a reference to the differences between access SQL and t-Sql.

Any and all help appreciated,

Thanx Gurmi

View 1 Replies View Related

How Can I Use Variables In This TSQL Statement

Mar 4, 2008

Hi all,I would like to replace the default directory location (c: emp) and thefilename (emails.csv) with variables like @FileDir and @FileName in thestatement below.SELECT @cnt = COUNT(*) FROM OpenRowset('MSDASQL', 'Driver={Microsoft TextDriver (*.txt; *.csv)}; DefaultDir=c: emp;','select * from "emails.csv"')However, my attempts have not been successful.Any ideas appreciated, and TIA.Greg

View 2 Replies View Related

Tsql Case Statement

Sep 12, 2007

Hi,
Here is the scenario. I want to add last year sale dollars in accordance with current period in exsiting fact table.
And below is the syntax.

Syntax:
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
select
a. store_key,
a.fisc_date_key,
sum(a.net_sale_Dollars) as sale_TY ,
sum ( b.net_sale_dollars ) as sale_LY ,
a.division_name,
a.department_number

fromFact 1 as a

,Fact 1 as b

Whereb.fisc_date_key = (a.fisc_date_key -364)
and a.division_name=b.division_name
and a.department_number =b.department_number
and a.store_key = b.store_key

group by
a.division_name,
a.department_number,
a.fisc_date_key,
a.store_key

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------


The current table from this query is showing like:

store_key date_key sale_TY sales_LY div dept
------------------------------------------------------------------------------------------------
1 1 30 20 ABC 1
2 1 20 20 ABC 3

But, if we assume that in the current date, dept = 2 has a sale amount, and in parallel year if dept=2 does not have any sale then this information was excluded.

The structure of table that I want to create must look like:


store_key date_key sale_TY sales_LY div dept
------------------------------------------------------------------------------------------------
1 1 30 20 ABC 1
2 1 20 20 ABC 3
2 1 15 0 ABC 2

>>>> want to put 0 value where only one side ( current or parrallel period) has sales info.

So, I'm thinking the case statement like:


Case statement logic like:
------------------------------------------------------------
if a. dept not exist in b.dept
then Sale TY -> a.net_sale_dollars
Sale LY -> 0

if b.dept not exist in a.dept
then sale TY -> 0
sale LY -> b. net_sale_dollars
-------------------------------------------------------------

below is the syntax which doesn't work (it's wrong):

Syntax:
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

select
a. store_key,
a.fisc_date_key,
sum(case when a.department_number = b.department_number then a.net_sale_dollars
else case when a.department_number NOT IN (b.department_number)then a.net_sale_dollars else null end)
as sale_TY ,
sum ( case when b.department_number =a.department_number then b.net_sale_dollars

else case when a.department_number NOT IN (b.department_number) as sale_LY,
a.division_name,
a.department_number

fromFact 1 as a

Fact 1 as b

Whereb.fisc_date_key = (a.fisc_date_key -364)
and a.division_name=b.division_name
and a.department_number =b.department_number
and a.store_key = b.store_key

group by
a.division_name,
a.department_number,
a.fisc_date_key,
a.store_key

,
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------


Is it possible to create these kind of structure?
Please give me some comments.
Thanks.

View 1 Replies View Related

What Is Wrong With This TSQL Statement

Mar 3, 2008



hello,

I have wriiten this query to select all records from those tables in a database that have "to_be_transffered" column where this "to_be_transferred_column" is not null


--code


EXEC sp_MSForEachTable 'IF EXISTS(SELECT c.table_name, c.column_name

FROM information_schema.columns c

INNER JOIN information_schema.tables t

ON c.table_name = t.table_name

WHERE c.table_name = SUBSTRING(''?'', (CHARINDEX(''['',''?'', 2) + 1),

((LEN(''?'') - (CHARINDEX(''['',''?'', 2) + 1))))

AND c.column_name = ''to_be_transferred''

AND t.table_type = ''BASE TABLE''

)

BEGIN



SELECT * FROM ?

WHERE to_be_transferred IS NOT NULL

END'



But I am getting the following error


Msg 207, Level 16, State 3, Line 12

Invalid column name 'to_be_transferred'

I thought the if statement filters all those tables with the specified column and do select statement fot those tables only

what did I do wrong, any suggestionm?

Thanks

View 6 Replies View Related

Using The IF EXISTS Conditional Statement

Apr 5, 2007

I'm trying to gather some user statistics based on 3 conditions. First I want to check if the referring querystring is already in the database. If not insert it into the db. Second, if the querystring is already in the database, then check if the ip-address of the user is already in the database. If it is, then check if the ip address was inserted today. If not, update the "refCountIn" field with +1. The problem lies in the third condition where we check if the ip-address was inserted today and if false, update the "refCountIn" field with +1 and if true, do nothing.Below is the code I have until now:  1 Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
2
3 ' *** Declare the variables
4 Dim getStatCmd As String
5 Dim updStatCmd As String
6
7 Dim myRef As String = Request.QueryString("ref")
8 Dim myQueryString As String = Request.ServerVariables("QUERY_STRING").Replace("ref=", "")
9 Dim myRemoteAddr As String = Request.ServerVariables("REMOTE_ADDR")
10 Dim myHttpReferer As String = Request.ServerVariables("HTTP_REFERER")
11
12 Dim dtNow As Date = DateTime.Now
13 Dim dtToday As Date = DateTime.Today
14
15 ' *** Conditional INSERT command
16 getStatCmd = _
17 "IF EXISTS(SELECT 'True' FROM tblReferers WHERE robotName = '" & myQueryString & "' AND refIpAddress = '" & myRemoteAddr & "' AND refTime = '" & dtToday & "') " & _
18 "BEGIN " & _
19 "SELECT 'This clickin has already been recorded!'" & _
20 "END ELSE BEGIN " & _
21 "SELECT 'Clickin recorded' " & _
22 "INSERT INTO tblReferers(robotName, refIpAddress, refReferer, refTime) " & _
23 "VALUES(" & _
24 "'" + myQueryString + "'," & _
25 "'" + myRemoteAddr + "'," & _
26 "'" + myHttpReferer + "'," & _
27 "'" + dtToday + "')" & _
28 "END "
29
30
31 ' *** Conditional UPDATE command
32 updStatCmd = _
33 "IF EXISTS(SELECT 'True' FROM tblReferers WHERE robotName = '" & myQueryString & "' AND refIpAddress = '" & myRemoteAddr & "' AND refTime <> '" & dtToday & "') " & _
34 "UPDATE tblReferers " & _
35 "SET refCountIn = refCountIn + 1, refTime = '" & dtNow & "' " & _
36 "WHERE refIpAddress = '" & myRemoteAddr & "' AND robotName = '" & myRef & "'"
37
38 Dim insConnCmd As New SqlCommand(getStatCmd, New SqlConnection(connSD))
39 Dim updConnCmd As New SqlCommand(updStatCmd, New SqlConnection(connSD))
40
41 insConnCmd.Connection.Open()
42 insConnCmd.ExecuteNonQuery()
43 insConnCmd.Connection.Close()
44
45 updConnCmd.Connection.Open()
46 updConnCmd.ExecuteNonQuery()
47 updConnCmd.Connection.Close()
48
49 End Sub
Anyone with an idea on how to solve this one? I think I need to write a subquery for the third condition, but I don't have a clue on how to handle this.Thanks in advance for your help! 
 

View 2 Replies View Related

SQL Help: Conditional Statement Using Inserted

Oct 29, 2007

I'm learning SQL and here I'm trying to use two things that I'm not familiar with - IF statements and the Inserted temporary table.
Here's the background - skip this paragraph if you like. I'm working on a tasking system for the Help Desk - they get requests from the web site for various items and I break up the request into Software, Hardware, Accounts, etc tables and list the status of each item as "Requested". I'm also keeping a Tasks table to make work orders for each item requested. I've got triggers on the Accounts and Hardware tables that automatically make a new task for those items but the Software is more tricky because all software for a given request should be just one task. Software installs are all done by one person at the same time.
So I'm trying to make a trigger that creates a new Task when a new Software record is inserted. But if a task already exists with the same RequestID (meaning they requested two peices of software and this is the second one), then I just want to update the task already created. Here's what I got:
 1 CREATE TRIGGER [NewSoftwareTask]
2 ON [dbo].[Software]
3 AFTER INSERT
4 AS
5 BEGIN
6
7 -- If a software task already exists for this request
8 -- then update it. Otherwise create a new task.
9
10 if exists(select TasksID
11 from Tasks
12 where Tasks.RequestsID = inserted.RequestsID and
13 TasksType = 'Software')
14 BEGIN
15 UPDATE [BGHelpdesk].[dbo].[Tasks]
16 SET [TasksDescription] = [TasksDescription] + vbcrlf + "Install " + inserted.SoftwareType + ". " + inserted.SoftwareComments
17 WHERE Tasks.RequestsID = inserted.RequestsID and
18 TasksType = 'Software'
19 END
20
21 else
22
23 BEGIN
24 INSERT INTO [BGHelpdesk].[dbo].[Tasks]
25 ([RequestsID]
26 ,[TasksType]
27 ,[TasksSubType]
28 ,[TasksTitle]
29 ,[TasksDescription])
30 SELECT
31 s.RequestsID
32 ,'Software'
33 ,s.SoftwareType
34 ,'New ' + s.SoftwareType + ' Account for Request ' + cast(s.RequestsID AS varchar)
35 ,s.SoftwareComments
36 FROM Software s join
37 inserted ON s.SoftwareID = inserted.SoftwareID
38 END
39 END
40 GO

It keeps balking at lines 12 and 17 saying "The multi-part identifier "inserted.RequestsID" could not be bound." The ELSE statement is what I use on the other tables and it works fine so the inserted temp record seems pretty straightforward but I must be doing something wrong...

View 4 Replies View Related

Using Conditional Statement In Where Clause

Mar 26, 2012

I'm trying to use a conditional statement in the where clause.

Here is my table
UID Amount ID PID Amount2
1 30000 8064 NULL NULL
2 30000 8042 8064 30000

What I'm trying to achieve:

If Amount = Amount2 for UID 2 then show UID 1

View 4 Replies View Related

Conditional Select Statement

Mar 23, 2004

Hello dbForumers,

Yet another puzzling question. I remember I saw somewhere a particular syntax to select a column based on a conditional predicate w/o using a user defined function. What I want to accomplish is this : SELECT (if column colA is empty then colB else colA) as colC from SomeTable. Possible ? Not possible? Have I hallucinated ?

Thank You!

View 6 Replies View Related

Conditional Sum With Select Statement

Feb 24, 2014

Table with water consumption per month and customer.

I want to sum up total consumption per customer with a select statement

View 4 Replies View Related

Adding Conditional Statement To A Sum

Jun 12, 2007

currently I am creating a total if the date is between two entered dates:

select sum(case when exigo_data_sync.orders.orderdate between @prevMonthStart and @prevMonthEnd then 1 else 0 end) as PrevMonthCount,

I need to check an additional column for ($1.00) or $1.00. If it contains $1.00 then proceed as normal and add to the total. If it contains ($1.00) then subtract one from the total. Any advice?

View 7 Replies View Related

IIf ANY Value In Table Conditional Statement

Oct 12, 2007

This is probably quite simple, but it isn't sticking out at me.

In a simple table with two groups, I want to conditionally set the color of the first group to red or green based upon whether ANY value for this field equals a particular string.

I know how to conditionally set the color of the field. However, the below code appears to only compare the FIRST value in Fields!myField.Value to the string "bad". I want it to return "Red" if ANY of the values for Fields!myField.Value = "bad".




Code Block=IIf(Fields!myField.Value = "bad", "Red", "Green")


myField is ouput in the detail scope if that makes any difference.


Is this possible?

View 3 Replies View Related

If Conditional Statement Replacement

Oct 2, 2007



Hi,
Is there a work around for the following query on sql server compact edition


IF(Some Condition) --

BEGIN


INSERT INTO @TEMP

SELECT 1, XYZ_ID FROM My_Table

END


Thanks,
Dp

View 3 Replies View Related

Writing Union Statement With 2 Order By's

Jun 20, 2008

Hi,

I'm trying to write a UNION statement with 2 selects and can't figure out for the life of me how to do it.


select top 10 * from tblusers ORDER BY dateJoined
UNION
select top 10 * from tblusers ORDER BY lastLogin

The union works fine without the order by's, but I really need the order by's in there and I don't understand why its so difficult to do. Surely there must be some other strategy?


Any help is much appreciated.. thanks!!
mike123

How can I reproduce this results ?

View 7 Replies View Related

Limit And Order In Union Statement

Mar 18, 2008

hi all,

i have the following union statement, which works like a charm:SELECT [Name], [EventID] AS [ItemID], [TourID], [Date], NULL AS [StartDate], [Date] AS [SortDate], [Type] FROM [Events] WHERE [TourID] IS NULL AND AccessLevel <= @AuthenticationLevel UNION SELECT [Name], [TourID] AS [ItemID], [TourID], NULL AS [Date], [StartDate], [StartDate] AS [SortDate], '2' AS [Type] FROM [Tours]WHERE AccessLevel <= @AuthenticationLevel ORDER BY [SortDate]

now i want to ad a limit to this statement, but i can't get it working. i have tried this:SELECT * FROM (SELECT [Name], [EventID] AS [ItemID], [TourID], [Date], NULL AS [StartDate], [Date] AS [SortDate], [Type] FROM [Events] WHERE [TourID] IS NULL AND AccessLevel <= @AuthenticationLevel UNION SELECT [Name], [TourID] AS [ItemID], [TourID], NULL AS [Date], [StartDate], [StartDate] AS [SortDate], '2' AS [Type] FROM [Tours]WHERE AccessLevel <= @AuthenticationLevel) ORDER BY [SortDate] LIMIT 2
but i keep getting an error message saying "Incorrect syntax near the keyword "ORDER". what am i doing wrong?

-bm

View 6 Replies View Related

UNION Statement Doesn't Work

Jul 20, 2005

Hi,I have a database stored in MS SQL 2000 and an application written inVB5, which connects the database via JET/ODBC.I have a problem with the UNION statement.When I run a simple query like:"SELECT field1 FROM table1 UNION SELECT field2 FROM table2"I get the following error:"Runtime error 3078 - The Microsoft Jet database engine cannot find theinput table or query 'select field1 from table1'. Make sure it existsand that its name is spelled correctly."I can run the queries separately "SELECT field1 FROM table1" and "SELECTfield1 FROM table2", so that I'm sure table and field names are correctand I have permission to access them.Both field1 and field2 are the same type (int).If I run the query in MS SQL Query Analyzer, it works fine.It doesn't work only when I run it from VB/JET/ODBC.Has anyone already had this kind of problem?Any help will be highly appreciated!Thank you so much for the attention.--Posted via http://dbforums.com

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved